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
    itshak Nov 14, 2019 11:10 AM

      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
          Pierre Yotti Nov 14, 2019 11:39 AM (in response to itshak)

          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
            itshak Nov 14, 2019 11:45 AM (in response to Pierre Yotti)

            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
              Pierre Yotti Nov 14, 2019 12:01 PM (in response to itshak)

              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
                itshak Nov 14, 2019 1:02 PM (in response to Pierre Yotti)

                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
                  Pierre Yotti Nov 15, 2019 9:37 AM (in response to itshak)

                  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
                    itshak Nov 17, 2019 9:52 AM (in response to Pierre Yotti)

                    It's a shame.

                     

                    Thanks very much Pierre.