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

Answer 1

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;

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.