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

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

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.

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;