passing paramets to oracle from unix

[hrishy]

Hi

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.

create table external_table
(EMPNO NUMBER(4)   
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by ',' )
location ('emp.dat')
  )
  /

Now you can write your sql statement as

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

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;

regards
Hrishy