Many developers work in environments with application software. The upside of application software is that you work around it and customize it. The downside of application software is that occasionally you need to upgrade it from an old release to a new one.

Before you upgrade application software, you need to know all the customizations. When you don’t know all the customizations you need to discover them. This article describes how you can discover customized tables, views, functions, procedures, and packages.

The article presumes two things. They are:

  • You create a generic or vanilla installation of your application software
  • You create a copy of your customized application software

While you can run the test in either the generic or customized database, you should consider running it from a development instance. You can do that by putting the network resolution to the other two instances in your development instance’s tnsnames.ora file. You use the TNS aliases to create database links.

Sometimes your DBA may not want to put the connection strings for test instances into the master tnsnames.ora file. You can create database links explicitly with SQL when they’re not available in a tnsnames.ora file.

You can create a database link explicitly like this:

SQL> CREATE DATABASE LINK tns_alias
  2    CONNECT TO schema_name IDENTIFIED BY schema_password
  3    USING
  4    '(DESCRIPTION=
         (ADDRESS=
           (PROTOCOL=tcp)
           (HOST=domain_name)
           (PORT=1521)
         )
         (CONNECT_DATA=
           (SERVICE_NAME=service_name)
           (INSTANCE_NAME=instance_name)
        )
      )'

While I formatted a generic TNS connection string to look like what you would see in the tnsnames.ora file, you can’t have line breaks or spaces in the string. You have many options for naming the database link on line 1 but you should ensure it doesn’t conflict with one in your tnsnames.ora file. On line 2 you need to provide a valid schema name and current password. On line 4 you need to put in a valid hostname and domain for the HOST value, a service name for the SERVICE_NAME value, and an instance name for the INSTANCE_NAME value.

You also have an opportunity to test this type of explicit database link on your own development instance. For example, you can set the following variable when you’re working with the Oracle Database Express Edition:

  • The HOST variable to localhost
  • The SERVICE_NAME variable to XE
  • The INSTANCE_NAME variables to XE

You can also test all of the code inside your own development database. You create two schemas, and then you install two slightly different sets of tables, views, functions, procedures, and packages in them. You will also need to change the views from those starting with DBA_ prefix to those starting with USER_ prefix.

The balance of the paper covers specific object types. It uses a test schema for the customized application instance and a base schema for the generic application instance.

Table Differences

Assuming you have privileges to access the DBA_ administrative views, you can use the following query to see the differences between the two table instances:

SQL> SELECT   custom.owner
  2  ,        custom.table_name
  3  FROM     dba_tables@test custom
  4  WHERE    NOT EXISTS
  5            (SELECT null
  6             FROM   dba_tables@basic base
  7             WHERE  custom.owner = base.owner
  8             AND    custom.table_name = base.table_name)
  9  ORDER BY custom.owner;

Lines 1, 7, and 9 refer to the owner column. The owner column only appears in the DBA_ administrative views. The column owner doesn’t exist in the USER_ administrative views.

The foregoing query returns the owner and table names from the test database that aren’t found in the basic database. The script lets you find the complete set of table differences between two instances.

You can rewrite query to work with the USER_ administrative view like:

SQL> SELECT   custom.table_name
  2  FROM     user_tables@test custom
  3  WHERE    NOT EXISTS
  4            (SELECT null
  5             FROM   user_tables@basic base
  6             WHERE  custom.table_name = base.table_name);

The foregoing query returns the table names from the test schema that aren’t found in the basic schema. It lets you compare the differences between the tables of two schemas.

View Differences

Views are stored in the text column of the DBA_VIEWS and USER_VIEWS administrative views. The text column is a LONG data type, which requires special handling for reading.

Like the prior example, the following query returns the owner and view names from the test database that aren’t found in the basic database.

SQL> SELECT   custom.owner
  2  ,        custom.view_name
  3  FROM     dba_views@test custom
  4  WHERE    NOT EXISTS
  5            (SELECT null
  6             FROM   dba_views@basic base
  7             WHERE  custom.owner = base.owner
  8             AND    custom.view_name = base.view_name);
  9  ORDER BY custom.owner;

You can rewrite query to work with the USER_ administrative view, like

SQL> SELECT   custom.view_name
  2  FROM     user_views@test custom
  3  WHERE    NOT EXISTS
  4            (SELECT null
  5             FROM   user_views@basic base
  6             WHERE  custom.view_name = base.view_name);

