A begginer question: How to get directly a sub-object in pl/sql

itshak

    Hi,

     

    I'm using a Oracle 18.7 DB version.

     

    I don't success to get directly the sub-object "department" from the next example.

    I received the error:

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

     

    I think that something is wrong when I writed the syntax

     

    declare

    j clob :=

    '{

      "departments":

        {

          "department":{

            "department_name":"DEV",

            "department_no":70,

            "employees":[

              {

                "employee_number":9000,

                "employee_name":"JONES",

                "salary":1000

              },

              {

                "employee_number":9001,

                "employee_name":"SMITH",

                "salary":2000

              }

            ]

          }

        }

    }';

      j2  JSON_OBJECT_T;

      j3    JSON_OBJECT_T;

    begin

      j2 := JSON_OBJECT_T( j );

      j3 := j2.get_object( 'departments.department' ); -- j2.get_object( '$.departments.department' );

      dbms_output.put_line( j3.to_string );

    --  dbms_output.put_line( j3.get_string( 'department_name' ) );

    end;

    /

     

    Thanks on advance for every one that cal helps me.

     

    Isaac

     

    p.d.

    My true target is to get the value of the "department_name" but I need to perform it in two steps:

    1) Get the Object parent

    2) Get the child value

      • 1. Re: A begginer question: How to get directly a sub-object in pl/sql
        Pierre Yotti

        That should works

         

        set serveroutput on
        
        declare
            l_object2     json_object_t;
            l_object3     json_object_t;
            j2            json_object_t;
            j3            json_object_t;
            l_departments           json_element_t;
            l_dept        json_element_t;
            l_dept_name   json_element_t;
            j             clob := '{
        
        
          "departments":
        
        
            {
        
        
              "department":{
        
        
                "department_name":"DEV",
        
        
                "department_no":70,
        
        
                "employees":[
        
        
                  {
        
        
                    "employee_number":9000,
        
        
                    "employee_name":"JONES",
        
        
                    "salary":1000
        
        
                  },
        
        
                  {
        
        
                    "employee_number":9001,
        
        
                    "employee_name":"SMITH",
        
        
                    "salary":2000
        
        
                  }
        
        
                ]
        
        
              }
        
        
            }
        
        
        }'
            ;
        begin
            j2 := json_object_t.parse(j);
            l_departments := j2.get('departments');
            l_object2 := json_object_t.parse(l_departments.to_string);
            l_dept := l_object2.get('department');
            l_object3 := json_object_t.parse(l_dept.to_string);
            l_dept_name := l_object3.get('department_name');
        
        
          --j3 := j2.get_object( 'departments.department' ); -- j2.get_object( '$.departments.department' );
            dbms_output.put_line(l_dept_name.to_string);
        
        
        --  dbms_output.put_line( j3.get_string( 'department_name' ) );
        end;
        /
        
        • 2. Re: A begginer question: How to get directly a sub-object in pl/sql
          itshak

          Hi Pierre,

           

          I need to perform the "get" in one unique call.

          In my real system, the "child" that I search by, can be 4 or 5 level down so I need the syntax to get directly the "child" in one unique call,

           

          Thanks

           

          Isaac

          • 3. Re: A begginer question: How to get directly a sub-object in pl/sql
            Pierre Yotti

            That is the way to go. You need to know how the Json looks like to get value. ALternative you can use JSON_TABLE

             

            select
                department_name,
                department_no,
                employee_number,
                employee_name,
                salary
            from
                json_table ( '{
            
            
              "departments":
            
            
                {
            
            
                  "department":{
            
            
                    "department_name":"DEV",
            
            
                    "department_no":70,
            
            
                    "employees":[
            
            
                      {
            
            
                        "employee_number":9000,
            
            
                        "employee_name":"JONES",
            
            
                        "salary":1000
            
            
                      },
            
            
                      {
            
            
                        "employee_number":9001,
            
            
                        "employee_name":"SMITH",
            
            
                        "salary":2000
            
            
                      }
            
            
                    ]
            
            
                  }
            
            
                }
            
            
            }'
                , '$.departments.department'
                    columns (
                        department_name varchar2 path '$.department_name',
                        department_no number path '$.department_no',
                        nested path '$.employees[*]'
                            columns (
                                employee_number number path '$.employee_number',
                                employee_name varchar2 path '$.employee_name',
                                salary number path '$.salary'
                            )
                    )
                );
            

             

             

            Or just do in PLSQL

             

                  dbms_output.put_line(json_object_t.parse( json_object_t.parse(json_object_t.parse(j).get('departments').to_string).get('department').to_string).get('department_name').to_string);  
            
            • 4. Re: A begginer question: How to get directly a sub-object in pl/sql
              itshak

              Hi Pierre,

               

              Thanks again but I need a solution for PL/SQL.

               

              In my real system, I have some function like it (The function is vey more complex but for the test case. This function returns the medication for some patient: pill, times at day, quantity, way of medication, etc. The information received is distinct for every type of medication ):

               

              Function GetValue

                 ( i_json_lob    in clob,

                   i_child_key   in varchar2,

                   i_argument  in varchar2 )

                return clob      is

                j     json_object_t := json_object_t ( i_json_lob );

              begin

                return( 'The patient must take ' || j.get_object( i_child_key ).get_string( i_argument ) || ' pill ' );

              end;

               

              Regards

               

              Isaac

              • 5. Re: A begginer question: How to get directly a sub-object in pl/sql
                Pierre Yotti

                Hallo Isaac,

                 

                that is not possible using PL/SQL Object Types for JSON

                 

                Regards

                 

                Pierre

                • 6. Re: A begginer question: How to get directly a sub-object in pl/sql
                  itshak

                  It's a shame.

                   

                  Thanks very much Pierre.