If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: ORA-06502: PL/SQL: numeric or value error in trigger

Hi,

I have a trigger on a table, which has a clob field. Whenever there is an update on this table, I have a trigger which will compare the ld and :new values of each column in the table an put the old and new values of changed columns into a variable. The variable which holds this changed data is declared as varchar2(5000). In some cases, when I'm updating the data in the clob table, I'm getting the following error:

What are you doing with the varchar2(5000) variable? The max length of varchar2 in tables is 4000, may be, that's your problem when you insert the varchar2(5000) variable into such a column.
You can store your data in a clob. to work with this, you need dbms_lob supplied package.

I am constructing a string of changed data and storing it in the varchar2(5000) variable. This data is then populated into a CLOB column in another table. I also have a CLOB column in my first table (table on which the trigger is defined). So, when the data in CLOB column is changed, I'm getting the error. I think it is because of the size constraint of the variable. I changed the variable to type CLOB. now I'm not getting the error. But i'm not mentioning dbms_lob package explicitly anywhere in my trigger. How do I use this package. Will there be any error in the future as i'm not using it?

IF (:NEW.columnName <> :OLD.columnName OR
(:NEW.columnName IS NOT NULL AND :OLD.columnName IS NULL ) OR
(:NEW.columnName IS NULL AND :OLD.columnName IS NOT NULL ) )
THEN
sql_ChangedData:=sql_ChangedData||'<Field name="columnName"><OldValue>'||:OLD.columnName||'</OldValue> <NewValue>'||:NEW.columnName||'</NewValue></Field>';
END IF;

One if the column in the table is a CLOB. I do the same for that column also.
later, i use this variable in the INSERT query, to populate a CLOB column in another table. It looks like the trigger is working fine.

I have modified only the declaration of the variable - from varchar2 to CLOB.
Do you find any possible problem in this? I'm sorry if this qn looks silly, but i donno abt CLOB handling. that's y.

You arre on Oracle 9i? I've tryed this on Oracle 8i and there the concatination operator || can't concat lobs to varchars, that's why I used dbms_lob.writeappend. If the || works for you it's fine. The only thing I would try is, if it already works with lobs bigger than 32K or 64K ...