The modified query returns the view names from the test schema that aren’t found in the basic views. It’s possible to extend this query by writing a function to analyze the text differences between views with the same names.

Function, Procedure, and Package Differences

There are similar approaches to comparing function owners and names. The following query returns the ower, type, and name of functions from the test system that aren’t found in the basic system:

SQL> SELECT   custom.owner
  2  ,        custom.object_type
  3  ,        custom.object_name
  4  FROM     dba_objects@test custom
  5  WHERE    custom.object_type = 'FUNCTION'
  6  AND      NOT EXISTS
  7            (SELECT null
  8             FROM   dba_objects@basic base
  9             WHERE  custom.owner = base.owner
 10             AND    custom.object_name = base.object_name
 11             AND    custom.object_type = base.object_type);

As discussed the owner column exists in the DBA_ prefaced administrative views but not in the USER_ views. The object_type column does exist in the DBA_ and USER_ prefaced administrative views.

You can change the basic query by altering the string literal on line 5 from FUNCTION to PROCEDURE, PACKAGE, or PACKAGE BODY. The change ensures that you can find the difference between test and basic database instances.

Beyond comparing the owner and file names, you can compare the actual contents of the source code. You just need to combine the query capability with a function that compares the number of lines and sum of the ASCII values in the stored programs.

You can find how to write a function that compares the line numbers and sum of the ASCII values in the How to Write a Check Sum for Stored Programs article. When you combine this check sum function with a comparative query, you can find stored programs that have coding differences while they have the same owner, object type and program name.

You would write the query like this:

SQL> SELECT dtp.owner
  2  ,      dtp.name
  3  ,      dtp.type
  4  ,      CASE
  5           WHEN INSTR(dtp.text,'/',1,1) = 1 THEN dtp.text
  6           WHEN INSTR(dtp.text,'/',1,4) > 0
  7             THEN SUBSTR(dtp.text
  8                , INSTR(dtp.text,'/',1,1)
  9                , INSTR(dtp.text,'/',1,4) - INSTR(dtp.text,'/',1,1) + 1)
 10         END AS text
 11  ,      tabto.nrows

 12  ,      tabto.nsize
 13  FROM   dba_source@test dtp CROSS JOIN
 14         TABLE(sum_lines(dtp.name,dtp.type,'TEST')) tabto
 15  WHERE  NOT EXISTS
 16          (SELECT NULL
 17           FROM   dba_source@test dt INNER JOIN
 18                  dba_source@basic dv
 19           ON     dt.owner = dv.owner
 20           AND    dt.name = dv.name
 21           AND    dt.type = dv.type
 22           AND    dt.text = dv.text CROSS JOIN
 23                  TABLE(sum_lines(dt.name,dt.type,'TEST')) tabt INNER JOIN
 24                  TABLE(sum_lines(dv.name,dv.type,'BASIC')) tabv
 25           ON     tabt.nrows = tabv.nrows
 26           AND    tabt.nsize = tabv.nsize
 27           WHERE  dt.owner = dtp.owner
 28           AND    dt.name = dtp.name
 29           AND    dt.type = dtp.type
 30           AND    REGEXP_LIKE(dt.text,'^.*\$Header.*$')
 31           AND    REGEXP_LIKE(dv.text,'^.*\$Header.*$'))
 32  AND    dtp.type = 'PACKAGE BODY'

 33  AND    REGEXP_LIKE(dtp.text,'^.*\$Header.*$');

Lines 4 through 10 uses a CASE-statement to normalize a header line. It was added to the query because I did volume testing against two Oracle E-Business Suite instances.

The CASE-statement filtered out a pattern variation found in the Oracle E-Business Suite. Lines 30, 31, and 33 are also new. Those additional lines were added to filter the query set so that it could analyze the owner, object type, header detail, and the physical number of lines and the sum of the ASCII values.

You should remove the header evaluations when you’re running it in something other than an Oracle E-Business Suite instance. Unless the application code includes similar headers.

The query took more than an hour to resolve in the test environment. So, it seemed necessary to add a table and manage the scope of the query results inside a PL/SQL block.

The following table captures the result of the query:

SQL> CREATE TABLE package_body_diff
  2  ( owner  VARCHAR2(30)
  3  , name   VARCHAR2(30)
  4  , type   VARCHAR2(12)
  5  , text   VARCHAR2(4000)
  6  , nrows  NUMBER
  7  , nsize  NUMBER );

