| Passing or getting parameter to SQL script [message #198947] |
Thu, 19 October 2006 05:12  |
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 #198957 is a reply to message #198947] |
Thu, 19 October 2006 05:45  |
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 |
joy_division
Messages: 4963
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 |
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
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
