How to view variable type in PL / SQL?

Question

Is there a function in PL / SQL to display the exact type of a variable, for example, a DUMP function in SQL?

I tried the following

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

But this leads to the following error:

PLS-00204: function or pseudo-column "DUMP" can be used inside SQL only

Answer 1

You can create this function using PL / Scope . But it will not work with anonymous blocks, and you will need to refer to 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 

Answer 2

As you should notice, DUMP is an overloaded function. It has 3 overloads.

So, you can simulate the same in 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 correctly, but should give you an idea of ​​how to solve this problem.

I hope this fulfills your requirements.

Note; You can put these functions in a package and use them accordingly.

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;