Forum Stats

  • 3,836,756 Users
  • 2,262,182 Discussions
  • 7,900,098 Comments

Discussions

Categories

Null CLOB value when inserting into table from trigger

WP v.2
WP v.2 Member Posts: 240 Blue Ribbon
edited Sep 8, 2018 8:49AM in SQL & PL/SQL

Hey all,

I have an "after insert or update" trigger on a database table that has a clob column. In the trigger I'm checking to see if the new clob value is different than the old value and if it is, I'm inserting both the new and old value in a separate table which is used for a change log. The comparison works fine and a row is indeed inserted into the change log table. However, the row in the change log table has null for the new clob value, while the old value gets inserted correctly. What is the cause of this and what can I do to insert the new clob value correctly?

The Oracle db version is 12.1.0.2

Thanks,

Bill

Tagged:
· Share on TwitterShare on Facebook
«1

Answers

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Sep 6, 2018 7:42PM 
    WP v.2 wrote:I have an "after insert or update" trigger on a database table that has a clob column. In the trigger I'm checking to see if the new clob value is different than the old value and if it is,Thanks,Bill

    Post the code of the trigger

    · Share on TwitterShare on Facebook
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Sep 6, 2018 7:49PM 
    WP v.2 wrote:Hey all,I have an "after insert or update" trigger on a database table that has a clob column. In the trigger I'm checking to see if the new clob value is different than the old value and if it is, I'm inserting both the new and old value in a separate table which is used for a change log. The comparison works fine and a row is indeed inserted into the change log table. However, the row in the change log table has null for the new clob value, while the old value gets inserted correctly. What is the cause of this and what can I do to insert the new clob value correctly?

    Please read the content of the URL below & respond accordingly.

    How do I ask a question on the forums? 

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Sep 6, 2018 9:28PM 
    I have an "after insert or update" trigger on a database table that has a clob column. In the trigger I'm checking to see if the new clob value is different than the old value and if it is, I'm inserting both the new and old value in a separate table which is used for a change log.

    And the obvious question is: WHY?

    1. why are you copying CLOB values around like that?

    2. why are you manually doing compares when a new value is provided?

    3. why aren't you storing those CLOBs in their own lookup table so you can avoid ALL of the mess you are getting yourself into?

    Unless your CLOBs are REALLY small, more like VARCHAR2 values, you are doing a lot of excessive, and mostly unnecessary, work.

    However, the row in the change log table has null for the new clob value, while the old value gets inserted correctly. What is the cause of this and what can I do to insert the new clob value correctly?

    Since Oracle ONLY inserts the values you give it the 'cause of this' is that you are only inserting a null value.

    It is also not clear if you really mean 'null' or if, instead, you mean 'empty'.

    https://docs.oracle.com/database/121/ADLOB/adlob_working.htm#ADLOB45134 

    LOB Column StatesThe techniques you use when accessing a cell in a LOB column differ depending on the state of the given cell.A cell in a LOB Column can be in one of the following states:    NULL    The table cell is created, but the cell holds no locator or value.    Empty    A LOB instance with a locator exists in the cell, but it has no value. The length of the LOB is zero.    Populated    A LOB instance with a locator and a value exists in the cell.

    I suggest you modify your architecture to store LOBS in a separate lookup table and only store the PK of the corresponding LOB in your other tables.

    1. create a MY_LOBS table with a PK that uses the checksum (i.e. MD5, SHA1) of the lob as the primary key value

    2. insert new lobs into the table using a new random value for the initial pk value

    3. during the insert (done using a simple java or pl/sql procedure) calculate the checksum of the new lob as it is streamed into the new EMPTY_CLOB() value.

    4. after the insert use the new checksum and check to see if a row with that PK value already exists and take appropriate action.

    A. if a row already exists then the LOB value already exists so return the PK (checksum) of the existing row so it can be used as a reference to the existing row

    B. if a row does NOT exist then use the newly computed checksum to replace the the initial 'random value' that was used for the primary key of the newly inserted row.

    The result off the above are:

    1. you have ONE centralized, standard way to deal with LOBs

    2. you will NOT have duplicates in the LOB table.

    3. you will NOT have duplicates of any lobs in other tables - other tables will only have pk references to the LOB table.

    4. you will NOT have the typical backup/recovery issues others have when a table containing 'lobs' has to be backed up even if the lob values haven't changed.

    · Share on TwitterShare on Facebook
  • WP v.2
    WP v.2 Member Posts: 240 Blue Ribbon
    edited Sep 7, 2018 10:28AM

    This is what's happening in the trigger...

    create or replace trigger my_table_iuarafter insert or update on my_tablereferencing new as new old as oldfor each rowdeclarebegin  if dbms_lob.compare(:old.clob_column, :new.clob_column) <> 0 then    insert into change_log (      previous_value,      current_value    ) values (      :old.clob_column,      :new.clob_column    );  end if;end;
    · Share on TwitterShare on Facebook
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Sep 7, 2018 10:46AM 
    WP v.2 wrote:This is what's happening in the trigger...
    1. createorreplacetriggermy_table_iuar
    2. afterinsertorupdateonmy_table
    3. referencingnewasnewoldasold
    4. foreachrow
    5. declare
    6. begin
    7. ifdbms_lob.compare(:old.clob_column,:new.clob_column)<>0then
    8. insertintochange_log(
    9. previous_value,
    10. current_value
    11. )values(
    12. :old.clob_column,
    13. :new.clob_column
    14. );
    15. endif;
    16. end;
    create or replace trigger my_table_iuar after insert or update on my_table referencing new as new old as old for each row declare begin  if dbms_lob.compare(:old.clob_column, :new.clob_column) <> 0 then  insert into change_log (  previous_value,  current_value  ) values (  :old.clob_column,  :new.clob_column  );  end if; end;

    Are you claiming that TRIGGER above results in NULL in CURRENT_VALUE column; but PREVIOUS_VALUE is non-NULL?

    · Share on TwitterShare on Facebook
  • WP v.2
    WP v.2 Member Posts: 240 Blue Ribbon
    edited Sep 7, 2018 11:06AM

    Hey rp0428,

    Thanks for the response!

    And the obvious question is: WHY?1. why are you copying CLOB values around like that?2. why are you manually doing compares when a new value is provided?3. why aren't you storing those CLOBs in their own lookup table so you can avoid ALL of the mess you are getting yourself into?

    I wasn't around for the early design decisions, I'm only tasked with maintaining and bug fixing but I can take a stab at answering these. My guess is the requirement for the change log came must later than the requirement for the features in the application that require CLOB types. And they either didn't think of it or couldn't find the time to refactor. 

    Unless your CLOBs are REALLY small, more like VARCHAR2 values, you are doing a lot of excessive, and mostly unnecessary, work.

    Most of the values could probably be stored in a VARCHAR2, however, there are some of values that exceed the VARCHAR2 limit. Therefore, copying the CLOBs shouldn't require that much work. Given that, I'm wondering if creating a separate table for CLOBs would be a bit of overkill. 

    Since Oracle ONLY inserts the values you give it the 'cause of this' is that you are only inserting a null value.It is also not clear if you really mean 'null' or if, instead, you mean 'empty'.

    Sorry, I was mistaken. The value being inserted is not NULL but in fact empty. However, at the point of comparison within the trigger, it is not empty. We are capturing all changes on the table in the change log, not just the CLOB field. If, in the application, I change a different field on the table, the trigger fires and the CLOB comparison shows no change (which is correct). However, if I change the CLOB value in the app the CLOB comparison in the trigger will show a change and therefore a row will be inserted into the change log table for the CLOB field. Each row in the change log table captures a particular change on a field.

    Short of doing what you're suggesting where we create a table to store LOBs. How can I get the "new" value of the CLOB to be copied correctly when inserting into the change log table? It seems like this should work just fine, albeit maybe not the best solution.

    Thanks,

    Bill

    · Share on TwitterShare on Facebook
  • WP v.2
    WP v.2 Member Posts: 240 Blue Ribbon
    edited Sep 7, 2018 11:10AM

    Hi John,

    It seems like at the time the trigger fires the old and new CLOB value is correct because the comparison is evaluating correctly. The trigger fires for changes on other columns on the same table as well, not just the CLOB column. When the change is on a different column, the trigger fires and the CLOB comparison shows no change (which is correct) and therefore a row isn't inserted in the change log for the CLOB field. However, if the change is on the CLOB field, the trigger fires and the CLOB comparison does show a change. The problem I think is when inserting/copying the "new" CLOB value into the change log table. Hope this makes sense.

    Thanks,

    Bill

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Sep 7, 2018 11:42AM 
    Most of the values could probably be stored in a VARCHAR2, however, there are some of values that exceed the VARCHAR2 limit. Therefore, copying the CLOBs shouldn't require that much work. Given that, I'm wondering if creating a separate table for CLOBs would be a bit of overkill.

    Actually 'overkill' applies to what you are trying to do now. Copying clobs and data around and duplicating them when it isn't necessary.

    We are capturing all changes on the table in the change log, not just the CLOB field. If, in the application, I change

    Sad - Oracle already has functionality to do that also - called Materialized View Log. Oracle can capture both the OLD and the NEW data.

    That will NOT capture LOBs themselves. But using the method I describe means the data tables don't contain lobs - they contain a primary key value for lobs that are stored in another table. The MV log process CAN capture before and after key values.

    We are capturing all changes on the table in the change log, not just the CLOB field. If, in the application, I change a different field on the table, the trigger fires and the CLOB comparison shows no change (which is correct). However, if I change the CLOB value in the app the CLOB comparison in the trigger will show a change and therefore a row will be inserted into the change log table for the CLOB field. Each row in the change log table captures a particular change on a field.

    All of that is what an MV log already does. You are duplicating functionality that already exists.

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Sep 7, 2018 11:45AM

    SHOW US:

    1. WHAT you do

    2. HOW you do it

    3. WHAT results you get

    That shows the code but is does NOT show:

    1. the source table

    2. the target table

    3. the dml being done

    4. the data being added/updated - including a new, non-empty lob

    5. the results showing a new lob that results in an empty lob

    We need ALL of the info in order to try to reproduce your problem.

    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,717 Blue Diamond
    edited Sep 7, 2018 8:05PM

    I can't explain your symptoms as you say they manifest, but there is definitely a problem with your logic, which is more likely a cause for strange results.  Your problem is if one of the values is null, it won't insert.  DBMS_LOB.COMPARE will return null in this case and the if statement will be null and won't execute.

    You need something like the following...

    if not(:old.clob_column is null and :new.clob_column is null) and -- Ignore two nulls (the same)

       nvl(dbms_lob.compare(:old.clob_column, :new.clob_column), -2) <> 0 then -- if one is null, set to -2, or they differ, it's different

    Note, this will also handle empty_clob() v null, which are different.

    · Share on TwitterShare on Facebook
This discussion has been closed.