how to force user to enter value for substitution variable?

Question

I have script which is SQLPLUS form. I ask user to enter values for substitution variables to execute insert statement.

some substitution variables are used for mandatory columns, so

how can I ask the user to fill again if substitution variable value is null?

how to assign a value to the same substitution variable depending on it's value itself by forcing user to enter it?

or how to force user to enter value for substitution variable ?

something like

psedu code:

accept x prompt please enter value for x
 while (&x is null) loop
       accept x prompt please enter value for x
 end loop
insert into ...

is it possible

I don't think you can. You can specify a data type, but I don't think you can stop it being null. Is forcing the script to terminate if no value is provided a suitable alternative? (Although the insert will fail anyway presumably...) — Alex Poole, Aug 15 '17 at 08:13

score 1 · Accepted Answer · answered Aug 15 '17 at 13:04

This is crazy but... based on this answer I gave to another question you can create a script called main.sql something like this:

accept x prompt please enter value for x
accept y prompt please enter value for y
accept z prompt please enter value for z

set term off verify off

column script new_value v_script

select case when '&&x.' is null or '&&y.' is null or '&&z.' is null
         then 'main'
         else 'do_insert' 
         end script
  from dual;

set term on verify on

@@&v_script.

Then create another script called do_insert.sql like this:

insert into mytable (a, b, c) values ('&&x.', '&&y.', '&&z.');

The main.sql script will keep re-running itself until all of X, Y and Z are not null - making the user re-enter all of them each time. Once all are not null, it will run do_insert.sql which uses the values.

Note that each time main.sql is re-run it is a nested call; SQL Developer (for example) fails with an error eventually if the user keeps omitting values:

SP2-0309: SQLcl command procedures may only be nested to a depth of 20.

But really, if you want a decent user interface, SQL Plus scripts are not the way to build one.

Recursion call, I like thinking out of the box, definitely this creative crazy answer answers my crazy question. ;) thanks a lot . — Dev X, Aug 15 '17 at 20:03

how to force user to enter value for substitution variable?

1 Answers1