Web based School

- Chapter 17 -
Using SQL to Generate SQL Statements

Objectives

ToChapter you learn the concepts behind generating one or more SQL statements from a query. By the end of the Chapter you should understand the following:

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. ToChapter 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. Figure 17.1 illustrates this procedure.

As you can see in Figure 17.1, 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 Chapter 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. (See Chapter 16, "Using Views to Retrieve Useful Information from the Data Dictionary.")

Figure 17.1.

The process of generating SQL from the database.

NOTE: Today's examples use Personal Oracle7. As always, you should apply the concepts discussed toChapter to the syntax of your specific database implementation.

Miscellaneous SQL*Plus Commands

Today's examples use a few new commands. These commands, known as SQL*Plus commands, are specific to Personal Oracle7 and control the format of your output results. (See Chapter 20, "SQL*Plus.") 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, for example, Transact-SQL. (Also see Chapter 19, "Transact-SQL: An Introduction.")

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

30 rows selected.

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 ]

spool filename/off

Spooling is the process of directing the results of your query to a file. In order to open a spool file, you enter

spool filename

To close your spool file, you would type

spool off

start `filename`

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.

START FILENAME

ed `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.

ED FILENAME

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. For example:

INPUT:

SELECT COUNT(*)
FROM TBL1;

OUTPUT:

COUNT(*)
--------
      29

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:

INPUT/OUTPUT:

SELECT * FROM CAT;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
ACCT_PAY                       TABLE
ACCT_REC                       TABLE
CUSTOMERS                      TABLE
EMPLOYEES                      TABLE
HISTORY                        TABLE
INVOICES                       TABLE
ORDERS                         TABLE
PRODUCTS                       TABLE
PROJECTS                       TABLE
VENDORS                        TABLE

10 rows selected.

ANALYSIS:

If you want to get a row count on all your tables, you could manually issue the COUNT(*) statement on each table. The feedback would be

10 rows selected.

The following SELECT statement creates more SELECT statements to obtain a row count on all the preceding tables.

INPUT/OUTPUT:

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL CNT.SQL
SQL> SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';'
  2  FROM CAT
  3  /

SELECT COUNT(*) FROM ACCT_PAY;
SELECT COUNT(*) FROM ACCT_REC;
SELECT COUNT(*) FROM CUSTOMERS;
SELECT COUNT(*) FROM EMPLOYEES;
SELECT COUNT(*) FROM HISTORY;
SELECT COUNT(*) FROM INVOICES;
SELECT COUNT(*) FROM ORDERS;
SELECT COUNT(*) FROM PRODUCTS;
SELECT COUNT(*) FROM PROJECTS;
select count(*) FROM VENDORS;

ANALYSIS:

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.

INPUT:

SQL> SPOOL OFF
SQL> ED CNT.SQL

OUTPUT:

SQL> SELECT 'SELECT COUNT(*) FROM '||TABLE_NAME||';'
  2  FROM CAT;

SELECT COUNT(*) FROM ACCT_PAY;
SELECT COUNT(*) FROM ACCT_REC;
SELECT COUNT(*) FROM CUSTOMERS;
SELECT COUNT(*) FROM EMPLOYEES;
SELECT COUNT(*) FROM HISTORY;
SELECT COUNT(*) FROM INVOICES;
SELECT COUNT(*) FROM ORDERS;
SELECT COUNT(*) FROM PRODUCTS;
SELECT COUNT(*) FROM PROJECTS;
SELECT COUNT(*) FROM VENDORS;
SQL> SPOOL OFF

ANALYSIS:

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.

SELECT COUNT(*) FROM ACCT_PAY;
SELECT COUNT(*) FROM ACCT_REC;
SELECT COUNT(*) FROM CUSTOMERS;
SELECT COUNT(*) FROM EMPLOYEES;
SELECT COUNT(*) FROM HISTORY;
SELECT COUNT(*) FROM INVOICES;
SELECT COUNT(*) FROM ORDERS;
SELECT COUNT(*) FROM PRODUCTS;
SELECT COUNT(*) FROM PROJECTS;
SELECT COUNT(*) FROM VENDORS;

Now, execute the file:

INPUT/OUTPUT:

SQL> SET ECHO ON
SQL> SET HEADING ON
SQL> START CNT.SQL

SQL> SELECT COUNT(*) FROM ACCT_PAY;

 COUNT(*)
---------
        7
SQL> SELECT COUNT(*) FROM ACCT_REC;

 COUNT(*)
---------
        9
SQL> SELECT COUNT(*) FROM CUSTOMERS;

 COUNT(*)
---------
        5
SQL> SELECT COUNT(*) FROM EMPLOYEES;

 COUNT(*)
---------
       10

SQL> SELECT COUNT(*) FROM HISTORY;

 COUNT(*)
---------
       26
SQL> SELECT COUNT(*) FROM INVOICES;

 COUNT(*)
---------
        0
SQL> SELECT COUNT(*) FROM ORDERS;

 COUNT(*)
---------
        0
SQL> SELECT COUNT(*) FROM PRODUCTS;

 COUNT(*)
---------
       10
SQL> SELECT COUNT(*) FROM PROJECTS;

 COUNT(*)
---------
       16
SQL> SELECT COUNT(*) FROM VENDORS;

 COUNT(*)
---------
       22
SQL>

ANALYSIS:

Set echo on enables you to see each statement that was executed. Set heading on displays the column heading COUNT(*) for each SELECT statement. If you had included

set feedback on

then

1 row selected.

would have been displayed after each count. This example executed the SQL script by using the SQL*Plus START command. However, what if you were dealing with 50 tables instead of just 10?

NOTE: The proper use of single quotation marks when generating an SQL script is vital. Use these quotations generously and make sure that you are including all elements that will make your generated statement complete. In this example single quotation marks enclose the components of your generated statement (output) that cannot be selected from a table; for example, 'SELECT COUNT(*) FROM' and ';'.

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.

INPUT:

SQL> SET ECHO OFF
SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL GRANTS.SQL
SQL> SELECT 'GRANT CONNECT, RESOURCE TO ' || USERNAME || ';'
  2  FROM SYS.DBA_USERS
  3  WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT','RYAN','PO7','DEMO')
  4  /

OUTPUT:

GRANT CONNECT, RESOURCE TO KEVIN;
GRANT CONNECT, RESOURCE TO JOHN;
GRANT CONNECT, RESOURCE TO JUDITH;
GRANT CONNECT, RESOURCE TO STEVE;
GRANT CONNECT, RESOURCE TO RON;
GRANT CONNECT, RESOURCE TO MARY;
GRANT CONNECT, RESOURCE TO DEBRA;
GRANT CONNECT, RESOURCE TO CHRIS;
GRANT CONNECT, RESOURCE TO CAROL;
GRANT CONNECT, RESOURCE TO EDWARD;
GRANT CONNECT, RESOURCE TO BRANDON;
GRANT CONNECT, RESOURCE TO JACOB;

INPUT/OUTPUT:

SQL> spool off

SQL> start grants.sql

SQL> GRANT CONNECT, RESOURCE TO KEVIN;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO JOHN;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO JUDITH;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO STEVE;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO RON;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO MARY;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO DEBRA;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO CHRIS;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO CAROL;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO EDWARD;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO BRANDON;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO JACOB;

Grant succeeded.

ANALYSIS:

In this example you saved many tedious keystrokes by generating GRANT statements using a simple SQL statement, rather than typing each one manually.

NOTE: The following examples omit the step in which you edit your output file. You can assume that the files are already edited.

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:

INPUT:

SQL> GRANT SELECT ON HISTORY TO BRANDON;

OUTPUT:

Grant succeeded.

Are you ready for some action? The next statement creates a GRANT statement for each of the 10 tables in your schema.

INPUT/OUTPUT:

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL GRANTS.SQL
SQL> SELECT 'GRANT SELECT ON ' || TABLE_NAME || ' TO BRANDON;'
  2  FROM CAT
  3  /

GRANT SELECT ON ACCT_PAY TO BRANDON;
GRANT SELECT ON ACCT_REC TO BRANDON;
GRANT SELECT ON CUSTOMERS TO BRANDON;
GRANT SELECT ON EMPLOYEES TO BRANDON;
GRANT SELECT ON HISTORY TO BRANDON;
GRANT SELECT ON INVOICES TO BRANDON;
GRANT SELECT ON ORDERS TO BRANDON;
GRANT SELECT ON PRODUCTS TO BRANDON;
GRANT SELECT ON PROJECTS TO BRANDON;
GRANT SELECT ON VENDORS TO BRANDON;

ANALYSIS:

A GRANT statement has been automatically prepared for each table. BRANDON is to have Select access on each table.

Now close the output file with the SPOOL command, and assuming that the file has been edited, the file is ready to run.

INPUT/OUTPUT:

SQL> SPOOL OFF

SQL> SET ECHO ON
SQL> SET FEEDBACK ON
SQL> START GRANTS.SQL

SQL> GRANT SELECT ON ACCT_PAY TO BRANDON;

Grant succeeded.

SQL> GRANT SELECT ON ACCT_REC TO BRANDON;

Grant succeeded.

SQL> GRANT SELECT ON CUSTOMERS TO BRANDON;

Grant succeeded.

SQL> GRANT SELECT ON EMPLOYEES TO BRANDON;

Grant succeeded.

SQL> GRANT SELECT ON HISTORY TO BRANDON;

Grant succeeded.

SQL> GRANT SELECT ON INVOICES TO BRANDON;

Grant succeeded.

SQL> GRANT SELECT ON ORDERS TO BRANDON;

Grant succeeded.

SQL> GRANT SELECT ON PRODUCTS TO BRANDON;

Grant succeeded.

SQL> GRANT SELECT ON PROJECTS TO BRANDON;

Grant succeeded.

SQL> GRANT SELECT ON VENDORS TO BRANDON;

Grant succeeded.

ANALYSIS:

Echo was set on and feedback was set on as well. Setting feedback on displayed the statement Grant succeeded. The Select privilege has been granted to BRANDON on all 10 tables with very little effort. Again, keep in mind that you will often be dealing with many more than 10 tables.

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.

INPUT:

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL DISABLE.SQL
SQL> SELECT 'ALTER TABLE ' || TABLE_NAME ||
  2         'DISABLE CONSTRAINT ' || CONSTRAINT_NAME || ';'
  3  FROM SYS.DBA_CONSTRAINTS
  4  WHERE OWNER = 'RYAN'
  5  /

OUTPUT:

ALTER TABLE ACCT_PAY DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE ACCT_REC DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE CUSTOMERS DISABLE CONSTRAINT FK_CUSTOMER_ID;
ALTER TABLE HISTORY DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE INVOICES DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE ORDERS DISABLE CONSTRAINT FK_ACCT_ID;

ANALYSIS:

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.

INPUT/OUTPUT:

SQL> SPOOL OFF

SQL> SET ECHO OFF
SQL> SET FEEDBACK ON
SQL> START DISABLE.SQL

Constraint Disabled.

Constraint Disabled.

Constraint Disabled.

Constraint Disabled.

Constraint Disabled.

Constraint Disabled.

ANALYSIS:

Notice that echo is set to off, which means that you will not see the individual statements. Because feedback is set to on, you can see the results.

Constraint Disabled.

If both echo and feedback were set to off, nothing would be displayed. There would simply be a pause for as long as it takes to execute the ALTER TABLE statements and then an SQL> prompt would be returned.

Now you can load your data without worrying about receiving errors caused by your constraints. Constraints are good, but they can be barriers during data loads. You may use the same idea to enable the table constraints.

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.

INPUT:

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL PUB_SYN.SQL
SQL> SELECT 'CREATE PUBLIC SYNONYM ' || TABLE_NAME || ' FOR ' ||
  2          OWNER || '.' || TABLE_NAME || ';'
  3  FROM SYS.DBA_TABLES
  4  WHERE OWNER = 'RYAN'
  5  /

OUTPUT:

CREATE PUBLIC SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY;
CREATE PUBLIC SYNONYM ACCT_REC FOR RYAN.ACCT_REC;
CREATE PUBLIC SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS;
CREATE PUBLIC SYNONYM EMPLOYEES FOR RYAN.EMPLOYEES;
CREATE PUBLIC SYNONYM HISTORY FOR RYAN.HISTORY;
CREATE PUBLIC SYNONYM INVOICES FOR RYAN.INVOICES;
CREATE PUBLIC SYNONYM ORDERS FOR RYAN.ORDERS;
CREATE PUBLIC SYNONYM PRODUCTS FOR RYAN.PRODUCTS;
CREATE PUBLIC SYNONYM PROJECTS FOR RYAN.PROJECTS;
CREATE PUBLIC SYNONYM VENDORS FOR RYAN.VENDORS;

Now run the file.

INPUT/OUTPUT:

SQL> SPOOL OFF
SQL> ED PUB_SYN.SQL
SQL> SET ECHO ON
SQL> SET FEEDBACK ON
SQL> START PUB_SYN.SQL

SQL> CREATE PUBLIC SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY;

Synonym created.

SQL> CREATE PUBLIC SYNONYM ACCT_REC FOR RYAN.ACCT_REC;

Synonym created.

SQL> CREATE PUBLIC SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS;

Synonym created.

SQL> CREATE PUBLIC SYNONYM EMPLOYEES FOR RYAN.EMPLOYEES;

Synonym created.

SQL> CREATE PUBLIC SYNONYM HISTORY FOR RYAN.HISTORY;

Synonym created.

SQL> CREATE PUBLIC SYNONYM INVOICES FOR RYAN.INVOICES;

Synonym created.

SQL> CREATE PUBLIC SYNONYM ORDERS FOR RYAN.ORDERS;

Synonym created.

SQL> CREATE PUBLIC SYNONYM PRODUCTS FOR RYAN.PRODUCTS;

Synonym created.

SQL> CREATE PUBLIC SYNONYM PROJECTS FOR RYAN.PROJECTS;

Synonym created.

SQL> CREATE PUBLIC SYNONYM VENDORS FOR RYAN.VENDORS;

Synonym created.

ANALYSIS:

Almost instantly, all database users have access to a public synonym for all tables that RYAN owns. Now a user does not need to qualify the table when performing a SELECT operation. (Qualifying means identifying the table owner, as in RYAN.VENDORS.)

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.

INPUT/OUTPUT:

SQL> CONNECT BRANDON
ENTER PASSWORD: *******
CONNECTED.

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL PRIV_SYN.SQL
SQL> SELECT 'CREATE SYNONYM ' || TABLE_NAME || ' FOR ' ||
  2          OWNER || '.' || TABLE_NAME || ';'
  3  FROM ALL_TABLES
  4  /

CREATE SYNONYM DUAL FOR SYS.DUAL;
CREATE SYNONYM AUDIT_ACTIONS FOR SYS.AUDIT_ACTIONS;
CREATE SYNONYM USER_PROFILE FOR SYSTEM.USER_PROFILE;
CREATE SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS;
CREATE SYNONYM ORDERS FOR RYAN.ORDERS;
CREATE SYNONYM PRODUCTS FOR RYAN.PRODUCTS;
CREATE SYNONYM INVOICES FOR RYAN.INVOICES;
CREATE SYNONYM ACCT_REC FOR RYAN.ACCT_REC;
CREATE SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY;
CREATE SYNONYM VENDORS FOR RYAN.VENDORS;
CREATE SYNONYM EMPLOYEES FOR RYAN.EMPLOYEES;
CREATE SYNONYM PROJECTS FOR RYAN.PROJECTS;
CREATE SYNONYM HISTORY FOR RYAN.HISTORY;

INPUT/OUTPUT:

SQL> SPOOL OFF
SQL>

SQL> SET ECHO OFF
SQL> SET FEEDBACK ON
SQL> START PRIV_SYN.SQL

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

ANALYSIS:

With hardly any effort, BRANDON has synonyms for all tables owned by RYAN and no longer needs to qualify the table names.

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:

INPUT:

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL VIEWS.SQL
SQL> SELECT 'CREATE VIEW ' || TABLE_NAME || '_VIEW AS SELECT * FROM ' ||
  2         TABLE_NAME || ';'
  3  FROM CAT
  4  /

OUTPUT:

CREATE VIEW ACCT_PAY_VIEW AS SELECT * FROM ACCT_PAY;
CREATE VIEW ACCT_REC_VIEW AS SELECT * FROM ACCT_REC;
CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;
CREATE VIEW EMPLOYEES_VIEW AS SELECT * FROM EMPLOYEES;
CREATE VIEW HISTORY_VIEW AS SELECT * FROM HISTORY;
CREATE VIEW INVOICES_VIEW AS SELECT * FROM INVOICES;
CREATE VIEW ORDERS_VIEW AS SELECT * FROM ORDERS;
CREATE VIEW PRODUCTS_VIEW AS SELECT * FROM PRODUCTS;
CREATE VIEW PROJECTS_VIEW AS SELECT * FROM PROJECTS;
CREATE VIEW VENDORS_VIEW AS SELECT * FROM VENDORS;

INPUT/OUTPUT:

SQL> SPOOL OFF
SQL> SET ECHO OFF
SQL> SET FEEDBACK ON
SQL> START VIEWS.SQL

View Created.

View Created.

View Created.

View Created.

View Created.

View Created.

View Created.

View Created.

View Created.

View Created.

ANALYSIS:

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.

INPUT:

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL TRUNC.SQL
SQL> SELECT 'TRUNCATE TABLE ' || TABLE_NAME || ';'
  2  FROM ALL_TABLES
  3  WHERE OWNER = 'RYAN'
  4  /

OUTPUT:

TRUNCATE TABLE ACCT_PAY;
TRUNCATE TABLE ACCT_REC;
TRUNCATE TABLE CUSTOMERS;
TRUNCATE TABLE EMPLOYEES;
TRUNCATE TABLE HISTORY;
TRUNCATE TABLE INVOICES;
TRUNCATE TABLE ORDERS;
TRUNCATE TABLE PRODUCTS;
TRUNCATE TABLE PROJECTS;
TRUNCATE TABLE VENDORS;

Go ahead and run your script if you dare.

INPUT/OUTPUT:

SQL> SPOOL OFF
SQL> SET FEEDBACK ON
SQL> START TRUNC.SQL

Table Truncated.

Table Truncated.

Table Truncated.

Table Truncated.

Table Truncated.

Table Truncated.

Table Truncated.

Table Truncated.

Table Truncated.

Table Truncated.

ANALYSIS:

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.

INPUT:

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL DROP_TS.SQL
SQL> SELECT 'DROP TABLESPACE ' || TABLESPACE_NAME || ' INCLUDING CONTENTS;'
  2  FROM SYS.DBA_TABLESPACES
  3  /

OUTPUT:

DROP TABLESPACE SYSTEM INCLUDING CONTENTS;
DROP TABLESPACE RBS INCLUDING CONTENTS;
DROP TABLESPACE TEMP INCLUDING CONTENTS;
DROP TABLESPACE TOOLS INCLUDING CONTENTS;
DROP TABLESPACE USERS 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.

INPUT/OUTPUT:

SQL> SPOOL OFF
SQL> SPOOL RM_FILES.SH
SQL> SELECT 'RM -F ' || FILE_NAME
  2  FROM SYS.DBA_DATA_FILES
  3  /

rm -f /disk01/orasys/db01/system0.dbf
rm -f /disk02/orasys/db01/rbs0.dbf
rm -f /disk03/orasys/db01/temp0.dbf
rm -f /disk04/orasys/db01/tools0.dbf
rm -f /disk05/orasys/db01/users0.dbf
SQL> spool off
SQL>

ANALYSIS:

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.

Summary

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

Q&A

Q How do I decide when to issue statements manually and when to write SQL to generate SQL?

A Ask yourself these questions:

How often will I be issuing the statements in question?
Will it take me longer to write the "mother" statement than it would to issue each statement manually?

Q From which tables may I select to generate SQL statements?

A You may select from any tables to which you have access, whether they are tables that you own or tables that reside in the data dictionary. Also keep in mind that you can select from any valid objects in your database, such as views or snapshots.

Q Are there any limits to the statements that I can generate with SQL?

A For the most part any statement that you can write manually can be generated somehow using SQL. Check your implementation for specific options for spooling output to a file and formatting the output the way you want it. Remember that you can always modify the generated statements later because the output is spooled to a file.

Workshop

The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."

Quiz

1. From which two sources can you generate SQL scripts?

2. Will the following SQL statement work? Will the generated output work?

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL CNT.SQL
SQL> SELECT 'COUNT(*) FROM  ' || TABLE_NAME || ';'
2  FROM CAT
3  /

3. Will the following SQL statement work? Will the generated output work?

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL GRANT.SQL
SQL> SELECT 'GRANT CONNECT DBA TO ' || USERNAME || ';'
2  FROM SYS.DBA_USERS
3  WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')
4  /

4. Will the following SQL statement work? Will the generated output work?

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'
2  FROM SYS.DBA_USERS
3  WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT)
4  /

5. True or False: It is best to set feedback ON when generating SQL.

6. True or False: When generating SQL from SQL, always spool to a list or log file for a record of what happened.

7. True or False: Before generating SQL to truncate tables, you should always make sure you have a good backup of the tables.

8. What is the ED command?

9. What does the SPOOL OFF command do?

Exercises

1. Using the SYS.DBA_USERS view (Personal Oracle7), create an SQL statement that will generate a series of GRANT statements to five new users: John, Kevin, Ryan, Ron, and Chris. Use the column called USERNAME. Grant them Select access to history_tbl.

2. Using the examples in this chapter as guidelines, create some SQL statements that will generate SQL that you can use.