| Controlling PL/SQL execution from UNIX script (merged 3) [message #347860] |
Sun, 14 September 2008 13:00  |
Nau
Messages: 24
Registered: October 2004
|
Junior Member |
|
|
I want to control the execution of a PL/SQL procedure from a UNIX shell script.
Below, I include the script.
The control variable which should recive the return of the procedure, dosen't work well.
I want to control the return, because I wanr to make a UNIX script to control the execution of
a load data process with some Oracle procedures.
---
#!/bin/ksh
echo "Executing procedure pl/sql"
SQLPLUS="sqlplus -s /"
ESQUEMA="esquema1"
echo "\
call ${ESQUEMA}.Z_PROC_PRUEBA();" | $SQLPLUS
echo "Controlling pl/sql execution"
var_err=$?
if [ $var_err -gt 0 ]
then
echo "Error executing pl/sql"
else
echo "pl/sql finished sucessfully"
fi
|
|
|
|
|
|
|
|
| Re: Controlling a procedure execution from a UNIX shell script [message #347952 is a reply to message #347875] |
Mon, 15 September 2008 03:09  |
Nau
Messages: 24
Registered: October 2004
|
Junior Member |
|
|
Thanks,
I have found one possible solution. I have included in the code executed by sqlplus the next sentence:
whenever SQLERROR exit 1
It works!!
---
#!/bin/ksh
echo "Executing procedure pl/sql"
SQLPLUS="sqlplus -s /"
ESQUEMA="esquema1"
echo "
whenever SQLERROR exit 1
call ${ESQUEMA}.Z_PROC_PRUEBA();" | $SQLPLUS
echo "Controlling pl/sql execution"
var_err=$?
if [ $var_err -gt 0 ]
then
echo "Error executing pl/sql"
else
echo "pl/sql finished sucessfully"
fi
|
|
|
|
| Redirecting pl/sql output to a log file from UNIX script [message #351655 is a reply to message #347860] |
Thu, 02 October 2008 02:30 |
Nau
Messages: 24
Registered: October 2004
|
Junior Member |
|
|
I want to redirect the output of a PL/SQL procedure to a log file. In this file I want to have all the output of the execution, the results of every step, errors, etc..
I have tried with a UNIX pipe ( >> sql_log.txt) and with spool sentences, but it dosen’t work.
Can anybody help me??
Attached the UNIX script which call the procedure.
Any advice will be greatly appreciated.
---
#!/bin/ksh
echo "Executing procedure pl/sql"
SQLPLUS="sqlplus -s /"
ESQUEMA="esquema1"
echo "\
call ${ESQUEMA}.Z_PROC_PRUEBA();" | $SQLPLUS
echo "Controlling pl/sql execution"
var_err=$?
if $var_err -gt 0
then
echo "Error executing pl/sql"
else
echo "pl/sql finished sucessfully"
fi
|
|
|
|
|
|
| Controlling PL/SQL execution from UNIX script [message #351663 is a reply to message #347860] |
Thu, 02 October 2008 04:02 |
Nau
Messages: 24
Registered: October 2004
|
Junior Member |
|
|
I’m using the attached script to control the result of the execution of a PL/SQL procedure from a UNIX script. It works well, but I have a little problem. If I include and ‘exception’ clause in the procedure to control errors, in this case the UNIX variable dosen’t recibe the error result, it recibes a 0. I include the exception in the PL/SQL in order to close cursors after an sqlerror. Is there anyway to return a 1 in the PL/SQL in the exception handler??. I want to control errors in the PL/SQL procedure and in the UNIX script, is tha possible??
Any advice will be greatly appreciated.
echo "Se ejecuta el procedimiento pl/sql"
#- El usuario de AIX debe tener permisos de ejec sobre el PL/SQL SQLPLUS="sqlplus -s /"
ESQUEMA="esquema1"
echo "
set serveroutput on
whenever SQLERROR exit 1
call ${ESQUEMA}.Z_PROC_PRUEB() ; " | $SQLPLUS > sqlout.txt
var_err=$?
echo "Resultado" $var_err
if [ $var_err -gt 0 ]
then
echo "Error al ejecutar el pl/sql"
else
echo "pl/sql ejecutado correctamente"
fi
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
