2021 – Can Object Bodies Be Used By JSON Queries Too?

Now that I know how flexible selecting JSON and object types are I am wondering if I can execute code in the object type body as well. I’ll try it now with this fully declared object type.

CREATE OR REPLACE TYPE mrn_date_o AS OBJECT
( os  NUMBER,
  mrn VARCHAR2(100),
  CONSTRUCTOR FUNCTION mrn_date_o 
       ( SELF IN OUT NOCOPY mrn_date_o,
         os  NUMBER,
         mrn VARCHAR2 )
  RETURN SELF AS RESULT)

And this object type body. I only have to pass OS and MRN values to execute it. And no matter what I pass in I will get 42 Sir! back because I assign that in the object body.

CREATE OR REPLACE TYPE BODY mrn_date_o AS
  CONSTRUCTOR FUNCTION mrn_date_o 
    ( SELF IN OUT NOCOPY mrn_date_o,
      os  NUMBER,
      mrn VARCHAR2 )
      RETURN SELF AS RESULT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Start Of Object Body');
    SELF.os := 42;
    SELF.mrn := 'Sir!?';
    RETURN;
  END;
END;

Then run it like this.

DECLARE
  v_mrn mrn_date_o;
BEGIN
  v_mrn := mrn_date_o(1,'ABC');
  DBMS_OUTPUT.PUT_LINE(v_mrn.os || ' ' || v_mrn.mrn);
END;
Start Of Object Body
42 Sir!?

It worked. I got the changed values of 42 and Sir! as well as the DBMS_OUTPUT string.

Here is my test data.

SELECT JSON_VALUE(patient_data,'$.mrns[0].OS') os,
       JSON_VALUE(patient_data,'$.mrns[0].MRN') mrn
  FROM json_patient;
OS MRN
-- ---
1  1
1  3

Now I am hoping that when I query into the object type it will return 42 Sir! for the OS and MRN values and display the DBMS_OUTPUT string!

Here goes…

BEGIN
  FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]'
                    RETURNING mrn_date_o ) mrn_sdate_o
               FROM json_patient ) LOOP
    DBMS_OUTPUT.PUT_LINE(x.mrn_sdate_o.os || ' ' ||
                         x.mrn_sdate_o.mrn);
  END LOOP;
END;
1 1
1 3

Bummer. It looks like object type bodies are not executed.

Thanks for reading!