How to use a datareader against an oracle stored procedure

[Guest]

Hi!
I have create an application with a lot of call to SQL Server setored
procedures. Now when i add support for Oracle 9i choose the Microsoft
OracleClient Provider for .NET.
My stored procedure returns a REF_CURSOR like the sample Q309361 at
http://support.microsoft.com/kb/309361/EN-US/. The problem is that i got the
following error when calling the procedure from C# code. "ORA-65550:row 1,
column 7: PLS-00103:.swedesh text..." .
Im shure that the statement in the call "{call
curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}" is for the
OleDb Provider. How should the syntax be for the OracleClient?
// Thanks

 

[Guest]

Hi Stefan,

Basically all you have to do is create a parameter for the cursor and add it
to your command object. Then when you call ExecuteReader you will be able to
access the results that were filled in that cursor. I have posted some
sample code for you and also posted the MSDN documentation to the use of the
OracleClient.

By looking at your posting I may not be answering exactly what you are
looking for. If I did not answer your question or if I have misunderstood
you please repost with some sample code (and data) so I may help you better.


I hope this helps.
-----------------------

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/msdnorsps.asp


using(OracleConnection conn = new OracleConnection(@"Data
Source=SIDGOESHERE;User Id=username;Password=password;"))
{

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "StoredProcNameGoesHere";
cmd.Parameters.Add("rsResults",OracleType.Cursor).Direction =
ParameterDirection.Output;
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
Console.WriteLine(dr[0].ToString());
}
conn.Close();

}

 

[Paul Clement]

¤ Hi!
¤ I have create an application with a lot of call to SQL Server setored
¤ procedures. Now when i add support for Oracle 9i choose the Microsoft
¤ OracleClient Provider for .NET.
¤ My stored procedure returns a REF_CURSOR like the sample Q309361 at
¤ http://support.microsoft.com/kb/309361/EN-US/. The problem is that i got the
¤ following error when calling the procedure from C# code. "ORA-65550:row 1,
¤ column 7: PLS-00103:.swedesh text..." .
¤ Im shure that the statement in the call "{call
¤ curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}" is for the
¤ OleDb Provider. How should the syntax be for the OracleClient?
¤ // Thanks

See the following article:

How To Return an Oracle Ref Cursor to a .NET DataReader Object by Using the .NET Managed Provider
for Oracle
http://support.microsoft.com/default.aspx?scid=kb;en-us;322160


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)