sql embedded into unix shell script

[drain]

Hello, if i run this code in unix shell interactive mode:

# var initialization

SCHEDULE=`$ORACLE_HOME/bin/sqlplus $USR/$PWD@$ORACLE_SID <<EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT SCHEDULE FROM $TAB WHERE NOME_REPORT='$NOME_REPORT';
EOF`


then works fine, instead when i try same code inside shell script
(.sh) i obtain these error:


SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|
SYSOPER}] where <logon> ::= <username>[/<password>][@<connect_string>]

how i can fix the script?


Thanks,
Drain

[ora...@msn.com]

Where did you define USR and PWD? Since those variables are NOT set
you end up with /@$ORACLE_SID and thus the error message occurs.
You'll need to set USR and PWD BEFORE you try to execute this command
from within a script:


#!/bin/ksh

# var initialization
export USR=flapjack
export PWD=syrup
export NOME_REPORT=grahamcracker

if [ "$ORACLE_SID" = "" ]
then
echo "Enter ORACLE_SID: \n"
read x
export ORACLE_SID=$x
fi

SCHEDULE=`$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect $USR/$PWD@$ORACLE_SID

set pagesize 0 feedback off verify off heading off echo off
SELECT SCHEDULE FROM $TAB WHERE NOME_REPORT='$NOME_REPORT';
EOF

Notice how sqlplus is called in this modified example; it keeps the
username and password from being visible to other users (a ps -ef will
display such information and the username and password are no longer
secure).


David Fitzjarrell

[drain]

nothing to do, i already set all variables in

# var initialization

but the following code returns:
SP2-0042: unknown command "EOF" - rest of line ignored.


SCHEDULE=`$ORACLE_HOME/bin/sqlplus -s $USR/$PWD@$ORACLE_SID <<EOF

set pagesize 0 feedback off verify off heading off echo off
SELECT SCHEDULE FROM $TAB WHERE NOME_REPORT='$NOME_REPORT';
EOF`

[drain]

found the curse error...

last EOF must be write at the end of the line:

SCHEDULE=`$ORACLE_HOME/bin/sqlplus -s $USR/$PWD@$ORACLE_SID <<EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT SCHEDULE FROM $TAB WHERE NOME_REPORT='$NOME_REPORT'; EOF`

[drain]

was wrong.. that modify doesn't fix the problem...

[ora...@msn.com]

> > > > David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

I can't understand what your issue is, really, as I can get such
scripts to work without error:

#!/bin/ksh

# variables

export USR=****
export PWD=####
export TAB=emp
export ENAME="JAMES"

export ORACLE_SID=%%%%%%%%%

if [ "$ORACLE_HOME" = "" ]
then

echo "Enter ORACLE_HOME: \n"
read a

export ORACLE_HOME=$a

fi

SCHEDULE=`$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect $USR/$PWD@$ORACLE_SID
set pagesize 0 feedback off verify off heading off echo off

select * from $TAB where ename = '$ENAME';
EOF`

print $SCHEDULE

And the output is:

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 14 11:11:39 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved. SQL> Connected.
SQL> SQL> 7900 JAMES CLERK 7698 03-DEC-81 950 30 7967 JAMES CLERK 7698
17-JAN-82 950 40 8012 JAMES CLERK 7698 17-JAN-82 950 50 8027 JAMES
CLERK 7698 03-MAR-82 950 60 SQL> Disconnected from Oracle Database 10g
Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Data
Mining option

As you can verify the script works without error.

For your information a 'here document' (such as you have attempted to
write) requires the EOF be on a separate line after the end of the
submitted text (note my example above).


David Fitzjarrell

[drain]

was an indentation problem in the main script...the code is correct
and works
thanks