Home » Infrastructure » Unix » capture PL/SQL o/p in unix script  () 1 Vote
capture PL/SQL o/p in unix script [message #125181] Thu, 23 June 2005 13:04 Go to next message
Manoj G
Messages: 2
Registered: May 2002
Junior Member
Hi,
I 've a unix script which is as below.


sqlplus -s user/password << !!
@script.sql
!!
I've set serverout on at beginning of script.sql
& calls to dbms_outputs in script.sql .
I want to see the o/p of dbms_output when I run the
unix script.
How can I change the above to get the desired
result

Re: capture PL/SQL o/p in unix script [message #125190 is a reply to message #125181] Thu, 23 June 2005 14:02 Go to previous messageGo to next message
Davesh_Manocha
Messages: 6
Registered: May 2005
Location: US
Junior Member
Hi Manoj.

Set serveroutput on works only for sql prompt.It wont work when u r in Unix Shell.I ll suggest the following change :
Direct the output of the sql script in a new file OUTPUT_FILE.
(This ll create the OUTPUT_FILE in ur working directory.)

sqlplus -s user/password << !! >> OUTPUT_FILE
@script.sql
!!

By looking into this file u can see the output of ur sql script.But the output wont be formatted.Infact this file ll store everything that wud have been on sqlprompt had u ru the sqlscript on the SQL prompt.

So u have to customise ur command in a manner relevant to ur sql script.For example : if u hav used a SELECT command in sql script
then type cat OUTPUT_FILE | grep "|" on the unix shell.Likewise if u have updated any row & want to see if thats done then type cat OUTPUT_FILE | grep "updated" ...But again result wont be formatted.

Davesh Manocha.
Re: capture PL/SQL o/p in unix script [message #125398 is a reply to message #125190] Fri, 24 June 2005 16:48 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I've caught dbms_output without any trouble:
#!/bin/ksh
RETVAL=`sqlplus -s scott/pass@dev <<EOF
set serveroutput on
WHENEVER SQLERROR EXIT 1
declare
x number;
begin
x := 999;
dbms_output.put_line('the_result_is '||x);
end;
/
exit;
EOF`

echo $RETVAL
X=`echo $RETVAL | awk '{print $2}'`
echo $X

sqlplus -s scott/pass@dev <<EOF | read RETVAL
set serveroutput on
WHENEVER SQLERROR EXIT 1
declare
x number;
begin
x := 999;
dbms_output.put_line('the_result_is '||x);
end;
/
exit;
EOF

echo $RETVAL

X=`echo $RETVAL | awk '{print $2}'`
echo $X

Output as follows:
------------------
/home/scott >t.ksh
the_result_is 999 PL/SQL procedure successfully completed.
999
the_result_is 999
999

Re: capture PL/SQL o/p in unix script [message #131067 is a reply to message #125398] Thu, 04 August 2005 03:34 Go to previous messageGo to next message
pgmillas
Messages: 1
Registered: August 2005
Junior Member
Add the following to avoid the unwanted information from sql
execution

set echo off
--set termout off
set feedback off
set heading off
set pages 0
set pagesize 1000
set linesize 300
set verify off
Capture SQL and OS error in korn shell [message #360813 is a reply to message #125181] Mon, 24 November 2008 01:29 Go to previous messageGo to next message
Soori
Messages: 5
Registered: November 2008
Junior Member
Hi

I have a korn shell that calls a sql file.Sql file contains code to spool off some data from tables.

I want to capture the SQLERROR and OSERROR in shell script.

Any working worked on this scenario , please help

Many Thanks..
Re: capture PL/SQL o/p in unix script [message #360818 is a reply to message #125398] Mon, 24 November 2008 01:34 Go to previous messageGo to next message
Soori
Messages: 5
Registered: November 2008
Junior Member
Hi Andrew,

In your previous reply how do you capture the WHENEVER SQLERROR EXIT 1 in shell script.

how do you get the SQLERROR message and log it into log file in korn shell.
Re: capture PL/SQL o/p in unix script [message #360824 is a reply to message #360818] Mon, 24 November 2008 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
$?

Regards
Michel
Re: Capture SQL and OS error in korn shell [message #360829 is a reply to message #360813] Mon, 24 November 2008 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question.
See answer in the other one you posted.

Regards
Michel
Re: capture PL/SQL o/p in unix script [message #360833 is a reply to message #360824] Mon, 24 November 2008 01:58 Go to previous messageGo to next message
Soori
Messages: 5
Registered: November 2008
Junior Member
I am usign the same

here is the code that is used

sqlplus -s scott/pwd@$ORACLE_SID spool_off.sql ${spoolfile} >> ${logfile}
error_flag=$?

I am getting an O/S Error: No such file or directory
message on the terminal output but not able to get the message in the log file.

Is there any thing that i have to do to capture that message in the log file named ${logfile}.
Re: capture PL/SQL o/p in unix script [message #360835 is a reply to message #360833] Mon, 24 November 2008 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Script must be called with "@<script file name>" (without < and >):
sqlplus scott/tiger@base @script

Regards
Michel
Re: capture PL/SQL o/p in unix script [message #360841 is a reply to message #360833] Mon, 24 November 2008 02:16 Go to previous messageGo to next message
Soori
Messages: 5
Registered: November 2008
Junior Member
I am sorry that is a typign mistake
I am using
sqlplus -s scott/pwd@$ORACLE_SID @spool_off.sql ${spoolfile} >> ${logfile}
error_flag=$?

I am getting an error
O/S Error: No such file or directory .

The log file ${logfile} is not having the message
O/S Error: No such file or directory .

I need to log this message also in the log file.
Re: capture PL/SQL o/p in unix script [message #360855 is a reply to message #360841] Mon, 24 November 2008 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to also redirect standard error to log file and not just standard output: 2>>${logfile}

Regards
Michel
Re: capture PL/SQL o/p in unix script [message #361048 is a reply to message #360855] Mon, 24 November 2008 23:31 Go to previous message
Soori
Messages: 5
Registered: November 2008
Junior Member
Thank you very much it worked.
Previous Topic: passing unix vairable to a PL SQL block
Next Topic: Ftp from Unix box to Windows
Goto Forum:
  


Current Time: Thu Mar 28 05:35:50 CDT 2024