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.

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.

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

Yes exatly you have to use ref cursors.

see the below link

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

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)

  AS

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

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?

Hi

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.

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.

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

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)

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

Dim ds As DataSet = New DataSet()

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!!!!

 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 

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

PROCEDURE myFirstListProc (

Parameter1 IN VARCHAR2,

Parameter2 IN VARCHAR2,

end myListPack;

PROCEDURE myFirstListProc (

Parameter1 IN VARCHAR2,

Parameter2 IN VARCHAR2,

o_remCursor OUT o_Cursor)

END myListPack; 

Full C# Code

Initialise connection and command.

cmd.CommandType = CommandType.StoredProcedure;

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

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

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

Thanks.

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();