Oracle Apex format multiple JSON requests to one

Question

I wrote a PL/SQL Code which requests a Dremio Service and receives JSON with database output. The output is saved to a REST_COLLECTION clob. The output will be shown in an APEX-Page. The problem with this Dremio service is, that I can only receive 500 datas at once. The solution was a loop and a concatination of the results each time..... Thats the problem I have.

The JSON looks like this:

{"rowCount":307,"schema":[{"name":"EXPORTDATUM","type":{"name":"DATE"}},   
{"name":"DOKUMENTE","type":{"name":"DOUBLE"}},{"name":"SEITEN","type":{"name":"DOUBLE"}}, 
{"name":"STAPELKLASSE","type":{"name":"VARCHAR"}},{"name":"FORMULAR","type"
{"name":"VARCHAR"}}],"rows":[{"EXPORTDATUM":"2019-02-04","DOKUMENTE":9.0,"SEITEN":37.0,"STAPELKLASSE":"TEST","FORMULAR":"TEST"}]}

Concat the JSON strings each time leads to a JSON error at the Page view, because of multiple root elements.

SUBSTR given strings is not a commom way, Dremio outpup could may change.

Creating multiple clobs each time is possible, but my query then dont know the amount of clobs. Query looks like this btw:

select 
    to_date(j."EXPORTDATUM", 'YYYY-MM-DD') as "EXPORTDATUM",
    j."DOKUMENTE",
    j."SEITEN",
    j."STAPELKLASSE",
    j."FORMULAR"

from apex_collections c, json_table(
    c.clob001 format json,
    '$.rows[*]'
    columns (
        "EXPORTDATUM"   VARCHAR2(4000)  path '$.EXPORTDATUM',
"DOKUMENTE"  VARCHAR2(4000) path '$.DOKUMENTE',
"SEITEN"   VARCHAR2(4000) path '$.SEITEN',
"STAPELKLASSE"   VARCHAR2(4000)  path '$.STAPELKLASSE',
"FORMULAR"   VARCHAR2(4000)  path '$.FORMULAR'
    )
) j

where c.collection_name = 'REST_COLLECTION'

I hope there is a solution for concat multiple JSONs without substring it.

Salim Hlayel · Accepted Answer · 2020-05-07 20:43:13Z

To mimic your JSON rows in the collection I have created a simple SQL with Union.

You can use APEX_JSON API to generate a JSON document.

The following is a sample code that can get you started. You just need to change a little to suit your requirements.

declare
l_json clob;
begin
  apex_json.initialize_clob_output;
  apex_json.open_array;
for rec in (select  * from(
select 
    to_date(j."EXPORTDATUM", 'YYYY-MM-DD') as "EXPORTDATUM",
    j."DOKUMENTE",
    j."SEITEN",
    j."STAPELKLASSE",
    j."FORMULAR"

from  json_table(
   '{
    "rowCount": 307,
    "schema": [{
            "name": "EXPORTDATUM",
            "type": {
                "name": "DATE"
            }
        },
        {
            "name": "DOKUMENTE",
            "type": {
                "name": "DOUBLE"
            }
        }, {
            "name": "SEITEN",
            "type": {
                "name": "DOUBLE"
            }
        },
        {
            "name": "STAPELKLASSE",
            "type": {
                "name": "VARCHAR"
            }
        }, {
            "name": "FORMULAR",
            "type": {
                "name": "VARCHAR"
            }
        }
    ],
    "rows": [{
        "EXPORTDATUM": "2019-02-04",
        "DOKUMENTE": 9.0,
        "SEITEN": 37.0,
        "STAPELKLASSE": "row1",
        "FORMULAR": "row1"
    },
    {
        "EXPORTDATUM": "2019-02-04",
        "DOKUMENTE": 9.0,
        "SEITEN": 37.0,
        "STAPELKLASSE": "row2",
        "FORMULAR": "row2"
    }]
}' format json,
    '$.rows[*]'
    columns (
        "EXPORTDATUM"   VARCHAR2(4000)  path '$.EXPORTDATUM',
"DOKUMENTE"  VARCHAR2(4000) path '$.DOKUMENTE',
"SEITEN"   VARCHAR2(4000) path '$.SEITEN',
"STAPELKLASSE"   VARCHAR2(4000)  path '$.STAPELKLASSE',
"FORMULAR"   VARCHAR2(4000)  path '$.FORMULAR'
    )
) j
union all
select 
    to_date(j."EXPORTDATUM", 'YYYY-MM-DD') as "EXPORTDATUM",
    j."DOKUMENTE",
    j."SEITEN",
    j."STAPELKLASSE",
    j."FORMULAR"

from  json_table(
   '{
    "rowCount": 307,
    "schema": [{
            "name": "EXPORTDATUM",
            "type": {
                "name": "DATE"
            }
        },
        {
            "name": "DOKUMENTE",
            "type": {
                "name": "DOUBLE"
            }
        }, {
            "name": "SEITEN",
            "type": {
                "name": "DOUBLE"
            }
        },
        {
            "name": "STAPELKLASSE",
            "type": {
                "name": "VARCHAR"
            }
        }, {
            "name": "FORMULAR",
            "type": {
                "name": "VARCHAR"
            }
        }
    ],
    "rows": [{
        "EXPORTDATUM": "2019-02-04",
        "DOKUMENTE": 9.0,
        "SEITEN": 37.0,
        "STAPELKLASSE": "row3",
        "FORMULAR": "row3"
    },
    {
        "EXPORTDATUM": "2019-02-04",
        "DOKUMENTE": 9.0,
        "SEITEN": 37.0,
        "STAPELKLASSE": "row4",
        "FORMULAR": "row4"
    }]
}' format json,
    '$.rows[*]'
    columns (
        "EXPORTDATUM"   VARCHAR2(4000)  path '$.EXPORTDATUM',
"DOKUMENTE"  VARCHAR2(4000) path '$.DOKUMENTE',
"SEITEN"   VARCHAR2(4000) path '$.SEITEN',
"STAPELKLASSE"   VARCHAR2(4000)  path '$.STAPELKLASSE',
"FORMULAR"   VARCHAR2(4000)  path '$.FORMULAR'
    )
) j) )
loop
    apex_json.open_object;
    apex_json.write('EXPORTDATUM', rec.EXPORTDATUM);
    apex_json.write('DOKUMENTE', rec.DOKUMENTE);
    apex_json.write('SEITEN', rec.SEITEN);
    apex_json.write('STAPELKLASSE', rec.STAPELKLASSE);
    apex_json.write('FORMULAR', rec.FORMULAR);
    apex_json.close_object;
end loop;
    apex_json.close_array;
    dbms_output.put_line(apex_json.get_clob_output( p_free => true ) );
end;

I hope this solves your issue.

Collectives™ on Stack Overflow

Oracle Apex format multiple JSON requests to one

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related