2.8 How do I…Use SQL*Plus to write repetitive scripts?

Problem

I need to run a SQL statement for each table in the database. It is a great deal of work to type in a statement for each table. I know that I can save the results of a query to a file and that I can run a file. How do I use SQL*Plus to write repetitive scripts?

Technique

The data dictionary contains just about everything you ever wanted to know about the structure of your database. Oracle enables you to concatenate text using the concatenation operator. By concatenating the keywords required by the statement you want to add to the query output, you can build a series of SQL statements based on a query. Because any string can be concatenated with the results of a query, the output of the query can be forced into the format of a SQL statement.

Steps

1. Run SQL*Plus and connect as the WAITE user account. Remove the column headings. A heading can be suppressed by setting the HEADING system variable to OFF.

SQL> SET HEADING OFF

SQL>

2. Remove the row count feedback by setting the FEEDBACK system variable to OFF. The FEEDBACK variable controls the row count displayed at the end of a query. The extra line of information will cause an error in the SQL script. Execute a SET command to turn off row feedback.

SQL> SET FEEDBACK OFF

SQL>

3. Widen the output line. SQL*Plus attempts to make it easier to read the output of a query by wrapping the lines. When creating a SQL statement with a query, the default line length might be too small. The LINESIZE system variable determines the length of a line. The maximum value of LINESIZE is operating-system–dependent. In Microsoft Windows, the maximum value is 32,000.

SQL> SET LINESIZE 132

SQL>

4. Construct the query that creates SQL statements. To do this, concatenate the keywords the statement requires to the table name as it is retrieved from the database. Figure 2.18 shows CHP2_15.SQL, which contains a query. Run the query once to check for syntax errors before writing the output to a file. Running the statement verifies the output.

Because there are no column headings and no row count, the output is a sequence of SQL statements without the undesirable statements.

5. Write the output to a file. The SPOOL command is used to create the output file.

SQL> SPOOL CMDS.SQL

SQL>

SQL*Plus commands, query results, and SQL*Plus feedback are written to the CMDS.SQL file. When spooling begins, only statements that produce SQL statements are desired. Be sure to execute all necessary SQL*Plus commands prior to executing the SPOOL command. Unfortunately, there will be a couple undesired lines because the slash and the SPOOL OFF commands are written to the file. These lines will create runtime errors, which can be ignored, or you can use any text editor to remove the unwanted lines from the file.

6. Execute the SQL statement from CHP2_15.SQL. This time when the query is executed, the results will be written to the CMDS.SQL file.

SQL> /

7. Stop writing to the file by executing the SPOOL OFF command. SPOOL OFF stops the spooling operation and closes the output file.

SQL> SPOOL OFF

A file that runs like a SQL*Plus command file is created. Figure 2.19 shows the results of the operation.

How It Works

Every SQL statement begins with keywords—such as SELECT COUNT(*) FROM, DROP TABLE, or INSERT INTO—and ends with a semicolon. The string concatenation function ( | | ) enables static text to be combined with the query output to create a SQL statement. The capability of SQL*Plus to write query output to a text file and execute the file enables you to dynamically build SQL scripts as the output from a query. Step 1 suppresses the headings usually displayed by the query. Step 2 suppresses the row count feedback displayed at the end of a query. Step 3 widens the length of the line to ensure that the output fits on a single line. Step 4 creates and runs a query to create the desired SQL statements. Step 5 begins writing the output of the query to a file. Step 6 runs the query, and Step 7 ends the spooling process.

Comments

Using SQL*Plus to create SQL commands is a very useful feature. If you look to this technique whenever you need to execute a large number of statements on the database, you will greatly increase your productivity. This technique is used in many places throughout the book.