Oralce PL/SQL: Prompt for choice in execution

person Jason Huangfolder_openCode, Oracle, SQLlocal_offer, , , access_time February 17, 2017

In Oracle PL/SQL, you can put in your script that would give the user a choice to either go on or quit. The following example gives you a prompt then a user choice of Y or N. Based on the user choice Y or N. Selected block is executed.

 


SET ECHO OFF
SET FEEDBACK ON
SET PAGES 99999
SET verify off
 
Spool AUDIT_INCREMENTS_FIX.log
 
select 'Current Audit related Increments in table: ' FROM dual;
select MINOR, LASTVAL FROM INCREMENTS where MAJOR = 'AUDITOR';
 
select 'Max DATA: ' || max(DATA) from AUDIT_DATA;
select 'Max EVENT: ' || max(EVENT) from AUDIT_EVENT;
select 'Max TRANSACTION: ' || max(TRANSACTION) from AUDIT_TRANSACTION;
 
accept choice prompt 'Fix Increments for audit (Y/N)?';
begin
    if '&choice' = 'Y' then
        -- update the increments
        update INCREMENTS set LASTVAL = (select max(DATA) from AUDIT_DATA) 
        WHERE MAJOR = 'AUDITOR' and MINOR = 'DATA';
 
        update INCREMENTS set LASTVAL = (select max(EVENT) from AUDIT_EVENT) 
        WHERE MAJOR = 'AUDITOR' and MINOR = 'EVENT';
 
        update INCREMENTS set LASTVAL = (select max(TRANSACTION) from AUDIT_TRANSACTION) 
        WHERE MAJOR = 'AUDITOR' and MINOR = 'TRANSACTION';
 
        commit;
    else
        SELECT 'Did not update.' from dual;
    end if;
end ;
 
 
SELECT 'Audit related Increments After the fix: ' from dual;
SELECT * FROM INCREMENTS where MAJOR = 'AUDITOR';
 
spool off

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>