Cloud - serialize IFS generated PL/SQL record type to JSON and convert it back later - on Oracle 19c

Question

Hi, I would like get some suggestions how to solve the following problem:

In short:

I want to convert an IFS generated PL/SQL defined complex type (record,array, etc) to JSON and back.

The ’to’ direction is ok, IFS generated code does the job (a sample from Core):

But I could not find a good way to do the opposite direction: JSON -> PL/SQL record type

Context:

We are upgrading a customer from App9 to Cloud.

In App9 there were some webservices implemented in IFS.

Some of their operations were too slow for online response, so the webservice just accepted the parameters in the request, returned a ’result id’ to the caller and started a background job to generate the results.

The caller later periodically queried for the results (polling) with the ’result id’ and if the result was ready it was returned.

The background job in App9 generated the result in XML format, saved it in a CLOB column in a result table from which it was returned to the caller later, for the Nth polling.

Now, upgrading to Cloud and JSON, we need to do something similiar and background job should generate the result (of the complex record type), serialize it (to JSON preferably), save it a to a result table and when later we need to return the result to the caller, we need to find it in the result table, convert it back from JSON to the IFS auto-generated PL/SQL record type and return it, doing it the IFS standard REST/Odata way with Projections and so on.

But I could not find a way so far for this :” convert it back from JSON to the IFS auto-generated PL/SQL record type”.

I do not want to write a manual converter for the JSON -> PL/SQL record type direction for each of the 30+ structures.

In Oracle 21c they say that this one works, but it does not work in Oracle 19c which we are on:

-- Direct conversion (Oracle 21c and higher)

v_json := JSON_SERIALIZE(v_rec);

-- Direct conversion from JSON string to PL/SQL Record

v_rec := JSON_VALUE(v_json, '$' RETURNING emp_rec_type);

So how can I solve it in a fairly easy, nice way, what is your suggestion?

IFS version: 25R2

InfFilipV · Answer

Hi,JSON_VALUE(...RETURNING <type>) is already supported in 19c, but only for SQL object types (CREATE TYPE ... AS OBJECT) -- not for PL/SQL record types, which is what the framework generates for structures.What worked instead: a small generic package that reads a structure's fields from ALL_PLSQL_TYPE_ATTRS/ALL_PLSQL_COLL_TYPES and dynamically builds a PL/SQL block using JSON_OBJECT_T/JSON_ARRAY_T, run via EXECUTE IMMEDIATE with the record bound in through USING. Handles flat fields, nested records, and nested arrays (including arrays of records) with no DDL and no per-structure hand-written code.Other options considered: SQL object types + native JSON_VALUE/JSON_OBJECT (works on 19c, but needs a CREATE TYPE per structure), and SYS.ANYDATA (same object-type requirement underneath, no real savings). Went with the JSON_OBJECT_T approach since it needed no DDL at all.Script attached.BRFilip

Did you find what you're looking for? If not:

Sign up

Login to the community