Timesavers: Using SQL as a code generator

Very often a DBA is presented with the problem of performing a repetitive operation on a large number of objects in a short amount of time, in a repeatable manner. Good DBAs will generally want to "script" their solutions because the scripts become both reusable assets, and historical change revisions against the schema, which may be tested and promoted to multiple test and production databases. There are commercial tools for this, but they are not cheap, and often are not agile, or may be overkill for the job. Let's take a common example.

A few months ago a consultant visited my office to audit one of my customers' databases and build some reports for them. He needed full access to the customer schema to do so, but was not a fully trusted party, so we started him out with read-only access to the schema. Oracle, like many other databases, does not provide schema level grants, so we must grant privileges for each object, and the way we reuse this access level is by granting the needed privileges to a role once, and granting that role to the end users. Creating a role is as easy as one line of SQL, but the grunt work is not. We need to identify the appropriate objects and then perform the initial grant for each of those objects to the target role. Many a junior DBA has sat down and busily started typing away grant after grant, unaware of a better alternative. Luckily, we can make use of the data dictionary to generate the script with one line of SQL. This method works with any database, not just Oracle, with minor syntax changes.

Lets create a script to grant the SELECT privilege for every object in PROD's schema to the SHIPPING_REPORTS role; all 133 tables. I'll only show 3 tables in the sample below, so use your imagination.

SQL> create role SHIPPING_REPORTS;
                      

Select all Open in new window

Role created.

SQL> set pagesize 0
                      SQL> set head off
                      SQL> select 'GRANT SELECT ON PROD.'||table_name||' TO SHIPPING_REPORTS;' from dba_tables where owner = 'PROD' order by table_name;
                      

Select all Open in new window

--Generated script follows
GRANT SELECT ON PROD.ACCOUNT TO SHIPPING_REPORTS;
GRANT SELECT ON PROD.ACCOUNT_SHIPMENT TO SHIPPING_REPORTS;
GRANT SELECT ON PROD.BOL TO SHIPPING_REPORTS;
[... snip 130 more tables, use your imagination ...]


Notice, I hard-coded the PROD. portion of the object above, but we could rewrite the SQL to make this script more portable by accepting a parameter. It is now a general purpose generator for all tables in a schema.

SQL> select 'GRANT SELECT ON '||owner||'.'||table_name||' TO SHIPPING_REPORTS;' from dba_tables where owner = '&user';
                      

Select all Open in new window

Enter value for user: PROD

For long scripts, you may want to spool the script to a file, rather than paste from the screen. Note, for the beginner, this only generates the script to the screen; it does NOT actually execute the commands. You must save and execute the commands explicitly, after giving the code a good check for safety and accuracy.

Another common application is to bulk drop certain tables, based on naming convention.

SQL> select 'DROP TABLE '||owner||'.'||table_name||';' from dba_tables where owner = '&user';
                      

Select all Open in new window

Again, make sure to review the SQL prior to actually executing it.

The experienced DBA takes this little shortcut for granted, but the data dictionary is a powerful metadata resource to be used creatively to reduce manual work. We need not stop at using the standard data dictionary. By adding our own tables with supplement data, we can extend the data dictionary and gain even more scripting power.

Other common applications include adding audit columns and triggers to every table in a schema to track modifications. We can extend the trick in Oracle, using PL/SQL, to generate more complex scripts such as stored procedures, triggers and views. I often choose this approach instead of using my modeling tools to perform a broad operation on a schema, instead preferring to import the changes into the model after the work has been performed.

SQL is a powerful tool, and the code that you choose to generate does not have to be limited to SQL; with a little work, you can generate high level language code the same way.