OraFAQ Forum: Client Tools » passing one parameter from Shell-script to Sql-script

Home » Other » Client Tools » passing one parameter from Shell-script to Sql-script

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

passing one parameter from Shell-script to Sql-script [message #269235]

Fri, 21 September 2007 05:22

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

Report message to a moderator

Re: passing one parameter from Shell-script to Sql-script [message #269244 is a reply to message #269235]

Fri, 21 September 2007 06:16

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: passing one parameter from Shell-script to Sql-script [message #269259 is a reply to message #269244]

Fri, 21 September 2007 06:49

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

Report message to a moderator

Re: passing one parameter from Shell-script to Sql-script [message #269262 is a reply to message #269259]

Fri, 21 September 2007 07:15

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Examples are in the chapter I give you the link.

Regards
Michel

Report message to a moderator

Re: passing one parameter from Shell-script to Sql-script [message #269327 is a reply to message #269235]

Fri, 21 September 2007 13:01

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

Report message to a moderator

Previous Topic:	a tools to display graph
Next Topic:	SQL*Plus Script Dropping Column Headings

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 03 08:47:46 CST 2025