unix or oracle

I have a problem. The following code is in my unix script file to spool a pipe delinmietd file from sqlplus.
My issue is the script calls sqlplus and also creates the txt file , but does not exit out of sqlplus. It just hangs so and after
that I am having to use the ps command to identify  the job - i see 2 processes - one for .ksh script and the other for the sqlplus
NOw I need to find out how to cleanly exit from .ksh code itself...
part of my script :

LOAD_DATA=`sqlplus -s user/passw@oracle <<EOF
    set heading off feedback off verify off timing off echo off trimspool off
    select verify_date from dual;
    exit
EOF`
#LOAD_DATA=`echo $LOAD_DATA | tr "[a-z]" "[A-Z]"`
echo "testing.."
echo $LOAD_DATA
if [ $LOAD_DATA -eq 0 ]
then
echo "testing0"
sqlplus /nolog  @/test/final.sql 1>/dev/null 2>&1  ( gets hanged here and not exiting from here)
fi

my sql file:

start /export/home/user/.db.up    (connection)
set feedback off
set echo off
set termout off
set heading off
set pagesize 0
set linesize 1000
set newpage 0
set space 0
set colsep ','
set trimspool on

spool /test/alli.txt

SELECT
ALIAN_ID||'|'||
NAT_ACCT_NBR||'|'||
NAT_ACCT_SGRP_NBR||'|'||
FEDEX_ACCT_NBR||'|'||
FEDEX_GRND_ACCT_NBR||'|'||
ALIAN_JOIN_DT||'|'||
ALIAN_CLOSE_DT||'|'||
DECODE (CUST_STAT,'O','OPN','C','CSD',CUST_STAT)||'|'||
DECODE (CUST_TYPE,'N','NEW','T','TFR',CUST_TYPE)||'|'||
DECODE (CLSD_RESN_CD,'D', 'DLK','M', 'MOV',CLSD_RESN_CD)
FROM user.FINAL
where rownum<3;

spool off;
Sara_j_11Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you miss the exit; at the end in the sql file:


0
 
Sara_j_11Author Commented:
I tried with exit ; If I put exit then what happens is it exits - script does not hang - BUT THE OUPUT FILE DOES NOT GET CRETAED. That is y I removed teh exit . Now it atleast creates the output file , but it hangs
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
1) select verify_date from dual;
will this work ? what is verify_date in dual table ?
2) Also after this spool off; --> there should be an exit ; so that you can come out of the sqlplus
3) Also "sqlplus /nolog  @/test/final.sql 1>/dev/null 2>&1 ' --> it should be
              sqlplus /nolog  @/test/final.sql 1>/dev/null 2>&
     right. I am not getting as to what does &1 at the end mean.

Thanks
0
 
Sara_j_11Author Commented:
verify_date is nothing but a function that will return a value of either 1 or 0
0
 
Sara_j_11Author Commented:
great all of a sudden the exit statement worked..!!!!!!!!!!!!! I just deleted some extra files to free up some disk space...thats all I did... very strange. But thanks a lot dear experts!!!!!!!!!!!!!!!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.