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!