Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Substitution Variables
- Thread starter soans
- Start date
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
Amol
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
[sig][/sig]
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
Amol
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
[sig][/sig]
Ok....... Could you give an example of this? Sounds good actually. [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href= Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. <grin>[/sig]
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
exit
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.
Amol
amolsonaikar@yahoo.com
[sig][/sig]
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
exit
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.
Amol
amolsonaikar@yahoo.com
[sig][/sig]
Thx Amol -- good of you, much appreciated. [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href= Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. <grin>[/sig]
- Status
Similar threads
