This article examines how you can compare versions of stored functions, procedures, packages and object types. It shows you how to write a function that acts like a check sum for code stored in data dictionary as plain text.

The code in this article is very helpful for version checking between development and stage, stage and test, or test and production instances. It helps identify source versions a little more accurately than simply using program headers, like the Oracle EBS software.

This article has two parts. They are:

  • How to write a simple character and line sum function in PL/SQL
  • How to write an intelligent character and line sum function in PL/SQL

The first part shows you how to create the necessary type and user-defined type for both the simple and intelligent character sum functions. After that, the first part shows you how to build a simple function to read the dba_source view from the data catalog. This function returns a value set of the number of lines and characters in the stored program.

The second part shows you how to improve the basic function by assigning values to individual characters. This version of the function returns a value set of the number of lines and the sum of the ASCII values in the stored program.

It should be noted that the value set doesn’t serve the same purpose as a check sum. That is, you’re not using it to validate the untampered value of a message transmission.

How to write a simple character and line sum function in PL/SQL

Before you learn how to write the checksum function, you need to understand how Oracle stores functions, procedures, packages, and types. Oracle stores the plain text program by line. You can see the content of any stored program with a tool like SQL Developer, Toad for Oracle, or other developer IDE for the Oracle database. Alternatively, you can use something primitive like SQL*Plus to see the code.

Here’s a query that uses a substitution variable for the stored program name and displays the content of a stored program:

SQL> SELECT   text
  2  FROM     dba_source
  3  WHERE    name = '&program_name';

Some stored programs are small and can be measured in dozens or hundreds of lines of code. Those small programs are the exception because most stored programs have several thousand lines of code.

The first check sum example is simple. It captures the number of lines of code and the number of characters in the stored program. The function returns a set of data or a two element data structure. The means we need to define an object type to hold the two numeric elements.

The following SQL statement creates a two element type:

SQL> CREATE OR REPLACE
  2    TYPE size_set IS OBJECT
  3    ( nrows  NUMBER
  4    , nsize  NUMBER );

Lines 3 and 4 use nrows and nsize element names to avoid a conflict with reserved words. The signature of the object is two numeric columns. That means it’s possible to call it incorrectly. Named notation is the best way to instantiate the size_set type.  

You only need a type like the size_set type to write a function that returns a two element structure. That type of function works well inside a PL/SQL-only scope but is suboptimal when calling the function inside SQL. That’s because a SQL statement is too complex when it accesses a function that returns a single row type.

It’s easier to access the function result by creating a collection of the size_set type. The following creates the size_tab collection:

SQL> CREATE OR REPLACE
  2    TYPE size_tab IS TABLE OF size_set;
  3  /

Having created the size_set type and size_tab collection, now you can create the check_sum function. The function design accepts a stored program name, type, and database link. You need to use NDS (Native Dynamic SQL) with this function because of the database link.

Here’s the code of the check_sum function:

