Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

The Oracle Developer Advocates Team

Steven Feuerstein-OracleJul 1 2015 — edited Jul 6 2020

The Oracle Developer Advocates Team

Formed in 2014, Oracle Developer Advocates is a team of expert and entertaining Oracle technologists with the following mission: help our users fully leverage the application development features of Oracle Database to build secure, fast and maintainable applications. Too many people think of databases as simple "bit buckets", good for little more than reading and writing rows of data, with all the interesting and important work done in the application layer. That might be true for some databases, but the Oracle Database is packed full of features that can greatly simplify application development, while at the same time providing better performance. These features include SQL, PL/SQL, edition-based redefinition, Oracle Text, Oracle Spatial, Oracle REST Data Services, and so much more.

Of course, Oracle Developer Advocates are not the only people inside and outside of Oracle to take on this mission. We plan to complement existing efforts by focusing on the creation of high quality multimedia resources, including videos, podcasts, MOOCs and more, that will more effectively transfer knowledge and reach the next generation of developers, who grew up on YouTube, Twitter, Facebook and LinkedIn.

If you need help on any aspect of Oracle Database - even something the team is not expert in - don't hesitate to get in touch with any of us (Steven's email is right there...click on it!), and we will make sure the right person gets your question.

Steven Feuerstein - Team Lead

Steven_Feuerstein.jpg

Steven Feuerstein is an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O'Reilly Media). Steven has been developing software since 1980, spent five years with Oracle back in the "old days" (1987-1992), and was PL/SQL Evangelist for Quest Software (and then Dell) from January 2001 to February 2014 - at which point he returned joyfully to Oracle Corporation. He was one of the original Oracle ACE Directors and writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG's Lifetime Achievement Award (2009).

YouTube: Practically Perfect PL/SQL

Twitter: @sfonplsql (with an added bonus of his personal Twitter account @stevefeuerstein)

Blog: stevenfeuersteinonplsql.blogspot.com

Email: steven.feuerstein@oracle.com


Connor McDonald

Connor_McDonald.jpg

Connor McDonald has, over the past 18 years, worked with Oracle Database systems in Australia, the UK, Southeast Asia, Europe, and the United States. He's co-authored 3 books, and is a popular speaker at Oracle conferences around the world, specializing in topics on the database engine, in particular SQL and PL/SQL. He has twice won the Inspirational Speaker award by the UK Oracle User Group. Whether speaking in a full day seminar to a room of developers, or just a "Hey, check out this cool feature" to the person sitting next to him, Connor has always loved sharing information about technology with others. He and Chris Saxon now form the Ask Tom Answer Team.

Twitter: @connor_mc_d

YouTube: Simply Smarter SQL

Blog: connormcdonald.wordpress.com


Chris Saxon

headshot-small.jpg

Over the past ten years Chris has worked as an Oracle developer, DBA and architect. During this time he's built up a passion for interacting with data using SQL and helping others do the same. Combining this with a love of games and quizzes, Chris started the Database Design quiz on the PL/SQL Challenge in 2013. He's looking forward to helping spread the word about SQL so that people get the most out of their data. He and Connor McDonald now form the backbone of the Ask Tom Answer Team, with Oracle Database product managers and developers also pitching in around their areas of expertise.

YouTube: The Magic of SQL YouTube Channel

Twitter: @sqldaily , @chrisrsaxon

Blogs: blogs.oracle.com/sql


Comments

jaramill

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

John Thorton

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? 

unknown-7404

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 States

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

WP v.2

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

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;

John Thorton

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;

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

WP v.2

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

WP v.2

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

unknown-7404

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.

unknown-7404

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.

Paulzip

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.

Solomon Yakobson

You are right on OP's code not handling NULLs, but as you said, "if one of the values is null, it won't insert", so it just can't be the cause of inserting null as new clob value. I bet what OP thinks is NULL is in fact EMPTY_CLOB():

SQL> create table tbl(null_clob clob,empty_clob clob)

  2  /

Table created.

SQL> insert into tbl

  2  values(to_clob(null),empty_clob())

  3  /

1 row created.

SQL> select * from tbl

  2  /

NULL_CLOB EMPTY_CLOB

--------- ----------

SQL>

And if we can't even visualize visualize it because empty_clob() is locator to NULL string :

SQL> set null >>>NULL<<<

SQL> select * from tbl

  2  /

NULL_CLOB  EMPTY_CLOB

---------- ----------

>>>NULL<<< >>>NULL<<<

SQL>

But we can:

SQL> select * from tbl where null_clob is null;

NULL_CLOB  EMPTY_CLOB

---------- ----------

>>>NULL<<< >>>NULL<<<

SQL> select * from tbl where empty_clob is null;

no rows selected

SQL> select * from tbl where dbms_lob.compare(empty_clob,empty_clob()) = 0;

NULL_CLOB  EMPTY_CLOB

---------- ----------

>>>NULL<<< >>>NULL<<<

SQL>

SY.

Paulzip

Yes, I expressed I couldn't explain what he was saying happening, but highlighting there was definitely a problem with his logic. I had the same hunch it could be empty clob related too, but without some definitive information from OP, felt circumspect about it.

1 - 12

Post Details

Added on Jul 1 2015
0 comments
10,608 views