5 Replies Latest reply on Aug 14, 2019 12:38 PM by Iniyavan

    JSON_OBJECT_T error "ORA-40573: Invalid use of PL/SQL JSON object type"

    Iniyavan

      Hi team,

       

      I have JSON issue as below.

       

      The procedure JSON_OBJ_ISSUE complied successfully. But while invoking from an anonymous block, I get "ORA-40573: Invalid use of PL/SQL JSON object type".

       

      Could you please help to resolve it?

       

      Thanks in advance!

       

      -----------------------------------------------------

       

      CREATE TABLE EMP (
        EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER(2)
      );

       

      INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

       

      COMMIT;

       

      create or replace PROCEDURE JSON_OBJ_ISSUE
      (
        pi_emp_id IN VARCHAR2
      , PO_JSON OUT JSON_OBJECT_T
      ) AS
      BEGIN
        po_json := NEW json_object_t;
       
        SELECT json_object_t (
               json_object(
              KEY 'EmpDetails'
              VALUE json_object (KEY 'EmpID' VALUE e.empno,
                                 KEY 'EmpName' VALUE e.ename)
                           )
                           )
        INTO po_json
        FROM   emp  e
        where e.empno = pi_emp_id;
       
      exception
        WHEN others THEN
          po_json := NULL;
          dbms_output.put_line('Error - '||sqlerrm);
      END JSON_OBJ_ISSUE;

      /

       

      DECLARE
      l_json json_object_t;
      BEGIN
        l_json := NEW json_object_t;
        json_obj_issue(7369,l_json);
        DBMS_OUTPUT.put_line(l_json.stringify);
      end;

      /

       

       

       

       

       

        • 1. Re: JSON_OBJECT_T error "ORA-40573: Invalid use of PL/SQL JSON object type"
          Paulzip

          Don't use when OTHERS to suck up exceptions, it's a hack.  Unexpected exceptions should be propagated.

           

          create or replace PROCEDURE JSON_OBJ_ISSUE

          (

            pi_emp_id IN VARCHAR2

          , PO_JSON OUT JSON_OBJECT_T

          ) AS

            vJSon clob;

          BEGIN

            SELECT

              json_object(

                KEY 'EmpDetails'

                VALUE json_object (

                  KEY 'EmpID' VALUE e.empno,

                  KEY 'EmpName' VALUE e.ename

                )

              )

            INTO vJSon

            FROM   emp  e

            where e.empno = pi_emp_id;

            po_json := json_object_t(vJSon);

          END JSON_OBJ_ISSUE;

          /

           

          DECLARE

            l_json json_object_t;

          BEGIN

            json_obj_issue(7369,l_json);

            DBMS_OUTPUT.put_line(l_json.stringify);

          end;

          /

          Procedure created.

          Statement processed.
          {"EmpDetails":{"EmpID":7369,"EmpName":"SMITH"}}

          • 2. Re: JSON_OBJECT_T error "ORA-40573: Invalid use of PL/SQL JSON object type"
            Iniyavan

            Thank you very much, Paul. It works!

             

            Also, I take your advice on WHEN OTHERS.

            • 3. Re: JSON_OBJECT_T error "ORA-40573: Invalid use of PL/SQL JSON object type"
              Iniyavan

              Hi Paul,

               

              an additional doubt. I am trying to append a JSON array to an JSON object. I get the following error while appending CLOBs.

               

              Could you please help?

               

              -------------------------------

               

               

              create or replace PROCEDURE JSON_OBJ_ISSUE
              (
                pi_dept_id IN integer
              , PO_JSON OUT JSON_OBJECT_T
              ) AS
                vjson_dept CLOB;
                vjson_emp CLOB;
                vJSon_final clob;
              BEGIN
                SELECT
                  json_object(
                    KEY 'DeptDetails'
                    VALUE json_object (
                      KEY 'DeptID' VALUE d.deptno,
                      KEY 'DeptName' VALUE d.dname
                    )
                  )
                INTO vjson_dept
                FROM   dept  d
                where d.deptno = pi_dept_id;
               
                SELECT
                  json_object(key 'EmpDetails' value
                  json_arrayagg(
                         json_object (KEY 'EmpID' VALUE e.empno,
                                      KEY 'EmpName' VALUE e.ename,
                                      KEY 'Salary' VALUE e.sal)
                                   )
                                   )
                INTO vjson_emp
                FROM   emp  e
                where e.deptno = pi_dept_id;
               
                dbms_lob.APPEND(vJSon_final,vjson_dept);
                dbms_lob.APPEND(vJSon_final,vjson_emp);
               
                po_json := json_object_t(vJSon_final);
              END JSON_OBJ_ISSUE;

              /

               

              DECLARE
                l_json json_object_t;
              BEGIN
                json_obj_issue(20,l_json);
                DBMS_OUTPUT.put_line(l_json.stringify);
              end;

              /

               

              I get "ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275". Am I missing anything while appending the CLOBs?

              • 4. Re: JSON_OBJECT_T error "ORA-40573: Invalid use of PL/SQL JSON object type"
                Paulzip

                You don't need to build up your JSON like that, you can do it in one select, e.g....

                 

                select

                  json_object(

                    key 'DeptDetails' value json_object (

                      key 'DeptID' value d.deptno,

                      key 'DeptName' value d.dname,

                      key 'EmpDetails' value (

                        select json_arrayagg(

                                json_object (

                                  key 'EmpID' value e.empno,

                                  key 'EmpName' value e.ename,

                                  key 'Salary' value e.sal

                                )

                              )

                        from emp e

                        where e.deptno = d.deptno

                      )

                    )  

                  ) my_json

                from  dept  d

                where d.deptno = 20;

                 

                {

                "DeptDetails" : {
                "DeptID" : 20,

                 

                "DeptName" : "RESEARCH",

                 

                "EmpDetails" : [
                {
                "EmpID" : 7566,

                 

                "EmpName" : "JONES",

                "Salary" : 2975

                },

                 

                {
                "EmpID" : 7788,

                 

                "EmpName" : "SCOTT",

                "Salary" : 3000

                },

                 

                {
                "EmpID" : 7902,

                 

                "EmpName" : "FORD",

                "Salary" : 3000

                },

                 

                {
                "EmpID" : 7369,

                 

                "EmpName" : "SMITH",

                "Salary" : 800

                },

                 

                {
                "EmpID" : 7876,

                 

                "EmpName" : "ADAMS",

                "Salary" : 1100

                }

                ]

                }

                }

                 

                Even if you did need to build it up, you have to be careful with DBMS_LOB concatenation routines APPEND / WRITEAPPEND, as they don't like nulls / empty_clob().