ORACLE SQL: Pause Execution until a user input

Question

I have a Oracle SQL script like this:

• Display some values from a table
• want to pause here until I can review the values from the table in above step, and hit a button to keep on executing. If the value looks good to me, I want to break the SQL script right there.
• execute some cleanup because the values does not look good, need to fix.

Is there anything like that in Oracle SQL? sort of like pause in a batch file, you have to hit space to keep on running, but I can cancel it

Answer 1

In SQLPlus you can build a script that accepts some value and then decides what to da based on the input data.

For example, a script like:

d:\x.sql:

set verify off;
select 'some data' from dual;
/* prompt a message and ask for a value to store in a variable */
accept choice prompt 'Update data (Y/N)?'; 
begin
    if '&choice' = 'Y' then             /* check the variable value */
        dbms_output.put_line('Update data');
    else
        dbms_output.put_line('Exit');
    end if;
end ;
/

Will do:

SQL> sta d:\x

'SOMEDATA
---------
some data
Update data (Y/N)?Y
Update data
SQL> sta d:\x

'SOMEDATA
---------
some data
Update data (Y/N)?N
Exit