OraFAQ Forum: SQL & PL/SQL » problems integrating sql in shell

Home » SQL & PL/SQL » SQL & PL/SQL » problems integrating sql in shell

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

problems integrating sql in shell [message #8050]

Wed, 23 July 2003 08:50

Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland

Senior Member

Hi,

I just try to integrate a sql-backup script into a shell script. By now the generation works but I got the source code also in my destination file.
If I try it from sqlplus it works.
My file looks like :
oracleSids=$(cat /var/opt/oracle/oratab | grep -v "#" | grep -v '^$' &#124grep -v "*
" &#124grep ${ORACLE_HOME} | awk -F: '{print $1}')
cd /usr/local/bin/backup
for i in $oracleSids
do
export ORACLE_SID=$i
sqlplus usr/passwd <<EOF

set serveroutput on
set feedback off
set echo off
set trimspool on
set line 500
set head off
set feed off

spool backup.sql
declare
.....<backupscript>
EOF
done
I imagine that it should be an OS problem (Solaris 9) but I can't figure out why the coplete sql-script is send to spoolfile. When I try only the sql-part from sqlplus I got only the result.
any hints welcome
Uwe

Report message to a moderator

Re: problems integrating sql in shell [message #8054 is a reply to message #8050]

Wed, 23 July 2003 15:23

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

in your script
[i]
declare
.....
[/i]
is placed after the spool command.
so it will also be placed in spooled text.
convert that pl/sql into an sql file and call the sqlplus in silent mode.
it should fix it.

----------------------------------------------------------------------

$ pwd
/home/oracle/scr/ora
$ cat myscript.sql
set feed off
/
set echo off
/
set termout off
/
spool dumm.lst
/
declare
a number;
begin
dbms_output.put_line('THIS IS A SAMPLE');
end;
/
spool off
/
exit

$ sqlplus -s dbadmin/password @myscript
$ cat dumm.lst
THIS IS A SAMPLE
$

Report message to a moderator

Re: problems integrating sql in shell [message #8061 is a reply to message #8054]

Thu, 24 July 2003 01:17

Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland

Senior Member

thanks mahesh - it works fine.

Report message to a moderator

Previous Topic:	Subquery is not allowed in "Group by" clause in Ora9.2
Next Topic:	Please help me immediately.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 18:53:24 CDT 2024