How to return result from Oracle stored procedure.

Article
2007-01-15

Question

_{Monday, January 15, 2007 5:38 AM}

Hi All,

I am basically a SQL Server developer but I have been asked to work on Oracle 8i appication. I would want to know

1.) how to write a stored procedure in oracle which gives resultset of the query ( select * from emp)

2.) call it in c# code

3.) use the results of the strored procedure from step1 to bind a datagrid.

All replies (15)

_{Tuesday, July 10, 2007 12:05 AM ✅Answered}

Hello..

If you want to reopen the cursor for some other purposes[assign some other select statements in the same procedure] in the same procedure then need to be closed explicitly and use it again like:

ref_cursor1.close

or else no need to close after your procedure execution curors and local varibles will vanish i bilieve.

I hope its bit clarified you.

Thanks

_{Thursday, January 18, 2007 2:00 AM}

Hi Ashutosh Bhardwaj,

You can do this by using cursors in Oracle.

Use cursors to select the results from the table and then return that cursor in stored procedure as a out parameter.

Use data set to catch the resulted output from the stored procedure and then bind with the data grid.

_{Tuesday, January 23, 2007 8:48 AM}

what type of cursors do I need to write, ref cursor?

_{Tuesday, January 23, 2007 11:05 PM}

Yes exatly you have to use ref cursors.

see the below link

http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php.

_{Thursday, January 25, 2007 12:22 PM}

CREATE OR REPLACE
PACKAGE "PKG_EMP" AS
TYPE EMP_CURSOR IS REF CURSOR;

PROCEDURE SP_GET_EMP( P_RETURNCUR OUT IMS_CURSOR);

Body part

PROCEDURE SP_GET_EMP( P_RETURNCUR OUT IMS_CURSOR)

E_RETURNCUR EMP_CURSOR;

BEGIN

OPEN E_RETURNCUR FOR

SELECT * FROM EMP;

P_RETURNCUR := E_RETURNCUR;

END SP_GET_EMP;

just use "PKG_EMP.SP_GET_EMP" instead of ur sql query in C# code

_{Sunday, February 25, 2007 7:54 PM}

