SQLPLUS use optional parameters

Question

Currently, I call an SQL File from a CMD file and transfer some parameters during the call. The code below works if I actually pass a value or press enter. However, my CMD/SQL will stop and wait for the parameter if there was none given. In such a case I would like to continue with default values instead.

CMD Code:

REM this works
sqlplus !dbuser! @!some_dir!\some_sql_file.sql test_text >> !log!

REM this STOPS and waits until user interaction happens
sqlplus !dbuser! @!some_dir!\some_sql_file.sql >> !log!

SQL Code:

set serveroutput on

SET LINESIZE 10000

declare
    l_some_text varchar2(1000);
begin

     select nvl('&&3','no_text_given') into l_some_text from dual;

    dbms_output.enable;
    dbms_lock.sleep(1);
    dbms_output.put_line('SQL uses: ' || l_some_text );

end ;

Littlefoot · Accepted Answer · 2023-02-06 21:08:15Z

3

Parameter can't be 3; there's only one, so it is supposed to be 1.

Here's option you might be interested in.

a.sql file:

set serveroutput on
set ver off

set termout off
  column 1 new_value 1
  select null as "1" from dual where 1 = 2;
set termout on

declare
    l_some_text varchar2(1000);
begin
   select nvl('&1', 'no_text_given') into l_some_text from dual;
   dbms_output.put_line('SQL uses: ' || l_some_text );
end ;
/
exit;

Let's test it: with parameter passed to it:

c:\temp>sqlplus -s scott/tiger@pdb1 @a.sql littlefoot
SQL uses: littlefoot

PL/SQL procedure successfully completed.

Without parameter:

c:\temp>sqlplus -s scott/tiger@pdb1 @a.sql
SQL uses: no_text_given

PL/SQL procedure successfully completed.


c:\temp>

Adjust it, if you have to (e.g. redirect to file; remove various settings I used, ...) but - generally - that's what you asked for.

answered Feb 6, 2023 at 21:08

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Frey Over a year ago

Hi Littlefoot, sorry for the &3 - I actually used more parameters and failed to simplify correctly. So basically, the VER and TERMOUT are what I was missing. I will test this and come back.

Magoo · Accepted Answer · 2023-02-06 21:25:09Z

0

In cmd,

if not defined parameter set "parameter=defaultvalue"
sqlplus !dbuser! @!some_dir!\some_sql_file.sql !parameter! >> !log!

if parameter has not been set, uses default. You haven't shown us how you provide test_text to the cmd procedure; you would need to assign its value to parameter

answered Feb 6, 2023 at 21:25

Magoo

80.8k8 gold badges68 silver badges92 bronze badges

1 Comment

Peter Frey Over a year ago

Hi, Magoo. Basically, I want to provide generic tools for me and my colleagues. So "test_text" would be one of the parameters that we hardcode when calling some_sql_file.sql. It might be convenient to skip the last parameter once in a while.

Collectives™ on Stack Overflow

SQLPLUS use optional parameters

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related