There are at least six approaches you can take to the problem of conditional execution. These are:

Some of these approaches are very specific to certain types of problems. Using REFCURSOR variables, for example, is a good solution when you simply need to choose which query to run based on user input or some other condition. Other approaches, such as the use of a multilevel file structure for your script, are more general in nature, and can be used for any type of branching.

Suppose you are writing a script to delete all data from the project_hours table. Before you delete the data, you want to ask the user to confirm the operation. You really want to write something like this:

ACCEPT s_delete_confirm PROMPT 'Delete project hours data (Y/N)?'

IF s_delete_confirm = 'Y' THEN
   DELETE 
     FROM project_hours;
 END IF

Of course, you can’t do that! SQL*Plus has no IF statement, remember? However, you can add a WHERE clause to the DELETE statement that will have the same effect. Here’s an example:

SET VERIFY OFF

ACCEPT s_delete_confirm PROMPT 'Delete project hours data (Y/N)?'

DELETE 
  FROM project_hours
 WHERE UPPER('&&s_delete_confirm') = 'Y';

When you execute the script, the DELETE will always be executed. However, if the user answers with an N, the WHERE clause will always evaluate to FALSE, and no rows will be deleted. Verification is set off only to prevent SQL*Plus from echoing the line of the WHERE clause that references the substitution variable. The UPPER function is used in this case in order to allow the user’s response to be case-insensitive. Here’s how it looks when a user runs this script and doesn’t confirm the delete:

SQL> @delete_hours
Delete project hours data (Y/N)?n

0 rows deleted.

If you wanted to, you could even write an additional query to give the user an error message if the response to the prompt was not a Y or an N. Adding these lines immediately after the ACCEPT statement would do that for you:

SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SELECT 'You must answer with a Y or N.'
  FROM DUAL
 WHERE UPPER('&&s_delete_confirm') NOT IN ('Y','N')
    OR '&&s_delete_confirm' IS NULL;
SET FEEDBACK ON
SET PAGESIZE 1
SET HEADING ON

To make the results of this query look like an error message, both headings and pagination are turned off. Feedback is also turned off to avoid giving the “1 row selected” message to the user. After the SELECT executes, these settings are returned to their defaults. Now, here is what happens when you run the modified script and don’t answer with a Y or N:

SQL> @delete_hours
Delete project hours data (Y/N)?X
You must answer with a Y or N.

0 rows deleted.

This technique has the advantage of keeping your entire script in one file, but it’s pretty much limited to handling the case where you have several possible queries to execute and must choose the correct one based on input from the user.

If you want to present the user with a choice of reports to run, you can place the conditional logic within PL/SQL and use a REFCURSOR variable to return the selected query to SQL*Plus, where the results can be formatted and printed.

The following script gives the user a choice of three different reports. The conditional logic is implemented in a PL/SQL block, and the results are returned to SQL*Plus via bind variables. A REFCURSOR bind variable is used to return a query that generates the report requested by the user.

--DESCRIPTION
--Print one of three user security reports

SET FEEDBACK OFF
SET PAGESIZE 20
SET LINESIZE 77
SET HEADING ON

--Ask the user what report to print
PROMPT 
PROMPT 1 - List users
PROMPT 2 - List users and table privileges
PROMPT 3 - List users and system privileges
PROMPT
ACCEPT s_report_choice PROMPT 'Enter your choice (1,2,3) >'

--A PL/SQL block will set the b_report bind variable
--to a query based on the user's response. Text for the
--report title will be returned in b_report_type.
VARIABLE b_report REFCURSOR
VARIABLE b_report_type VARCHAR2(30)

--Interpret the user's choice.
BEGIN
  IF '&&s_report_choice' = '1' THEN
    --Return some text for the title to identify this report.
    :b_report_type := 'User Listing';

    --Return a query that will list all users.
    OPEN :b_report FOR 
      SELECT username 
        FROM dba_users
      ORDER BY username;
  ELSIF '&&s_report_choice' = '2' THEN
    --Return some text for the title to identify this report.
    :b_report_type := 'User Table Privileges';

    --Return a query that will list users and any
    --privileges they have on tables in the database.
    OPEN :b_report FOR
      SELECT username, privilege, owner, table_name
        FROM dba_users, dba_tab_privs
       WHERE username = grantee
      ORDER BY username, owner, table_name, privilege;
  ELSIF '&&s_report_choice' = '3' THEN
    --Return some text for the title to identify this report.
    :b_report_type := 'User System Privileges';

    --Return a query that lists users and any system
    --privileges they have been granted.
    OPEN :b_report FOR 
      SELECT username, privilege
        FROM dba_users, dba_sys_privs
       WHERE username = grantee
      ORDER BY username, privilege;
  ELSE
    --Return some text for the title to identify this report.
    :b_report_type := 'Invalid Report Choice';

    --The user made an invalid choice, so
    --return a query that will display an error message.
    OPEN :b_report FOR 
      SELECT 'You must choose either 1, 2, or 3' error_message
        FROM dual;
  END IF;
