Forum Stats

  • 3,758,577 Users
  • 2,251,411 Discussions
  • 7,870,248 Comments

Discussions

Unable to parse JSON in plsql

User_CI7SY
User_CI7SY Member Posts: 34 Green Ribbon

Hi ,I get below error while I try to write PLSQL code to parse JSON.

Error:


ORA-30625: method dispatch on NULL SELF argument is disallowed

ORA-06512: at line 130

30625. 00000 - "method dispatch on NULL SELF argument is disallowed"

*Cause:  A member method of a type is being invoked with a NULL SELF

      argument.

*Action:  Change the method invocation to pass in a valid self argument.



Here is my PLSQL code:I am trying to store searchResults into one JSON_OBJECT_T variable RES. "searchResults" is under "response" in the JSON data.

SET SERVEROUTPUT ON

DECLARE

DOC CLOB :='{

 "response" : {

  "status" : "Success",

  "statusDetails" : {

   "messages" : null

  },

  "didYouMean" : null,

  "binning" : null,

  "searchResults" : {

   "totalCount" : 6,

   "document" : [ {

    "content" : [ {

     "name" : "Catalog_number",

     "type" : "text",

     "value" : "VMVS2A250GP/MT"

    }, {

     "name" : "UPC",

     "type" : "text",

     "value" : "782274850955"

    }, {

     "name" : "EAN",

     "type" : "text",

     "value" : ""

    } ]

   }, {

    "content" : [ {

     "name" : "Catalog_number",

     "type" : "text",

     "value" : "165541"

    }, {

     "name" : "UPC",

     "type" : "text",

     "value" : ""

    }, {

     "name" : "EAN",

     "type" : "text",

     "value" : "4015081620593"

    } ]

   }, {

    "content" : [ {

     "name" : "Catalog_number",

     "type" : "text",

     "value" : "41200"

    }, {

     "name" : "UPC",

     "type" : "text",

     "value" : "051712623995"

    }, {

     "name" : "EAN",

     "type" : "text",

     "value" : ""

    } ]

   }, {

    "content" : [ {

     "name" : "Catalog_number",

     "type" : "text",

     "value" : "1303W"

    }, {

     "name" : "UPC",

     "type" : "text",

     "value" : "032664308703"

    }, {

     "name" : "EAN",

     "type" : "text",

     "value" : ""

    } ]

   }, {

    "content" : [ {

     "name" : "Catalog_number",

     "type" : "text",

     "value" : "EMBH108R30C"

    }, {

     "name" : "UPC",

     "type" : "text",

     "value" : ""

    }, {

     "name" : "EAN",

     "type" : "text",

     "value" : "4015081488230"

    } ]

   }, {

    "content" : [ {

     "name" : "Catalog_number",

     "type" : "text",

     "value" : "EL650USBIEC"

    }, {

     "name" : "UPC",

     "type" : "text",

     "value" : ""

    }, {

     "name" : "EAN",

     "type" : "text",

     "value" : "3553340620737"

    } ]

   } ]

  },

  "request" : {

   "searchApplication" : "etndisinv",

   "searchApplicationKey" : "abc123",

   "function" : "search",

   "searchTerms" : "ignore",

   "language" : "en_US",

   "startingRecordNumber" : 0,

   "numberOfRecordsToReturn" : 30,

   "filters" : [ {

    "filterName" : "Catalog_Number",

    "filterValue" : [ "VMVS2A250GP/MT", "165541" ],

    "preferenceValue" : null

   }, {

    "filterName" : "EAN",

    "filterValue" : [ "3553340620737", "4015081488230" ],

    "preferenceValue" : null

   }, {

    "filterName" : "UPC",

    "filterValue" : [ "051712623995", "032664308703" ],

    "preferenceValue" : null

   } ],

   "userName" : null

  }

 }

}';

J_DOC JSON_OBJECT_T;

RES JSON_OBJECT_T;


BEGIN

J_DOC :=JSON_OBJECT_T(DOC);

--DBMS_OUTPUT.PUT_LINE('JSON IS ' || J_DOC.STRINGIFY);

RES :=J_DOC.GET_OBJECT('searchResults');

