Last post Aug 13, 2010 11:49 AM by rogeriobg
Jan 15, 2007 05:38 AM|Ashutosh Bhardwaj|LINK
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.
Jan 18, 2007 02:00 AM|ramana123|LINK
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.
Jan 23, 2007 08:48 AM|Ashutosh Bhardwaj|LINK
what type of cursors do I need to write, ref cursor?
Jan 23, 2007 11:05 PM|ramana123|LINK
Yes exatly you have to use ref cursors.
see the below link
Jan 25, 2007 12:22 PM|AjPtl|LINK
CREATE OR REPLACE
PACKAGE "PKG_EMP" AS
TYPE EMP_CURSOR IS REF CURSOR;
PROCEDURE SP_GET_EMP( P_RETURNCUR OUT IMS_CURSOR);
PROCEDURE SP_GET_EMP( P_RETURNCUR OUT IMS_CURSOR)
OPEN E_RETURNCUR FOR
SELECT * FROM EMP;
P_RETURNCUR := E_RETURNCUR;
just use "PKG_EMP.SP_GET_EMP" instead of ur sql query in C# code
Feb 25, 2007 07:54 PM|asiaenforcer|LINK
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?
Feb 25, 2007 10:49 PM|ramana123|LINK
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();
DataTable dt = ds.Tables.
//Tables will contains the data from the database which your stored porcedure refcursor returns.
one more thing you no need to pass the equalent parameter from c# to Oracle procedure for refcursor as defined in abouve post.
Jul 09, 2007 09:51 AM|Ashutosh Bhardwaj|LINK
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.
Using REF cusror with ADO.net
Jul 10, 2007 12:05 AM|ramana123|LINK
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:
or else no need to close after your procedure execution curors and local varibles will vanish i bilieve.
I hope its bit clarified you.
May 22, 2008 07:16 AM|keyser|LINK
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:
And the body of the proc. -this is a very simple example- will look like this:
Allright. Then the code in my ASP.NET page (in VB.NET) is the next one:
As String =
"User Id=USER;Password=PASS;Data Source=DS;"
oracmd.Connection = oraconn
oracmd.CommandType = CommandType.StoredProcedure
'oracmd.Parameters.Add("o_Cursor", OracleType.Cursor, ParameterDirection.Output) <---------------------------- PEOPLE FROM FORUMS.ASP.NET!!!!! LOOK AT THIS!!!!
GridView1.DataSource = ds.Tables(
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!!!!
Jun 05, 2008 06:00 AM|Ahmish|LINK
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
Jul 17, 2008 02:58 AMfirstname.lastname@example.org|LINK
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.
Full C# Code
Initialise connection and command.
cmd.Connection = cn;
Jul 20, 2009 04:12 PM|OmarMuslim|LINK
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;
Jul 02, 2010 09:27 AM|vjikrishna|LINK
Jul 02, 2010 09:30 AM|vjikrishna|LINK
Thank You very much.....It is really of great help....
Aug 13, 2010 11:49 AM|rogeriobg|LINK
The solution for me was http://msdn.microsoft.com/en-us/library/ms971506.aspx
in section Result Sets and REF CURSORS