OracleCommand.ExecuteScalar and PLSQL stored procedures

  • Thread starter Thread starter A.M-SG
  • Start date Start date
A

A.M-SG

Hi,



How can I use OracleCommand.ExecuteScalar method to have the value returned
from a PLSQL stored procedure?



I think there are some techniques with PLSQL code that I am missing.



Thank you,

Alan
 
Hi Alan,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get the returned value from
an Oracle SP. If there is any misunderstanding, please feel free to let me
know.

The ExecuteScalar and ExecuteOracleScalar executes the query, and returns
the first column of the first row in the result set returned by the query.
If the return data is an Oracle specific type, use ExecuteOracleScalar.
These two methods return an object type, you have to cast it to the
expected type.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
hi
refer the code
Dim sExecute As OracleDataReader = sConnCommand.ExecuteReader
sReturn = sConnCommand.Parameters("OracleVariable").Value
Return (sReturn)

here the OracleVariable is Stored Proc OUTPUT variable.
 
Hi Kevin,



Thank you for reply.



Please consider the following facts with respect to my post:



1) I am fine with sending direct queries to Oracle by using ADO.NET. My
problem is retrieving data from PLSQL stored procedures by using
OracleCommand.ExecuteScalar



2) Oracle's stored procedures return scalar and row-set results totally
different than SQL server. I think your answer is correct for SQL server,
but it doesn't work for Oracle.



I do know how to develop PLSQL stored procedures, but I don't know how to
setup a PLSQL stored procedure, so ADO.NET's OracleCommand.ExecuteScalar can
read the returned value.



To re-word the question, what are OracleCommand.ExecuteScalar expectations
from a PLSQL stored procedure to be able to have the scalar values?



I searched google and I couldn't find any PLSQL and ADO.NET interpretability
guidelines. If you know any, that would be great.



I hope the question makes sense now.



Thank you,

Alan
 
Thank you for reply,



But you used ExecuteReader. I am trying to use ExecuteScalar. It doesn't
work the way you expect (first row first column value)



I also tried to use OUTPUT parameters, the command object keeps throwing
exceptions...



I am looking for some PLSQL stored procedures and ADO.NET interoperability
guidelines. Do know any?



Alan
 
Hi Alan,

The ExecuteScalar method gets the first column value of the first row. So
the stored procedure can be any of a simple SELECT statement which returns
a result set. In the MSDN document, there is a simple example of counting
the number of regions like the following.

CommandText = "select count(*) from region";
Int32 count = (int32) ExecuteScalar();

Although PLSQL stored procedures is different from SQL server, but the
select statement is the same. Make sure the result set is NOT a REF CURSOR,
or a null reference will be returned by the ExecuteScalar.

Please check the following link for more information.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdataoracleclientoraclecommandclassexecutescalartopic.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Kevin,

What is the proper result set type?
Do you have any example that shows me the correct shape for PLSQL stored
procedures to have their result set back through ExcecuteScalar ?

Thank you,
Alan
 
¤ Hi Kevin,
¤
¤ >> Make sure the result set is NOT a REF CURSOR
¤
¤ What is the proper result set type?
¤ Do you have any example that shows me the correct shape for PLSQL stored
¤ procedures to have their result set back through ExcecuteScalar ?

AFAIK, ExecuteScalar and ExecuteOracleScalar cannot return a REF CURSOR. An Oracle REF CURSOR can
only be returned in an output parameter.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi Paul,



Do you have s simple PLSQL SP signature that shows how a PLSQL stored
procedure can be compatible with ADO.NET's OracleClient.ExecuteScalar ?



I am shocked that I couldn't find any on MSDN or Google!



Thank you for reply,

Alan
 
¤ Hi Paul,
¤
¤
¤
¤ Do you have s simple PLSQL SP signature that shows how a PLSQL stored
¤ procedure can be compatible with ADO.NET's OracleClient.ExecuteScalar ?
¤
¤
¤
¤ I am shocked that I couldn't find any on MSDN or Google!
¤

That's because it isn't possible. ;-)

An Oracle stored procedure returns a resultset as a REF CURSOR and the only way to return a REF
CURSOR to ADO.NET is through an OUT parameter. The ExecuteScalar method doesn't use the OUT
parameter so it is useless for Oracle stored procedures.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Think of the ExecuteScalar method as a simple shortcut to get the
first column of the first row in the resultset returned by the query. It's
designed to simplify scenarios where you're issuing a query like "SELECT
COUNT(*) FROM MyTable WHERE ...".

It sounds like you're looking to retrieve the value of an
output/return parameter for a stored procedure call. That data is returned
through a different structure - a parameter object. Your best bet is to
call ExecuteNonQuery and then check the Value property of the parameter
object.

I've included some C# sample code to create the stored procedure, as
well as to execute the stored procedure and check the return value.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.



OracleCommand cmd = cn.CreateCommand();

//Create procedure
cmd.CommandText = "CREATE FUNCTION GetProduct " +
" (pX IN NUMBER, pY IN NUMBER) " +
" RETURN NUMBER IS " +
" return_value NUMBER; " +
"BEGIN " +
" RETURN pX * pY; " +
"END;";
cmd.ExecuteNonQuery();

//Prepare Command
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetProduct";
cmd.Parameters.Add("pX", OracleType.Number).Value = 6;
cmd.Parameters.Add("pY", OracleType.Number).Value = 7;
cmd.Parameters.Add("return_value", OracleType.Number);
cmd.Parameters["return_value"].Direction = ParameterDirection.ReturnValue;

//Execute stored procedure, check return value
cmd.ExecuteNonQuery();
Console.WriteLine(cmd.Parameters["return_value"].Value);
 
Back
Top