Shell Programming and Scripting

I need help . I am not getting anything back from my setup. I have defined an oracle package which I am calling from within my sql call, but is returning no rows. If I run my sql from sqlplus works find, does not work whe I try to run from UNIX shell script

My korn script test.ksh

#!bin/ksh
set -x
#
#export ORACLE_HOME=/unixsxxx/apps/oracle/dist/xxxx
export ORACLE_SID=xxxxxx
export ORAENV_ASK=NO
export ORA_USERNAME=xxxxxx
export ORA_PASSWORD=xxxxxxx
export logfileTMP=/xxxxxxx/home/help.log
export TNS_ADMIN=/usr/local/bin/oracle
export PATH=$PATH:$TNS_ADMIN
. oraenv
sqlplus-S ${ORA_USERNAME}\/${ORA_PASSWORD}@${ORACLE_SID} <<-! 2>/dev/null 1>>$logfileTMP
@/xxxxx/home/help.sql
exit
/
****************************************************

help.sql is explained below

set serveroutput on size 1000000
SET AUTO on;
SET LINESIZE 500;
SET NEWPAGE none;
SET SPACE 0;
SET TRIMSPOOL on;
SET PAGESIZE 0;
SET ECHO off;
SET FEEDBACK off;
spool /home/xxxx/marina.txt;
declare
type rcursor is ref cursor;
emptab rcursor;
emprec PS_CIBC_ENV_VARS%rowtype;
begin
help_package.proc1('XXXXXX',emptab);
loop
fetch emptab into emprec;
exit when emptab%notfound;
IF emprec.COL_WIDTH = 2 then
dbms_output.put_line('EXPORT ' || emprec.LABEL_ID || '= ' || emprec.STRING_TEXT || ';');
else
dbms_output.put_line('EXPORT ' || emprec.LABEL_ID || '= ' || emprec.STRING_TEXT || emprec.STRING_ID || ';');
end if;
end loop;

end;
spool off;

Code:

#!bin/ksh
set -x
#
#export ORACLE_HOME=/unixsxxx/apps/oracle/dist/xxxx
export ORACLE_SID=xxxxxx
export ORAENV_ASK=NO
export ORA_USERNAME=xxxxxx
export ORA_PASSWORD=xxxxxxx
export logfileTMP=/xxxxxxx/home/help.log
export TNS_ADMIN=/usr/local/bin/oracle
export PATH=$PATH:$TNS_ADMIN
. oraenv
sqlplus-S ${ORA_USERNAME}\/${ORA_PASSWORD}@${ORACLE_SID} <<EOF 2>/dev/null 1>>$logfileTMP
@/xxxxx/home/help.sql
exit
/
EOF

I changed your -! to EOF so you can see they have to match on both sides of a here document. The "last" EOF has to be in column 1 of the script. My /bin/ksh parser does not like -! as a here doc delimiter -- ie., it does not see it properly.

runs successfully bit not results are spooled.

Can you try it without the 'here statement'? Not sure if you need it at all in this situation:

Code:

sqlplus -s "${ORA_USERNAME}/${ORA_PASSWORD}@${ORACLE_SID}" @/xxxxx/home/help.sql

echo $?
...

sqlplus -s "${ORA_USERNAME}\/${ORA_PASSWORD}@${ORACLE_SID}"@/unixsxxx/xxxxx/xxxenv.sql
echo $?

ksh: sqlplus: not found
+ echo 127
127
+ exit

but still no output
last part returned
+ 0<<
@/unixs317/home/cibcenv.sql
echo 0
exit
/

I figured it ou at last. was suggesful in generating my list.

thanks all for the help. Hopefully one day I too can provide assistance.