Oracle Stored Procedure Cursor and Return Value

  • Thread starter Thread starter Hakan Dolas
  • Start date Start date
H

Hakan Dolas

Hi,

I have a StoredProcedure that returns a ref cursor and a numeric value.
I want to populate a datatable with the returning cursor, use the numeric
value elsewhere.

What is the method for this ?

I know that handling multiple cursors are easy but one cursor and one
numeric value is a bit confusing..

Thanks.
 
¤ Hi,
¤
¤ I have a StoredProcedure that returns a ref cursor and a numeric value.
¤ I want to populate a datatable with the returning cursor, use the numeric
¤ value elsewhere.
¤
¤ What is the method for this ?
¤
¤ I know that handling multiple cursors are easy but one cursor and one
¤ numeric value is a bit confusing..

Haven't tried this but I believe the single value is returned via the output Parameter you have
defined for the Command object.

Also, if you are using a DataReader you must close it first, or, read to the end of the data stream
before the output parameter value is available.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul Clement said:
¤ Hi,
¤
¤ I have a StoredProcedure that returns a ref cursor and a numeric value.
¤ I want to populate a datatable with the returning cursor, use the numeric
¤ value elsewhere.
¤
¤ What is the method for this ?
¤
¤ I know that handling multiple cursors are easy but one cursor and one
¤ numeric value is a bit confusing..

Haven't tried this but I believe the single value is returned via the output Parameter you have
defined for the Command object.

Also, if you are using a DataReader you must close it first, or, read to the end of the data stream
before the output parameter value is available.
Click to expand...

I believe that this is just a limitation on SqlServer. In Oracle, at least
with ODP.NET, you can access any number of output parameters, ref cursors
and DataReaders in any order you want.

David
Click to expand...
 
I am using ODP.NET.

Here is my CommandText:

BEGIN PKGLOBAL.PSM_RUNNER(
pDESC => 'j2d2039',
pCRITERIA => '*',
pTYPE => -1,
List_Cursor => :cCursor,
rTOTALCOUNT => :rTotal);
END;

I am passing this text to CommandText property of OracleCommand which is
declared as "mOraCommand".
I am creating two OracleParameter classes.


Dim oraParamCursor As New OracleParameter("cCursor", OracleDbType.RefCursor)
oraParamCursor.Direction = ParameterDirection.Output

Dim oraParamCount As New OracleParameter("rTotal", OracleDbType.Int32)
oraParamCount.Direction = ParameterDirection.Output

mOraCommand.Parameters.Add(oraParamCursor)
mOraCommand.Parameters.Add(oraParamCount)


Now I want to fill a datatable with oraParamCursor, return the value of
oraParamCount.
Do I need to use OracleCommand.ExecuteNonQuery() or OracleDataAdapter.Fill()
or both ?

Because when I use either, following error is returned:
ORA-06550: line Y, column X:
PLS-00306: wrong number or types of arguments in call to 'PSM_RUNNER'
Any help would be great..

Thanks.
 
Hakan Dolas said:
I am using ODP.NET.

Here is my CommandText:

BEGIN PKGLOBAL.PSM_RUNNER(
pDESC => 'j2d2039',
pCRITERIA => '*',
pTYPE => -1,
List_Cursor => :cCursor,
rTOTALCOUNT => :rTotal);
END;

I am passing this text to CommandText property of OracleCommand which is
declared as "mOraCommand".
I am creating two OracleParameter classes.


Dim oraParamCursor As New OracleParameter("cCursor", OracleDbType.RefCursor)
oraParamCursor.Direction = ParameterDirection.Output

Dim oraParamCount As New OracleParameter("rTotal", OracleDbType.Int32)
oraParamCount.Direction = ParameterDirection.Output

mOraCommand.Parameters.Add(oraParamCursor)
mOraCommand.Parameters.Add(oraParamCount)


Now I want to fill a datatable with oraParamCursor, return the value of
oraParamCount.
Click to expand...

Good, you're right on.

Next run your command with ExecuteNonQuery, and use the output parameters.

mOraCommand.ExecuteNonQuery()

'Now just get an OracleRefCursor
Dim rc as OracleRefCursor =
DirectCast(oraParamCursor.Value,OracleRefCursor)
dim count as Integer = DirectCast(oraParamCount.Value,Integer)

'then fill a dataset or datatable from the OracleRefCursor
dim da as new OracleDataAdapter();
dim ds as new DataSet();

da.Fill(ds,rc);

David
 
Thank you David,
It works now.


David Browne said:
Good, you're right on.

Next run your command with ExecuteNonQuery, and use the output parameters.

mOraCommand.ExecuteNonQuery()

'Now just get an OracleRefCursor
Dim rc as OracleRefCursor =
DirectCast(oraParamCursor.Value,OracleRefCursor)
dim count as Integer = DirectCast(oraParamCount.Value,Integer)

'then fill a dataset or datatable from the OracleRefCursor
dim da as new OracleDataAdapter();
dim ds as new DataSet();

da.Fill(ds,rc);

David
Click to expand...
 
Back
Top