OraFAQ Forum: Unix » calling sqlscript from shell script(2 Merged)

Home » Infrastructure » Unix » calling sqlscript from shell script(2 Merged) (oracle 9.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

calling sqlscript from shell script(2 Merged) [message #515041]

Thu, 07 July 2011 19:38

Chinniah
Messages: 2
Registered: July 2011
Location: USNorthEast

Junior Member

Hi,
I am calling an sqlscript from a shell script below. The sql script gets executed successfully but then the control does not come back to shell to execute the Test (RC='grep ...) function in the shell script.

Appreciate your help.

Here is the shell scipt:

#!/usr/bin/ksh
set -x

ENV_PATH=$(dirname $0)
echo ${ENV_PATH}

# Environnent PATH initialisation
. ${ENV_PATH}/../var/purge_bdl_tables

BIN_APPLI=${ENV_PATH}
CONF_APPLI=${ENV_PATH}/../var
SQL_APPLI=${ENV_PATH}/../sql
LOG_APPLI=${ENV_PATH}/../log

# Oracle environnent initialisation
export ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK

RC=0
export LOG_FICH=$LOG_APPLI/$LOG_NAME
export LOG_ERROR=$LOG_APPLI/$ERROR_LOG
export MAIL_LIST=$CONF_APPLI/bdl_purge_mail_list

sqlplus / @$SQL_APPLI/purge_bdl_tables.sql << EOF
exit
EOF

# Analyze any return code
RC=`grep "^ORA-" ${LOG_FICH}|cut -d":" -f2`
if test `grep -c "^ORA-" ${LOG_FICH}` != 0
then
echo "BDL tables purge error " >> $LOG_FICH
echo "Exit Status: $RC" >> $LOG_FICH
cat $LOG_FICH > $LOG_ERROR
cat $MAIL_LIST >> $LOG_ERROR
exit 1

else
echo "Purge Successful">>$LOG_FICH
echo "Exit Status: $RC">>$LOG_FICH
echo ${LOG_FICH}
echo $LOG_FICH
exit 0
fi

Here is the sqlscript:

spool $LOG_FICH
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
col run_as for a12
col run_in for a20
SELECT USER AS run_as, TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS run_date, GLOBAL_NAME AS run_in FROM GLOBAL_NAME
/

set serveroutput on

-- select * from dual ;

DECLARE
I_KEEP_DAYS NUMBER;
BEGIN
I_KEEP_DAYS := 10;

ELOADER.SA_PURGE_DBDL_TABLE(
I_KEEP_DAYS => I_KEEP_DAYS
);

dbms_output.put_line('dbdl purge completed') ;

ELOADER.SA_PURGE_BDL_TABLE(
I_KEEP_DAYS => I_KEEP_DAYS
);
dbms_output.put_line('bdl purge completed') ;
END;
/
spool off ;
exit ;

Here is the output and the place where it stops and does not come back to execute the test in the shell.

RUN_AS RUN_DATE RUN_IN
------------ ------------------- --------------------
OPS$OCADFT 2011-07-08 02:24:51 OCDEV4.WORLD

0 rows deleted
dbdl purge completed
0 rows deleted
bdl purge completed

PL/SQL procedure successfully completed.

Report message to a moderator

Re: calling sqlscript from shell script [message #515043 is a reply to message #515041]

Thu, 07 July 2011 20:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>sqlplus / @$SQL_APPLI/purge_bdl_tables.sql << EOF
>exit
>EOF
make sure that the line above "EOF" contains only 3 characters & does NOT contain a trailing space character

Report message to a moderator

Re: calling sqlscript from shell script(2 Merged) [message #515074 is a reply to message #515041]

Fri, 08 July 2011 01:09

Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Fri, 08 July 2011 01:09]

Report message to a moderator

Re: calling sqlscript from shell script [message #515204 is a reply to message #515043]

Fri, 08 July 2011 09:50

Chinniah
Messages: 2
Registered: July 2011
Location: USNorthEast

Junior Member

Thanks for the reply. Actually there was no problem with the script. After the execution of the script it was updating behind the scene audit table, which is not part of my script. Since the development system was very slow it took a longer time for the update to complete. Once the update got complete it went back to the shell script and executed the Test. Things are ok now.

Report message to a moderator

Previous Topic:	Error in script
Next Topic:	List jobs scheduled in cron between two specified date-times

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 28 20:33:12 CST 2023