How to view the type of a variable in PL/SQL dynamically?

Question

This link shows how to get a procedure/function variable's type in Oracle: View Type of a variable.

It does so through the function "get_plsql_type_name":

create or replace function get_plsql_type_name
(
    p_object_name varchar2,
    p_name varchar2
) return varchar2 is
    v_type_name varchar2(4000);
begin
    select reference.name into v_type_name
    from user_identifiers declaration
    join user_identifiers reference
        on declaration.usage_id = reference.usage_context_id
        and declaration.object_name = reference.object_name
    where
        declaration.object_name = p_object_name
        and declaration.usage = 'DECLARATION'
        and reference.usage = 'REFERENCE'
        and declaration.name = p_name;

    return v_type_name;
end;
/

alter session set plscope_settings = 'IDENTIFIERS:ALL';

create or replace type my_weird_type is object
(
    a number
);

create or replace procedure test_procedure is
    var1 number;
    var2 integer;
    var3 my_weird_type;
    subtype my_subtype is pls_integer range 42 .. 43;
    var4 my_subtype;
begin
    dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR1'));
    dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR2'));
    dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR3'));
    dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR4'));
end;
/

begin
    test_procedure;
end;
/

The problem with the above method is that it is static and I need to verify the type of a variable that can be a subtype of the one declared in the procedure/function scope.

Using the above method I get the following.

Create the type and its subtype:

create or replace type my_weird_type is object
(
    a number
) NOT FINAL;

CREATE OR REPLACE TYPE my_weird_subtype UNDER my_weird_type(  
   b number
);
/

Create a table and populates it:

create table test_my_weird_type(
x my_weird_type,
y my_weird_subtype
);

INSERT INTO test_my_weird_type (x,y) VALUES (my_weird_type(100),my_weird_subtype(100,200));
COMMIT;

Function creation (it has two my_weird_type parameters, and sometimes I am going need to use its subtypes):

create or replace function test_procedure (
    inn_type my_weird_type,
    out_subtype my_weird_type
) RETURN number is
    var1 number;
    var2 integer;
begin
    dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR1'));
    dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR2'));
    dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'INN_TYPE'));
    dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'OUT_SUBTYPE'));

   return 1;
end;
/

The below query:

select test_procedure(x,y) from test_my_weird_type;

Gives the following output:

NUMBER
INTEGER
MY_WEIRD_TYPE
MY_WEIRD_TYPE

However, the right output is:

NUMBER
INTEGER
MY_WEIRD_TYPE
MY_WEIRD_SUBTYPE

The function needs to recognize which subtype is being used, therefore the function "get_plsql_type_name" needs to be improved. Is there a way to do it?

Answer 1

ANYDATA and ANYTYPE allow complete, dynamic control over Oracle objects. This approach is unrelated to the static code analysis approach.

For example, this function returns the real type name for any object input:

create or replace function get_dynamic_type_name(
    p_anydata anydata
) return varchar2 is
    v_typecode pls_integer;
    v_anytype anytype;

    v_prec        pls_integer;
    v_scale       pls_integer;
    v_len         pls_integer;
    v_csid        pls_integer;
    v_csfrm       pls_integer;
    v_schema_name varchar2(128);
    v_type_name   varchar2(128);
    v_version     varchar2(32767);
    v_numelems    pls_integer;
    v_result pls_integer;
begin
    v_typecode := p_anydata.getType(v_anytype);

    v_result := v_anytype.GetInfo
    (
        prec        => v_prec,
        scale       => v_scale,
        len         => v_len,
        csid        => v_csid,
        csfrm       => v_csfrm,
        schema_name => v_schema_name,
        type_name   => v_type_name,
        version     => v_version,
        numelems    => v_numelems
    );

    return v_type_name;
end get_dynamic_type_name;
/

Before calling the function the objects must be converted with AnyData.ConvertObject:

select
    get_type_name(AnyData.ConvertObject(x)) x_type,
    get_type_name(AnyData.ConvertObject(y)) y_type
from test_my_weird_type;

X_TYPE          Y_TYPE
------          ------
MY_WEIRD_TYPE   MY_WEIRD_SUBTYPE

That function is probably not the most convenient way to simply get the type name. But it demonstrates how to use the ANY types to implement PL/SQL reflection and manipulate objects without knowing anything about them ahead of time. For example, my answer is based on my other answer here, which demonstrates how to find the first attribute of an object.

The ANY types are interesting but they should be used sparingly. It's usually faster and easier to use dynamic SQL to generate static code that handles the data, rather than doing all the processing in dynamic code. I try to avoid object-relational database features when possible. Make your schema smart but keep your columns dumb.

Answer 2

The function needs to recognize wich subtype is beeing used, therefore the function "get_plsql_type_name" needs to be improved. Is there a way to do it?

No. there is no way. USER_IDENTIFIERS displays information about the identifiers in the stored objects like (Packages/Procedure/Function etc) owned by the current user.

Oracle doesnot provide any data dictionary for standalone Object created under SQL scope to identify TYPE and SUBTYPE. You can at max identify them as TYPE.

for example in your case the below one will only return TYPE even thought its a SUBTYPE.

SELECT *
  FROM all_objects
 WHERE object_name = 'MY_WEIRD_SUBTYPE'

Edit:

One other way i can think of is to check if for any Type you pass has a SUPERTYPE. If so then it would imply that the type is a subtype.

You can use a query like:

SELECT 1
  FROM user_types
 WHERE type_name  = 'MY_WEIRD_SUBTYPE'
 and supertype_name is not null;

You can implement this feature in your function to check if its a SUBTYPE or not

Answer 3

You can't test the type based on the function specification but you can test the type of the passed in objects using the IS OF( type ) operator or the SYS_TYPEID function:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE type my_weird_type IS OBJECT
(
  a NUMBER
) NOT FINAL
/

CREATE TYPE my_weird_subtype UNDER my_weird_type
(
   b NUMBER
)
/

CREATE FUNCTION getType(
  i_type my_weird_type
) RETURN VARCHAR2
IS
BEGIN
  IF i_type IS OF( my_weird_subtype ) THEN
    RETURN 'subtype';
  ELSIF i_type IS OF( my_weird_type ) THEN
    RETURN 'type';
  ELSE
    RETURN 'other';
  END IF;
END;
/

CREATE FUNCTION getType2(
  i_type my_weird_type
) RETURN VARCHAR2
IS
  o_type USER_TYPES.TYPE_NAME%TYPE;
BEGIN
  SELECT type_name
  INTO   o_type
  FROM   user_types
  WHERE  typeid = SYS_TYPEID( i_type );

  RETURN o_type;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

create table test_my_weird_type(
  value my_weird_type
)
/

INSERT INTO test_my_weird_type (value)
SELECT my_weird_type(1)      FROM DUAL UNION ALL
SELECT my_weird_subtype(2,3) FROM DUAL UNION ALL
SELECT NULL                  FROM DUAL
/

Query 1:

SELECT t.value.a AS a,
       TREAT( t.value AS my_weird_subtype ).b AS b,
       getType( value ),
       getType2( value )
FROM   test_my_weird_type t

Results:

|      A |      B | GETTYPE(VALUE) |  GETTYPE2(VALUE) |
|--------|--------|----------------|------------------|
|      1 | (null) |           type |    MY_WEIRD_TYPE |
|      2 |      3 |        subtype | MY_WEIRD_SUBTYPE |
| (null) | (null) |          other |           (null) |