Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter