Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

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

There may be performance implications to this technique, as the query optimizer may scan the entire table, evaluating UPPER('&&s_delete_confirm') = 'Y' for each row. Thus, the technique is best confined to use on smaller tables.

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.

To run Example 11-21, you will need to be the DBA or at least have access to the DBA data dictionary views.

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.

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.

Be creative with the technique shown in Example 11-24. You don't need to limit yourself to writing SQL*Plus scripts. You can use SQL*Plus to generate shell script files, SQL*Loader files, DOS batch files, or any other type of text file.

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.

The reports menu can't be implemented in PL/SQL because the menu needs to run another SQL*Plus script corresponding to the user's choice. PL/SQL runs inside the database and can't invoke a SQL*Plus script.

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.