Unanswered: ORA-06502: PL/SQL: numeric or value error in trigger
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:
Transaction UPDATE BL1_XML_CONFIG set XML=@XML, SYS_UPDATE_DATE=SYSDATE, DL_SERVICE_CODE=@DL_SERVICE_CODE, APPLICATION_ID=@APPLICATION_ID WHERE SCHEMA='AMC_BILLING' AND OBJECT_NAME='PROCESS' execution failed!.
System.Data.OleDb.OleDbException: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "FINREFO26.AT7_BL1_XML_CONFIG_A_U", line 79
ORA-04088: error during execution of trigger 'FINREFO26.AT7_BL1_XML_CONFIG_A_U'
at amdocs.Infra.Net.CSharp.Data.Backends.OLE.BackendO LE.executeImpl(Transaction transaction, Object preparedTransaction, Boolean asynchronous, Object parameters)
at amdocs.Infra.Net.CSharp.Data.Backends.Backend.exec ute(Transaction transactions, Boolean asynchronous, Object parameters)
I have searched in the net for an answer. From what i read, i have a doubt - is it the size of the variable [varchar2(5000) ] that is giving the problem?
Can I change the variable to type CLOB? If I change the type to CLOB, what is the maximum amout of data it can hold? Can I access and modify the data of a CLOB variable in the same way as varchar2?
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?
sql_ChangedData CLOB; -- this was sql_ChangedData varchar2(5000); earlier
For each column in the table, i write
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 ) )
sql_ChangedData:=sql_ChangedData||'<Field name="columnName"><OldValue>'||:OLD.columnName||'</OldValue> <NewValue>'||:NEW.columnName||'</NewValue></Field>';
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 ...