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.")
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.
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 ]
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 ]
spool filenameTo close your spool file, you would type
spool off
START FILENAME
ED FILENAME
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:
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.
SELECT COUNT(*) FROM TBL1;
COUNT(*) -------- 29That 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; 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.
10 rows selected.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 || ';' 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;
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
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
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:
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>
set feedback onthen
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 ';'.
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 /
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;
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.
NOTE: The following examples omit the step in which you edit your output file. You can assume that the files are already edited.
First, review a simple GRANT to one table:
SQL> GRANT SELECT ON HISTORY TO BRANDON;
Grant succeeded.Are you ready for some action? The next statement creates a GRANT statement for each of the 10 tables in your schema.
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;
Now close the output file with the SPOOL command, and assuming that the file has been edited, the file is ready to run.
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.
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 /
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;
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.
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.
The following example creates public synonyms for all tables owned by RYAN.
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 /
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.
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.
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 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;
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.
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 /
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;
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.
The following example truncates all tables in a specified schema.
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 /
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.
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.
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.)
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> 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 /
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.
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>
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.
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.
A Ask yourself these questions:
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.
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 /
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 /
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 /
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?
2. Using the examples in this chapter as guidelines, create some SQL statements that will generate SQL that you can use.