Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to pass string into sql query?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to pass string into sql query?
# 1  
Old 10-29-2013
How to pass string into sql query?
Hi Gurus,

I have a request which needs to pass string into sql.
dummy code as below:
Code:
sqlplus -s user/password@instance << EOF >>output.txt
set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep ,
select emp_no, emp_name from emp
where emp_no in ('a', 'b', 'c');
exit;
EOF

for above query, I need pass string 'a', 'b', 'c' dynamically.

any input is really appreciate.

Thanks in advance.
# 2  
Old 10-29-2013
Using positional parameters:

Code:
a=$1
b=$2
c=$3

sqlplus -s user/password@instance << EOF >>output.txt
set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep ,
select emp_no, emp_name from emp
where emp_no in ($a, $b, $c);
exit;
EOF

Last edited by balajesuri; 10-29-2013 at 01:53 AM..
This User Gave Thanks to balajesuri For This Post:
ken6503
# 3  
Old 10-29-2013
Quote:
Originally Posted by balajesuri
Using positional parameters:

Code:
a=$1
b=$2
c=$3

sqlplus -s user/password@instance << EOF >>output.txt
set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep ,
select emp_no, emp_name from emp
where emp_no in ($a, $b, $c);
exit;
EOF

Thanks for you reply. this works.
one more question, I have file list like below:
Code:
a
b
c
d

I want to create parameter as
para='a','b','c','d'

would you please help me out this.

Thanks in advance
# 4  
Old 10-29-2013
Read file and create parameter:
Code:
while read p
do
        [ -z "$para" ] && para="'${p}'" || para="${para},'${p}'"
done < file

Use the variable in sql:
Code:
where emp_no in ( $para );

This User Gave Thanks to Yoda For This Post:
ken6503
# 5  
Old 10-29-2013
Quote:
Originally Posted by Yoda
Read file and create parameter:
Code:
while read p
do
        [ -z "$para" ] && para="'${p}'" || para="${para},'${p}'"
done < file

Use the variable in sql:
Code:
where emp_no in ( $para );

Thanks this code work perfect.

Thanks again.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

Need sql query to string split and normalize data

Hello gurus, I have data in one of the oracle tables as as below: Column 1 Column 2 1 NY,NJ,CA 2 US,UK, 3 AS,EU,NA fyi, Column 2 above has data delimited with a comma as shown. I need a sql query the produce the below output in two columns... (5 Replies)
Discussion started by: calredd
5 Replies

2. Shell Programming and Scripting

Need script to pass all sql file names in a directory to DB query

Hi All, In this path /home/all_files we have follwing files and direcotries proc_edf_sot.sql proc_ssc_sot.sql func_dfg_sot.sql sot unic cmr sdc under sot directory we have other directories sql pas ref under sql directory we have sql_sot sql_mat (6 Replies)
Discussion started by: ROCK_PLSQL
6 Replies

3. Shell Programming and Scripting

How to pass variable to a query?

Hi All, How to pass date variable to a query? I have tried the below one , but it's not working. ost.ksh #!/bin/ksh v_date=$1 var=$(sqlplus -s $ORACON <<ENDOFSQL SELECT TO_DATE('$v_date','DD-MON-YYYY'),-1) FROM DUAL; exit; ENDOFSQL ) #End I have executed as below. (7 Replies)
Discussion started by: ROCK_PLSQL
7 Replies

4. UNIX for Dummies Questions & Answers

To pass multiple arguments from file in to an sql query

Hi all , I want to pass contents from a file say f1 as arguments to a sql query which has In statement using a script example select * from table_1 where login in ( `cat f1`) ; will this work or is there any other way to do it. (1 Reply)
Discussion started by: zozoo
1 Replies

5. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

6. Programming

JDBC code to pass the SQL query as parameter and execute?

Below i have the sample code. i need to pass the entire query from file or as parameter and read the results and write into a output file. here the number of columns are unknown. some times it may be 2,3 or entire columns from the table. read all the column results and write into a comma... (0 Replies)
Discussion started by: laknar
0 Replies

7. Shell Programming and Scripting

Pass a variable to SQL script

Hi Guys, I like to pass a variable to a sql file in a unix script.. I tried a below code.. var=200903 db2 -vf test.sql 200903 test.sql is as below. select * from db2.users where quarter = $1; Please tell me where i go wrong.. Thanks in advance, Magesh (2 Replies)
Discussion started by: mac4rfree
2 Replies

8. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

9. Shell Programming and Scripting

Pass variable to sql

Please help. I got these error. I'm try to pass variable extract from data-file.txt to sql file(select.sql). cat: cannot open select cat: cannot open * cat: cannot open from cat: cannot open user cat: cannot open where cat: cannot open name=$list; #!/bin/bash list=`sed q... (3 Replies)
Discussion started by: killboy
3 Replies

10. Shell Programming and Scripting

pass result from one query to another

Can any one help me how to pass the value of result of one query to another query. I to pass the value of result of 'select max(id) from a' into another query like update table set col =<value from last query> where ....; updatestaging() { xx=`$ORACLE_HOME/bin/sqlplus -s... (1 Reply)
Discussion started by: u263066
1 Replies
Login or Register to Ask a Question