get The Return Value of stored procedure

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
mehrdad
Posts: 1
Joined: Fri 27 Jun 2008 10:16

get The Return Value of stored procedure

Post by mehrdad » Fri 27 Jun 2008 10:42

Hi ,

I am trying to run a stored procedure and get the Return Value of the Procedure back (not a Out Parameter).my code seems like this :

oraclecommand.CommandType = System.Data.CommandType.StoredProcedure
oraclecommand.CommandText = "procedure_name"
oraclecommand.ParameterCheck = True
oraclecommand.Prepare()
oraclecommand.Parameters("in_pidstate").Value = myjob.Stapel.PIDSTATE_REAL
oraclecommand.Parameters("in_pidtyp").Value = myjob.Stapel.PIDTYP_STRUCT
oraclecommand.Parameters("in_logstate").Value = myjob.Stapel.LOGSTATE_STOCK_RECEIPT
dim ret as integer = OracleCommand.ExecuteNonQuery


It works fine but ,it does not matter if it is successful or not ,it returns every time -1 and it is not that what i actually need .
my Procedure :

FUNCTION STA_INSERT(
in_pidstate IN sta.pidstate%TYPE,
in_pidtyp IN sta.pidtyp%TYPE,
in_logstate IN sta.logstate%TYPE,
in_eqid IN sta.eqid%TYPE
) return number IS
ret_val NUMBER;
BEGIN

select sta_pid.nextval into ret_val from dual;
DBMS_OUTPUT.PUT_LINE('ret_val: '||TO_CHAR(ret_val));
INSERT INTO STA
(PID,
RECHNER_NR,
PIDSTATE,
PIDTYP,
LOGSTATE,
EQID,
LAENGE,
BREITE,
HOEHE,
GEWICHT,
VOLUMEN
)
values
(ret_val,
3,
in_pidstate,
in_pidtyp,
in_logstate,
in_eqid,
0,
0,
0,
0,
0);
commit;
RETURN ret_val;
EXCEPTION
WHEN OTHERS THEN
DECLARE
error_code NUMBER := SQLCODE;
error_msg VARCHAR2 (300) := SQLERRM;
error_info VARCHAR2 (30);
BEGIN
DBMS_OUTPUT.PUT_LINE('SQL error: '||error_msg);
RETURN -1;
END; -- End of anonymous block.
END STA_INSERT;

how can i get the real return value ?
i am looking forward for every effective reply.

thanks

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 27 Jun 2008 12:19

Hello,

Please ckeck this code out

Code: Select all

Dim oc As New OracleCommand("STA_INSERT", OracleConnection1)

        oc.CommandType = System.Data.CommandType.StoredProcedure
        Dim op As New OracleParameter("retval", OracleDbType.Integer)
        op.Direction = ParameterDirection.ReturnValue
        oc.Parameters.Add(op)
        oc.Parameters.Add(New OracleParameter("in_pidstate", 1))
        oc.Parameters.Add(New OracleParameter("in_pidtyp", 1))
        oc.Parameters.Add(New OracleParameter("in_logstate", 1))
        oc.Parameters.Add(New OracleParameter("eqid", 1))
        OracleConnection1.Open()
        Dim ret As Integer
        Try
            oc.ExecuteNonQuery()
            ret = oc.Parameters(0).Value

        Catch ex As Exception
        Finally
            OracleConnection1.Close()
        End Try
Regards,
Alexey.

Post Reply