Can you provide codes from ASP.NET 1.1 (VB.NET or C#) ?

How to extract the data from the stored procedures which has a data type of refcursor?

_{Sunday, February 25, 2007 10:49 PM}

You dont need bother about how to extract data from ref cursors.

Use data adapter and use fill method to fill the data set.

Let's take exp here go:

OracleDataAdapter da = new OracleDataAdapter(YourCammandObject,YourConnection)

//-: You have to provide stored procedure parameters to the command object before this.I think you may aware of this.

DataSet ds = new Dataset();

Da.Fill(ds);

DataTable dt = ds.Tables[0].

//Tables[0] will contains the data from the database which your stored porcedure refcursor returns.

Thats's it.

one more thing you no need to pass the equalent parameter from c# to Oracle procedure for refcursor as defined in abouve post.

_{Monday, July 9, 2007 9:51 AM}

Hi,

Thank you for your reply. I had been using this approach for quiet some time now but there is a question which is striking in my mind:

How is the ref cursor clsoed? Is it being handled automatically or do we have to write any code to close the cursor explicitly.

Please let me know your comments on this.

_{Thursday, May 22, 2008 7:16 AM}

Hi... I know this post is old, but maybe there's someone out there who can help me... I'm using stored procedures from Oracle for first time with asp.net and I have a problem related with this. Let's explain with some code. I have this package:

CREATE OR REPLACE PACKAGE myListPack

TYPE o_Cursor IS REF CURSOR;

PROCEDURE miFirstListProc (

Parameter1 IN VARCHAR2,

Parameter2 IN VARCHAR2,

o_remCursor OUT o_Cursor);

[...]

And the body of the proc. -this is a very simple example- will look like this:

CREATE OR REPLACE PACKAGE BODY myListPack AS

PROCEDURE myFirstListProc (

Parameter1 IN VARCHAR2,

Parameter2 IN VARCHAR2,

o_remCursor OUT o_Cursor)

BEGIN

OPEN o_remCursor FOR SELECT * FROM myListTable;

END myFirstListProc;

[...]

Allright. Then the code in my ASP.NET page (in VB.NET) is the next one:

Dim strConnectionString As String = "User Id=USER;Data Source=DS;"

Dim oraconn As OracleConnection = New OracleConnection(strConnectionString)

oraconn.Open()

Dim oracmd As OracleCommand = New OracleCommand()

oracmd.Connection = oraconn

oracmd.CommandType = CommandType.StoredProcedure

oracmd.Parameters.Add("param1", OracleType.LongVarChar, 8).Value = Request.Params.Get("param1").ToString()

oracmd.Parameters.Add("param2", OracleType.LongVarChar, 8).Value = Request.Params.Get("param2").ToString()

'oracmd.Parameters.Add("o_Cursor", OracleType.Cursor, ParameterDirection.Output) < PEOPLE FROM FORUMS.ASP.NET!!!!! LOOK AT THIS!!!!

oracmd.CommandText = "myListPack.myFirstListProc"

Dim da As OracleDataAdapter = New OracleDataAdapter(oracmd)

Dim ds As DataSet = New DataSet()

da.Fill(ds, "TABLENAME")

GridView1.DataSource = ds.Tables("TABLENAME")

Well, it's the same if I include the "o_Cursor" as an output parameter or I just comment that line as it is shown above, the error which I get when I launch the page is the same in both cases:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'MYFIRSTLISTPROC' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

So the question is... where's the problem? which is the error with the parameter? If I take out the output parameter (from asp.net code and the package&procedure, without returning anything), there's no error, so the problem is with this cursor. CAN ANY BODY HELP ME, PLEASE??? Thanks a lot!!!!

_{Thursday, June 5, 2008 6:00 AM}

I am also facing the same problem when i get result form Oracle Stored Procedure,

I was getting the error

"wrong number or types of arguments in call to even m not passing any parameters"

when i included package name with the name of stored procedure then i getting error

"component 'PROC_GETCUSTOMER' must be declared"

what is the problem

_{Thursday, July 17, 2008 2:58 AM}

Sorry buddy, Your SP is named miFirstListProc and myFirstListProc in two places change it to one you like. Also about he line you commented use parameter name (o_remCursor) and not parameter type (o_Cursor).

Its pretty simple and it works.

SQL CODE:

CREATE OR REPLACE PACKAGE myListPack

TYPE o_Cursor IS REF CURSOR;

PROCEDURE myFirstListProc (

Parameter1 IN VARCHAR2,

Parameter2 IN VARCHAR2,

o_remCursor OUT o_Cursor);

end myListPack;

CREATE OR REPLACE PACKAGE BODY myListPack AS

PROCEDURE myFirstListProc (

Parameter1 IN VARCHAR2,

Parameter2 IN VARCHAR2,

o_remCursor OUT o_Cursor)

BEGIN

OPEN o_remCursor FOR SELECT * FROM myListTable;END myFirstListProc;

END myListPack;

Full C# Code

Initialise connection and command.

OracleConnection cn = new OracleConnection("Data Source=server;User ID=sys;Unicode=True");OracleCommand cmd = new OracleCommand();

cn.Open();

cmd.Connection = cn;

cmd.CommandText = "myListPack.myFirstListProc";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new OracleParameter("Parameter1",OracleType.VarChar,50));

cmd.Parameters.Add(new OracleParameter("Parameter2",OracleType.VarChar,50));

cmd.Parameters[0].Value = "testPAram1"; cmd.Parameters[1].Value = "testPAram2";

//oracmd.Parameters.Add("o_Cursor", OracleType.Cursor, ParameterDirection.Output)

cmd.Parameters.Add("o_remCursor", OracleType.Cursor);

cmd.Parameters[2].Direction = ParameterDirection.Output;

DataSet ds = new DataSet(); OracleDataAdapter da = new OracleDataAdapter(cmd);

da.Fill(ds);

Thanks.

_{Monday, July 20, 2009 4:12 PM}

HI,   RESOLVED

You Can Solve Your Problem by calling Stored Procedures this way... "begin yourStoredProce(param);end;"

NOTE:  COMMAND TYPE IS TEXT NOT STORED pROCEDURE

OracleCommand cmd = new OracleCommand("begin U_50004REG_REPORTS.USRUNI114(2612);end;", this.conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

_{Friday, July 2, 2010 9:27 AM}

_{Friday, July 2, 2010 9:30 AM}

Thank You very much.....It is really of great help....

_{Friday, August 13, 2010 11:49 AM}

The solution for me was http://msdn.microsoft.com/en-us/library/ms971506.aspx

in section Result Sets and REF CURSORS

Perfect!

Share via

How to return result from Oracle stored procedure.

Question

All replies (15)

Additional resources