This link shows how to get the type of a procedure / function variable in Oracle: View the type of a variable .
He does this through the function "get_plsql_type_name":
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;
/
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;
/
The problem with the above method is that it is static, and I need to check the type of the variable, which can be a subtype of the one declared in the procedure / function area.
Using the above method, I get the following.
Create the type and its subtype:
create or replace type my_weird_type is object
(
a number
) NOT FINAL;
CREATE OR REPLACE TYPE my_weird_subtype UNDER my_weird_type(
b number
);
/
Create a table and fill it in:
create table test_my_weird_type(
x my_weird_type,
y my_weird_subtype
);
INSERT INTO test_my_weird_type (x,y) VALUES (my_weird_type(100),my_weird_subtype(100,200));
COMMIT;
Creating a function (it has two parameters my_weird_type, and sometimes I need to use their subtypes):
create or replace function test_procedure (
inn_type my_weird_type,
out_subtype my_weird_type
) RETURN number is
var1 number;
var2 integer;
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', 'INN_TYPE'));
dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'OUT_SUBTYPE'));
return 1;
end;
/
The following query:
select test_procedure(x,y) from test_my_weird_type;
Gives the following output:
NUMBER
INTEGER
MY_WEIRD_TYPE
MY_WEIRD_TYPE
However, the correct output is:
NUMBER
INTEGER
MY_WEIRD_TYPE
MY_WEIRD_SUBTYPE
, ,
"get_plsql_type_name" . ?