Home » SQL & PL/SQL » Client Tools » passing one parameter from Shell-script to Sql-script
passing one parameter from Shell-script to Sql-script [message #269235] Fri, 21 September 2007 05:22 Go to next message
subodhbansal
Messages: 2
Registered: September 2007
Junior Member
Dear Friends,

Please help me to achieve the following:

I want to pass one parameter from Shell-script to Sql-script.
Example:

My ShellScript.sh is calling report.sql like this:
/bin/sqlplus /reports.sql

And

My report.sql is calling many Stored-Procedures like this:
exec SP_REPORT_1( TO_DATE('20070921', 'YYYYMMDD') );
exec SP_REPORT_2( TO_DATE('20070921', 'YYYYMMDD') );
exec SP_REPORT_2( TO_DATE('20070921', 'YYYYMMDD') );


So I want to passs this date ('20070921') from ShellScript.sh to report.sql, somthing like this:
/bin/sqlplus /reports.sql '20070921'


Please tell me how can I achieving this, I am very poor in Shell and SQL scripting.

Many Thanks,
Subodh Bansal
Re: passing one parameter from Shell-script to Sql-script [message #269244 is a reply to message #269235] Fri, 21 September 2007 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
/bin/sqlplus /reports.sql

This surely not works:
- sqlplus is in $ORACLE_HOME/bin directory
- syntx is "sqlplus ... @reports.sql"

reports.sql can take parameters which can be used inside it.
For instance "sqlplus ... @reports.sql '20070921'"

SQL*PlusŪ User's Guide and Reference, Chapter 5 Using Scripts in SQL*Plus

Regards
Michel
Re: passing one parameter from Shell-script to Sql-script [message #269259 is a reply to message #269244] Fri, 21 September 2007 06:49 Go to previous messageGo to next message
subodhbansal
Messages: 2
Registered: September 2007
Junior Member
Thanks Michel,

Actually calling sql-scripts from my shell-script works without any problem. This works without any problem.
Like this:
/u01/oracle/9.2.0/bin/sqlplus user/pass @/reports/rbt_reports.sql

But I want to pass a param (date value) to Sql-script.
You tried to explain me but as I have little understanding about scripting stuff, would you Please explain me in little bit more detail with example.

I will really appreciate your help.

Thanks,
Subodh
Re: passing one parameter from Shell-script to Sql-script [message #269262 is a reply to message #269259] Fri, 21 September 2007 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Examples are in the chapter I give you the link.

Regards
Michel
Re: passing one parameter from Shell-script to Sql-script [message #269327 is a reply to message #269235] Fri, 21 September 2007 13:01 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try this:
rbt_reports.sql
===============
--set verify off
exec SP_REPORT_1( TO_DATE('&1', 'YYYYMMDD') );
exec SP_REPORT_2( TO_DATE('&1', 'YYYYMMDD') );
exec SP_REPORT_2( TO_DATE('&1', 'YYYYMMDD') );


Then call the script...
sqlplus user/pass @/reports/rbt_reports.sql 20070921
Previous Topic: a tools to display graph
Next Topic: SQL*Plus Script Dropping Column Headings
Goto Forum:
  


Current Time: Mon Dec 05 12:36:47 CST 2016

Total time taken to generate the page: 0.16775 seconds