Hi All,
I am try to convert / read data of a clob column but getting ORA-22835.
Kindly requesting you to advise how I can read a CLOB data as rows in oracle database 12.1.
WITH t (order_number, line#, line, debug_message) as (
SELECT order_number, 1, regexp_substr (TO_CHAR (debug_message), '^.*?$', 1, 1, 'm'), substr (TO_CHAR (debug_message), regexp_instr (debug_message, '$', 1, 1, 1, 'm') + 1)
FROM order_logs
WHERE order_number = 789587
UNION ALL
SELECT order_number, line#+1, regexp_substr(debug_message, '^.*?$', 1, 1, 'm'), substr(debug_message, regexp_instr(debug_message, '$', 1, 1, 1, 'm')+1)
FROM t
WHERE debug_message IS NOT NULL) CYCLE order_number, line# SET IS_CYCLE to '1' default '0'
SELECT line#, line
FROM t
ORDER BY order_number, line#;
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 764123, maximum: 4000)
22835. 00000 - "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*Cause: An attempt was made to convert CLOB to CHAR or BLOB to RAW, where
the LOB size was bigger than the buffer limit for CHAR and RAW
types.
Note that widths are reported in characters if character length
semantics are in effect for the column, otherwise widths are
reported in bytes.
*Action: Do one of the following
1. Make the LOB smaller before performing the conversion,
for example, by using SUBSTR on CLOB
2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.