2

I have had the need to verify that a particular pl/sql object (say a procedure) matches between development, test, and production environments. In order to compare and know with 100% certainty that the code matches, I have written a simple function such as this:

function get_plsql_hash (p_owner varchar2, p_name varchar2, p_type varchar2) return number as
    v_hash number;
begin

select sum(ora_hash(line||'!'||text)) into v_hash
from dba_source
where owner = p_owner
  and name = p_name
  and type = p_type;

if v_hash is null then
    return 0;
else
    return v_hash;
end if;

exception
when others then
    return 0;
end get_plsql_hash;

It works great. If I change even just one character of code, I will get a different hash value (checksum or whatever you want to call it). I am able to use Oracle dictionary views to not only verify pl/sql code, but also data in reference/lookup tables, table structure, table privs, indexes, constraints, sequences, etc. The function I pasted above was just one example.

Here is my question.... does something in the Oracle data dictionary already exist that I do not know about that could give me a unique value representing each object? It would be awesome if dba_objects had a column named OBJ_CKSUM or something similar. If Oracle could keep this internally every time an object is modified, it would completely eliminate the need for the code I wrote (which is only a few hundred lines, but zero lines with no code to maintain is far superior). If such a feature does not exist, would Oracle possibly consider adding this in a future release? I think it is very helpful, but I am not sure if other developers, testers, or release management would agree. I do not know how to proceed with requesting a feature from Oracle.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Rob F.
  • 21
  • 1
  • There must be something available, because when I do `CREATE OR REPLACE PACKAGE ...` with a big package it runs much faster if my code is equal to existing one. When I modify my code the command takes longer. However, I do not know if you can access it from outside. – Wernfried Domscheit Nov 02 '15 at 11:39
  • 1
    As an industry standard, I use a good text editor able to a diff. between the files form the **source control**. – Lalit Kumar B Nov 02 '15 at 11:52
  • "*If such a feature does not exist, would Oracle possibly consider adding this in a future release*" - I doubt that - unless you pay them a **lot** of money. But your whole setup is doing it the wrong way round: you need to store any change that you make into a source control system and then deploy changes to the various environments based on those scripts. Have a look at tools like Liquibase or Flyway - they help you organize and manage this –  Nov 02 '15 at 11:54
  • I did not mean to minimize using source control for the code, we do that. There are several pieces to a successful deployment. Many times we need to add new columns to a table, add a record to a reference table, have the DBA add an index, update a pl/sql procedure, etc. If any one of these moving parts is missed, then the end result is not complete. – Rob F. Nov 02 '15 at 12:04
  • When management wants us to assure them that we did not miss anything, we of course follow as good of change control process as possible, but since human beings are involved.... there are issues from time to time. My code was an effort of verification on the night of deployment that would not be possible by eyeballing the table structure, reference data, and other Oracle data dictionary tables. – Rob F. Nov 02 '15 at 12:04

0 Answers0