Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Substitution Variables

Substitution Variables

Substitution Variables

How do I pass substitution variables to sqlplus script through a shell script?

RE: Substitution Variables

Well I suppose you want to pass value to sqlplus program from unix shell

IF you have to pass value in variable to sql from shell I will just tell you what to we used do.
if you have certain variable in declared in sql
the in your shell script you will require to call that program using  
sqlplus -s @ /...path/program_name var1
you can asingn the value to var1 from shell script directly
or in a loop (for continuous calling of same program for different values)
-s option automatically calls for values for sqlplus file


sqlplus command may ask you for username/password
dont forget to specify it in ops$username/username
I hope this will help u some way.Correct Syntax for your machine you can find out

RE: Substitution Variables

Oh I will have to type a lot now ...
Any way you have answered my questions many times so I will do it for you as a good friend.

I had an example which I did for my orgn. previously where I had to calculate incentive for each branches numbering from 400 and 450 and It included generating individual reports (branchwise seperate files creation) for each branch there it was not possible to take from to values from sqlscript itself. However you have to accept the values from sqlscript using &varname
Now I have a sqlscript as follows (mike.sql)

accept branch
spool &branch   rem-- creates a file for branch
Select branch_cd, proposal_no, flat_rate*amount from
prop_mst where period >= <date> and period >= <date1>
and branch_cd = &branch;
spool off

The above program you will have to call from a shell script
suppose mike.com (I will give raw format of script as I dont have it right now)

i = 400
while (i < 451)    # Please use proper syntax as per unix
sqlplus -s @ /dpir/flt1/mike.sql i  #may require username
i = i + 1

the above com file will pass value e.g. 400 to mike.sql and mike.sql will generate output file 400.lst
remember  dont forget exit in sql you have come out of oracle for next value.
thus it will create 400 to 450 i.e. 50 o/p files which you can send to 50 states in US (I hope my count is correct there are 30 in India.)

I hope this will clarify certain things even though it may not be totally correct.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close