Writing Generated Code with Q-Quotes

Q-quotes have just made generating code too easy. It used to take several tries for me to get it right, but no more! I'm usually generating code because I want to pull some different values from tables (i.e., table_name).

Here are the steps:

1) Write out a sample of the code you want generated. Obviously, you'll want to run it first to make sure you're getting the final results needed.

Select 'Table Name: '||'SALGRADE', 'Row Count: '||Count(*) From SALGRADE;

2) Break down the query into literal, and variable parts. In this example, in two different places, I want the actual table name from User_Tables to be inserted into the code.

Select 'Table Name: '||'-- the ending quote prefaces the following table_name
SALGRADE
', 'Row Count: '||Count(*) From -- the beginning quote closes the table_name
SALGRADE
;

3) Change the sample table name (SALGRADE) to the actual column name (Table_Name), and add the concatenation bars on the front, and back.

Select 'Table Name: '||'
||table_name||
', 'Row Count: '||Count(*) From
||table_name||
;

4) Add q'[ and ]' to the front and end of each literal line. (As you may know, it can be other things than square brackets [].) On the last literal of a semicolon, I just stuck with the regular single quotes since there were no embedded quotes on that line.

q'[Select 'Table Name: '||']'
||table_name||
q'[', 'Row Count: '||Count(*) From ]'
||table_name||
';'

5) Now add a 'SELECT' at the beginning, and a 'FROM' at the end (and any other options you want ORDER BY or WHERE).

Select q'[Select 'Table Name: '||']' 
       ||table_name|| 
       q'[', 'Row Count: '||Count(*) From ]' 
       ||table_name|| 
       ';'
  From User_Tables 
 Where Table_Name in ('EMP','DEPT','SALGRADE');

6) Add a SPOOL command, and use a 'SQL' extension. Spool Off at the end. If you're brave, you can run the queries immediately. I usually test a line or two before I do that. In this example, I had previously SET PAGESIZE=0 to kill the headers.

Spool TestQ.SQL

Select q'[Select 'Table Name: '||']' 
       ||table_name|| 
       q'[', 'Row Count: '||Count(*) From ]' 
       ||table_name||
       ';' 
  From User_Tables 
 Where Table_Name in ('EMP','DEPT','SALGRADE');

Spool Off

/* Run the spooled query */
@TestQ

Go forth and enjoy!