Better to know some
... than all
Using SQL to Generate SQL Statements
• The benefits of generating SQL statements from a query
• How to make the output from a query appear in the form of another SQL statement
• How to use the data dictionary, database tables, or both to form SQL statements
The Purpose of Using SQL to Generate SQL Statements
Generating SQL from another SQL statement simply means writing an SQL statement whose output forms another SQL statement or command. Until now, all the SQL statements that you have learned to write either do something, such as manipulate the data in a table, one row at a time, or produce some kind of report from a query. Today you learn how to write a query whose output forms another query or SQL statement.
Why you would ever need to produce an SQL statement from a query? Initially, it is a matter of simplicity and efficiency. You may never need to produce an SQL statement, but without ever doing so you would be ignoring one of SQL's most powerful features, one that too many people do not realize exists.
Generating SQL is rarely mandatory because you can manually create and issue all SQL statements, although the process can be tedious in certain situations. On the same note generating SQL statements may be necessary when you have a tight deadline. For example, suppose your boss wants to grant access on a new table to all 90 users in the marketing department (and you want to get home for dinner). Because some users of this database do not work in marketing, you cannot simply grant access on the table to public. When you have multiple groups of users with different types of access, you may want to enforce role security, which is a built-in method for controlling user access to data. In this situation you can create an SQL statement that generates GRANT statements to all individuals in the marketing department; that is, it grants each individual the appropriate role(s).
You will find many situations in which it is advantageous to produce an SQL statement as output to another statement. For example, you might need to execute many similar SQL statements as a group or you might need to regenerate DDL from the data dictionary. When producing SQL as output from another statement, you will always get the data for your output from either the data dictionary or the schema tables in the database. A SELECT statement can be issued to the database, drawing its output results either from the data dictionary or from application tables in the database. Your statement can arrange the retrieved data into one or more SQL statements. For instance, if one row is returned, you will have generated one SQL statement. If 100 rows are returned from your statement, then you will have generated 100 SQL statements. When you successfully generate SQL code from the database, you can run that code against the database, which may perform a series of queries or database actions.
The remainder of the day is devoted to examples that show you how to produce output in the form of SQL statements. Most of your information will come from the data dictionary, so you may want to review yesterday's material.
Miscellaneous SQL*Plus Commands
These commands, known as SQL*Plus commands, are specific to Personal Oracle7 and control the format of your output results. SQL*Plus commands are issued at the
SQL> prompt, or they can be used in a file.
NOTE: Although these commands are specific to Oracle, similar commands are available in other implementations.
set echo on/off
When you set echo on, you will see your SQL statements as they execute. Set echo off means that you do not want to see your SQL statements as they execute--you just want to see the output.
SET ECHO [ ON | OFF ]
set feedback on/off
Feedback is the row count of your output. For instance, if you executed a SELECT statement that returned 30 rows of data, your feedback would be SET FEEDBACK ON displays the row count; SET FEEDBACK OFF eliminates the row count from your output.
SET FEEDBACK [ ON | OFF ]
set heading on/off
The headings being referred to here are the column headings in the output of a SELECT statement, such as LAST-NAME or CUSTOMER-ID. SET HEADING ON, which is the default, displays the column headings of your data as a part of the output. SET HEADING OFF, of course, eliminates the column headings from your output.
SET HEADING [ ON | OFF ]
Spooling is the process of directing the results of your query to a file. In order to open a spool file, you enter
To close your spool file, you would type
Most SQL commands that we have covered so far have been issued at the SQL> prompt. Another method for issuing SQL statements is to create and then execute a file. In SQL*Plus the command to execute an SQL file is START FILENAME.
ED is a Personal Oracle7 command that opens a file (existing or file). When you open a file with ed, you are using a full-screen editor, which is often easier than trying to type a lengthy SQL statement at the SQL> prompt. You will use this command to modify the contents of your spool file. You will find that you use this command often when generating SQL script because you may have to modify the contents of the file for customization. However, you can achieve most customization through SQL*Plus commands.
Counting the Rows in All Tables
The first example shows you how to edit your spool file to remove irrelevant lines in your generated code, thus allowing your SQL statement to run without being tarnished with syntax errors.
NOTE: Take note of the editing technique used in this example because we will not show the step in the rest of today's examples. We assume that you know the basic syntax of SQL statements by now. In addition, you may choose to edit your spool file in various ways.
Start by recalling the function to count all rows in a table: COUNT(*). You already know how to select a count on all rows in a single table.
SELECT COUNT(*) FROM TBL1;
That technique is handy, but suppose you want to get a row count on all tables that you own or that are in your schema. For example, here's a list of the tables you own:
SELECT * FROM CAT;
If you want to get a row count on all your tables, you could manually issue the COUNT(*) statement on each table. The following SELECT statement creates more SELECT statements to obtain a row count on all the preceding tables.
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL CNT.SQL
SQL> SELECT 'SELECT COUNT(*) FROM '||TABLE-NAME||';'FROM CAT
The first action in the preceding example is to use some SQL*Plus commands. Setting echo off, feedback off, and heading off condenses the output to what is actually being selected. Remember, the output is not being used as a report, but rather as an SQL statement that is ready to be executed. The next step is to use the SPOOL command to direct the output to a file, which is specified as cnt.sql. The final step is to issue the SELECT statement, which will produce output in the form of another statement. Notice the use of single quotation marks to select a literal string. The combination of single quotation marks and the concatenation (||) allows you to combine actual data and literal strings to form another SQL statement. This example selects its data from the data dictionary. The command SPOOL OFF closes the spool file.
TIP: Always edit your output file before running it to eliminate syntax discrepancies and to further customize the file that you have created.
SQL> SPOOL OFF
SQL> ED CNT.SQL
The command SPOOL OFF closes the spool file. Then the ED command edits the file. At this point you are inside the file that you created. You should remove unnecessary lines from the file, such as the SELECT statement, which was used to achieve the results, and the SPOOL OFF at the end of the file. Here is how your file should look after the edit. Notice that each line is a valid SQL statement.
Granting System Privileges to Multiple Users
As a database administrator or an individual responsible for maintaining users, you will often receive requests for user IDs. In addition to having to grant privileges to users that allow them proper database access, you also have to modify users' privileges to accommodate their changing needs. You can get the database to generate the GRANT statements to grant system privileges or roles to many users.
SQL> SELECT 'GRANT CONNECT, RESOURCE TO ' || USERNAME || ';'
WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT','RYAN','PO7','DEMO')
Granting Privileges on Your Tables to Another User
Granting privileges on a table to another user is quite simple, as is selecting a row count on a table. But if you have multiple tables to which you wish to grant access to a role or user, you can make SQL generate a script for you--unless you just love to type.
First, review a simple GRANT to one table:
SQL> GRANT SELECT ON HISTORY TO BRANDON;
Disabling Table Constraints to Load Data
When loading data into tables, you will sometimes have to disable the constraints on your tables. Suppose that you have truncated your tables and you are loading data into your tables from scratch. More than likely, your tables will have referential integrity constraints, such as foreign keys. Because the database will not let you insert a row of data in a table that references another table (if the referenced column does not exist in the other table), you may have to disable constraints to initially load your data. Of course, after the load is successful, you would want to enable the constraints.
SQL> SPOOL DISABLE.SQL
SQL> SELECT 'ALTER TABLE ' || TABLE-NAME || 'DISABLE CONSTRAINT ' ||
CONSTRAINT-NAME || ';'FROM SYS.DBA-CONSTRAINTS WHERE OWNER = 'RYAN'
The objective is to generate a series of ALTER TABLE statements that will disable the constraints on all tables owned by RYAN. The semicolon concatenated to the end of what is being selected completes each SQL statement.
Creating Numerous Synonyms in a Single Bound
Another tedious and exhausting task is creating numerous synonyms, whether they be public or private. Only a DBA can create public synonyms, but any user can create private synonyms.
The following example creates public synonyms for all tables owned by RYAN.
SQL> SPOOL PUB-SYN.SQL
SQL> SELECT 'CREATE PUBLIC SYNONYM ' || TABLE-NAME || ' FOR ' ||
OWNER||'.'||TABLE-NAME||';'FROM SYS.DBA-TABLES WHERE OWNER = 'RYAN'
What if public synonyms do not exist? Suppose that BRANDON has Select access to all tables owned by RYAN and wants to create private synonyms.
SQL> CONNECT BRANDON
SQL> SPOOL PRIV-SYN.SQL
SQL> SELECT 'CREATE SYNONYM ' || TABLE-NAME || ' FOR ' || OWNER || '.' ||
TABLE-NAME || ';' FROM ALL-TABLES
Creating Views on Your Tables
If you want to create views on a group of tables, you could try something similar to the following example:
SQL> SELECT 'CREATE VIEW ' || TABLE-NAME || '-VIEW AS SELECT *
FROM ' || TABLE-NAME || ';' FROM CAT
The file views.sql was generated by the previous SQL statement. This output file has become another SQL statement file and contains statements to create views on all specified tables. After running views.sql, you can see that the views have been created.
Truncating All Tables in a Schema
Truncating tables is an event that occurs in a development environment. To effectively develop and test data load routines and SQL statement performance, data is reloaded frequently. This process identifies and exterminates bugs, and the application being developed or tested is moved into a production environment.
The following example truncates all tables in a specified schema.
SQL> SPOOL TRUNC.SQL
SQL> SELECT 'TRUNCATE TABLE ' || TABLE-NAME || ';' FROM ALL-TABLES
WHERE OWNER = 'RYAN'
Truncating all tables owned by RYAN removes all the data from those tables. Table truncation is easy. You can use this technique if you plan to repopulate your tables with new data.
TIP: Before performing an operation such as truncating tables in a schema, you should always have a good backup of the tables you plan to truncate, even if you are sure that you will never need the data again. (You will--somebody is sure to ask you to restore the old data.)
Using SQL to Generate Shell Scripts
You can also use SQL to generate other forms of scripts, such as shell scripts. For example, an Oracle RDBMS server may be running in a UNIX environment, which is typically much larger than a PC operating system environment. Therefore, UNIX requires a more organized approach to file management. You can use SQL to easily manage the database files by creating shell scripts.
The following scenario drops tablespaces in a database. Although tablespaces can be dropped using SQL, the actual data files associated with these tablespaces must be removed from the operating system separately. The first step is to generate an SQL script to drop the tablespaces.
SQL> SPOOL DROP-TS.SQL
SQL> SELECT 'DROP TABLESPACE ' || TABLESPACE-NAME || ' INCLUDING CONTENTS;'
Next you need to generate a shell script to remove the data files from the operating system after the tablespaces have been dropped.
SQL> SPOOL RM-FILES.SH
SQL> SELECT 'RM -F ' || FILE-NAME FROM SYS.DBA-DATA-FILES
Now that you have generated both scripts, you may run the script to drop the tablespaces and then execute the operating system shell script to remove the appropriate data files. You will also find many other ways to manage files and generate non-SQL scripts using SQL.
Reverse Engineering Tables and Indexes
Even though many CASE tools allow you to reverse-engineer tables and indexes, you can always use straight SQL for this purpose. You can retrieve all the information that you need from the data dictionary to rebuild tables and indexes, but doing so effectively is difficult without the use of a procedural language, such as PL/SQL or a shell script.
We usually use embedded SQL within a shell script. Procedural language functions are needed to plug in the appropriate ingredients of syntax, such as commas. The script must be smart enough to know which column is the last one, so as to not place a comma after the last column. The script must also know where to place parentheses and so on. Seek the tools that are available to regenerate objects from the data dictionary, whether you use C, Perl, shell scripts, COBOL, or PL/SQL.
Generating statements directly from the database spares you the often tedious job of coding SQL statements. Regardless of your job scope, using SQL statement generation techniques frees you to work on other phases of your projects.
What you have learned today is basic, and though these examples use the Oracle database, you can apply the concepts to any relational database. Be sure to check your specific implementation for variations in syntax and data dictionary structure. If you keep an open mind, you will continually find ways to generate SQL scripts, from simple statements to complex high-level system management.