Accept input and pass into a query : Accept « SQL Plus « Oracle PL / SQL






  1. Oracle PL / SQL
  2. SQL Plus
  3. Accept

Accept input and pass into a query

   



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>








Related examples in the same category

1.ACCEPT variable value from user input
2.Use accept to get user input value and use them in a select statement
3.ACCEPT command creates a variable of datatype CHAR by default
4.Using the ACCEPT command
5.Read user input and use if to determine which table to print
6.Read value from prompt and output with pl/sql