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 next bet is to look into using your operating system's scripting language, if there is one.
This said, you can do a couple of things in SQL*Plus that might get you the same result as writing a loop:
The first option has some severe limitations, and I don't recommend it. The second option I use all the time, especially when performing database maintenance tasks .
11.3.1 Recursive Execution
You can't loop, but you can execute the same script recursively. Suppose 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> @ex11-27 employee INDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ EMPLOYEE_PK EMPLOYEE_ID EMPLOYEE_BY_NAME EMPLOYEE_NAME Next table > project INDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ PROJECT_PK PROJECT_ID Next table > project_hours INDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ PROJECT_HOURS_PK PROJECT_ID EMPLOYEE_ID TIME_LOG_DATE Next table > Goodbye!
It sure does look like a loop, but it's not. Example 11-27 shows the script that is being run.
Example 11-27. Using recursion to simulate a loop
SET ECHO OFF SET VERIFY OFF 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', '','ex11-27_empty.sql', 'ex11-27.sql ' UPPER('&&s_next_table')) next_script FROM dual; SET TERMOUT ON @&&s_next_script
The key to the looping is in the last part of the script, following the ACCEPT statement. If you enter another table name, the SELECT statement will return another call to the ex11-27.sql script. So, when you type "project" in response to the prompt, the s_next_script substitution variable ends up being this:
The only thing missing is the at sign, and that is supplied by the command at the bottom of Example 11-27. In this case, the command:
will be translated to:
If you don't enter a table name at the prompt, the s_next_table variable will be null, and the DECODE statement will return "ex11-27_empty.sql". The ex11-27_empty.sql script is necessary because the @ command must be executed. ex11-27_empty.sql gives you a clean way out of the recursion. In this case, ex11-27_empty.sql displays a goodbye message:
PROMPT PROMPT Goodbye! PROMPT
Recursive execution is a limited technique. You can't nest scripts forever. You can only go 20 levels deep, and on some older versions of SQL*Plus 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 rerun the script.
11.3.2 Looping Within PL/SQL
You should 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 loop that requires user interaction. Example 11-27, which repeatedly prompts 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. Example 11-28 shows a script that uses a PL/SQL block to display indexes on all tables that you own, having names matching a pattern that you specify.
Example 11-28. Looping is often best done in PL/SQL
SET ECHO OFF SET VERIFY OFF SET SERVEROUTPUT ON ACCEPT table_name PROMPT 'Show indexes on what table >' DECLARE BEGIN IF '&table_name' IS NOT NULL THEN --Loop for each table and index selected by the user FOR xtable IN ( SELECT table_owner, table_name, index_name FROM user_indexes WHERE table_name LIKE UPPER('&table_name') ORDER BY table_owner, table_name, index_name) LOOP --Display the table and index names DBMS_OUTPUT.PUT_LINE(CHR(9)); DBMS_OUTPUT.PUT_LINE('Index ' xtable.index_name ' on ' xtable.table_owner '.' xtable. table_name); --Loop through each column in the index FOR xcolumn IN ( SELECT column_name FROM user_ind_columns WHERE index_name = xtable.index_name ORDER BY column_position) LOOP DBMS_OUTPUT.PUT_LINE(' ' xcolumn.column_name); END LOOP; END LOOP; END IF; END; /
Output from Example 11-28 looks like this:
SQL> @ex11-28 Show indexes on what table > employee Index EMPLOYEE_BY_NAME on GENNICK.EMPLOYEE EMPLOYEE_NAME Index EMPLOYEE_PK on GENNICK.EMPLOYEE EMPLOYEE_ID
Example 11-28 prompts once at the beginning of the script. Then control falls into a PL/SQL block that uses an outer loop to go through each matching table and index, and an inner loop to display each column from each index. PL/SQL can't write to your display. Instead, Example 11-28 uses the DBMS_OUTPUT package to write output to an in-memory buffer on the database server. When the block completes, SQL*Plus reads and displays the contents of that buffer.
DBMS_OUTPUT and Blank Lines
Using DBMS_OUTPUT.PUT_LINE, you can't normally generate blank lines in your output stream. None of the following invocations will result in any output:
DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' ');
Yet Example 11-28 manages to generate blank lines using the DBMS_OUTPUT package. How is that done? Example 11-28 accomplishes the effect of blank lines by sending tab characters , which are CHR(9) , to the output stream. A tab character isn't a blank, so it isn't ignored. However, the effect on most displays is to move the cursor to the right, an effect that is, ultimately, is invisible. The net effect is a blank line.
You can issue the command SET SERVEROUTPUT ON FORMAT WRAPPED, which preserves any blank lines in the output stream but at the cost of potentially wrapping a line in the middle of a word (the default FORMAT is WORD_WRAPPED).
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
Authors: Jonathan Gennick
Simiral book on Amazon
The New Solution Selling: The Revolutionary Sales Process That Is Changing the Way People Sell [NEW SOLUTION SELLING 2/E]