0
Declare 
     sqlstmt1 clob;
     TYPE uwa_assoc_array IS TABLE OF CLOB INDEX BY PLS INTEGER;
      uwa_data uwa_assoc_array;
      prev number;
BEGIN 
   sqlstmt1:= ‘create or replace ins_wafer_summary_extend_trig instead of insert on insp_wafer_summary_extend referencing new as new old as old for each row’ || chr(10);
     sqlstmt1 := sqlstmt1 || ‘DECLARE uwadata clob; BEGIN’ || CHR(10);
      for confrecord IN (SELECT column_key,column_name from udbc_defect_extend_conf where event_type = 2) LOOP
           uwa_data(uwa_data.COUNT+1) := CHR(10) || ‘if :new.’ || trim(confrecord.column_name) || ‘!= :old.’ || trim(confrecord.column_name) || ‘then’ || CHR(10) || ‘ dbms_lob.append(uwadata, ‘’’|| trim(confrecord.column_key) || ‘:’ || ‘’’ || : new.’ || trim(confrecord.column_name) || ‘);’ || CHR(10) || ‘end if;’;
       End loop;

       For i in 1..uwa_data.COUNT LOOP
         dbms_lob.append(sqlstmt1,uwa_data(i);
       End loop;

       sqlstmt1 := sqlstmt1 || CHR(10) || ‘INSERT INTO insp_wafer_summary_extend_m(inspection_time,wafer_key,UWA_ATTR) values (:new.inspection_time,:new.wafer_key,uwadata);’ || chr(10) || ‘END;’;
       Execute immediate sqlstmt1;
End;

Insp_wafer_summary_extend is a view and it consists of nearly 700 columns and I tried inserting values into it. Untill 450 values everything was working fine but after that I am gettting

ORA-01499: result of string concatenation is too long error

I expect to insert values for all my 700 columns.

Sanjaykrishnan M
  • 1
  • 1
  • If this is a one-off way to generate the trigger code, why not use SQL to generate the list of columns and simply plug that into the middle of the manually written rest of the trigger code? – Boneist Aug 14 '23 at 11:27

1 Answers1

0

While a CLOB in SQL is virtually unlimited in size, PL/SQL cannot handle a string larger than 32K. If your column names concatenate into a string longer than that, your script will fail. The issue is that while your variable is defined as a CLOB, concatenation produces VARCHAR2, and EXECUTE IMMEDIATE takes VARCHAR2 as input, so you'll never be able to get beyond 32K in this way.

The solution, as described here (Oracle - How to handle 32K+ string length in variables) is to use the DBMS_SQL package.

pmdba
  • 6,457
  • 2
  • 6
  • 16