passing paramets to oracle from unix

[perot]

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.

[hrishy]

Hi

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.

regards
Hrishy

[perot]

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

[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