Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Location
    Bangalore, India
    Posts
    5

    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:

    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?

    Please help.

    Thanks and Regards,
    Sim

  2. #2
    Join Date
    Sep 2004
    Posts
    17
    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.

    Alex

  3. #3
    Join Date
    Dec 2003
    Location
    Bangalore, India
    Posts
    5
    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?

  4. #4
    Join Date
    Sep 2004
    Posts
    17
    How you are writing varchar2 data to the clob variable without using dbms_lob package? I do it that way using dbms_lob:

    PHP Code:
    declare
      
    changed_data clob;
      
    str_data     varchar2(20) := 'Text';
    begin
      dbms_lob
    .createtemporary (changed_datatrue10);
      
    dbms_lob.open (changed_datadbms_lob.lob_readwrite);
      
    dbms_lob.writeappend (changed_datalength (str_data), str_data);
    end
    You don't need dbms_lob if you only take the lob data from your table. But if there are other types you will need dbms_lob - afaik.

    Alex

  5. #5
    Join Date
    Dec 2003
    Location
    Bangalore, India
    Posts
    5
    DECLARE
    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 ) )
    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.

  6. #6
    Join Date
    Sep 2004
    Posts
    17
    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 ...

    Alex

  7. #7
    Join Date
    Dec 2003
    Location
    Bangalore, India
    Posts
    5
    yes, i'm working on Oracle 9i. I have tried with CLOB data which is of size >200K.

    Thanks for your help and suggestions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •