[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 967
  • Last Modified:

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

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 statements..in this sql file which uses id=$batch_id
  • 2
1 Solution
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
sunilbainsAuthor Commented:
Do i need to put following in 'Declare' section or 'Begin' section of sql-
define batch_id=&1
     prompt batch_id = &&batch_id
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


Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now