Home » SQL & PL/SQL » SQL & PL/SQL » Passing or getting parameter to SQL script  () 1 Vote
Passing or getting parameter to SQL script [message #198947] Thu, 19 October 2006 05:12 Go to next message
KCee
Messages: 16
Registered: October 2006
Junior Member
I know how to pass a parameter to an SQL script: &1.

But how can I use an SQL script that can do both:
1. Getting parameter 1 if not passed
2. Using parameter 1 is passed
in a nice way? See below:

If I use for instance '&value' in the script,
the script doesn't know that parameter 1 is for that value.
But if I use '&1' I get that terrible message:
'Enter value for 1:'

So, how should it be to get a good question
example:
define ts=&1
select TABLESPACE_NAME,STATUS from sys.dba_data_files
where TABLESPACE_NAME = '&ts';

This will go ok when passing parameter SYSTEM:
sqlplus / as sysdba @show_tablespace SYSTEM

but without parameter:
Enter value for 1: ...

--> How to get a correct question?

Thanks, KCee
Re: Passing or getting parameter to SQL script [message #198955 is a reply to message #198947] Thu, 19 October 2006 05:38 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
http://www.orafaq.com/node/515

You can use ACCEPT with PROMPT.

By
Vamsi
Re: Passing or getting parameter to SQL script [message #198957 is a reply to message #198947] Thu, 19 October 2006 05:45 Go to previous messageGo to next message
KCee
Messages: 16
Registered: October 2006
Junior Member
For getting a correct question your right:
accept ts char prompt 'Enter Tablespace name:'
select TABLESPACE_NAME,STATUS from sys.dba_data_files
where TABLESPACE_NAME = upper('&ts');

will give a correct question.
But now I can't submit a parameter from the prompt:

sqlplus / as sysdba @show_tablespace.sql system

will not work now.
Re: Passing or getting parameter to SQL script [message #198969 is a reply to message #198957] Thu, 19 October 2006 07:59 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
KCee wrote on Thu, 19 October 2006 06:45

sqlplus / as sysdba @show_tablespace.sql system

will not work now.


This will work, but in your script, you have to have:
define var1 = &1 -- this is the first parameter you send

then use &var1 in your query.

Or you can skip the DEFINE statement altogether and use the &1 directly in the query.

Re: Passing or getting parameter to SQL script [message #198970 is a reply to message #198969] Thu, 19 October 2006 08:04 Go to previous message
KCee
Messages: 16
Registered: October 2006
Junior Member
No, now only sending 1 parameter is ok:

define var1 = &1 -- this is the first parameter you send
select TABLESPACE_NAME,STATUS from sys.dba_data_files
where TABLESPACE_NAME = upper('&var1');

because when I don't pass parameter 1, I'll get the
stupid question again:
Enter value for 1:

So, what I want is:
if ( <parameter1> is passed)
then
var1 = <parameter1>
else
# Show correct question instead of 'Enter value for 1:'
Enter name of the tablespace : _
fi
Previous Topic: GROUP BY
Next Topic: TABLESPACE REG.
Goto Forum:
  


Current Time: Sun Dec 04 19:06:45 CST 2016

Total time taken to generate the page: 0.12050 seconds