How to pass parameter from file to sqlplus in UNIX?

Applications Programming
1

i have file in which i have employee id are there and every time number of employee id are different in file means number of count of employee id in file are every time different.

 
343535435 
365765767
343534543 
343543543

i want to pass this file to sqlplus
and sql command is

 

SELECT * FROM TABLENAME WHERE EMPLYOE_ID IN (ALL RECORDS FROM ABOVE FILE)

and i am calling this .sql file from unix

sqlplus -s user/password@servername @.sql
please let us know how i can pass above mentioned filename in unix to sqlplus

2

Why new/same posting again?

Note: following code is partly involving RudiC's code from http://www.unix.com/302923560-post4.html

$ echo "SELECT * FROM TABLENAME WHERE EMPLYOE_ID IN ( $(sed '/^\s*$/d;s/ *//g' file | paste -sd,) )" > acc.sql
$ 
$ cat acc.sql
SELECT * FROM TABLENAME WHERE EMPLYOE_ID IN ( 343535435,365765767,343534543,343543543 )
$

or the same task divided in two pieces:

$ accnbrs="$(sed '/^\s*$/d;s/ *//g' file | paste -sd,)"
$ echo "SELECT * FROM TABLENAME WHERE EMPLYOE_ID IN ( "$accnbrs" )" > acc.sql
$ cat acc.sql
SELECT * FROM TABLENAME WHERE EMPLYOE_ID IN ( 343535435,365765767,343534543,343543543 )
$
3

HI Junior,

Thanks, i pasted here because they peoiple told me to put in sql forum, that is why i pasted same question here again.

appreciate if you let me know how what sed command is doing here

sed '/^\s*$/d;s/ *//g' file

---------- Post updated at 06:28 AM ---------- Previous update was at 06:05 AM ----------

this is removing space in start of every line

s/ *//g

but what this is doing, i know d for delete and ^ means starting of line and \s is space character but what this command is doing, please let me know.

/^\s*$/d;
4

Actually, I used s/ *//g to remove the trailing whitespaces from the records you posted. In fact it should also delete whitespaces in the beginning of the line, but it will not delete a line which has whitespaces.

/^\s*$/d deletes "real" empty lines, as well as empty lines which contain whitespaces or tabs, such as the one above the records you have posted.

5

Hi Junior,

thanks a lot,

can you also help me how i can put single quotes with the help of sed command in each record

like this

'3423432432432'
'2343243243243'
'3243243242343'

if my file is without single quote

sed  's/^*/\'/  filenname

above command is not working

6

Just substitute sed '/^\s*$/d;s/ *//g' file by sed '/^\s*$/d;s/ *//g;s/.*/\x27&\x27/' file

7

Hi Junior,

I have modified my script like this

and file has below content

'324324324'
'232423434'
'232424444'
'234324444'

UNIX SCRIPT

d=`paste -sd, file.txt`
sqlplus -s username/password@servername @file.sql $d >> file1.txt

file.sql

define id=&1
select * from acct_table where acct in ($id)
exit

But i am not getting any o/p, seems like variable is not extended in sql, please let me know where i did mistake.

8

Build the sql file from your script:

d=`paste -sd, file.txt`
echo "select * from acct_table where acct in ( $d );" >file.sql
echo exit >>file.sql
sqlplus -s username/password@servername @file.sql >>file.out

Hope this helps.