oracle plsql if not found repeat

Question

set verify off

accept project prompt ' project : '
select locknr,description,couserid,ciuserid from dgdtw_lockedinfo where     
description = '&project' and ciuserid is null;

accept lock prompt ' locknumber  : '

update dgdtw_lockedinfo set ciuserid = couserid where locknr = &lock;
update dgdtw_topografie set locknr = '' where locknr = &lock;
update dgdtw_topografie set verval=sysdate where id= &lock;
commit;

accept var prompt 'repeat process?  [Y/N] ? '
define doit = 'H:\Scripts\stop.sql'
column doit new_value doit noprint
set termout off
select 'H:\Scripts\unlock.sql' doit from dual where upper('&var') like 'Y%';
set termout on
start &doit.

I need a loop script so if project is empty or wrong, the script asks to repeat or stop. Something like:

accept var prompt 'project number is wrong try again?  [Y/N] ? '

Until project number is correct or answer is "N"

sql*plus is somewhat interactive, but loops are a pl/sql construct that are executed on the server as a block, so you can't have ongoing user interaction with them. That said - it CAN be done as shown here, but I'm not sure that this is a great option: http://stackoverflow.com/questions/1870670/how-to-loop-accepting-user-input-with-pl-sql — Michael Broughton, Jan 21 '16 at 15:06

score 1 · Accepted Answer · answered Jan 28 '16 at 12:30

The example below will immediately restart unlock.sql when there are no locks for that project, by redefining the call to unlock.sql as a call to empty.sql whenever at least one row is returned.

set verify off

accept project prompt ' project : '

define doit = 'H:\Scripts\unlock.sql'
column doit new_value doit noprint
select 'H:\Scripts\empty.sql' as doit, locknr,description,couserid,ciuserid from dgdtw_lockedinfo where     
description = '&project' and ciuserid is null;
start &doit. 

accept lock prompt ' locknumber  : '

update dgdtw_lockedinfo set ciuserid = couserid where locknr = &lock;
update dgdtw_topografie set locknr = '' where locknr = &lock;
update dgdtw_topografie set verval=sysdate where id= &lock;
commit;

accept var prompt 'repeat process?  [Y/N] ? '
define doit = 'H:\Scripts\stop.sql'
column doit new_value doit noprint
set termout off
select 'H:\Scripts\unlock.sql' doit from dual where upper('&var') like 'Y%';
set termout on
start &doit.

As an improvement I suggest moving the repeat question into a separate SQL file then call that using an argument that tells it which script to restart (see https://docs.oracle.com/cd/B10501_01/server.920/a90842/ch13.htm#1013716)

For instance, from 'unlock.sql' call 'repeat.sql' like this:

start 'repeat.sql' unlock

with repeat.sql as something like this:

accept var prompt 'repeat &1 process?  [Y/N] ? '
define doit = 'H:\Scripts\stop.sql'
column doit new_value doit noprint
set termout off
select 'H:\Scripts\&1.sql' doit from dual where upper('&var') like 'Y%';
set termout on
start &doit.

oracle plsql if not found repeat

1 Answers1

Linked