Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Dynamic Code Generation Oracle Tips by Burleson

Dynamic code generation is the meat and potatoes of script development, allowing a developer or DBA to write a single script to perform one task against many different data sources. An excellent example of this type of application is the HTMLCODE.SQL script, which uses multiple SQL statements to generate HTML documentation of source code by querying the Oracle7 data dictionary. The HTMLCODE.SQL script generates HTML code by selecting tags as text from the database, thus generating a document in HTML format. Listing 1.4 shows the HTMLCODE.SQL script.

Listing 1.4 The HTMLCODE.SQL script.

SELECT '<H2>'
FROM   DUAL;

SELECT rtrim (object_type) || ': ' || '<A NAME=>' ||
       rtrim (upper (object_name)) || '"</A>' ||
       rtrim (upper (object_name))
FROM   ALL_OBJECTS
WHERE  owner       = upper ('&&2')
AND    object_name = upper ('&&1');

SELECT '</H2>'
FROM   DUAL;

SELECT ' '
FROM   DUAL;

SELECT '<P> The ' || rtrim (object_name) || ' ' ||
       rtrim (object_type) ||
       ' calls these procedures owned by ' ||
       upper (&&2) ||
       '</P>'
FROM   ALL_OBJECTS
WHERE  object_name = upper ('&&1')
AND    owner       = upper ('&&2')
AND    object_name IN
       (SELECT DISTINCT name
        FROM   ALL_DEPENDENCIES
        WHERE  owner = upper ('&&2')
        AND    name  = upper ('&&1')
        AND    (type = 'PROCEDURE'
        OR      type = 'FUNCTION'
        OR      type = 'PACKAGE BODY');
        AND    referenced_owner = '&&2'
        AND    (referenced_type = 'PROCEDURE'
        OR      referenced_type = 'FUNCTION'
        OR      referenced_type = 'PACKAGE BODY');

SELECT ' '
FROM   DUAL;

SELECT '<CENTER>'
FROM   DUAL;

SELECT '<LI><A HREF="#' || rtrim (referenced_name) || '">' ||
       rtrim (referenced_name) || '</A>'
FROM   ALL_DEPENDENCIES
WHERE  owner            = upper ('&&2')
AND    name             = upper ('&&1')
AND    referenced_owner = '&&2'
AND    (referenced_type = 'PROCEDURE'
OR      referenced_type = 'FUNCTION'
OR      referenced_type = 'PACKAGE BODY');

SELECT '</CENTER>'
FROM   DUAL;

SELECT ' '
FROM   DUAL;

SELECT '<PRE>'
FROM   DUAL;

SELECT rtrim (replace (text, chr (9), '    '))
FROM   ALL_SOURCE
WHERE  name  = upper ('&&1')
AND    owner = upper (rtrim ('&&2'))
ORDER BY line;

SELECT '</PRE>'
FROM   DUAL;

SELECT '<HR>'
FROM   DUAL;

Another example of dynamic code generation is a script that recompiles all the invalid procedures, functions, and packages in the Oracle database. Listing 1.5 shows a script that recompiles stored, invalid objects.

Listing 1.5 A script to recompile stored objects that are marked as invalid.

set pagesize 0
set feedback off
set head off

spool recompile.sql

SELECT 'ALTER ' ||
       decode (object_type, 'PACKAGE BODY', 'PACKAGE', object_type) ||
       ' ' ||
       object_name ||
       ' COMPILE ' ||
       decode (object_type, 'PACKAGE BODY', 'BODY', NULL) ||
       ';'
FROM ALL_OBJECTS
WHERE status = 'INVALID'
ORDER BY decode (object_type, 'FUNCTION',  'A',
                              'PROCEDURE', 'B',
                              'PACKAGE',   'C', object_type);
exit

The code shown in Listing 1.5 generates SQL commands by first selecting strings of text from the database. Then, appropriate portions of the command are selected as literals and real data is concatenated in the right spots, generating a valid SQL command. The use of the decode() function in the script forces the query to return commands to recompile functions first, then procedures, and finally package specs. The output of the script is shown in Listing 1.6.

Listing 1.6 Generated code to recompile invalid PL/SQL objects.

ALTER FUNCTION Feet_To_Inches COMPILE ;
ALTER PROCEDURE Calculate_GPA COMPILE ;
ALTER PACKAGE Student_Addresses COMPILE BODY;

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.