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