passing paramets to oracle from unix
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: passing paramets to oracle from unix

  1. #1
    Join Date
    Nov 2006
    Posts
    8

    passing paramets to oracle from unix

    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.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Nov 2006
    Posts
    8

    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?

    or appreciate if you have any other ideas.


    Thanks,
    perot

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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.

    Code:
    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

    Code:
    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

    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;
    regards
    Hrishy
    Last edited by hrishy; 11-19-2006 at 10:23 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width