If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

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

Code:

declare
file_handle utl_file.file_type;
s varchar2(1000); --idelay this should be number
begin
file_hande := utl_file.fopen (path, filename, 'R');
WHILE TRUE
LOOP
utl_file.get_line (file_hande, s); -- read the first line
select * from emptable where empid in (to_numbr(s)); --cast to number
END LOOP;
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.fclose(file_handle);
END;