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?
select * from emp
where empid in
(select empno from external_table)
Look up the oracle docs on the nuances of creating a 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