Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Can anyone correct the error in this script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Can anyone correct the error in this script
# 1  
Old 12-21-2011
Question Can anyone correct the error in this script
Code:
ret=`sqlplus -s  /nolog << EOF
    connect $db_user/$db_pwd@$db_sid;
    SPOOL ./$DirectoryName/TableData.txt;
    set pagesize 0 feedback off verify off heading off echo off linesize 150 
    while read var_ack_party_name
    do
    select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and  search_text = '$var_ack_party_name' ;
     done < ./TimeStamp.txt
    SPOOL OFF;
    EXIT;
    EOF`

The query output should be in TableData.txt

but the output am getting is :
Code:
SP2-0734: unknown command beginning "while read..." - rest of line ignored.
SP2-0042: unknown command "do" - rest of line ignored.
SP2-0734: unknown command beginning "done < ./M..." - rest of line ignored.

The input to the loop is a file(TimeStamp.txt) with contents as:
56978008977
45897085578
.
.
so on..
# 2  
Old 12-21-2011
I Guess, you cannot use the while loop inside the sqlplus.

Instead of that, you can create a .sql file ( using the while read... bla..bla.. )

after that execute the .sql file in sqlplus in single shot.

eg:
Code:
while read a
do
echo "your sql query" >> myquery.sql
done < input.txt

#connect sqlplus here..

sqlplus.... bla..bla..

# 3  
Old 12-21-2011
Similar post of your type .. This might help ..
https://www.unix.com/shell-programmin...oracle-db.html
# 4  
Old 12-22-2011
Quote:
Originally Posted by itkamaraj
I Guess, you cannot use the while loop inside the sqlplus.

Instead of that, you can create a .sql file ( using the while read... bla..bla.. )

after that execute the .sql file in sqlplus in single shot.

eg:
Code:
while read a
do
echo "your sql query" >> myquery.sql
done < input.txt

#connect sqlplus here..

sqlplus.... bla..bla..

when I tried this :
Code:
while read var_ack_party_name
    do
    ret=`sqlplus -s  /nolog << EOF
    connect $db_user/$db_pwd@$db_sid;
    SPOOL ./TableData.txt;
    set pagesize 0 feedback off verify off heading off echo off linesize 150 
     select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and  search_text = '$var_ack_party_name' ;
      SPOOL OFF;
    EXIT;
    EOF`
    done < ./TimeStamp.txt

It is working fine,but the frst query output is being replaced by the second query.
Input file has two tele num's:
4567888977
3676897099

but the result(TableData.txt)has only output for the second number '3676897099'.

The data for both the num's exist in the database.

Can you help me in this Smilie
# 5  
Old 12-22-2011
because, you are over writing the output. so obviously it will store the last sql output.

Code:
ret=`sqlplus -s  /nolog << EOF
    connect $db_user/$db_pwd@$db_sid;
    SPOOL ./TableData.txt;
    set pagesize 0 feedback off verify off heading off echo off linesize 150 
     select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and  search_text = '$var_ack_party_name' ;
      SPOOL OFF;
    EXIT;

EOF`
# 6  
Old 12-22-2011
Quote:
Originally Posted by itkamaraj
because, you are over writing the output. so obviously it will store the last sql output.

Code:
ret=`sqlplus -s  /nolog << EOF
    connect $db_user/$db_pwd@$db_sid;
    SPOOL ./TableData.txt;
    set pagesize 0 feedback off verify off heading off echo off linesize 150 
     select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and  search_text = '$var_ack_party_name' ;
      SPOOL OFF;
    EXIT;

EOF`
Can you please suggest me the change I need to do to get rid of this problemSmilie
# 7  
Old 12-22-2011
How many unix.com id do you have ?

If you want to store the result in a file. then use it like this..

Code:
while read var_ack_party_name
do
$(sqlplus -s  /nolog << EOF
connect $db_user/$db_pwd@$db_sid;
SPOOL ./TableData.txt;
set pagesize 0 feedback off verify off heading off echo off linesize 150 
select *  from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and  search_text = '$var_ack_party_name' ;
SPOOL OFF;
EXIT;
EOF) >> output.txt
done < ./TimeStamp.txt
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Can some one correct this script

