001-007 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

Dynamic Code Generation

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;

Testing

For an application developer, one of the most important uses of scripting is the ability to create and later re-create conditions for testing pieces of code. A good unit test defines a set of test data, documents the conditions established by the test data, predicts the output of the code being tested , and allows the developer to compare (or automatically compares ) the predicted results with the actual results. Listing 1.7 displays a simple script that allows testing of a function.

Listing 1.7 A simple script that allows testing of a function.

 set serveroutput on set timing on set verify off set pause off set linesize 80 set pagesize 0 set feedback off spool test_out.txt -- -- Set up some state abbreviations. -- INSERT INTO   STATE_CODES        (state_name,         state_code) VALUES ('MISSISSIPPI',         'MS'); -- -- Calling the function Get_State_Name() with a parameter of 'MS' -- will return the value 'Mississippi'. -- DECLARE    vFullStateName   varchar2 (20); BEGIN    vFullStateName := Get_State_Name (vStateAbbr => 'MS');    DBMS_Output.Put_Line ('The function returned '                           vFullStateName); EXCEPTION    WHEN OTHERS THEN         DBMS_Output.Put_Line (SQLERRM); END; -- -- Calling the function Get_State_Name() with a parameter of 'AK' -- will not return 'Mississippi'. -- DECLARE    vFullStateName   varchar2 (20); BEGIN    vFullStateName := Get_State_Name (vStateAbbr => 'AK');    DBMS_Output.Put_Line ('The function returned '                           vFullStateName); EXCEPTION    WHEN OTHERS THEN         DBMS_Output.Put_Line (SQLERRM); END; spool off

This script can be re-executed at any point in the future when the function Get_State_Name() is changed. While this is a very simple example, this type of script can be used to automate testing of almost any piece of code. The ability to easily repeat a test of complex code easily offsets the time required to develop a test for the same code ”a 200 line procedure that drives part of a major application is not something that should rely on ad hoc testing.

Table of Contents