DBMS_OUTPUT.PUT_LINE('JSON IS ' || RES.TO_STRING);

CASE WHEN J_DOC.IS_OBJECT

THEN DBMS_OUTPUT.PUT_LINE('RES IS JSON');

ELSE 

   DBMS_OUTPUT.PUT_LINE('RES IS NOT JSON');

END CASE;


END;

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,730 Black Diamond
    Accepted Answer
    DECLARE
        DOC CLOB :='{
     "response" : {
      "status" : "Success",
      "statusDetails" : {
       "messages" : null
      },
      "didYouMean" : null,
      "binning" : null,
      "searchResults" : {
       "totalCount" : 6,
       "document" : [ {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "VMVS2A250GP/MT"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : "782274850955"
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : ""
        } ]
       }, {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "165541"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : ""
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : "4015081620593"
        } ]
       }, {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "41200"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : "051712623995"
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : ""
        } ]
       }, {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "1303W"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : "032664308703"
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : ""
        } ]
       }, {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "EMBH108R30C"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : ""
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : "4015081488230"
        } ]
       }, {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "EL650USBIEC"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : ""
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : "3553340620737"
        } ]
       } ]
      },
      "request" : {
       "searchApplication" : "etndisinv",
       "searchApplicationKey" : "abc123",
       "function" : "search",
       "searchTerms" : "ignore",
       "language" : "en_US",
       "startingRecordNumber" : 0,
       "numberOfRecordsToReturn" : 30,
       "filters" : [ {
        "filterName" : "Catalog_Number",
        "filterValue" : [ "VMVS2A250GP/MT", "165541" ],
        "preferenceValue" : null
       }, {
        "filterName" : "EAN",
        "filterValue" : [ "3553340620737", "4015081488230" ],
        "preferenceValue" : null
       }, {
        "filterName" : "UPC",
        "filterValue" : [ "051712623995", "032664308703" ],
        "preferenceValue" : null
       } ],
       "userName" : null
      }
     }
    }';
        J_DOC JSON_OBJECT_T;
        RES JSON_OBJECT_T;
    BEGIN
        J_DOC :=JSON_OBJECT_T(DOC);
        --DBMS_OUTPUT.PUT_LINE('JSON IS ' || J_DOC.STRINGIFY);
        RES :=J_DOC.GET_OBJECT('response').GET_OBJECT('searchResults');
        DBMS_OUTPUT.PUT_LINE('JSON IS ' || RES.STRINGIFY);
        CASE
          WHEN J_DOC.IS_OBJECT THEN DBMS_OUTPUT.PUT_LINE('RES IS JSON');
          ELSE DBMS_OUTPUT.PUT_LINE('RES IS NOT JSON');
        END CASE;
    END;
    /
    JSON IS
    {"totalCount":6,"document":[{"content":[{"name":"Catalog_number","type":"text","
    value":"VMVS2A250GP/MT"},{"name":"UPC","type":"text","value":"782274850955"},{"n
    ame":"EAN","type":"text","value":""}]},{"content":[{"name":"Catalog_number","typ
    e":"text","value":"165541"},{"name":"UPC","type":"text","value":""},{"name":"EAN
    ","type":"text","value":"4015081620593"}]},{"content":[{"name":"Catalog_number",
    "type":"text","value":"41200"},{"name":"UPC","type":"text","value":"051712623995
    "},{"name":"EAN","type":"text","value":""}]},{"content":[{"name":"Catalog_number
    ","type":"text","value":"1303W"},{"name":"UPC","type":"text","value":"0326643087
    03"},{"name":"EAN","type":"text","value":""}]},{"content":[{"name":"Catalog_numb
    er","type":"text","value":"EMBH108R30C"},{"name":"UPC","type":"text","value":""}
    ,{"name":"EAN","type":"text","value":"4015081488230"}]},{"content":[{"name":"Cat
    alog_number","type":"text","value":"EL650USBIEC"},{"name":"UPC","type":"text","v
    alue":""},{"name":"EAN","type":"text","value":"3553340620737"}]}]}
    RES IS JSON
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL>
    
    

    SY.

  • User_CI7SY
    User_CI7SY Member Posts: 34 Green Ribbon
    Accepted Answer

    Wow, This is great. I tried it ran perfectly fine.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,730 Black Diamond
    Accepted Answer
    DECLARE
        DOC CLOB :='{
     "response" : {
      "status" : "Success",
      "statusDetails" : {
       "messages" : null
      },
      "didYouMean" : null,
      "binning" : null,
      "searchResults" : {
       "totalCount" : 6,
       "document" : [ {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "VMVS2A250GP/MT"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : "782274850955"
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : ""
        } ]
       }, {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "165541"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : ""
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : "4015081620593"
        } ]
       }, {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "41200"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : "051712623995"
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : ""
        } ]
       }, {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "1303W"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : "032664308703"
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : ""
        } ]
       }, {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "EMBH108R30C"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : ""
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : "4015081488230"
        } ]
       }, {
        "content" : [ {
         "name" : "Catalog_number",
         "type" : "text",
         "value" : "EL650USBIEC"
        }, {
         "name" : "UPC",
         "type" : "text",
         "value" : ""
        }, {
         "name" : "EAN",
         "type" : "text",
         "value" : "3553340620737"
        } ]
       } ]
      },
      "request" : {
       "searchApplication" : "etndisinv",
       "searchApplicationKey" : "abc123",
       "function" : "search",
       "searchTerms" : "ignore",
       "language" : "en_US",
       "startingRecordNumber" : 0,
       "numberOfRecordsToReturn" : 30,
       "filters" : [ {
        "filterName" : "Catalog_Number",
        "filterValue" : [ "VMVS2A250GP/MT", "165541" ],
        "preferenceValue" : null
       }, {
        "filterName" : "EAN",
        "filterValue" : [ "3553340620737", "4015081488230" ],
        "preferenceValue" : null
       }, {
        "filterName" : "UPC",
        "filterValue" : [ "051712623995", "032664308703" ],
        "preferenceValue" : null
       } ],
       "userName" : null
      }
     }
    }';
        J_DOC JSON_OBJECT_T;
        RES JSON_OBJECT_T;
    BEGIN
        J_DOC :=JSON_OBJECT_T(DOC);
        --DBMS_OUTPUT.PUT_LINE('JSON IS ' || J_DOC.STRINGIFY);
        RES :=J_DOC.GET_OBJECT('response').GET_OBJECT('searchResults');
        DBMS_OUTPUT.PUT_LINE('JSON IS ' || RES.STRINGIFY);
        CASE
          WHEN J_DOC.IS_OBJECT THEN DBMS_OUTPUT.PUT_LINE('RES IS JSON');
          ELSE DBMS_OUTPUT.PUT_LINE('RES IS NOT JSON');
        END CASE;
    END;
    /
    JSON IS
    {"totalCount":6,"document":[{"content":[{"name":"Catalog_number","type":"text","
    value":"VMVS2A250GP/MT"},{"name":"UPC","type":"text","value":"782274850955"},{"n
    ame":"EAN","type":"text","value":""}]},{"content":[{"name":"Catalog_number","typ
    e":"text","value":"165541"},{"name":"UPC","type":"text","value":""},{"name":"EAN
    ","type":"text","value":"4015081620593"}]},{"content":[{"name":"Catalog_number",
    "type":"text","value":"41200"},{"name":"UPC","type":"text","value":"051712623995
    "},{"name":"EAN","type":"text","value":""}]},{"content":[{"name":"Catalog_number
    ","type":"text","value":"1303W"},{"name":"UPC","type":"text","value":"0326643087
    03"},{"name":"EAN","type":"text","value":""}]},{"content":[{"name":"Catalog_numb
    er","type":"text","value":"EMBH108R30C"},{"name":"UPC","type":"text","value":""}
    ,{"name":"EAN","type":"text","value":"4015081488230"}]},{"content":[{"name":"Cat
    alog_number","type":"text","value":"EL650USBIEC"},{"name":"UPC","type":"text","v
    alue":""},{"name":"EAN","type":"text","value":"3553340620737"}]}]}
    RES IS JSON
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL>
    
    

    SY.

  • User_CI7SY
    User_CI7SY Member Posts: 34 Green Ribbon
    Accepted Answer

    Wow, This is great. I tried it ran perfectly fine.