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 ;
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.