END;
/ 

--Specify formats for all possible report columns.
COLUMN username FORMAT A12 HEADING 'User'
COLUMN privilege FORMAT A20 HEADING 'Privilege'
COLUMN owner FORMAT A12 HEADING 'Table Owner'
COLUMN table_name FORMAT A30 HEADING 'Table Name'
COLUMN error_message FORMAT A40 HEADING 'Error Message'

--Set up the page title. First we have to get the contents of 
--b_report_type into a substition variable.
set termout off
COLUMN b_report_type FORMAT A30 NOPRINT NEW_VALUE s_report_type
SELECT b_report_type FROM dual;
set termout on

TTITLE LEFT s_report_type RIGHT 'Page ' FORMAT 999 SQL.PNO SKIP 2

--Run the report requested by the user
PRINT b_report

Note that the script contains COLUMN commands for all possible columns from the three different queries. These don’t need to be conditionally executed, because format definitions for columns not used in the final query are ignored by SQL*Plus. Also note that the PL/SQL code does return a query even for the case where the user’s input is invalid; this query simply selects an error message from the DUAL table. Here is the output from running this script, first showing the results of an invalid input, then showing the output from one of the reports:

SQL> @user_security

1 - List users
2 - List users and table privileges
3 - List users and system privileges

Enter your choice (1,2,3) >4

User Listing                                                        Page    1

Error Message
----------------------------------------
You must choose either 1, 2, or 3
SQL> @user_security

1 - List users
2 - List users and table privileges
3 - List users and system privileges

Enter your choice (1,2,3) >2

User Listing                                                        Page    1

User         Privilege            Table Owner  Table Name
------------ -------------------- ------------ ------------------------------
SYSTEM       EXECUTE              SYS          AQ$_AGENT
SYSTEM       EXECUTE              SYS          AQ$_DEQUEUE_HISTORY
SYSTEM       EXECUTE              SYS          AQ$_HISTORY
SYSTEM       EXECUTE              SYS          AQ$_RECIPIENTS
SYSTEM       EXECUTE              SYS          AQ$_SUBSCRIBERS
SYSTEM       EXECUTE              SYS          DBMS_AQADM
SYSTEM       EXECUTE              SYS          DBMS_AQ_IMPORT_INTERNAL
SYSTEM       EXECUTE              SYS          DBMS_DEFER_IMPORT_INTERNAL
SYSTEM       ALTER                SYS          INCEXP
SYSTEM       DELETE               SYS          INCEXP
SYSTEM       INDEX                SYS          INCEXP

In this example, the query output is only displayed on the screen. If you wanted to print it, you would just need to add a SPOOL command to send the output to a file, which you could later send to a printer.

The most generic and flexible approach to branching that you can implement using SQL*Plus is to write your script to execute one of several alternative files based on user input or other criteria. This is best explained by example, so here is a simplified version of the security reports menu shown previously in this chapter:

--Ask the user what report to print
PROMPT 
PROMPT 1 - List users
PROMPT 2 - List users and table privileges
PROMPT 3 - List users and system privileges
PROMPT
ACCEPT s_report_choice PROMPT 'Enter your choice (1,2,3) >'

--Execute the appropriate report
@user_security_&&s_report_choice

The key to this approach is in the last line, where the user’s response is used to form the name of another SQL file to execute. If the user chooses option 1, for example, the last line in the above script will be translated to:

@user_security_1

Of course, you have to make sure that a file named USER_SECURITY_1.SQL exists and that it will generate the correct report. When you use this approach to branching, you will end up with a set of script files that form an inverted tree structure. The tree diagram in Figure 7.1 shows the relationship between the menu script and the scripts that run the individual reports.

Figure 7-1. Structure for the security reports menu, using a multilevel file structure

Because this branching technique executes another SQL*Plus script, you can continue to ask the user questions, and even branch again depending on the user’s response. The one thing you have to watch out for is that SQL*Plus cannot nest scripts indefinitely. SQL*Plus can currently nest scripts only 20 levels deep, and some older versions only allow 5 levels of nesting.

A useful variation on this technique is to code it using a SELECT statement to analyze the user’s input and derive the name of the next script to call. You get two benefits from this: the script names are not directly linked to the user’s input, and it’s easier to designate one script to be called when the user makes an invalid choice. The only penalty is a small amount of added complexity in your script. The following script is an example of this technique:

