Home » Infrastructure » Unix » Controlling PL/SQL execution from UNIX script (merged 3)
Controlling PL/SQL execution from UNIX script (merged 3) [message #347860] Sun, 14 September 2008 13:00 Go to next message
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 #347864 is a reply to message #347860] Sun, 14 September 2008 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
dosen't work well.

This is neither an Oracle nor an Unix error message.

Regards
Michel
Re: Controlling a procedure execution from a UNIX shell script [message #347875 is a reply to message #347860] Sun, 14 September 2008 14:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sqlplus will ALWAYS return 0.
You need a different mechanism to indicate an error; such as the existence of a file or not; or the contents of a file.
Re: Controlling a procedure execution from a UNIX shell script [message #347952 is a reply to message #347875] Mon, 15 September 2008 03:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Redirecting pl/sql output to a log file from UNIX script [message #351657 is a reply to message #351655] Thu, 02 October 2008 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
echo "Controlling pl/sql execution"
var_err=$?

Of course var_err contains the return code from echo.

Otherwise, what does "it dosen’t work" mean?

And please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel
Controlling PL/SQL execution from UNIX script [message #351663 is a reply to message #347860] Thu, 02 October 2008 04:02 Go to previous messageGo to next message
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
Re: Controlling PL/SQL execution from UNIX script [message #351664 is a reply to message #351663] Thu, 02 October 2008 04:18 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
yes, make sure your exception section contains a RAISE; in it.
Re: Controlling PL/SQL execution from UNIX script [message #351668 is a reply to message #351663] Thu, 02 October 2008 04:37 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question.

Regards
Michel
Previous Topic: .hpacucli command
Next Topic: remote 9i installation on solaris 9
Goto Forum:
  


Current Time: Thu Mar 28 08:51:10 CDT 2024