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.

On Tue, 16 Mar 2004 16:06:13 +0200, "Hakan Dolas" <(E-Mail Removed)> wrote:

¤ 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 Removed)
Microsoft MVP (Visual Basic)

"Paul Clement" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Tue, 16 Mar 2004 16:06:13 +0200, "Hakan Dolas" <(E-Mail Removed)>
wrote:
>
> ¤ 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.
>

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

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" <(E-Mail Removed)> wrote in message
news:ubv%(E-Mail Removed)...
> 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.

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" <davidbaxterbrowne no potted (E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> "Hakan Dolas" <(E-Mail Removed)> wrote in message
> news:ubv%(E-Mail Removed)...
> > 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.
>
> 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
>
>