After creating the package_body_diff table, you can create the get_package_diff procedure. The procedure doesn’t require an input column, and it runs against all the

  

SQL> CREATE OR REPLACE PROCEDURE get_package_diff IS
  2
  3    /* A record type for bulk operation. */
  4    TYPE package_info IS RECORD
  5    ( owner    VARCHAR2(30)
  6    , name     VARCHAR2(30)
  7    , type     VARCHAR2(12)
  8    , text     VARCHAR2(4000)
  9    , nrows    NUMBER
 10    , nsize    NUMBER );
 11
 12    /* A table of the record type. */
 13    TYPE package_info_tab IS TABLE OF package_info;
 14
 15    /* A local variable of the local type. */
 16    lv_package_info  PACKAGE_INFO_TAB;
 17
 18    /* A cursor to gather results. */
 19    CURSOR get_package_info IS
 20      SELECT dtp.owner
 21      ,      dtp.name
 22      ,      dtp.type
 23      ,      CASE
 24               WHEN INSTR(dtp.text,'/',1,1) = 1 THEN dtp.text
 25               WHEN INSTR(dtp.text,'/',1,4) > 0
 26               THEN SUBSTR(dtp.text
 27                  , INSTR(dtp.text,'/',1,1)
 28                  , INSTR(dtp.text,'/',1,4) - INSTR(dtp.text,'/',1,1) + 1)
 29             END AS text
 30      ,      tabto.nrows
 31      ,      tabto.nsize
 32      FROM   dba_source@test dtp CROSS JOIN
 33             TABLE(check_sum(dtp.name,dtp.type,'TEST')) tabto
 34      WHERE  NOT EXISTS
 35              (SELECT NULL
 36               FROM   dba_source@test dt INNER JOIN
 37                      dba_source@basic dv
 38               ON     dt.owner = dv.owner
 39               AND    dt.name = dv.name
 40               AND    dt.type = dv.type
 41               AND    dt.text = dv.text CROSS JOIN
 42                      TABLE(check_sum(dt.name,dt.type,'TEST')) tabt INNER JOIN
 43                      TABLE(check_sum(dv.name,dv.type,'BASIC')) tabv
 44               ON     tabt.nrows = tabv.nrows
 45               AND    tabt.nsize = tabv.nsize
 46               WHERE  dt.owner = dtp.owner
 47               AND    dt.name = dtp.name
 48               AND    dt.type = dtp.type
 49               AND    REGEXP_LIKE(dt.text,'^.*\$Header.*$')
 50               AND    REGEXP_LIKE(dv.text,'^.*\$Header.*$'))
 51      AND    dtp.type = 'PACKAGE BODY'
 52      AND    REGEXP_LIKE(dtp.text,'^.*\$Header.*$');
 53
 54  BEGIN
 55    OPEN get_package_info;
 56    LOOP
 57      FETCH get_package_info BULK COLLECT INTO lv_package_info LIMIT 20;
 58      EXIT WHEN lv_package_info.COUNT = 0;

 59
 60      /* Bulk insert and commit of data in set of twenty. */
 61      FORALL i IN lv_package_info.FIRST..lv_package_info.LAST
 62        INSERT INTO package_body_diff
 63        VALUES
 64        ( lv_package_info(i).owner
 65        , lv_package_info(i).name
 66        , lv_package_info(i).type
 67        , lv_package_info(i).text
 68        , lv_package_info(i).nrows
 69        , lv_package_info(i).nsize );
 70        COMMIT;
 71    END LOOP;

 72  END;
 73  /

 The local package_info record type lets you perform a BULK COLLECT INTO operation, which lets you query 20 rows per fetch on line 57. The FORALL on lines 61 through 70 inserts the results into the package_body_diff table. The COMMIT inside the loop lets you see rows as they’re written.

It’s important to note that if you try to run the get_package_diff procedure on a single instance that has two self-referencing database links, it will raise the following error:

Errors for PROCEDURE GET_PACKAGE_DIFF:
LINE/COL ERROR
-------- ---------------------------------------------
20/5     PL/SQL: SQL Statement ignored
20/5     PL/SQL: ORA-01775: looping chain of synonyms

This article has shown you how to compare tables, views, and stored programs. It shows how to leverage the check_sum function. you that when s to find a set of rows in one table.

As always, I hope this helps you learn another facet of enabling SQL with your own user-defined PL/SQL stored programs.