How to set the default value for the missing substitution parameter

advertisements

I'm calling a procedure in a script and passing variables like this:

@@./procedudure.sql 'var1' 'var2' 'var3'

In the procedure I'm using the values like this:

DEFINE variable1 = '&1'
DEFINE variable2 = '&2'
DEFINE variable3 = '&3'
...
insert into TABLE (id, text) values ('&varibale1', '&variable2&variable3')

I want to be able to call the procedure with only two parameters instead of 3 so that the last one is replaced with empty string. But when I call it like this, I get this prompt:

Enter value for 3:

I've also tried to use the parameters like this, but with the same result:

DEFINE variable2 = '&2' || '&3'

I've found this page for using prompt for missing variable, but couldn't find anywhere how to set a default value: https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#9_14

As you can see from the code, I want the last parameters to be concatenated. This should be a workaround for 240-character limit.


When you code it as DEFINE variable3 = '&3' in your procedure, the system thinks 3 is variable and it will always prompt for its value.

In case you want to pass a default value for 3, you should do DEFINE 3 = YOUR_VALUE_FOR_PARAM_3