During my work at the current customer site I was asked to create files with the changes to the data in the database. Normally you would use triggers on the tables to create logs of the records changed. Or use a Flashback Data Archive, which is only available starting Oracle 11G and I am working with Oracle 10G. The trouble in this situation is that we want to see if something changed on the data of a materialized view. This materialized view is fully refreshed overnight.<\/p>\n
First idea I had was to check for the RowID. I figured that would change when the row was changed. Problem is that the RowID either always changes, whether or not the data changed, or didn\u2019t change, even when the data changed. This proved not to be a reliable way to check for changes in the data.<\/p>\n<\/p>\n
<\/p>\n
Of course I could make a copy of the MV before it gets refreshed, but that would mean duplicating a lot of data. And of course we would have to check every column for changes. This would be an easy task if the tables don\u2019t have too many columns and if they are simple columns (i.e. varchar2s, numbers and dates).<\/p>\n
How about making some sort of hash<\/a> value for the entire row and store that value. This involved a lot of thinking and some investigating. Chances that 2 rows will hash to the same value are really small. According to Stack Overflow<\/a>:<\/p>\n (there are 2128<\/sup><\/strong> possible hashes total so the chance of two random strings producing the same hash is theoretically close to 1 in 1038<\/sup>).<\/p>\n<\/blockquote>\n That means hashing is not flawless, but it is good (enough).<\/p>\n I want to make it dynamic, I don\u2019t want to create similar code for every table (or materialized view in this case) I need to check. These can be tens, maybe hundreds or even thousands. How can I make this a generic solution. I can of course put all the fields of a record in one big string and hash that string. Let\u2019s check some documentation. First of all, I need to find all the columns in the table. Luckily Oracle<\/a> provides us with a data dictionary<\/a> in which we can found out just about everything we need to know about our objects. Column information is stored in the ALL_TAB_COLUMNS<\/a> view (ALL for all objects we have access to. We can also use USER_TAB_COLUMNS<\/a> but that shows us only the columns in table in our own schema. There is also DBA_TAB_COLUMNS, which is pretty much the same as ALL_TAB_COLUMNS but it needs the DBA Role and it shows us every column in every table in every schema).<\/p>\n I need the names and types of columns in a certain table (possibly owned by a different user) so I need to retrieve the columns COLUMN_NAME and DATA_TYPE based on TABLE_NAME and OWNER. The SQL statement I need to execute is something like this:<\/p>\n SELECT atc.column_name FROM all_tab_columns atc<\/p>\n WHERE atc.table_name = <table_name><\/p>\n and atc.OWNER = <owner><\/font><\/p>\n Since I am using PL\/SQL it is easy to return everything I need in one roundtrip to the SQL Engine using Bulk Operations<\/a>. The means I want to BULK COLLECT<\/a> whatever is returned. When I retrieve a single value I can use a scalar variable to select into. But now I will retrieve probably more than one row, so I need a collection to fetch into. Actually I need a collection of records so I create a record and a collection in my package:<\/p>\n TYPE column_rt IS RECORD( Using this collection type and my SQL statement I create a function like this:<\/p>\n FUNCTION get_column_names(table_name_in IN VARCHAR2 Now that I know what columns are in the table I can construct an SQL statement to retrieve all columns concatenated in one column. Something like this:<\/p>\n SELECT <concatenated_columns> <alias> I also need to find a way to link the result back to the originating table. The only way (I can think of) I can do this (without relying on Primary Keys which may or may not be defined) is using the ROWID. So my SQL statement will become something like this:<\/p>\n SELECT <concatenated_columns> <alias>, rowid Using Native Dynamic SQL<\/a> I can construct the SQL statement at runtime, hence using the data dictionary to define what I really select.<\/p>\n Oracle does a lot of implicit conversion on which I could rely, but I think it is better (and faster) to do explicit conversion. That way I have more control over the actual statement being generated. Also, my statement will fail if there is no implicit conversion available (and it did, since I have a couple of Oracle Spatial<\/a> columns in my test Materialized Views).<\/p>\n So, to build the select part of the statement which is dynamic I created another function:<\/p>\n FUNCTION create_select(columns_in IN column_aat As you can see in the code, I am currently only hashing the scalar fields in the table. This makes it easier for me to test. If there are special columns (like the Spatial datatype) it gets skipped for now, but I do log that this datatype is currently not processed (In the currently working version I do have some code to do hashing on the SDO_GEOMETRY column type). This function can \u2018easily\u2019 be expanded to reflect new, different types of columns that I might encounter. I could even create support for User Defined dataTypes<\/a> that I may store in a table.<\/p>\n I now have a SQL statement that will return two columns. The concatenated values from a row and the rowid. The rowid must be returned as is. This will be the way of connecting the originating table to the value returned by this bit of PL\/SQL. The concatenated value of the entire row should be hashed so it returns a value of predefined size (Varchar2(32)). <\/p>\n To hash a value I use the hash function in the dbms_crypto<\/a> package. Since I don\u2019t want to write the code needed here in my statements (hide the logic behind a layer of code (Steven Feuerstein<\/a>)) I created a simple function for it:<\/p>\n FUNCTION hashvalue(value_in IN maxvarchar) RETURN VARCHAR2 IS (maxvarchar is a subtype<\/a> defined so I don\u2019t need to remember the maximum size of a varchar in PL\/SQL<\/p>\n SUBTYPE maxvarchar IS VARCHAR2(32767); Now I need to create a table function<\/a> (a function that can be called from SQL using the TABLE() operator) that gets me the hashed values for all the rows.<\/p>\n\n
\n
, atc.data_type<\/p>\n
\n
<\/font> column_name user_tab_columns.column_name%TYPE
\n
<\/font>, data_type user_tab_columns.data_type%TYPE);
\n
<\/font>TYPE column_aat IS TABLE OF column_rt INDEX BY BINARY_INTEGER;<\/font><\/p>\n
\n
<\/font>,owner_in IN VARCHAR2 DEFAULT NULL) RETURN column_aat IS
\n
<\/font> l_returnvalue column_aat;
\n
<\/font>BEGIN
\n
<\/font>SELECT atc.column_name
\n
<\/font>, atc.data_type BULK COLLECT
\n
<\/font>INTO l_returnvalue
\n
<\/font>FROM all_tab_columns atc
\n
<\/font> WHERE atc.table_name = table_name_in
\n
<\/font> AND (atc.owner = owner_in OR owner_in IS NULL);
\n
<\/font>RETURN l_returnvalue;
\n
<\/font>END get_column_names;<\/font><\/p>\n
\n
<\/font>FROM <user>.<table> <table_alias><\/font><\/p>\n
\n
<\/font>FROM <user>.<table> <table_alias><\/font><\/p>\n
\n
<\/font> ,table_name_in IN VARCHAR2
\n
<\/font>,owner_in IN VARCHAR2) RETURN VARCHAR2 IS
\n
<\/font> l_prefix VARCHAR2(30);
\n
<\/font>l_suffix VARCHAR2(30);
\n
<\/font>l_skip BOOLEAN;
\n
<\/font>l_returnvalue maxvarchar;
\n
<\/font>BEGIN
\n
<\/font>l_returnvalue := ”;
\n
<\/font>FOR indx IN nvl(columns_in.first, 0) .. nvl(columns_in.last, -1) LOOP
\n
<\/font>l_skip := FALSE;
\n
<\/font>CASE columns_in(indx).data_type
\n
<\/font>WHEN c_varchar THEN
\n
<\/font>BEGIN
\n
<\/font>l_prefix := c_prefix_varchar;
\n
<\/font>l_suffix := c_suffix_varchar;
\n
<\/font>END;
\n
<\/font>WHEN c_number THEN
\n
<\/font>BEGIN
\n
<\/font>l_prefix := c_prefix_number;
\n
<\/font>l_suffix := c_suffix_number;
\n
<\/font>END;
\n
<\/font> WHEN c_date THEN
\n
<\/font>BEGIN
\n
<\/font>l_prefix := c_prefix_date;
\n
<\/font>l_suffix := c_suffix_date;
\n
<\/font>END;
\n
<\/font>ELSE
\n
<\/font>BEGIN
\n
<\/font>l_prefix := c_prefix_empty;
\n
<\/font>l_suffix := c_suffix_empty;
\n
<\/font>l_skip := TRUE;
\n
<\/font>logging.add(9, ‘data_type’, columns_in(indx).data_type);
\n
<\/font>END;
\n
<\/font>END CASE;
\n
<\/font>IF NOT (l_skip) THEN
\n
<\/font>l_returnvalue := l_returnvalue || ‘|| ‘ || l_prefix || columns_in(indx).column_name || l_suffix;
\n
<\/font>END IF;
\n
<\/font>END LOOP;
\n
<\/font>RETURN l_returnvalue;
\n
<\/font>END create_select;<\/font><\/p>\n
\n
<\/font>BEGIN
\n
<\/font>RETURN dbms_crypto.hash(src => utl_i18n.string_to_raw(value_in, ‘AL32UTF8’)
\n
<\/font> ,typ => dbms_crypto.hash_md5);
\n
<\/font>END hashvalue;<\/font><\/p>\n
\n
<\/font>)<\/p>\n