passing paramets to oracle from unix
Hi,
I am trying to call a sqlscript which takes empids as parameters from shell script.
output of the sql command i am storing in a textfile.
i am calling my sql script thru unix as follows.
1)
i have datafile called data.txt which contains the empid's as follows
123
234
456
678
999
i am passing this data.txt to my unix script as follows.
myunixscript.sh data.txt
in myscript.sh i am framing the values as
'123','234','456','678','999' and stroing them in a variable called empids
3) calling sql script as follows
sqlplus userid/password@sid @mysql.sql empids.
===
mysql.sql
=========
set serveroutput on
spool on
spool /tmp/empids
select * from emptable where empid in ('&1');
spool off
=========================================
when i run the above script its always selecting only one record ie, the fist record for empid = 123
could someone help me in knowing what changes should be done to fetch all the records for the passed empids.
passing paramets to oracle from unix Reply to Thread
Hi Hrishy,
Thanks for your reply. basically the number of values which i am going to read from data.txt is unknown and hence i am reading all these values one by one in unix scirpt and framing them as one string as '123','234','345' etc.... and store it in a unix variable.
d = '123','234','345'
I had one idea that, if it is not possible by passing it as parameter then as an alternate way export this unix variable.
if there is any possibility that i can read this unix exported variable (enviroment variable) in oracle then i think it will resolve my issue.
ie., is there anyway that i can read unix environment variables in oracle?
or appreciate if you have any other ideas.
Thanks,
perot