Passing shells cript parameter value to sql file and control file in same script

Posted on 2009-12-28
Last Modified: 2013-12-21
How can we pass value from unix shell script parameter to sql file and sqlloader control file.
Example- In shell script, i have value for batch_id column say 4 which is step1 in script,
Ques1- In the same script, step 2 calls sqlloader to load ctrlfile into Oracle table. How can i add $batch_id value(step2
generated value) in ctrl file.
Example- load data
         into table test
          fields terminated by "|"
          trailing nullcols
           pmv constant 'L'
           id   $batch_id(?? i want to load $batch_id value which is 4  to id column in ctrl file.How can i do this?)

Ques2- Step 3 in same script, calls sql file which is a.sql. In sql file, there are many update and select statements.I want to
substitute id column to $batch_id in sql file
Example - a.sql
select name from emp where id=$batch_id;(?? i want to load $batch_id value which is 4 to id column in sql file.How can i do this?)
There are many other select update this sql file which uses id=$batch_id
Question by:sunilbains
    LVL 20

    Expert Comment

    We use the following approach
    1) sqlloader ctrlfile
         #define a unique place_holder in the ctrlfile for example ==batchid==
         # in the shell-script create a unique tempory copy of the ctrflfile replacing it with the id
        # (im not sure of the exact sed command)
         sed -e s/==batchid==/$batch_id/ ctrlfile > /tmp/ctrlfile_$$
         # use sqlload with the temporary file /tmp/ctrlfile_$$ as controlfile

    2)  in the sqlplus-script  use  &&batch_id  as placeholder
         begin the script with
         define batch_id=&1
         prompt batch_id = &&batch_id
         .. rest of script
         update ..  where ... = &&batch_id; --  

         call the script passing the batch_id as the first argument
         sqlplus  user/pwd@service @script $batch_id
         this assign $batch_id in the shell to &1 in the script and you did the define of batch_id :
         you can use it by &&batch_id

    Author Comment

    Do i need to put following in 'Declare' section or 'Begin' section of sql-
    define batch_id=&1
         prompt batch_id = &&batch_id
    LVL 20

    Accepted Solution

    neither these are sqlplus-statements not pl/sql-statements

    set serveroutput on
    define batch_id=&1
    prompt batch_id = &&batch_id

       dbms_output.put_line('batch_id in pl/sql:  &&batch_id');

    select 'batch_id in sql-statement:  &&batch_id' from dual


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now