JSON data and XML data can be used in Oracle Database in similar ways. Unlike relational data, both can be stored, indexed, and queried without any need for a schema that defines the data. Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views.
In our usecase we are going to focus on generating JSON data for migration purposes out of Oracle DB Data.
You can generally use SQL code, including SQL code that accesses JSON data, within PL/SQL code. You cannot use an empty JSON field name in any SQL code that you use in PL/SQL.
The following SQL/JSON functions and conditions are also available as built-in PL/SQL functions: json_value, json_query, json_object, json_array, and json_exists. (In PL/SQL, SQL condition json_exists is a Boolean function.)
To generate JSON object out of Oracle DB data following new built-in functions can be used :
We have a usecase, where we are asked to migrate specific customer information from CRM System Oracle Siebel to new system via json files. We will use new JSON SQL function to achieve this.
The Table S_CONTACT contains customer data and we are asked specifically to extract marketing relevant data SUPPRESS_CALL_FLG, SUPPRESS_EMAIL_FLG, SUPPRESS_MAIL_FLG.
These marketing flag are to be in the array [] json element for which we may use json_array. Every JSON element is to be created by json_object function.
In the normal SQL relation DB world the query looks like :
SELECT C.CSN,
NVL(C.SUPPRESS_CALL_FLG,'N'),
NVL(C.SUPPRESS_MAIL_FLG,'N'),
NVL(C.SUPPRESS_EMAIL_FLG,'N'),
NVL(C.X_SUPPRESS_RAMA_FLG,'N')
FROM siebel.S_CONTACT C
To build a json message for the export, following SQL statement is to be used :
select json_object
('requestInformation' value 'export marketing customer',
'identifier' VALUE csn,
'purposes' VALUE json_array(json_object('name' value 'NO_CALL','TransactionType' value decode(CON.SUPPRESS_CALL_FLG,'Y','NO_OPT_OUT','OPT_OUT')),
json_object('name' value 'NO_EMAIL','TransactionType' value decode(CON.SUPPRESS_EMAIL_FLG,'Y','NO_OPT_OUT','OPT_OUT')),
json_object('name' value 'NO_MAIL','TransactionType' value decode(CON.SUPPRESS_MAIL_FLG,'Y','NO_OPT_OUT','OPT_OUT'))))
from siebel.s_contact con,siebel.s_party par
where
con.par_row_id = par.row_id and con.PRIV_FLG='N' and par.PARTY_TYPE_CD <> 'Suspect' and con.CUST_STAT_CD = '1' and con.X_CAMP_CON_FLG = 'N' and con.EMP_flg <> 'Y' and con.CON_CD = 'Partner'
and CON.CSN IS NOT NULL and CON.X_SOURCE = '100'
Results ma look like :
{"requestInformation":"export marketing customer","identifier":"P-XXXX-1212","purposes":[{"name":"NO_CALL","TransactionType":"OPT_OUT"},{"name":"NO_EMAIL","TransactionType":"OPT_OUT"},{"name":"NO_MAIL","TransactionType":"OPT_OUT"}]}
{"requestInformation":"export marketing customer","identifier":"P-AAAA-BBBB","purposes":[{"name":"NO_CALL","TransactionType":"OPT_OUT"},{"name":"NO_EMAIL","TransactionType":"OPT_OUT"},{"name":"NO_MAIL","TransactionType":"OPT_OUT"}]}
{"requestInformation":"export marketing customer","identifier":"P-4444-3333","purposes":[{"name":"NO_CALL","TransactionType":"OPT_OUT"},{"name":"NO_EMAIL","TransactionType":"OPT_OUT"},{"name":"NO_MAIL","TransactionType":"OPT_OUT"}]}