Hi, I tried writing a script and there was a problem with SFTP part can some one correct where is is the mistake Enveronment file #!/bin/bash export HOST_NAME=<> export USER_NAME=<> export PASSWORD=<> export SOURCE_PATH=/u03/informatica/current/server/infa_shared/TgtFiles/mfg export... (4 Replies)
Discussion started by: spradeep86
4 Replies

2. Shell Programming and Scripting

Need output of script on screen and file with correct return status of the called script.

Hi, I am trying to capture logs of the script in the file as well as on the screen. I have used exec and tee command for this. While using exec command I am getting the correct output in the file but, script output is not getting displayed on the screen as it get executed. Below is my sample... (14 Replies)
Discussion started by: Prathmesh
14 Replies

3. Shell Programming and Scripting

Please Correct My script

############### #filename.sh ############### CUREENT_DATE=02 log_file_path="$CUREENT_DATE"-"${0##%*/}`|cut -d "." -f1|awk -F "/" '{print $NF}'`"".log" echo $log_file_path ################ #output required 02-filename.log (6 Replies)
Discussion started by: mohitmehral
6 Replies

4. Shell Programming and Scripting

Please correct the error in the following script

#!/bin/ksh db_user=$DB_USER_NAME db_pwd=$DB_PASSWORD db_sid=$TWO_TASK if ; then echo "\tUsage: MoveUsageProcessing <BC Log file Name>" exit 1 else BCLogFileName=$1 fi grep -i 'MoveUsage daemon needs to run on this account before it can be billed' $1 |awk -F\| '{for(i=0;++i<=NF;) if($i ~... (1 Reply)
Discussion started by: Rajesh Putnala
1 Replies

5. Emergency UNIX and Linux Support

Please correct my script

Please correct my script if it any exceptional, since im calling other external script with in my script. #!/bin/bash FE1=TMM DT1=/home/myHome/dated_1 REPORT=/home/otherHome/Report.sh ## Run the report for FE1 & DT1 cd /home/myHome SERV_LST=/home/myHome/srvc-lst echo "Welcome to... (5 Replies)
Discussion started by: raghunsi
5 Replies

6. Shell Programming and Scripting

Awk Script Counting of Correct vs. Error Responses

Hello, I have been trying to use an awk script to parse out correct and incorrect answers in a simple tab-delimited text file. I am trying to compare the user's response to the stimulus presented (in this case, an arrow pointing left or right; e.g., "<--" vs. "-->"). I have the data for the... (6 Replies)
Discussion started by: Jahn
6 Replies

7. Shell Programming and Scripting

Please correct my Script

I am new to awk, can somone please correct the following script awk -F "," 'BEGIN { foreach ((getline < "file1.csv") > 0) {i++ a=1 --> assuming the first column in the row will be stored in a foreach (a in f1) { if (a == $5) continue print a; } } } file2.csv' Here is what I... (11 Replies)
Discussion started by: nuthalapati
11 Replies

8. Shell Programming and Scripting

Is the script correct ???

Dear Collegues is the below given is correct ? #!/usr/bin/perl $a = @ARGV; while ($a = @ARGV) { exec "./jagan ../dat/ml_in @ARGV"; } Jagan (0 Replies)
Discussion started by: jaganadh
0 Replies

9. Shell Programming and Scripting

Correct the error plz

Hi, I'll get a file whose first line comprises of system name, timestamp of file creation and the seq number. System name and seq num I need to other computation. My requirement is, I'll have to check whether the timestamp is greater than current timestamp and also check if the timestamp is... (2 Replies)
Discussion started by: Mandab
2 Replies

10. Shell Programming and Scripting

Error : Field $() is not correct

Hi All, I'm new to shell scripting. Trying to extract substring using awk script as shown below : Flag=$1 Length=`echo ${#Flag}` NewLen=$Length-2 NewFlag=`echo $Flag|awk '{print substr($Flag,0,$NewLen)}'` echo "New string is : $NewFlag" exit When I execute this script the following... (3 Replies)
Discussion started by: abbey
3 Replies
Login or Register to Ask a Question