Fetching multiple recordset using Oracledatareader - c#

Question

The sql variable may contain more than sql statement ... i am getting error (Invalid character) while executing the command cmd.ExecuteReader() ....

Psuedo:

....
string sql = @"SELECT * FROM table1 WHERE col1=:p1;" +
"SELECT MAX (col3) FROM table2  WHERE col2 = :p2 "
cmd = DBConnection.GetCommand(); // Get the connection
cmd.CommandText = sql;
cmd.Parameters.Clear();
cmd.Parameters.Add(":p1", "Somevalue1");
cmd.Parameters.Add(":p2", "somevalue2");
OracleDataReader reader = cmd.ExecuteReader(); //Error: Invalid Character

if (reader.HasRows)
{
 reader.Read();
........
}

reader.NextResult();

.....

Answer 1

AFAIK Oracle does not allow the execution of multiple SQL statements in this fashion. You can sometimes get around it by wrapping the SQL statements in a PL/SQL anonymous block, e.g. "BEGIN SELECT ...; SELECT...; END;", but then you need to use an INTO clause on the SELECTs with bind variables as the targets in order to access the query results in the calling code.