get The Return Value of stored procedure
Posted: 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
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