Home » SQL & PL/SQL » SQL & PL/SQL » passing parameters to SQL file through shell scripts
passing parameters to SQL file through shell scripts [message #335574] Tue, 22 July 2008 13:49 Go to next message
rareraja
Messages: 22
Registered: March 2005
Location: NJ, USA
Junior Member
Hi,
I need to pass date parameter such as fromdate and todate from shell script to SQL file, which in trun executes query based on the date parameters.

I will appreciate if any body tell me the format of the code..

Thanks in advance..

Raj
Re: passing parameters to SQL file through shell scripts [message #335575 is a reply to message #335574] Tue, 22 July 2008 14:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>I will appreciate if any body tell me the format of the code..
We can't because we don't know your system.
The answer depends upon how the OS & DB are configured.
Re: passing parameters to SQL file through shell scripts [message #335576 is a reply to message #335574] Tue, 22 July 2008 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to Ana's post, search in the appropriate OS forum you will find many examples of this.

Regards
Michel
Re: passing parameters to SQL file through shell scripts [message #335610 is a reply to message #335576] Tue, 22 July 2008 23:41 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Here is a sample code for .vbs file.(Save the text in file name sample.vbs) and try and do google.

   
Dim fromDate
Dim toDate
Dim sAppendStr
Dim Shell

Set Shell = CreateObject("WScript.Shell")    
fromDate = Month(Now) & "/" & Day(Now) & "/" & Year(Now)
toDate   = Month(Now) & "/" & Day(Now) & "/" & Year(Now)
    
sAppendStr = fromDate & "," & toDate
Shell.Run "<pass Sqlplus.exe path and sqlfile path here> " & sAppendStr


Regards,
Rajat
Re: passing parameters to SQL file through shell scripts [message #335629 is a reply to message #335610] Wed, 23 July 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think .vbs file works on Unix.

Regards
Michel
Re: passing parameters to SQL file through shell scripts [message #335659 is a reply to message #335574] Wed, 23 July 2008 03:23 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

sqlplus -s $login <<-EOF
set feedback off
set serveroutput on
declare
--  variable declarations
....
....
begin
--sql statements
.....
.....
end;
/
exit
EOF


In the syntax above,
$login is the unix variable which stores username/password.
EOF is the identifier to indicate start and end of sql statements.
UNIX variables can be referred inside sqlplus by using $ sign.

Similarly you can call a SQL file and also spool data and also can pass the required parameters. Syntax changes based on your exact requirements...

Regards
Himanshu
Re: passing parameters to SQL file through shell scripts [message #335664 is a reply to message #335659] Wed, 23 July 2008 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe but this will not work on Windows.

Regards
Michel
Re: passing parameters to SQL file through shell scripts [message #335666 is a reply to message #335664] Wed, 23 July 2008 04:05 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Michel Cadot wrote on Wed, 23 July 2008 14:30
Maybe but this will not work on Windows.

Regards
Michel



It would not as it is for Unix/Linux shell script syntax. Was it requested for windows, as the OP asked for shell script syntax? Razz
Re: passing parameters to SQL file through shell scripts [message #335669 is a reply to message #335666] Wed, 23 July 2008 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OP has been asked to post his OS, he didn't want to do so, he didn't deserve to be have answers.

Regards
Michel
Re: passing parameters to SQL file through shell scripts [message #335671 is a reply to message #335669] Wed, 23 July 2008 04:19 Go to previous message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Michel Cadot wrote on Wed, 23 July 2008 14:39
OP has been asked to post his OS, he didn't want to do so, he didn't deserve to be have answers.

Regards
Michel



hmm.. So he should be YOYO (You On Your Own) Laughing
Previous Topic: runtime table type
Next Topic: Instance terminated by user, pid=27875
Goto Forum:
  


Current Time: Sat Dec 10 03:27:18 CST 2016

Total time taken to generate the page: 0.09961 seconds