Thanks for the question, Tony.

Asked: September 02, 2016 - 6:05 pm UTC

Last updated: September 09, 2016 - 1:56 am UTC

Version: 12.1.0.1.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'd like to be able to run a script in SQLPlus with an input parameter (i.e. SQL> @My_Proc ABC).

However, if I do not enter the input parameter, SQLPlus displays a prompt: Enter a Value for 1:

How do I suppress the SQLPLus prompt and still be able to use it in the script below?

begin
if trim('&&1') is null then
dbms_output.put_line('#1 cannot be null');
else
dbms_output.put_line('You entered: ' || '&&1');
end if;
end;
/

Note that if I use SET DEFINE OFF, I do not get the value even if I pass the value in as input parameter.

Thanks for your help.
Tony

and Chris said...

When you "set define on", Oracle interprets ampersands as substitution variables. To use them Oracle needs to know their values.

If they're not already define it'll prompt you. To overcome this there are a couple of methods you could use:

Pass an empty string as arguments to the script:

SQL> ho cat define.sql
begin
  if trim ( '&&1' ) is null then
    dbms_output.put_line ( '#1 cannot be null' ) ;
  else
    dbms_output.put_line ( 'You entered: ' || '&&1' ) ;
  end if;
end;
/

SQL> @define ""
old   2:   if trim ( '&&1' ) is null then
new   2:   if trim ( '' ) is null then
old   5:     dbms_output.put_line ( 'You entered: ' || '&&1' ) ;
new   5:     dbms_output.put_line ( 'You entered: ' || '' ) ;
#1 cannot be null

PL/SQL procedure successfully completed.

Define the variable before calling it. Once you've done this it will keep the value until you end the session or undefine it:

SQL> define 1 = ""
SQL> @define
old   2:   if trim ( '&&1' ) is null then
new   2:   if trim ( '' ) is null then
old   5:     dbms_output.put_line ( 'You entered: ' || '&&1' ) ;
new   5:     dbms_output.put_line ( 'You entered: ' || '' ) ;
#1 cannot be null

PL/SQL procedure successfully completed.

SQL> select '&1' from dual;
old   1: select '&1' from dual
new   1: select '' from dual

'
-

SQL> undefine 1
SQL> @define
Enter value for 1: abc
old   2:   if trim ( '&&1' ) is null then
new   2:   if trim ( 'abc' ) is null then
old   5:     dbms_output.put_line ( 'You entered: ' || '&&1' ) ;
new   5:     dbms_output.put_line ( 'You entered: ' || 'abc' ) ;
You entered: abc

PL/SQL procedure successfully completed.

http://docs.oracle.com/database/121/SQPUG/ch_twelve040.htm#SQPUG073
http://docs.oracle.com/database/121/SQPUG/ch_twelve017.htm#SQPUG037

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Tony, September 06, 2016 - 2:33 pm UTC

Thanks Tom, for a quick response. I should have been more clear with my question, and I apologize for that.

Method 1 (pass an empty string) works fine. However, is it possible not having to pass an empty string?

SQL> @define
Result: Enter value for 1:
Expected Result: #1 cannot be null

SQL> @define ABC
Result (as expected): You entered: ABC

With method 2 (define a variable), I'm not sure what I'm doing wrong but I do not get the expected result either. Below is the script and results:

define 1 = ""
begin
if trim ( '&&1' ) is null then
dbms_output.put_line ( '#1 cannot be null' ) ;
else
dbms_output.put_line ( 'You entered: ' || '&&1' ) ;
end if;
end;
/
undefine 1

SQL> @define
Result (as expected): #1 cannot be null

SQL> @define ABC
Result: #1 cannot be null
Expected Result: You entered: ABC

September 06, 2016 - 3:53 pm UTC

You've defined 1 inside the script. This overrides the definition outside the script.

If you want to use the "pre-define" 1 method, you need to do this as part of the process that calls your script. Not the script itself!

A reader, September 08, 2016 - 1:41 pm UTC

Thanks Tom, but this is a stand-alone script to be run in SqlPlus, there's no other process.

Thanks again for all your help.

Regards,
Tony

September 08, 2016 - 4:08 pm UTC

