CODEWITHSUNDEEP
c#plsql
Mohammed Ali Kachbouri
Mohammed Ali Kachbouri

Reputation: 43

get result of an anonymous PL/SQL

I'm trying to execute anonymous PL/SQL in C#. This is my code:

var requete = " DECLARE \n";
requete = requete + "type cursor_out is ref cursor; \n";
requete = requete + " cur cursor_out; \n";
requete = requete + "person_row XXX%rowtype; \n";
requete = requete + "procedure Select_Proc(result out cursor_out) is \n";
requete = requete + " begin \n";
requete = requete + "open result for select * from XXX \n";
requete = requete + "end; \n";
requete = requete + "begin \n";
requete = requete + "Select_Proc(cur); \n";
requete = requete + "loop \n";
requete = requete + "fetch cur into person_row; \n";
requete = requete + "exit when cur%notfound; \n";

requete = requete + "end loop; \n";
requete = requete + "close cur; \n";
requete = requete + "end;";

var oc = new OracleConnection(connectionString);
oc.Open();
var cmd = new OracleCommand
{
    Connection = oc,
    CommandText = requete,
    CommandType = CommandType.StoredProcedure

};
OracleDataReader odr = cmd.ExecuteReader();

When I run the some PLSQL script in SqlPlus it runs (I added "/"), but in C# I got some error like this one:

PLS-00103: Encountered the symbol ";" when expecting one of the following:

Upvotes: 1

Views: 209

Answers (1)

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

Remove the ; after the final end in your PL/SQL code.

The ; (and the /) are used by SQL*PLUS to seperate commands. When using ODBC you send in one command at a time, so the ; isn't needed, and even causes an exception.

Upvotes: 2

Related Questions