--Ask the user what report to print
PROMPT 
PROMPT A - List users
PROMPT B - List users and table privileges
PROMPT C - List users and system privileges
PROMPT                                            
ACCEPT s_report_choice PROMPT 'Enter your choice (A,B,C) >'

--DECODE the user's input.
SET TERMOUT OFF
COLUMN user_choice NOPRINT NEW_VALUE s_next_script
SELECT DECODE (UPPER('&s_report_choice'),
               'A','USER_SECURITY_1.SQL',
               'B','USER_SECURITY_2.SQL',
               'C','USER_SECURITY_3.SQL',
               'USER_SECURITY_4.SQL') user_choice
  FROM DUAL;
SET TERMOUT ON 

--Execute the appropriate report
@@&s_next_script

The key to this script is the call to DECODE in the SELECT statement. DECODE is a SQL function that allows you to arbitrarily specify an output value for any given input value. In this case, the input value is UPPER(`&s_report_choice'). By using the UPPER function, we allow the user to respond in either uppercase or lowercase. Following the input are three value pairs, each specifying the output for a specific input value. An input of “A” causes this DECODE to return “USER_SECURITY_1.SQL”, an input of “B” causes “USER_SECURITY_2.SQL” to be returned, and so forth. The final value, “USER_SECURITY_4.SQL”, is returned if the user’s choice did not match any of the others. In this case, the file USER_SECURITY_4.SQL would display some sort of error message, telling the user what he did wrong.

If you decide to develop a set of scripts like this, it’s best to spend some time up front working out the structure before you begin scripting. Making changes after you’ve started writing a set of scripts like this can become cumbersome very quickly because so many files are involved. Try to keep things as modular as possible, too. In this example, any of the reports can be run as standalone scripts without going through the menu.

Another way to branch that also involves a multilevel file structure is simply to spool some output to a new SQL file, then execute that file. To implement the security report menu using this technique, you could spool one of three SELECT statements to a file based on the user’s report choice. Here’s a version of the script that does that:

--DESCRIPTION
--Print one of three user security reports

SET FEEDBACK OFF
SET PAGESIZE 20
SET LINESIZE 77
SET HEADING ON

--Ask the user what report to print
PROMPT 
PROMPT 1 - List users
PROMPT 2 - List users and table privileges
PROMPT 3 - List users and system privileges
PROMPT
ACCEPT s_report_choice PROMPT 'Enter your choice (1,2,3) >'

--Specify formats for all possible report columns.
COLUMN username FORMAT A12 HEADING 'User'
COLUMN privilege FORMAT A20 HEADING 'Privilege'
COLUMN owner FORMAT A12 HEADING 'Table Owner'
COLUMN table_name FORMAT A30 HEADING 'Table Name'
COLUMN error_message FORMAT A40 HEADING 'Error Message'

--Set up the page title. First we have to get the contents of 
--b_report_type into a substition variable.
set termout off
COLUMN b_report_type FORMAT A30 NOPRINT NEW_VALUE s_report_type
SELECT DECODE ('&&s_report_choice', 
               '1','User List',
               '2','User Table Privileges',
               '3','User System Privileges',
               'Invalid Choice') b_report_type
  FROM dual;
set termout on

TTITLE LEFT s_report_type RIGHT 'Page ' FORMAT 999 SQL.PNO SKIP 2

--Generate the query for the report requested by the user.
--Spool that query to a file.
SET TERMOUT OFF
SET PAGESIZE 0
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
COLUMN next_query FORMAT A60
SPOOL user_security_choice.sql

--This query will be successful if the user chooses 1
SELECT 'SELECT username ' || CHR(10) ||
       '  FROM dba_users ' || CHR(10) ||
       'ORDER BY username;' || CHR(10) next_query
  FROM dual
 WHERE '&&s_report_choice' = '1';

--This query will be successful if the user chooses 2
SELECT 'SELECT username, privilege, owner, table_name' || CHR(10) ||
       '  FROM dba_users, dba_tab_privs' || CHR(10) ||
       ' WHERE username = grantee' || CHR(10) ||
       'ORDER BY username, owner, table_name, privilege;'
  FROM dual
 WHERE '&&s_report_choice' = '2';

SELECT 'SELECT username, privilege' || CHR(10) ||
       '  FROM dba_users, dba_sys_privs' || CHR(10) ||
       ' WHERE username = grantee' || CHR(10) ||
       'ORDER BY username, privilege;' 
  FROM dual
 WHERE '&&s_report_choice' = '3';

SELECT 'PROMPT You must choose either 1, 2, or 3'
  FROM dual
 WHERE '&&s_report_choice' NOT IN ('1','2','3')
    OR '&&s_report_choice' IS NULL;

SPOOL OFF
SET TERMOUT ON
SET PAGESIZE 20
SET HEADING ON
SET VERIFY ON

--Now execute the query that we just spooled.
@user_security_choice

--Reset all the settings back to their defaults
SET FEEDBACK ON
CLEAR COLUMNS
TTITLE OFF

You have to be very careful when using this technique to turn off anything that could cause extraneous text to be written to the temporary command file. This includes page headings, column headings, and verification. That’s why the script in the example included these commands:

SET TERMOUT OFF
SET PAGESIZE 0
SET HEADING OFF
SET VERIFY OFF

Terminal output was turned off to prevent the user from seeing the results of the SELECT on the display. One last thing you have to worry about is the filename itself. In the example shown above, the filename is hardwired into the script and does not include a path. Because no path is specified, the file will be written to the current directory. That’s why a single ampersand or @ was used to run the intermediate file. Using @ causes SQL*Plus to look in the current directory for the script.

Having the filename hardwired into the script can cause problems if multiple users execute the script at the same time and from the same directory. If you are concerned about this, you could write some SQL or PL/SQL code to generate a unique filename based on the Oracle username or perhaps the session identifier (SID) from the V$SESSION view.

Be creative with this technique. You don’t need to limit yourself to writing SQL*Plus scripts, either. You can use SQL*Plus to generate shell script files, SQL*Loader files, DOS batch files, or any other type of text file.

Always consider the possibility of using PL/SQL to implement any type of complex procedural logic. After all, that’s the reason PL/SQL was invented in the first place. If you can manage to prompt the user up front for any needed information, and if you don’t need to interact with the user during the operation, PL/SQL is the way to go.

An ideal candidate for the use of PL/SQL would be the example where we asked the user a simple yes/no question, and then deleted data from the PROJECT_HOURS table if the user responded with a Y. Here’s how that script looked:

SET VERIFY OFF

ACCEPT s_delete_confirm PROMPT 'Delete project hours data (Y/N)?'

DELETE 
  FROM project_hours
 WHERE UPPER('&&s_delete_confirm') = 'Y';

This script works, and because the DELETE statement is so simple, it’s not too hard to understand. Still, there are people who would look at it and become very confused. The more complicated the WHERE clause gets, the greater the likelihood of confusion. Wrapping a simple IF statement, which everyone understands, around the DELETE statement would add clarity to the script. The PL/SQL version of the above script looks like this:

SET VERIFY OFF

ACCEPT s_delete_confirm PROMPT 'Delete project hours data (Y/N)?'

SET SERVEROUTPUT ON

DECLARE
  users_yn_response CHAR := UPPER('&&s_delete_confirm'),
BEGIN
  IF users_yn_response = 'Y' THEN
    DELETE 
      FROM project_hours;
    COMMIT;

    DBMS_OUTPUT.PUT_LINE('All PROJECT_HOURS data has been deleted.'),
  ELSIF users_yn_response = 'N' THEN
    DBMS_OUTPUT.PUT_LINE('No data was deleted.'),
  ELSE
    DBMS_OUTPUT.PUT_LINE('You must answer with a Y or N.'),
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('The PROJECT_HOURS data could not be deleted. ' 
                         || SQLERRM);
    ROLLBACK;
END;
/

This script is a bit longer, but it’s also more robust. The script will roll back the operation if the DELETE fails for any reason. It’s also very clear now that the DELETE statement is going to delete all rows from the table.

Don’t overlook the possibility that you can use your operating-system scripting language to good advantage. Any Unix shell will allow you to write more complex scripts than you could using SQL*Plus alone. Here’s an implementation of the user security report menu using the Unix Korn shell:

while : 
        do 
        print " " 
        print "1 - List users" 
        print "2 - List users and table privileges" 
        print "3 - List users and system privileges" 
        print "4 - Quit" 
        print  
        print -n "Enter your choice (1,2,3,4) > " 
  
        read 

        case $REPLY in 
                1 ) 
                        sqlplus -s jgennick/beaner @user_security_1 
                        ;; 
                2 ) 
                        sqlplus -s jgennick/beaner @user_security_2 
                        ;; 
                3 ) 
                        sqlplus -s jgennick/beaner @user_security_3 
                        ;; 
                4 ) 
                        exit 
                        ;; 
                * ) 
                        print "Please enter 1, 2, 3, or 4" 
                        ;; 
        esac 
done

Perl is also something you should consider. The Perl scripting language is available for both Unix and Windows. It has the advantages of being widely used, and of not tying you to one specific operating system.