hello friend good morning
I have a problem, how can I take the value that the PROCEDURE returns to me in the variable "CodError", when the connection to the bbdd is closed I lose the value and I need it in the shell
#AIX
cat <<EOF | sqlplus -s ${ORA_LOGIN}/${ORA_PASSWORD} > $logftmp
set feedback off;
set serveroutput on size 1000000
set line 500;
DECLARE
coderror NUMBER;
desc_error varchar2(200);
fecha_ini VARCHAR2(10);
fecha_aux varchar2(10);
BEGIN
coderror :=0;
desc_error :=null;
RPY_OMSJSOSTKPTG(coderror,desc_error);
dbms_output.put_line('CodError:' || CodError);
dbms_output.put_line('Desc Error:' || desc_error);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
END;
/
exit;
EOF
echo "controlo error"
as a friend? I do not understand
See oracle docs about the WHENEVER SQLERROR clause
SQL*Plus Command Reference
When your code hits an error, your code invokes the whenever clause return the error value to the calling shell. You set the error number to what you need in the clause.
You did not mention what shell you are using but with most shells the the variable $? is the value of the return code from the previous process, you can also call wait to get a return code.
A bash example of wait would be:
$( sqlplus command ................ )
wait $!
status=$?
echo Job 2 exited with status "$status"
# now you exit from the main script with your error code if needed (like you ran this using cron):
exit $status
Note carefully. Remove the cat command because it will interfere with the return code you want. --
All of this is based on some assumptions, if you need more help please give us the specific shell and version of AIX and Oracle.
1 Like
what happens is that I have this call to the procedure, and this one returned me a code, that code when closing the connection to the bbdd I need to rescue it and use that code in the shell