Home » Other » Client Tools » Pass parameters to oracle SQL Query
Pass parameters to oracle SQL Query [message #568089] Tue, 09 October 2012 08:21 Go to next message
ramtin
Messages: 37
Registered: November 2008
Member
Hi I am trying to pass the parameters to query and I need to pass few parameters to query. Can I make it like like then I run it, then it come up and ask the parameter like this:

SQL> @text
para1 XXXXX
para2 XXXXX

And then the result of query will come up. I would like to put label for each parameter( likepara1). Please advise

Thank you for your help in advance

Regards,
Mohammad
Re: Pass parameters to oracle SQL Query [message #568091 is a reply to message #568089] Tue, 09 October 2012 08:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>Can I make it like like then I run it,
I give up.
Can you?
I can.
Re: Pass parameters to oracle SQL Query [message #568093 is a reply to message #568089] Tue, 09 October 2012 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use ACCEPT command.

Regards
Michel

Re: Pass parameters to oracle SQL Query [message #568102 is a reply to message #568093] Tue, 09 October 2012 08:45 Go to previous messageGo to next message
ramtin
Messages: 37
Registered: November 2008
Member
I have query and need to run it in SQLplus. I found out with following like I can get the parameter but I still need to put the label for parameter because they are few parameter and user need to know with the parameter he/she need to put in order.

AND WFR_YEAR_CODE >= '&f_year_code'
AND WFR_YEAR_CODE <= '&t_year_code'

Thanks
Re: Pass parameters to oracle SQL Query [message #568103 is a reply to message #568102] Tue, 09 October 2012 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 09 October 2012 15:31
Use ACCEPT command.

Regards
Michel


Re: Pass parameters to oracle SQL Query [message #568105 is a reply to message #568103] Tue, 09 October 2012 08:50 Go to previous messageGo to next message
ramtin
Messages: 37
Registered: November 2008
Member
How should I use ACCEPT command in SQL query ??
Re: Pass parameters to oracle SQL Query [message #568113 is a reply to message #568105] Tue, 09 October 2012 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not in a query in a SQL script as you said you call a SQL script (read your first post).

Regards
Michel
Re: Pass parameters to oracle SQL Query [message #568116 is a reply to message #568113] Tue, 09 October 2012 10:04 Go to previous messageGo to next message
ramtin
Messages: 37
Registered: November 2008
Member
Okay the problem is I have a query which I need to put parameter with Label for that. Please advise.

Thank you in advance

Mohammad
Re: Pass parameters to oracle SQL Query [message #568118 is a reply to message #568116] Tue, 09 October 2012 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
which I need to put parameter with Label for that


What does this mean?

Regards
Michel
Re: Pass parameters to oracle SQL Query [message #568122 is a reply to message #568118] Tue, 09 October 2012 10:24 Go to previous messageGo to next message
ramtin
Messages: 37
Registered: November 2008
Member
That mean is I have query that I would like when I run it it will come up and ask for parameter like

SQL>@test
Year from XXXX
To Year XXXX

I need to indicate the parameter for From and To year

Thanks
Re: Pass parameters to oracle SQL Query [message #568123 is a reply to message #568122] Tue, 09 October 2012 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So I think you didn't read the link I provided you.

Regards
Michel
Re: Pass parameters to oracle SQL Query [message #568212 is a reply to message #568123] Wed, 10 October 2012 04:42 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
If you read the link posted by Michel,You can understand.
----This My script content.
 --This will accept "Char" datatype as an Input and it will pront you when you execute the script as 'Please Enter start_date:'

Dev>host type e:\test.sql
undef start_date
accept start_date char prompt 'Please Enter start_date:'  
select to_date(&start_date,'yyyymmdd') from dual;

---Now I am calling that script from sqlplus

Dev>@ e:\test.sql
Please Enter start_date:20121010
old   1: select to_date(&start_date,'yyyymmdd') from dual
new   1: select to_date(20121010,'yyyymmdd') from dual

TO_DATE(2
---------
10-OCT-12

Dev>
Previous Topic: Not able to run SQL *Plus.
Next Topic: First Login with SQL DEVELOPER
Goto Forum:
  


Current Time: Fri Mar 29 10:44:38 CDT 2024