Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Guidance needed for a typical shell script with sql query

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Guidance needed for a typical shell script with sql query
# 1  
Old 10-13-2011
Question Guidance needed for a typical shell script with sql query
Hi ,
I have a txt file with contents like:
Code:
1234
2345
3456
7891

I need to write a script which takes input file as txt file..run a sql query for that number and place the output of query in another file..

Code:
select * from bus_event where acct_nbr='1234'( from input txt file)

the query should run for all numbers in txt file and place the result in another txt file line by line..
Please anyone help me Smilie
Last edited by radoulov; 10-13-2011 at 06:41 AM.. Reason: Code tags!
# 2  
Old 10-13-2011
Code:
printf "select 
  * 
from 
  bus_event 
where 
  acct_nbr='%s';\n" $(<infile)

Last edited by radoulov; 10-13-2011 at 09:14 AM.. Reason: Corrected.
# 3  
Old 10-13-2011
Shell independent option:

Code:
nawk -v q="'" '{ print "select * from bus_event where acct_nbr = " q $1 q  ";"}' infile

# 4  
Old 10-13-2011
Quote:
Originally Posted by radoulov
Code:
printf 'select 
  * 
from 
  bus_event 
where 
  acct_nbr='%s';\n' $(<infile)

I need to send the output of query executed for each number present in input file to a txt file (output file)
# 5  
Old 10-13-2011
Code:
printf > outputfile "select 
  * 
from 
  bus_event 
where 
  acct_nbr='%s';\n" $(<infile)

Last edited by radoulov; 10-13-2011 at 09:14 AM.. Reason: Corrected.
# 6  
Old 10-13-2011
Quote:
Originally Posted by radoulov
Code:
printf > outputfile 'select 
  * 
from 
  bus_event 
where 
  acct_nbr='%s';\n' $(<infile)

I have executed the above code but it throwed an error:
Code:
db_user=`echo $DB_USER_NAME`
db_pwd=`echo $DB_PASSWORD`
db_sid=`echo $TWO_TASK`

`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
set pagesize 0 feedback off verify off heading off echo off
printf > out.txt 'select   * from   bus_event  where   bus_event_seq_nbr='%s';\n' $(<./seqnbr.txt)
exit;
EOF`

Error throwed is -
SP2-0734:: not found
# 7  
Old 10-13-2011
Yes,
you've got an error because you need something different.

Code:
# these seem unnecessary ...
db_user=$DB_USER_NAME
db_pwd=$DB_PASSWORD
db_sid=$TWO_TASK

{ 
  printf 'set pages 0 feed off ver off head off echo off\n'
  printf "select   
    * 
  from   
    bus_event  
  where   
    bus_event_seq_nbr='%s';\n
  " $(<./seqnbr.txt)
  } | 
    sqlplus -s "$db_user"/"$db_pwd"@"$db_sid"

Or even:

Code:
{    
  printf 'set pages 0 feed off ver off head off echo off\n'    
  printf 'select   
    * 
  from   
    bus_event  
  where   
    bus_event_seq_nbr in ( %s );\n' $(
      printf "'%s'\n" $(<./seqnbr.txt) |
        paste -sd, -
        ) 
    } |
        sqlplus -s "$db_user"/"$db_pwd"@"$db_sid"

Last edited by radoulov; 10-13-2011 at 09:19 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script to execute sql query.

Hi Experts, Need your support. Not able to use sql query alias in shell script. Could you please help me in finding right way to use alias with sql query in shell script. Below is the code i am using. #!/bin/bash sqlplus -s abc/abc@abc << EOF> bcd.csv set trimspool on select zone_id... (4 Replies)
Discussion started by: as7951
4 Replies

2. Shell Programming and Scripting

How do I read sql query into shell script?

Hello All, I'm trying to put together a shell script that will: 1. connect to an oracle database 2. execute a query 3. save the output to a csv file I know that I can execute the sqlplus -s user/pass @dbsid and get logged in. What I would like to do is have my query in a separate text... (9 Replies)
Discussion started by: bbbngowc
9 Replies

3. Shell Programming and Scripting

How to embed sql query into our shell script?

Hi I would like to embed a sql query in my shell script. Also, before any the sql query is executed, i would like to validate username and password. (1 Reply)
Discussion started by: arghadeep adity
1 Replies

4. Red Hat

Sql query through shell script

hey , i am using this code to store value of a sql query and and then use it in other query but after some time , but it is not working. please help #!/bin/bash val_1=$( sqlplus -s rte/rted2@rel76d2 << EOF setting heading off select max(stat_id) from cvt_stats; exit EOF ) nohup... (5 Replies)
Discussion started by: ramsavi
5 Replies

5. Shell Programming and Scripting

$ symbol in sql query in shell script

Hi Team, Can you please help me to resolve this issue. Am unable to use this $ symbol in sql query in the shell script. For Example: # !/bin/sh export USER_NAME=XXX export PASSWORD=YYY export ORACLE_SID=xamdb echo $ORACLE_SID echo " Session Details ..." ... (1 Reply)
Discussion started by: indira_s
1 Replies

6. Shell Programming and Scripting

query sql using shell script

query sql using shell script, is it possible? my friend told me to do a file.sql and link to my shell script, but can i query sql using shell script? thanks in advance! (2 Replies)
Discussion started by: kingpeejay
2 Replies

7. Shell Programming and Scripting

executing a SQL query in shell script

Hi ALL, I need an help in connecting to oracle database, executing a select query and printing it on the screen. Can any one please write a simple code or psuedo code and let me know. select query returns multiple values( say select name from emp) Thanks in advance LM (1 Reply)
Discussion started by: lijju.mathew
1 Replies

8. Shell Programming and Scripting

Executing Sql Query Using Shell Script

HI ALL i have a requirement like this. i have to write a shell script to run a sql query. DB is oracle. once the query is run, the results of the query has to be published in a data file. can you please advice me how to go about it. i am absolutely new to shell scripts and this is a part of my job. (14 Replies)
Discussion started by: ragha81
14 Replies

9. UNIX for Advanced & Expert Users

Connecting DB in the Shell Script to do SQL Query

Any link or example to write shell script for the Connecting Oracle for Quering through SQL thanks in advance ... Cheers !! Mehul Doshi (3 Replies)
Discussion started by: mehuldoshi
3 Replies

10. UNIX for Dummies Questions & Answers

Executing a SQL query from a shell script

I cannot figure out how to run a SQL script, or just a sqlplus query, from a shell script (bash or ksh). Basically, I need to su - oracle from root and run a query, then test the exit status. (3 Replies)
Discussion started by: 98_1LE
3 Replies
Login or Register to Ask a Question