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

Question

Is there a function in PL/SQL to show a variable's exact type, like the DUMP function in SQL?

I've tried the following

DECLARE
   l_variable   INTEGER := 1;
BEGIN
   DBMS_OUTPUT.PUT_LINE (DUMP (l_variable));
END;

But it gives the following error:

PLS-00204: function or pseudo-column 'DUMP' may be used inside a SQL statement only

Hogfish

Why do you want to do this? DUMP() is a diagnostic tool, mainly used for investigating the contents of table columns. Why do you need to interrogate the datatypes of PL/SQL variables? (I have a couple of hypotheses, but I think the question would be improved by more details regarding the use case.) – Dominate

Initially I've just wanted to find out the exact type of the autodeclared loop variable in a numeric FOR LOOP. Then I started wondering how can I programmatically get the actual type of any variable, like in any modern language like C#, java or python, and I've not found any way. – Hogfish

Answer 1

You can create this function using PL/Scope. But it won't work with anonymous blocks, and you'll need to reference the variable as a string.

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;
/

Example:

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;
/

NUMBER
INTEGER
MY_WEIRD_TYPE
MY_SUBTYPE

Stairway

I just realized that this may run into some issues depending on how it's used. If the variable doesn't exist you'll get a NO_DATA_FOUND, and if there are two variables with the same name in the object you'll get ORA-01422: exact fetch returns more than requested number of rows. – Stairway

No problem, I've got the idea. I only used older versions than 11g so I didn't know about this feature. Looks like I have to update my knowledge because the 12c is also out :) – Hogfish

Answer 2

as you should notice, DUMP is an overloaded function. it has 3 overloads.

So you can simulate the same thing within your code.

function myDump (x Varchar2) return varchar2 is begin return('string') ; end ;
function myDump (x number) return varchar2 is begin return('integer') ; end ;
function myDump (x date) return varchar2 is begin return('date') ; end ;

the above code may not work properly but should give you the idea how to deal the problem.

I hope that this will fulfil your requirements.

Note; you can put these functions in a Package and use them accordingly.

Peake

This solution will not be able to differentiate between a NUMBER and an INTEGER. If you create a function for each type in a package it will compile, but when you call it you'll get the error message PLS-00307: too many declarations of 'MYDUMP' match this call. The really tricky part of this question is differentiating between subtypes, because PL/SQL doesn't seem to know the difference in every context. – Stairway

Hi Ali, creative approach. It's not perfect as @jonearles pointed out, but still better than others in DB versions under 11g. I could accept this for answer under 11g. But I've not constrainted the DB version in the question, and the 11g solution is better, so I have to give the bounty to jonearles – Hogfish

These functions will work only for oracle native types. And oracle Native types do not make any difference between Number and Integer. @jonearles gave a good explanation, reason I gave you an other alternative is that you might have a simpler request. – Interstadial

Answer 3

declare
  a number(10,3);
  type_info varchar2(400);
begin
  a := 55.5;
  select dump(a) into type_info from dual;
  DBMS_OUTPUT.PUT_LINE(type_info);
end;

Lachrymator

I've thought about this solution, but there is implicit conversion happens in this case. For example if I pass in a PLS_INTEGER, it becomes a typ=2 NUMBER. – Hogfish

@csadam: And that is correct. PLS_INTEGER is a subtype of BINARY_INTEGER which is a subtype of INTEGER which is a alias for NUMBER. So I guess it returns correct value. – Yeta

@csadam: If you think that you will call some proc/func based on type discovery then you are going in wrong direction. There is a way for it but is not recommended. – Yeta

@Yeta My intention was just to dig under the hood, look what really happens inside, behind the scenes. For example in .NET I can dig until the "virtual" bytecode, look what really executes, and see what the optimizer changes in my source code. Nothing serious however, just curiosity. – Hogfish