SQL & PL/SQL

Hi All,

Need your Help in solving the below concern. I am getting below error while trying to insert a record into one of the table involving CLOB datatype.

Oracle DB Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Error: ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6698, maximum: 4000)

Problem Description:

I am having a master table : Atc_request_rpt having column : REPORT_FILTER_CRITERIA(CLOB) and when there is a Update/insert in that field there

needs to a record inserted into Audit table: Atc_request_rpt_audit into columns REPORT_FILTER_CRITERIA_new(clob),REPORT_FILTER_CRITERIA_old(clob).

when there is an insert/update done on that REPORT_FILTER_CRITERIA field in Atc_request_rpt (master table), the trigger through which data inserts into Audit table

Atc_request_rpt _audit(Audit table) is throwing an error :: ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6698, maximum: 4000)

code:

CREATE OR REPLACE TRIGGER ATC_RPT_REQ_AUDIT_TRG_NEW
   AFTER INSERT OR UPDATE
   ON Atc_request_rpt REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
DECLARE
   l_action_code   VARCHAR2 (2);
BEGIN
   IF INSERTING
   THEN
      l_action_code := 'I';
   ELSIF UPDATING
   THEN
      l_action_code := 'U';
   END IF;

DBMS_OUTPUT.PUT_LINE('1');
   IF (l_action_code = 'I'
       OR (l_action_code = 'U'
           AND ( NVL ( :OLD.REPORT_FILTER_CRITERIA, 'X')  <>
                     NVL ( :NEW.REPORT_FILTER_CRITERIA, 'X')
                )))

   THEN
   DBMS_OUTPUT.PUT_LINE('2');
      INSERT
        INTO Atc_request_rpt_audit (REQUEST_ID,
                                                AUDIT_TYPE,
                                                REPORT_FILTER_CRITERIA_NEW,
                                                REPORT_FILTER_CRITERIA_OLD
                                                )
      VALUES (
                DECODE (:OLD.REQUEST_ID,
                        NULL, :NEW.REQUEST_ID,
                        :OLD.REQUEST_ID),
                l_action_code,
                DECODE (
                   l_action_code,
                   'I',  :NEW.REPORT_FILTER_CRITERIA,
                   'U',  :NEW.REPORT_FILTER_CRITERIA),
                DECODE (
                   l_action_code,
                   'I', NULL,
                   'U', :OLD.REPORT_FILTER_CRITERIA)
                );

   END IF;
      EXCEPTION
  WHEN OTHERS THEN
                 DBMS_OUTPUT.PUT_LINE(sqlerrm);

END;
/

So I have added dbms_lob.substr( :OLD.REPORT_FILTER_CRITERIA, 4000, 1 ), now record is getting inserted but the result of the column is truncated to 4000(characters) but

the requirement is that the REPORT_FILTER_CRITERIA value in Master table: ATC_RPT_REQ should be inserted into audit table : ATC_RPT_REQ_AUDIT without truncation of 4000 characters.

is there any way to do it ?

Thanks