<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Timesavers: Using SQL as a code generator

Published on
14,481 Points
3,981 Views
5 Endorsements
Last Modified:
Awarded
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;

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;

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';

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';

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.
5
Comment
Author:mrjoltcola
2 Comments
LVL 74

Expert Comment

by:Qlemo
I might add that the sql code spooled into a file can be read in the same sql session to execute it. This is a further step beginners tend to not know, though it seems obvious. Something like

spool  tempfile.sql
select 'grant ' /* and so on ... */
spool off
@tempfile.sql
host del tempfile.sql

And, btw, if one is in need to use the same user input more than once, don't use the syntax &var, but &&var. It will ask only once for the value (the & form asks at each occurance).
0
LVL 40

Author Comment

by:mrjoltcola
@Qlemo, Thanks for the comments, I will update the article with that input.
0

Featured Post

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month