C# Calling PACKAGE.PROCEDURE with OUT parameter

Jsue Ngron

From C# Code, I'm trying to call a PACKAGE.PROCEDURE() from Oracle. In this simple example I should get one value from the procedure call, but all I get is error:

> wrong number or types of arguments in call to 'RETURN_NUM'

The procedure is declared as follows:

PROCEDURE return_num(xNum OUT NUMBER) AS
BEGIN
xNum:= 50;
dbms_output.put_line('hello world ' || xNum);
END;

C# code:

Oraclecon.Open();
OleDbCommand myCMD = new OleDbCommand("TEST.return_num", Oraclecon);
myCMD.CommandType = CommandType.StoredProcedure;
myCMD.Parameters.Add("xNum", OleDbType.Numeric);
OleDbDataReader myReader;
myReader = myCMD.ExecuteReader();

Can some one please point out what I'm doing wrong. Then in a real scenario I would like to call a procedure that returns a set of values from a custom Type, such as:

TYPE r_interface_data IS RECORD
(
object_id VARCHAR2(16),
obj_type VARCHAR2(32)
);
TYPE t_interfase_data IS TABLE OF r_interface_data;

How can I approach that. Thanks!

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jsue Ngron9y ago
I see. Thanks Nitin Sontakke. Anyway I manage to get my code working. i had to return a cursor from the procedure and instead of using DataReader, I had to use a DataSet. Thanks all for your help!
Nitin Sontakke9y ago
As stated in my post earlier (which seem to have just been ignored) Oracle DOES NOT support this behaviour.
Chetan Ranpariya9y ago
Where are you not receiving data? In out paramter or in the reader?

To get the data from the stored procedure you need to select some data in it. Your stored procedure does not have any select statement that's why it is not giving any data.
Jsue Ngron9y ago
Thanks for the help! I did as suggested and modified my Procedure to:
PROCEDURE return_num(xNUM OUT NUMERIC) IS
  BEGIN
    SELECT 123456
    INTO  xNUM
    FROM dual;
  END;
And my c# code as:
myCMD.Parameters.Add("xNum", OleDbType.Numeric).Direction = ParameterDirection.Output;

        OleDbDataReader myReader;
        myReader = myCMD.ExecuteReader();

        while (myReader.Read())
        {
            System.Console.WriteLine("Values = " + myCMD.Parameters["xNum"].Value);
            System.Console.ReadKey();
        }
But still no value. Doing some troubleshooting I notice that my code by-passes the while statement; any suggestions?
Nitin Sontakke9y ago
To the best of my knowledge:

There is no "as is" implementation in Oracle which is in SQL Server, whereby your last select statement returns a result set to the client. There might be workarounds available which I am not aware of.

However, in above case, you can try using .ExecuteNonQuery and try accessing the value of parameter after that statement as Chetan suggested. After all, there is only one scalar value and not a result set.

Your idea of creating a data-table in C# then passing it as StructuredData type output parameter to Oracle and populating it in Oracle procedure is clever and something that I haven't really tried, so cannot really comment on that.
Chetan Ranpariya9y ago
As Jsue mentioned, you need to change the direction of the parameter.

To access the parameter value after the execution you need to do myCMD.Parameters ["xNum"].Value.

Now the stored procedure is not returning any data coz there is no SELECT statement in the procedure.
Jsue Ngron9y ago
UPDATE:
So if I specify the parameter's direction to OUT (view code below), then my code compiles; but I've receive no data...
myCMD.Parameters.Add("xNum", OleDbType.Numeric).Direction = ParameterDirection.Output;
Any ideas?