Branching in SQL*Plus

SQL*Plus has no IF statement, which is vexing. Script writing is similar to programming. It's natural to want to take different actions depending on user input or some other condition. Imagine how frustrated you would be if your favorite programming language suddenly lost its IF statement. Despite the lack of an IF statement in SQL*Plus, you can take some approaches to get equivalent results. Some are more straightforward than others. All involve some compromises.

You can take at least six approaches to the problem of conditional execution:

• Simulate branching by adjusting the WHERE clause in a query.
• Use REFCUSOR variables .
• Use a multilevel file structure.
• Use SQL to write SQL.
• Use PL/SQL for conditional logic.
• Use an operating-system scripting language.

Some of these approaches are specific to certain types of problems. Using REFCURSOR variables, for example, is a good solution when you 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.

11.2.1 Simulating Branching by Adjusting the WHERE Clause

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 want to write something like the following:

ACCEPT s_delete_confirm PROMPT 'Delete project hours data (Y/N)?' IF s_delete_confirm = 'Y' THEN DELETE FROM project_hours; END IF

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, as shown in Example 11-19.

Example 11-19. Embedding conditional logic into a WHERE clause

SET ECHO OFF 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 be executed. However, if the user answers with anything but Y , the WHERE clause will evaluate to FALSE and no rows will be deleted. Verification is set off 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 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 want to, you can write an additional query to give the user an error message if the response to the prompt was not Y or N . Example 11-20 shows a version of the script that does this.

Example 11-20. Adding an error message

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 the query in Example 11-20 look like an error message, the headings and pagination are turned off. Feedback is turned off to avoid giving the "1 row selected" message to the user. After the SELECT executes, these settings are returned to their defaults. Here is what happens when you run the modified script and don't answer with Y or N :

SQL> @ex11-20 SQL> SET ECHO OFF Delete project hours data (Y/N)? bad 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 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. You must think about the performance implications from the full tablescans that this technique is likely to unleash .

11.2.2 Simulate Branching by Using REFCURSOR Variables

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. Example 11-21 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.

Example 11-21. Implementing a choice of reports via REFCURSOR variables

--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 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

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. 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.

The following 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> @ex11-21 1 - List users 2 - List users and table privileges 3 - List users and system privileges Enter your choice (1,2,3) > 4 Invalid Report Choice Page 1 Error Message ---------------------------------------- You must choose either 1, 2, or 3 SQL> @ex11-21 1 - List users 2 - List users and table privileges 3 - List users and system privileges Enter your choice (1,2,3) > 2 User Table Privileges Page 1 User Privilege Table Owner Table Name ------------ -------------------- ------------ ------------------------------ BI SELECT OE BOMBAY_INVENTORY BI SELECT OE CUSTOMERS BI SELECT OE INVENTORIES BI SELECT OE ORDERS B

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

11.2.3 Branching Using a Multilevel File Structure

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 Example 11-22 shows a simplified version of the security reports menu shown previously in Example 11-21.

Example 11-22. Branching via a multilevel file structure

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 @ex11-22_&&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 this:

@ex11-22_1

You have to make sure that a file named ex11-22_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 11-1 shows the relationship between the menu script and the scripts that run the individual reports.

Figure 11-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 branch again depending on the user's response. The one thing you have to watch for is that SQL*Plus can't nest scripts indefinitely. SQL*Plus can currently nest scripts only 20 levels deep, and some older versions allow only five 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 designating one script to be called when the user makes an invalid choice is easier. The penalty is a small amount of added complexity in your script. Example 11-23 shows this technique.

Example 11-23. Using a SELECT statement to get user input

SET ECHO OFF --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','ex11-22_1.sql', 'B','ex11-22_2.sql', 'C','ex11-22_3.sql', 'ex11-23_bad.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, you allow the user to respond in uppercase or lowercase. Following the input are three value pairs, each specifying the output for a specific input value. An input of "A" causes DECODE to return "ex11-22_1.sql," an input of "B" causes it to return "ex11-22_2.sql," and so forth. The final value, "ex11-23_bad.sql," is returned if the user's choice doesn't match any of the others. In this case, that script displays an 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 written a set of nested scripts can become cumbersome because so many files are involved. Keep things as modular as possible. In this example, any of the reports can be run as standalone scripts without going through the menu.

11.2.4 Using SQL to Write SQL

Another way to branch that also involves a multilevel file structure is to spool some output to a new SQL file and execute that file. To implement the security report menu using this technique, you can spool one of three SELECT statements to a file based on the user's report choice. Example 11-24 presents a version of the script that does that.

Example 11-24. Implementing conditional logic by writing, then executing, a new script

--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 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. You'll want to turn off terminal output to prevent the user from seeing the results of the SELECT on the display. This is why the script in Example 11-24 includes these commands:

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

One last thing you have to worry about is the filename itself. In Example 11-24, the filename is hardwired into the script and doesn't include a path. Because no path is specified, the file will be written to the current directory. That's why @, a single at sign, was used to run the intermediate file. Using @ causes SQL*Plus to look in the current directory for the script.

However, having the filename hardwired into the script can cause problems if multiple users execute the script simultaneously and from the same directory. If you are concerned about this, you can 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 data dictionary view.

11.2.5 Using PL/SQL

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. 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.

Example 11-19 presents an ideal candidate for the use of PL/SQL because that script asks the user a simple yes/no question and proceeds to delete or not delete data from the project_hours table. One problem with that approach is the potential performance impact from a needless, full table scan. Another problem is that the predicate to implement the conditional logic can be hard to spot in a complex WHERE clause, making such queries difficult to fathom, at least at first glance. PL/SQL avoids these issues. Example 11-25 shows a PL/SQL approach to the script from Example 11-19.

Example 11-25. Using PL/SQL to implement conditional logic

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; /

Example 11-25 is a bit longer than Example 11-19, but it's a more robust script. Example 11-25 rolls back the operation if the DELETE fails for any reason, and the DELETE statement is executed only when really necessary.

11.2.6 Using an Operating-System Scripting Language

Don't overlook the possibility that you can use your operating system's scripting language to good advantage. Any Unix shell allows you to write more complex scripts than you can using SQL*Plus alone. Example 11-26 provides an implementation of the user security report menu using the Unix Bash shell.

Example 11-26. User security report implemented as a Unix Bash shell script

#!/bin/bash while : do echo " " echo "1 - List users" echo "2 - List users and table privileges" echo "3 - List users and system privileges" echo "4 - Quit" echo echo -n "Enter your choice (1,2,3,4) > " read case $REPLY in 1 ) sqlplus -s gennick/secret @ex11-26_1 ;; 2 ) sqlplus -s gennick/secret @ex11-26_2 ;; 3 ) sqlplus -s gennick/secret @ex11-26_3 ;; 4 ) break ;; * ) echo "Please enter 1, 2, 3, or 4" ;; esac done

Perl, Python, and other scripting languages are worth considering. The Perl and Python scripting languages are available for Unix and Windows. Both have the advantage of being widely used and of not tying you to one specific operating system.