Accept input and pass into a query : ACCEPT « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. ACCEPT

SQL> set termout on
SQL>
SQL> ACCEPT continue_flag CHAR PROMPT 'Do you wish to DROP the tables first (Y/N)?'
Do you wish to DROP the tables first (Y/N)?
SQL> define flag1 = 'n'
SQL> SET TERMOUT OFF
SQL> COLUMN continue_flag NEW_VALUE continue_flag
SQL> SELECT LOWER('&continue_flag') continue_flag FROM dual;
old   1: SELECT LOWER('&continue_flag') continue_flag FROM dual
new   1: SELECT LOWER('') continue_flag FROM dual



C
-


1 row selected.

SQL> SET TERMOUT ON
SQL>
SQL> SET SERVEROUTPUT ON
SQL> PROMPT

SQL> BEGIN
  2    IF '&flag1' = 'n' THEN
  3      DBMS_OUTPUT.PUT_LINE('You must answer either Y or N.');
  4      DBMS_OUTPUT.PUT_LINE('Please rerun the command file and answer correctly.');
  5    END IF;
  6  END;
  7  /
old   2:   IF '&flag1' = 'n' THEN
new   2:   IF 'n' = 'n' THEN
You must answer either Y or N.
Please rerun the command file and answer correctly.

PL/SQL procedure successfully completed.

SQL>
SQL> --Let the calling script know that we were successful.
SQL> define flag2 = 'Y'
SQL>
SQL>