Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====Einen CLOB gesplittet in SQL*Plus ausgeben  um einen - ORA-22835: Buffer too small for CLOB - zu vermeiden=====


Das Problem:

<fc #800000>**ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4509, maximum: 4000)**</fc> 


Bei der Auswertung des Error Trigger Logging ( siehe =>  [[dba:oracle_sqlfehler_protokoll|Erstellen eines Protokolls zur Überwachung von fehlerhaften SQL Statements in der Datenbank - AFTER SERVERERROR Trigger verwenden]] ) kommt es beim einfachen Selektieren der Log Tabelle zu einem **ORA-22835: Buffer too small**.

Mehr als 4k an Zeichen kann in SQL*Plus aus einer CLOB Spalte nicht direkt gelesen werden, ein Setzen des Buffers für Long/RAW Daten mit "set buffer 65000" hilft hier leider auch nicht.

Eine Lösung kann sein den CLOB in 4K Blöcke mit **dbms_lob.substr (clob, amount, position )** aufzusplieten und die mehren Spalten auszugeben.



Die Lösung:


CLOB in einzelne "Chunks" aufteilen:
<code sql>
with ErrorLog as 
  ( select   stmt
           , log_date
		   , LOG_USR
		   , ERR_NR
		   , substr(ERR_MSG,1,300) mesg
		   , dbms_lob.getlength(STMT) len 
	FROM SYSTEM.ora_errors 
   WHERE nvl(log_usr,'n/a') NOT IN ('SYS','SYSMAN','DBSNMP')
	)
  select
		COUNT (*) AS anzahl
	  , to_char(min(log_date),'dd.mm.yyyy hh24:mi') first_log_entry
	  , to_char(max(log_date),'dd.mm.yyyy hh24:mi') last_log_entry		
	  , LOG_USR
	  , ERR_NR
	  , mesg
	  , dbms_lob.substr(stmt,4000,1) sql_part1
	  , case when len > 4000 then dbms_lob.substr(stmt,4000,4001)    end sql_part2
	  , case when len > 8000 then dbms_lob.substr(stmt,4000,8001)    end sql_part3
	  , case when len > 12000 then dbms_lob.substr(stmt,4000,12001)  end sql_part4
	  , case when len > 16000 then dbms_lob.substr(stmt,4000,165001) end sql_part5
  FROM ErrorLog
 GROUP BY LOG_USR
	  , ERR_NR
	  , mesg
	  , dbms_lob.substr(stmt,4000,1) 
	  , case when len > 4000 then dbms_lob.substr(stmt,4000,4001)    end 
	  , case when len > 8000 then dbms_lob.substr(stmt,4000,8001)    end 
	  , case when len > 12000 then dbms_lob.substr(stmt,4000,12001)  end 
	  , case when len > 16000 then dbms_lob.substr(stmt,4000,165001) end 
ORDER BY 1 
/
</code>



==== Quellen ====


Oracle Doku => SUBSTR https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349


AskTom => https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9539494200346328435