Home » SQL & PL/SQL » SQL & PL/SQL » Converting XML to JSON using Apex (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Converting XML to JSON using Apex [message #681584] Wed, 05 August 2020 14:34 Go to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

Hello Everyone,

There is a table(xxln_vs_publish_stg) has xmltype column(xml_data) which stores XML data. I have a requirement to convert XML data to json data.
For that, I am using apex_json.write to convert. While executing below logic for changing it, I am getting Error as: ORA-20987: APEX - JSON.WRITER.NOT_OPEN - Contact your application administrator.
Can you please help what is it I am doing which is wrong.
DECLARE
    l_xml            sys.xmltype;
    l_amount         BINARY_INTEGER := 32000;
    l_buffer         RAW(32000);
    l_pos            INTEGER := 1;
    l_stage          NUMBER;
    content          CLOB;
    content_blob     BLOB;
    content_length   NUMBER;
BEGIN
    SELECT
        xml_data
    INTO l_xml
    FROM
        xxln_vs_publish_stg
    WHERE
        xml_data IS NOT NULL
        AND ROWNUM < 2;

    content := xmltype.getclobval(l_xml);
    xxln.convert_clob_to_blob(content, content_blob);
    content_length := dbms_lob.getlength(content_blob);
    dbms_output.put_line(content_length);
    apex_json.initialize_clob_output;
    IF dbms_lob.getlength(content_blob) < 32000 THEN
        apex_json.write(content);
    ELSE
        WHILE l_pos < content_length--DBMS_LOB.GETLENGTH(v_output_file_blob)
         LOOP
            dbms_lob.read(content_blob, l_amount, l_pos, l_buffer);
            apex_json.write(content);
            l_pos := l_pos + l_amount;
        END LOOP;
    END IF;

    dbms_output.put_line(apex_json.get_clob_output);
    apex_json.free_output;
END;

Thanks
Mandeep Kaur
Re: Converting XML to JSON using Apex [message #681585 is a reply to message #681584] Wed, 05 August 2020 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know but there are many hits in Google:
https://www.google.com/search?q=ORA-20987%3A+APEX+-+JSON.WRITER.NOT_OPEN+-+Contact+your+application+administrator

Re: Converting XML to JSON using Apex [message #681586 is a reply to message #681585] Wed, 05 August 2020 20:44 Go to previous message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

I went through these hits already but did not find any resolution for the issue.I even checked Oracle Metalink for resolution but did not find any. Thats why I have this question here.
Previous Topic: set default values to current epoch (2 merged)
Next Topic: - PDML disabled because single fragment or non partitioned table used
Goto Forum:
  


Current Time: Mon Sep 21 02:37:38 CDT 2020