1.txt (177.81 KB)DB Version: 12.2.0.1.0
Hi,
i have attached xml. when i am trying to convert to json, i am getting error
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 123564, maximum: 4000).
It is for the file_data column. Appreciate your assistance
DECLARE
l_json_body CLOB;
CURSOR xmldata IS
SELECT *
FROM xml_tab;
BEGIN
FOR rec in xmldata
loop
SELECT json_arrayagg(json_object(
'document_id' VALUE document_id
,'file_category' VALUE file_category
,'file_type' VALUE file_type
,'content_type' VALUE content_type
,'file_data' VALUE file_data
))
INTO l_json_body
FROM xmltable('/attachments/attachment' passing(rec.xml_data)
columns
document_id NUMBER path 'document_id'
,file_category VARCHAR2(240) path 'file_category'
,file_type VARCHAR2(240) path 'file_type'
,content_type VARCHAR2(240) path 'content_type'
,file_data CLOB path 'file_data'
);
end loop;
END;