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

Answer 1

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.