PL/SQL Generalized Invocation

Posted in: Oracle, Technical Track

Did you know PL/SQL generalized invocation has been available since 11g? The generalized invocation allows a subtype to invoke a method of a parent type (a supertype) using the following syntax:

(SELF AS supertype_name).method_name

Yes, this is the concept of inheritance applied to PL/SQL. Check the example below to understand it. First, to create the original type:

CREATE OR REPLACE TYPE type_test AS OBJECT (long_text VARCHAR2(50), MEMBER FUNCTION return_text RETURN VARCHAR2) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY type_test 
 AS MEMBER FUNCTION return_text RETURN VARCHAR2 IS 
  BEGIN 
   RETURN long_text; 
  END; 
END;
/

Now to create a subtype of this object, which adds a new attribute and method as well as overriding the member’s function.

CREATE OR REPLACE TYPE subtype_test UNDER type_test (short_text VARCHAR2(20), OVERRIDING MEMBER FUNCTION return_text RETURN VARCHAR2,MEMBER FUNCTION return_parent_text RETURN VARCHAR2);
/

CREATE OR REPLACE TYPE BODY subtype_test AS 
 OVERRIDING MEMBER FUNCTION return_text RETURN VARCHAR2 IS 
  BEGIN RETURN (self AS type_test).return_text || short_text; 
  END; 
 MEMBER FUNCTION return_parent_text RETURN VARCHAR2 IS 
  BEGIN RETURN (self AS type_test).return_text; 
 END; 
END;
/

And when calling:

SET SERVEROUTPUT ON
DECLARE l_subtype subtype_test := subtype_test('This is the original text.', ' Subtype Addition!');
BEGIN
 DBMS_OUTPUT.put_line('Parent Content= ' || l_subtype.return_parent_text);
 DBMS_OUTPUT.put_line('Subtype Content= ' || l_subtype.return_text);
END;
/
Parent Content= This is the original text.
Subtype Content= This is the original text. Subtype Addition!

A type can invoke the member functions of any parent type in this way, regardless of the depth of the inheritance.

Interesting, right?

Hope you like it!

If you have any questions or thoughts about this, please feel free to leave them in the comments.

email

Interested in working with Matheus? Schedule a tech call.

About the Author

Lead Database Consultant
Well known in the Oracle community in Latin America and Europe where he participates regularly in technology events, Matheus is actually the youngest Oracle ACE Director in the world. Lead Database Consultant at Pythian, Matheus is a Computer Scientist by PUCRS and has been working as an Oracle DBA for the last 10 years.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *