Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Run SQL with parameters from a file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Run SQL with parameters from a file
# 1  
Old 09-14-2009
Run SQL with parameters from a file
Hello,
I need to run a SQL which will get parameters from a .txt file. Basically I need to run following query

select * from a table where identity_id in <list of values in A.txt file>

A.txt file is having values
1
2
3
4
5

I need the SQL to pick the values from the file and insert in the where clause. Is there any way to achieve this in Unix?

Thank you !
# 2  
Old 09-14-2009
Quote:
Originally Posted by asdban
...
I need the SQL to pick the values from the file and insert in the where clause. Is there any way to achieve this in Unix?
...
A similar example is given below. Assume that this is your "a.txt" file:

Code:
$
$ cat a.txt
7369
7844
7521
7902
7698
$

The following command pipeline creates an "IN-list" from the contents of a.txt:

Code:
$
$ cat a.txt | tr '\n' ',' | sed -e 's/,$/);/' -e 's/^/(/'
(7369,7844,7521,7902,7698);$
$

And it can be used in the portion of a shell script that interacts with Oracle, thusly -

Code:
$
$ cat testscr.sh
#!/usr/bin/bash
sqlplus -s scott/tiger <<EOF
select * from emp where empno in $(cat a.txt|tr '\n' ','|sed -e 's/,$/);/' -e 's/^/(/')
exit
EOF
$
$

So the query that you are going to execute (though not apparent) is:

Code:
select * from emp where empno in (7369,7844,7521,7902,7698);

And the proof is:

Code:
$
$ . testscr.sh
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
$
$

HTH,
tyler_durden
# 3  
Old 09-18-2009
Thank you very much durden_tyler !!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Linux parameters in SQL command

Hello. It's my first steps in creat bash skript. This is my skript : mysql -e " UPDATE datebase.table SET U_O_ID=NULL WHERE U_O_ID LIKE '"$w4"' AND N_U != '"$w1"' " -u admin -p""Password"" ; It doesn't work. I find a lot of topic about it, but I didn't find simple answer.... (7 Replies)
Discussion started by: karp
7 Replies

2. Shell Programming and Scripting

Shell script to run sql query having a long listing of parameters

Hi, I have a query regarding execution of a sql query having long listing of parameters ..I need to execute this query inside a shell script. The scenario is like.... Suppose I have a file abc.txt that has the card numbers..it could be in thousands.. then I need to fire a query like ... (12 Replies)
Discussion started by: vsachan
12 Replies

3. Shell Programming and Scripting

Run a program-print parameters to output file-replace op file contents with max 4th col

Hi Friends, This is the only solution to my task. So, any help is highly appreciated. I have a file cat input1.bed chr1 100 200 abc chr1 120 300 def chr1 145 226 ghi chr2 567 600 unix Now, I have another file by name input2.bed (This file is a binary file not readable by the... (7 Replies)
Discussion started by: jacobs.smith
7 Replies

4. 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

5. Shell Programming and Scripting

Need help to run sql query from a script..which takes input from a file

I need to run sql script from shell script which takes the input from a file and contents of file will be like : 12345 34567 78657 and query will be like : select seq_nbr from bus_event where event_nbr='12345'; select seq_nbr from bus_event where event_nbr='34567'; select seq_nbr... (1 Reply)
Discussion started by: rkrish
1 Replies

6. Shell Programming and Scripting

Calling sql file from shell script with parameters.

Hi, I am calling a sql file script.sql from shell script and passing few parameters also as shown below: sqlplus -S id/password @script.sql $param1 $param2 Now,In sql file I have to create a extract text file after querying oracle tables based on the parameters passed(param1,param2) as... (7 Replies)
Discussion started by: anil029
7 Replies

7. Shell Programming and Scripting

Calling sql in shell script with parameters

Dear All, I want to call an sql script within a unix shell script. I want to pass a parameter into the shell script which should be used as a parameter in teh sql script. e.g $ ./shell1.sh 5000129 here 5000129 is a prameter inside shell script i am calling one sql script e.g. ... (2 Replies)
Discussion started by: Radhe
2 Replies

8. Shell Programming and Scripting

Run SQL queries in DB2 and output to file

Hi, I new to Unix and scripting. Following is my requirement. Can someone tell me whether its possible or not. Also please let me know how to proceed further if this is possible. List of queries are stored in a file. For example, I have to run a query like this: Select * from &XYZ where... (0 Replies)
Discussion started by: simhasuri
0 Replies

9. Shell Programming and Scripting

any possible to run sql result and output to file

Hi, I search all post...and no soluation about..if i would like to run a sql statement and output the result to txt file. for example, i usually run "sql" to logon the database and run select statement. Then I need to copy the output into the result.txt. Can I run the script to do this... (7 Replies)
Discussion started by: happyv
7 Replies

10. Shell Programming and Scripting

parameters in PL/SQL script

Hi , I am writing a unix script, calls an PL/SQL block which uses the parameters I passed to the Unix script. There are 3 sets of parameters I am passing: set 1 - a or b (only one parameter) set 2 - 2 2 (two parameters) set 3 - 2 10000 20000 (three parameters) Now, my question is I want... (3 Replies)
Discussion started by: reddyp
3 Replies
Login or Register to Ask a Question