6 Replies Latest reply on Nov 17, 2019 9:52 AM by itshak

    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.