passing paramets to oracle from unix
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.
i have datafile called data.txt which contains the empid's as follows
i am passing this data.txt to my unix script as follows.
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.
set serveroutput on
select * from emptable where empid in ('&1');
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.
I think you cannot do it that way.
You need to use either var1 var2 var3 var4 and var5
or $1 to $5 in your case or you need to use array korn shell for example supports arrays.
passing paramets to oracle from unix Reply to Thread
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.
What version of oracle are you using if you are on 9i and later then the simplest way of solving your problem with the least complication is to use external tables.
Now you can write your sql statement as
create table external_table
( type oracle_loader
default directory data_dir
( fields terminated by ',' )
Look up the oracle docs on the nuances of creating a external table.
select * from emp
where empid in
(select empno from external_table)
Your other problem could also be solved by the same approach instead of using the utl_file you can use external tables.
I will not use unix environment variables the way you said instead will still go by $1 ,$2 $3 etc..i hope you have less then 9 values at any given time.
the you can still do
select ename,empno from emp
where eno in ($1,$2,$3)
The other approach is to use utl_file
s varchar2(1000); --idelay this should be number
file_hande := utl_file.fopen (path, filename, 'R');
utl_file.get_line (file_hande, s); -- read the first line
select * from emptable where empid in (to_numbr(s)); --cast to number
WHEN OTHERS THEN
Last edited by hrishy; 11-19-2006 at 10:23 PM.
Click Here to Expand Forum to Full Width