There is no way to write a real loop using SQL*Plus. Your best option, if you need to do something iteratively, is to use PL/SQL. PL/SQL, however, doesn’t allow you any interaction with the user, so it’s not always suitable for the task at hand. Your second-best bet is to look into using your operating system’s scripting language, if there is one. Having said this, I’ll point out that there are a couple of things you can do in SQL*Plus that might get you the same result as writing a loop. These are:
Recursive execution
Generating a file of commands, and then executing it
The first option has some severe limitations, and I don’t recommend it too strongly. The second option I use all the time, especially when performing database maintenance tasks.
You can’t loop, but you can execute the same script recursively. Say you have a script that displays some useful information, and you want to give the user the option of running it again. You can do that by recursively executing the script. Take a look at the following interaction, in which the user is looking at indexes for various tables. It looks like a loop. Each time through, the user is prompted for another table name, and the indexes on that table are displayed.
SQL>@list_indexes employeeINDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ EMPLOYEE_PK EMPLOYEE_ID Next table >projectINDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ PROJECT_PK PROJECT_ID PROJECT_BY_NAME PROJECT_NAME Next table >project_hoursINDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ PROJECT_HOURS_PK PROJECT_ID EMPLOYEE_ID TIME_LOG_DATE Next table > Thank you for using LIST_INDEXES. Goodbye!
It sure does look like a loop, but it’s not. Here is the LIST_INDEXES script that is being run:
COLUMN index_name FORMAT A30
COLUMN column_name FORMAT A30
BREAK ON index_name NODUPLICATES
SELECT index_name, column_name
FROM user_ind_columns
WHERE table_name = UPPER('&1'),
--Ask the user if he wants to do this again.
PROMPT
ACCEPT s_next_table PROMPT 'Next table >'
--Execute either list_indexes.sql or empty.sql,
--depending on the user's response.
COLUMN next_script NOPRINT NEW_VALUE s_next_script
SET TERMOUT OFF
SELECT DECODE ('&&s_next_table',
'','empty.sql',
'list_indexes ' || UPPER('&&s_next_table')) next_script
FROM dual;
SET TERMOUT ON
@&&s_next_scriptThe key to the looping is in the last part of the script, following the ACCEPT statement. If the user enters another tablename, the SELECT statement will return another call to the LIST_INDEXES script. So when the user types “project” in response to the prompt, the s_next_script substitution variable ends up being:
list_indexes PROJECT
The only thing missing is the ampersand sign, and that is supplied by the command at the bottom of the script. In this case, the command:
@&&s_next_script
will be translated to:
@list_indexes PROJECT
If the user doesn’t enter a table name at the prompt, the
s_next_table variable will be null, and the DECODE statement will
return “empty.sql”. EMPTY.SQL is
necessary because the @ command must be executed.
EMPTY.SQL gives you a clean way out of the
recursion. In this case EMPTY.SQL prints a
message, and is implemented like this:
PROMPT PROMPT Thank you for using LIST_INDEXES. Goodbye! PROMPT
Recursive execution is a very limited technique. You can’t nest scripts forever. SQL*Plus limits you to nesting only 20 scripts, and on some older versions the limit may be as low as 5. Exceed that limit, and you will get the following message:
SQL*Plus command procedures may only be nested to a depth of 20.
Still, recursion can be useful. What are the odds that you will want to type in 20 table names in one sitting? In this case, the convenience may outweigh any chance of exceeding that limit on nesting scripts. And if you do exceed the limit, so what? You can just rerun the script.
If you need to loop a fixed number of times, say once for each table you own, you can use a SQL query to build a second file of SQL commands, then execute that file. This is known as using SQL to write SQL, and it’s a very powerful scripting technique. You’ve already seen an example of this technique in a previous section, where it was used to implement the equivalent of an IF statement in a SQL*Plus script. This technique can also be used to perform a repetitive operation, if the basis for that operation can be the results of a SQL query.
Suppose, for instance, that you want to write a script that will analyze and compute statistics for all your tables. You don’t want to hardcode the table names in the script, because then you would need to remember to change the script each time you drop or create a table. Thinking in terms of pseudocode, you might envision a script like this:
FOR xxx = FIRST_TABLE TO LAST_TABLE ANALYZE xxx COMPUTE STATISTICS; NEXT xxx
Of course, that script would never fly in SQL*Plus. Instead, you need a way to execute the ANALYZE command for each table without really looping. One way to do that is to write a SQL query to create that command. Take a look at the following query:
SELECT 'ANALYZE ' || table_name || ' COMPUTE STATISTICS;' FROM user_tables;
Since SQL is a set-oriented language, it will return a result set consisting of one instance of the ANALYZE command for each table you own. Running this query against the sample database used for this book will give the following results:
ANALYZE EMPLOYEE COMPUTE STATISTICS; ANALYZE PROJECT COMPUTE STATISTICS; ANALYZE PROJECT_HOURS COMPUTE STATISTICS;
At this point, if this were just a once-off job, and if you were using a GUI version of SQL*Plus, you could simply copy the output and paste it back in as input. The commands would execute and all your tables would be analyzed. To make a script you can run periodically, all you need to do is to spool the ANALYZE commands to a file and then execute that file. The following script does that.
SET ECHO OFF --DESCRIPTION --Analyze and compute statistics for all tables --owned by the user. --We only want the results of the query written to the --file. Headings, titles, feedback, etc. aren't valid --commands, so turn all that stuff off. SET HEADING OFF SET PAGESIZE 0 SET VERIFY OFF SET FEEDBACK OFF SET TERMOUT OFF --Create a file of ANALYZE commands, one for --each table. PROMPT commands are used to write --SET ECHO ON and OFF commands to the spool file. SPOOL analyze_each_table.sql PROMPT SET ECHO ON SELECT 'ANALYZE TABLE ' || table_name || ' COMPUTE STATISTICS;' FROM user_tables; PROMPT SET ECHO OFF SPOOL OFF --Execute the ANALYZE commands. SET TERMOUT ON @analyze_each_table --Reset settings back to defaults SET HEADING ON SET PAGESIZE 14 SET VERIFY ON SET FEEDBACK ON
Most of the commands in the script are there to prevent any extraneous information, such as column headings or page titles, from being written to the spool file. The real work is done by the SPOOL and SELECT commands, and also by the command used to run the resulting script file. The ECHO setting is turned on prior to running the ANALYZE commands so you can watch the ANALYZE commands as they execute. A PROMPT command is used to write a SET ECHO ON command to the output file. Here are the results from running the script:
SQL> @analyze_tables
SQL> ANALYZE TABLE EMPLOYEE COMPUTE STATISTICS;
SQL> ANALYZE TABLE PROJECT COMPUTE STATISTICS;
SQL> ANALYZE TABLE PROJECT_HOURS COMPUTE STATISTICS;
SQL> SET ECHO OFFAll the tables in the schema were analyzed. Once this is set up, you never need to worry about it again. Each time the script is run, ANALYZE commands are generated for all the tables currently in the schema.
My experience has been that the technique of using SQL to write SQL proves most useful when you are writing scripts to perform database maintenance tasks such as the one shown in the previous example. Information about all database objects is accessible via the data dictionary views, so it’s usually a simple matter to issue queries against those views to generate whatever commands you need.
You should always consider PL/SQL when you need to implement any type of complex procedural logic, and that includes looping. Because PL/SQL executes in the database, you can’t use it for any type of loop that requires user interaction. The table index example shown earlier in this chapter, where the user was continually prompted for another table name, could never be implemented in PL/SQL. It’s also impossible to call another SQL*Plus script from PL/SQL. However, if you can get around those two limitations, PL/SQL may be the best choice for the task.
As an example of what you can do using PL/SQL, let’s revisit the ANALYZE_TABLE script shown earlier. It’s very easy, using PL/SQL, to write a loop to iterate through all the tables you own. Here’s one way to do that:
SQL>SET SERVEROUTPUT ONSQL>BEGIN2FOR my_table in (3SELECT table_name4FROM user_tables) LOOP5 5--Print the table name6DBMS_OUTPUT.PUT_LINE(my_table.table_name);7END LOOP;8END;9/EMPLOYEE PROJECT PROJECT_HOURS PL/SQL procedure successfully completed.
This example uses what is called a cursor FOR loop. A cursor FOR loop executes once for each row returned by the query you give it. In this example, that query returns a list of tables you own.
You might think you could just put an ANALYZE TABLE command inside the loop and pass it the table name as a parameter, but it’s not quite that simple. The ANALYZE command is a DDL command, and prior to version 8.1 of Oracle, PL/SQL did not allow you to embed DDL commands within your code. The following is an example of what will happen if you try.
SQL>BEGIN2ANALYZE TABLE employee COMPUTE STATISTICS;3END;4 / ANALYZE TABLE EMPLOYEE COMPUTE STATISTICS; * ERROR at line 2: ORA-06550: line 2, column 11: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ;
With the earliest versions of PL/SQL, this was a hard-and-fast limitation, but now Oracle provides a PL/SQL package for executing dynamic SQL statements that you can also use to execute DDL. A dynamic SQL statement is one you build up programmatically, and often the exact statement isn’t known until runtime. The package name is DBMS_SQL, and you can read about it in the Oracle8 Server Application Developer’s Guide.[1]
There are five steps to using the DBMS_SQL package to execute a DDL statement:
Allocate a cursor for the statement.
Put the statement to be executed in a VARCHAR2 variable.
Parse the statement.
Execute the statement.
Deallocate the cursor.
Steps 2 through 4 are repeated for as many statements as you need to execute.
The first step is to allocate the cursor. Even though you aren’t selecting data, all SQL statements are executed using a cursor. The code to do that looks like this:
ddl_cursor_id := DBMS_SQL.OPEN_CURSOR;
The variable ddl_cursor_id should be declared as an INTEGER. This integer is a cursor ID, used to refer to the actual cursor, which the DBMS_SQL package maintains internally. The call to DBMS_SQL.OPEN_CURSOR should go outside the loop. You can use the same cursor for multiple SQL statements, so you only need to allocate it once. The next step is to create the SQL statement you want executed. The following statement, placed inside the loop, will do that:
analyze_command := 'ANALYZE TABLE '
|| my_table.table_name
|| ' COMPUTE STATISTICS';Tip
Be sure not to include a semicolon to terminate the statement you are creating. Semicolons are used to tell SQL*Plus that a SQL statement has ended. They should never be passed to Oracle. If you ever run a PL/SQL block and get an “invalid character” on the line containing your call to the DBMS_SQL.PARSE procedure, look for this problem.
Once you have the SQL statement, pass it to the DBMS_SQL package with a call to the PARSE procedure, like this:
DBMS_SQL.PARSE (ddl_cursor_id, analyze_command, DBMS_SQL.NATIVE);
The PARSE procedure takes three arguments: the cursor ID, the SQL statement to be executed, and a flag telling it whether you want Oracle6 behavior, Oracle7 behavior, or native behavior. Specifying NATIVE results in the normal behavior for whatever version of Oracle you are connected to. The statement is parsed and stored in the cursor you previously allocated. Next, you execute it with a call like the following to the EXECUTE function:
ignore_for_ddl := DBMS_SQL.EXECUTE (ddl_cursor_id);
The DBMS_SQL.EXECUTE function’s return value indicates the number of rows affected by an INSERT, UPDATE, or DELETE statement, and should be ignored when executing anything else.
Tip
Currently, DDL statements are actually executed when they are parsed, so the call to DBMS_SQL.EXECUTE is superfluous. Oracle doesn’t guarantee this behavior, however, so you shouldn’t depend on it.
Once you are done executing commands, you should dispose of the cursor with a call like the following to the CLOSE_CURSOR procedure:
DBMS_SQL.CLOSE_CURSOR(ddl_cursor_id);
The final script to analyze all your tables, using PL/SQL and dynamic SQL, now looks like this:
SET ECHO OFF
--DESCRIPTION
--Analyze and compute statistics for all tables
--owned by the user.
--Turn SERVEROUTPUT on so we see output from the
--PL/SQL block we are going to execute.
SET SERVEROUTPUT ON
--The following PL/SQL block will build up and execute
--an ANALYZE TABLE command for each table. The DBMS_SQL
--package is used for this purpose.
DECLARE
ddl_cursor_id INTEGER;
analyze_command VARCHAR2(80);
ignore_for_ddl INTEGER;
BEGIN
--Allocate a cursor to use when executing DDL commands.
ddl_cursor_id := DBMS_SQL.OPEN_CURSOR;
--Execute an ANALYZE command for each table.
FOR my_table in (
SELECT table_name
FROM user_tables) LOOP
--Create the command to analyze this table.
analyze_command := 'ANALYZE TABLE '
|| my_table.table_name
|| ' COMPUTE STATISTICS';
--Display the statement to be executed.
DBMS_OUTPUT.PUT_LINE(analyze_command);
--Parse & execute this ANALYZE command.
DBMS_SQL.PARSE (ddl_cursor_id, analyze_command, DBMS_SQL.NATIVE);
ignore_for_ddl := DBMS_SQL.EXECUTE (ddl_cursor_id);
END LOOP;
--Deallocate the cursor.
DBMS_SQL.CLOSE_CURSOR(ddl_cursor_id);
END;
/For a script like this, I think the technique of using SQL to write SQL is a better choice. It’s certainly easier to write, and just as easy to understand, if not more so. Still, using PL/SQL gives you more control over the process if you need it.
[1] Another good source of information on the DBMS_SQL package is Oracle Built-in Packages, by Steve Feuerstein, Charles Dye, and John Beresniewicz (O’Reilly & Associates, 1998). Not only does this book cover DBMS_SQL, it also covers all the other standard packages provided by Oracle.