SQL> CREATE OR REPLACE
  2    FUNCTION check_sum
  3    ( pv_name  VARCHAR2
  4    , pv_type  VARCHAR2
  5    , pv_link  VARCHAR2 ) RETURN size_tab IS
  6
  7    /* Declare a record type and variable of the record type. */
  8    TYPE line IS RECORD (length NUMBER);
  9    TYPE coll IS TABLE OF line;
 10

 11    /* Declare a counter and generic statement. */
 12    lv_size    NUMBER := 0;
 13    lv_line    LINE; -- Not used directly but a placeholder for next program.
 14    lv_coll    COLL;
 15    lv_tab     SIZE_TAB := size_tab(size_set(null,null));
 16    lv_stmt    VARCHAR2(4000);

 17    lv_cursor  SYS_REFCURSOR;
 18  BEGIN
 19    /* Declare dynamic cursor as a string. */
 20    lv_stmt := 'SELECT LENGTH(text)'||CHR(10)
 21            || 'FROM   dba_source@'||pv_link||CHR(10)
 22            || 'WHERE  name = '''||pv_name||''''||CHR(10)
 23            || 'AND    type = '''||pv_type||'''';
 24
 25    /* Conditional compilation debugging. */
 26    $IF $$DEBUG = 1 $THEN parse_rows(lv_stmt); $END
 27
 28    /* Open and read cursor. */
 29    OPEN  lv_cursor FOR lv_stmt;
 30    FETCH lv_cursor BULK COLLECT INTO lv_coll;
 31    CLOSE lv_cursor;
 32

 33    /* Sum the lines into a total. */
 34    FOR i IN 1..lv_coll.COUNT LOOP
 35      lv_size := lv_size + lv_coll(i).length;
 36    END LOOP;
 37

 38    /* Translate to object structure. */
 39    lv_tab(1).nrows := lv_coll.COUNT;
 40    lv_tab(1).nsize := lv_size;
 41

 42    /* Return code length. */
 43    RETURN lv_tab;
 44  END;
 45  /

Lines 8 and 9 declare the line PL/SQL record type and the coll collection of the line record type. Line 13 declares the lv_line variable of the line record type. Line 14 declares the lv_coll variable as a non-initialized collection. Line 15 declares lv_tab collection and initializes it with a single element of the SQL line type. Line 20 through 23 declares a SQL statement. Line 20 uses the LENGTH function to convert each line of text into a numeric length value. Line 21 qualifies the database catalog table with a database link in the FROM clause.

The execution block uses a BULK COLLECT INTO on lines 29 through 31 to read all the lines of a stored program in one step. Lines 34 through 36 adds the length of the lines together. Line 39 assigns the number of lines to the nrows element and line 40 assigns the character count to the nsize element. Then, the function returns a coll collection of the line object type.

If you don’t have access to the dba_source view, all schemas can access the user_source view. Simply change dba_source on line 21 to user_source and the function will work in a schema with limited privileges.

You can test the function with the following query:

SQL> SELECT *
  2  FROM   TABLE(check_sum('CHECK_SUM','FUNCTION','XE'));

Line 2 substitutes the local TNS name for a database link. It returns:

     NROWS      NSIZE
---------- ----------
        36       1219

While it’s clearly possible for more than one function to have the same number of characters, it’s less likely that two functions have the same number of characters and lines. The next section shows you how to count the ASCII values of the characters from the function.  

How to write an intelligent character and line sum function in PL/SQL

There are only a few simple changes required to improve the prior check_sum function. You need to change some local variables and embed a nested loop when reading the lines of the stored program. You have the complete program because it improves the readability of differences.

The following displays all the required changes:

SQL> CREATE OR REPLACE
  2    FUNCTION check_sum
  3    ( pv_name  VARCHAR2
  4    , pv_type  VARCHAR2
  5    , pv_link  VARCHAR2 ) RETURN size_tab IS
  6
  7    /* Declare a record type and variable of the record type. */
  8    TYPE line IS RECORD (text VARCHAR2(200));
  9    TYPE coll IS TABLE OF line;
 10
 11    /* Declare a counter and generic statement. */
 12    lv_size    NUMBER := 0;
 13    lv_char    NUMBER;
 14    lv_line    NUMBER;
 15    lv_coll    COLL;

 16    lv_tab     SIZE_TAB := size_tab(size_set(null,null));
 17    lv_stmt    VARCHAR2(4000);
 18    lv_cursor  SYS_REFCURSOR;
 19  BEGIN
 20    /* Declare dynamic cursor as a string. */
 21    lv_stmt := 'SELECT text'||CHR(10)
 22            || 'FROM   dba_source@'||pv_link||CHR(10)
 23            || 'WHERE  name = '''||pv_name||''''||CHR(10)
 24            || 'AND    type = '''||pv_type||'''';
 25
 26    /* Conditional compilation debugging. */
 27    $IF $$DEBUG = 1 $THEN parse_rows(lv_stmt); $END
 28
 29    /* Open and read cursor. */
 30    OPEN lv_cursor FOR lv_stmt;
 31    FETCH lv_cursor BULK COLLECT INTO lv_coll;
 32    CLOSE lv_cursor;
 33
 34    /* Sum the ASCII characters of lines into a total. */
 35    FOR i IN 1..lv_coll.COUNT LOOP
 36      lv_line := 0;
 37      FOR j IN 1..LENGTH(lv_coll(i).text) LOOP
 38        lv_char := ASCII(SUBSTR(lv_coll(i).text,j,1));
 39        lv_line := lv_line + lv_char;
 40      END LOOP;
 41      lv_size := lv_size + lv_line;
 42    END LOOP;

 43
 44    /* Translate to object structure. */
 45    lv_tab(1).nrows := lv_coll.COUNT;
 46    lv_tab(1).nsize := lv_size;
 47
 48    /* Return code length. */
 49    RETURN lv_tab;
 50  END;
 51  /

Line 13 declares a lv_char variable that will hold the ASCII value of characters. Line 14 declares a lv_line variable that will hold the sum of the ASCII values for each line. You no longer need the length of the line of text, so line 21 changes the value to the text of the line in the SELECT-list.

Line 36 initializes the lv_line to zero before each line read operation. Line 37 shows you how to treat the variable length string as a character array. Line 38 converts the character to its native ASCII value and assigns it to the lv_char variable. Line 39 adds the new ASCII value the sum of the ASCII values for each line. After exiting the loop on line 40, line 41 adds the ASCII sum of the values to the size of the program.

You can test the function with the same test query:

SQL> SELECT *
  2  FROM   TABLE(check_sum('CHECK_SUM','FUNCTION','XE'));

Like before, line 2 substitutes the local TNS name for a database link. It returns the following different amounts because there are more lines and the ASCII values aren’t all equal to one.

     NROWS      NSIZE
---------- ----------
        42     106923

If you run the test, you’ll find your count will differ slightly because this test uses the user_source view not the dba_source view. This is a great function for validating whether you have the same code in two database instances. While it’s possible that another program would have the same ASCII count and number of rows, it’s very unlikely. You can find the source code for these along with test scripts on my github.com account.