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?

Catgut

Answer 1

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) |

Pentateuch

This is very much question oriented solution not a generic one. I mean if OP created some type then (s)he need to modify the function each time and i guess that's not the intention. However, it was good to know IS OF( type ) operator. – Lipski

ok... If it is not possible to get the object type that is beeing passed by the user, how can I know its attributes in order to manipulate it? – Catgut

@Lipski You can also use the SYS_TYPEID function. – Pentateuch

@Pentateuch And do what with SYS_TYPEID function. This would return on number. Can you please explain bit more – Lipski

@Catgut Updated for an alternate method - see this page for a list of helpful operators/function to use with objects. You appear to have an XY-problem and are asking about your solution and not about your problem; write a new question and ask about what you are trying to do not about how you think you are solving it. – Pentateuch

@Lipski That is detailed in the update to my answer that i posted before I tagged you in that comment. It is a string (not a number) and you can look it up in the USER_TYPES dictionary table to find the exact type of the object. – Pentateuch

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

Lipski

Ok... If it is not possible to get the object type that is beeing passed by the user, how can I know its attributes in order to manipulate it? – Catgut

If it is not possible to get the object type that is beeing passed by the user, how can I know its attributes in order to manipulate it - You cannot manipulate it then. – Lipski

Answer 3

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.

Finer

Thanks... I am going to test your solution and give the feedback as soon as I can! – Catgut