Teach Yourself SQL in 21 Days, Second Edition
![Previous chapter](../buttonart/previous.gif) ![Next chapter](../buttonart/next.gif)
- Day 17 -
Using SQL to Generate SQL Statements
Objectives
Today you learn the concepts behind generating one or more SQL statements from
a query. By the end of the day 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. 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.
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 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. (See Day 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 today 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
Day 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 Day 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 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.
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.
![Previous chapter](../buttonart/previous.gif) ![Next chapter](../buttonart/next.gif) ![Contents](../buttonart/contents.gif)
© Copyright, Macmillan Computer Publishing. All
rights reserved.
|