Search This Blog

Thursday, January 16, 2020

Json Parsing Using Json Table in PLSQL Oracle 12c Release 1

DECLARE
V_JSON VARCHAR2(4000);
ECO VARCHAR2(3000);
BEGIN
/*
{
"ChallanNo":"2020-0000000047",
"RefNo":"1234567",
"BankBranchNameEn":"BANK ASIA LTD., HEAD OFFICE",
"BankBranchNameBn":"BANK ASIA LTD., HEAD OFFICE",
"ClientNameEn":"Md Zafar Iqbal Edit",
"ClientNameBn":"মোঃ জাফর ইকবাল আপডেট",
"Amount":3450.0,
"ChallanEconomic":[
{
"EconomicCode":"1422311",
"EconomicNameEn":"Passport fee",
"EconomicNameBn":"পাসপোরà§à¦Ÿ ফি",
"ChallanSubNo":"01",
"Amount":3000.0
},
{
"EconomicCode":"1141102",
"EconomicNameEn":"Supplementary duty on domestically produced commodities",
"EconomicNameBn":"দেশজ উৎপাদিত পণà§à¦¯à§‡à¦° ওপর সমà§à¦ªà§‚রক শà§à¦²à§à¦•",
"ChallanSubNo":"02",
"Amount":450.0
}
],
"SuccessFlag":"Y"
}
*/
  SELECT a.JSON_RESPONSE.ChallanEconomic.EconomicCode
     INTO eco
     FROM
     IN_OUT_JSON a
    WHERE REQUESTID = 27;

   eco := LTRIM (RTRIM (eco, ']'), '[');

   FOR FOO IN (    SELECT REGEXP_SUBSTR (eco,
                                         '[^,]+',
                                         1,
                                         LEVEL)
                             TXT
                     FROM DUAL
               CONNECT BY REGEXP_SUBSTR (eco,
                                         '[^,]+',
                                         1,
                                         LEVEL)
                             IS NOT NULL)
   LOOP
      DBMS_OUTPUT.PUT_LINE (FOO.TXT);
   END LOOP;
END;

No comments:

Post a Comment

Split A String By Coma (,)

Step 1.  create or replace type split_tbl as table of varchar2(32767); Step 2. create or replace function split (     p_list varchar2,...