How to view the type of a variable in PL/SQL?
22,972
Solution 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
Solution 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.
Solution 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;
Author by
csadam
Updated on July 21, 2022Comments
-
csadam almost 2 years
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
-
csadam about 11 yearsI'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.
-
Jon Heller almost 11 yearsI 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 getORA-01422: exact fetch returns more than requested number of rows
. -
the_slk almost 11 years@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.
-
the_slk almost 11 years@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.
-
Jon Heller almost 11 yearsThis solution will not be able to differentiate between a
NUMBER
and anINTEGER
. If you create a function for each type in a package it will compile, but when you call it you'll get the error messagePLS-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. -
csadam almost 11 years@the_slk 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.
-
csadam almost 11 yearsNo 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 :)
-
csadam almost 11 yearsHi 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
-
Ali Avcı almost 11 yearsThese 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.