[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

unix or oracle

Posted on 2007-08-02
5
Medium Priority
?
593 Views
Last Modified: 2013-12-20
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;
0
Comment
Question by:Sara_j_11
  • 3
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 19618568
you miss the exit; at the end in the sql file:


0
 

Author Comment

by:Sara_j_11
ID: 19618731
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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 1000 total points
ID: 19618814
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
 

Author Comment

by:Sara_j_11
ID: 19618907
verify_date is nothing but a function that will return a value of either 1 or 0
0
 

Author Comment

by:Sara_j_11
ID: 19618931
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month18 days, 22 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question