Previous Table of Contents Next


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.


Previous Table of Contents Next