Dynamic typing in PL/SQL

Introduction

PL/SQL is a statically typed language. This means that datatypes must be declared and checked at compile time. There are also occasions when you really need the capabilities of dynamic typing and for those occasions, the Any types were introduced in PL/SQL (back then in 9i). These dynamic datatypes enable you to write programs that manipulate data when you don't know the type of that data until runtime. You determine the type of the value at runtime through introspection (using gettype function, as you will see in example).

You cannot manipulate the internal structure of Any types, you must use procedures and functions for that.

We will use following family members of Any:
AnyData (can hold a single value of any type, whatever it's built-in scalar datatype or user-defined object type).
AnyType (can hold a description of a type -- you will see).

In following example I create three user-defined types that are representing some kind of transport mean. The subsequent PL/SQL code then uses SYS.AnyType to define a heterogeneous array of transports.

CREATE OR REPLACE TYPE airplane_o AS OBJECT(
        engine_type VARCHAR2(35),
        lift NUMBER                                       
)
/
CREATE OR REPLACE TYPE car_o AS OBJECT(
        engine_power NUMBER,                             
        color VARCHAR2(35)       
)  
/
CREATE OR REPLACE TYPE train_o AS OBJECT (
       engine_type VARCHAR2(35),
       speed NUMBER                
)
/

SET SERVEROUTPUT ON;

DECLARE    
    TYPE transports_t IS VARRAY(6) OF SYS.AnyData;
    transports transports_t;
    airplane airplane_o;
    car car_o;
    train train_o;    
    ret_val NUMBER;
BEGIN
    
    transports := transports_t(
                    AnyData.ConvertObject(
                        airplane_o('turboprop', 2300)), 
                    AnyData.ConvertObject(
                        airplane_o('jet', 3500)),                              
                    AnyData.ConvertObject(
                        car_o(55, 'red')),
                    AnyData.ConvertObject(
                        train_o('electric', 80)),
                    AnyData.ConvertObject(
                        train_o('steam', 45)),
                    AnyData.ConvertObject(
                        airplane_o('ramjet', 9000))                                    
                              );
                
    
    FOR i IN 1..transports.COUNT LOOP        
        IF transports(i).GetTypeName = 'HR.AIRPLANE_O' THEN            
            ret_val := transports(i).GetObject(airplane);
            --ret_val can be success or no_data...I did not check this.
            DBMS_OUTPUT.put_line('Airplane: ' || 'engine type: ' || 
                        airplane.engine_type || ', lift: ' || airplane.lift || 'lbs');
        ELSIF transports(i).GetTypeName = 'HR.CAR_O' THEN
            ret_val := transports(i).GetObject(car);
            DBMS_OUTPUT.put_line('Car: ' || 'engine power: ' || 
                        car.engine_power || 'KW, color: ' || car.color);
        ELSIF transports(i).GetTypeName = 'HR.TRAIN_O' THEN
            ret_val := transports(i).GetObject(train);    
            DBMS_OUTPUT.put_line('Train: ' || 'engine type: ' || 
                        train.engine_type || ', speed: ' || train.speed || 'KMh');
        END IF;
        
    END LOOP;    
    
END;

Execution of this program in Toad generates following output:

Now I will comment important points in this code.
28 - 41: Here are heterogeneous transports stored in a VARRAY. airplane_o, car_o, train_o are constructors of an object, and AnyData.ConvertObject cast this objects into instance of AnyData.
45, 50, 54: Here we introspect current object (in the loop) and get its type.
46, 51, 55: Retrieve the specific object. We are ignoring return code.

48, 52, 56: Once I had the object in a variable, I can write its properties in DBMS_OUTPUT.