Oracle Shell script

[devendrap]

${ORACLE_HOME}/bin/sqlplus -s $USER_DW_ADMIN/$PWD_DW_ADMIN << EOF
set colsep ''
set pagesize 0
set heading off
set feedback off
set echo off
set arraysize 5000
set termout off
SET WRAP OFF
spool $DW_LST/temp11

while read plant TS_date junk
do
[ "$plant" = "Plant" ] && continue
@${DW_CTL}/Temp_check.sql
done < $DW_LST/temp_check.dat > $DW_LST/temp12

spool off
EOF

Above loop is not working, It is generating error for plant not found. Can you please help?

Thanks,
Dave

 

[LKBrwnDBA]

You cannot "EMBED" shell script command within SQL*Plus commands!



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Dave,

You can "shell out" from SQL*Plus, issuing an o/s command with the SQL*Plus "host" command.

If, at the SQL> prompt, you say "host<enter>", then a new window opens for you to issue manual o/s commands until you issue an "exit", which returns you to SQL*Plus control.

If, instead, you say "host <script_name>", then if the contents of <script_name> are valid o/s commands, then the o/s command processor executes the commands, then returns control to SQL*Plus.

Let us know if this is helpful.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

I think that for this specific case it's more suitable to call sql*plus from shell script within a loop.

Regards, Dima

http://seminihin.narod.ru

 

[LKBrwnDBA]

I think you are still missing something: the 'loop' executes the same script for each non "Plant" line? Maybe you are missing some parameter of some sort?
Perhaps the script should look similar to this:

# ....
while read plant TS_date junk
do
  [ "$plant" = "Plant" ] && continue
  echo "@${DW_CTL}/Temp_check.sql TS_date" 
done < $DW_LST/temp_check.dat >  $DW_LST/temp12.sql
${ORACLE_HOME}/bin/sqlplus -s $USER_DW_ADMIN/$PWD_DW_ADMIN << EOF
set colsep ''
set pagesize 0
set heading off
set feedback off
set echo off
set arraysize 5000
set termout off
SET WRAP OFF
spool $DW_LST/temp11
@$DW_LST/temp12.sql
spool off
EOF

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[devendrap]

I used following script, it is workig fine, I don't believe this is good way to do it. Please provide your suggestions:

while read plant TS_date junk
do
[ "$plant" = "Plant" ] && continue

${ORACLE_HOME}/bin/sqlplus -s $USER_DW_ADMIN/$PWD_DW_ADMIN << EOF
set colsep ''
set pagesize 0
set heading off
set feedback off
set echo off
set arraysize 5000
set termout off
SET WRAP OFF
set verify off
spool $DW_LST/$temp
@@${DW_CTL}/$extract.sql "$plant" "$TS_date"
spool off
EOF

cat $DW_LST/$temp.lst >> $DW_LST/main.lst

done < $DW_LST/$temp_list.dat

 

[KenCunningham]

If it works (and more particularly if it 'works fine'), don't fix it!

 

[LKBrwnDBA]

It is similar to what I suggested, except in mine sqlplus is executed only once:

while read plant TS_date junk
 do
  [ "$plant" = "Plant" ] && continue
  echo "@@${DW_CTL}/$extract.sql \"$plant\" \"$TS_date\" "
done < $DW_LST/$temp_list.dat >$DW_LST/$temp.sql

${ORACLE_HOME}/bin/sqlplus -s $USER_DW_ADMIN/$PWD_DW_ADMIN << EOF
set colsep ''
set pagesize 0
set heading off
set feedback off
set echo off
set arraysize 5000
set termout off
SET WRAP OFF
set verify off
spool $DW_LST/main
@@$DW_LST/$temp.sql
spool off
EOF

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[devendrap]

You are right. Thanks you.