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
get The Return Value of stored procedure
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
Hello,
Please ckeck this code out
Regards,
Alexey.
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
Alexey.