It's Chris answering, but what's in a name? ;)

Anyway, as part of whatever you're building to call the script could specify the define *before* you call it. Then when you call the script, if the input is null you won't be prompted. And if it's not it'll still pickup the value you passed:

SQL> define 1 = ""
SQL> @define
old   2:   if trim ( '&&1' ) is null then
new   2:   if trim ( '' ) is null then
old   5:     dbms_output.put_line ( 'You entered: ' || '&&1' ) ;
new   5:     dbms_output.put_line ( 'You entered: ' || '' ) ;
#1 cannot be null

PL/SQL procedure successfully completed.

SQL> @define abc
old   2:   if trim ( '&&1' ) is null then
new   2:   if trim ( 'abc' ) is null then
old   5:     dbms_output.put_line ( 'You entered: ' || '&&1' ) ;
new   5:     dbms_output.put_line ( 'You entered: ' || 'abc' ) ;
You entered: abc

PL/SQL procedure successfully completed.

A reader, September 08, 2016 - 6:12 pm UTC

Thanks Chris. I really appreciate your effort. As I've mentioned before, there's no process that calls the script. The script is to be run directly from SQLPlus prompt. User will log into the database via SQLPlus and run the script (i.e. SQL> @define), nothing before and nothing after. I'd like to keep the "process" as simple as possible as it'll be run by a user who will forget how to do it in 3 days and then wonder why the script doesn't work ;)

Anyways, as you have suggested, I've pre-defined 1 before running the script. Please see the outputs below:

SQL> define 1 = ""
SQL> @define
#1 cannot be null

SQL> @define abc
You entered: abc

But here's the problem - it still picks up the last value, which I'd like to avoid, when there's no input.

SQL> @define
You entered: abc

Unless I re-pre-define 1

SQL> define 1 = ""
SQL> @define
#1 cannot be null

The closest I could get the script to work the way I wanted it to work is to add DEFINE 1 at the end of the script:

begin
if trim ( '&&1' ) is null then
dbms_output.put_line ( '#1 cannot be null' ) ;
else
dbms_output.put_line ( 'You entered: ' || '&&1' ) ;
end if;
end;
/
define 1 = ""

Then I pre-define 1 and run the script. Note that this time, I do not have to re-define 1 as it has already been done from inside the script.

SQL> define 1 = ""
SQL> @define
#1 cannot be null

SQL> @define abc
You entered: abc

SQL> @define
#1 cannot be null

SQL> @define 123
You entered: 123

SQL>

Thanks again.
Tony

September 09, 2016 - 1:56 am UTC

Is something like this an acceptable compromise for the users:

SQL> @c:\temp\what_the_user_runs
what table name are you after TE
old   2:   if '&&1' = 'MISSING' then
new   2:   if 'TE' = 'MISSING' then

PL/SQL procedure successfully completed.

old   3: where tname like '&&1%'
new   3: where tname like 'TE%'

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEXT_TAB                       TABLE
TEST_XML                       TABLE
TEST_VIEW                      VIEW
TEST_TAB_X                     TABLE
TEST_STATS                     TABLE
TEST_RANK                      TABLE
TEST_FLUSH                     TABLE
TESTEMP                        TABLE
TEACHER                        TABLE

9 rows selected.

SQL> @c:\temp\what_the_user_runs
what table name are you after
old   2:   if '&&1' = 'MISSING' then
new   2:   if 'MISSING' = 'MISSING' then
begin
*
ERROR at line 1:
ORA-20000: You didnt supply a parameter
ORA-06512: at line 3


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

what_the_user_runs.sql
======================
set define '&'
accept xxx char default MISSING prompt 'what table name are you after '
@@my_real_script &&xxx

my_real_script.sql
==================
whenever sqlerror exit
begin
if '&&1' = 'MISSING' then
raise_application_error(-20000,'You didnt supply a parameter');
end if;
end;
/
whenever sqlerror continue
select *
from tab
where tname like '&&1%';

Cheers,
Connor

A reader, September 09, 2016 - 1:47 pm UTC

Thanks Connor. That is one of the options I had proposed but unfortunately no prompt is a requirement.

Regards,
Tony

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Use of & and prompt

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs