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:
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:
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.
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.
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.
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.