Hello everyone!
Oracle version 11.2
In db1 created following db objects:
create or replace type t_dates as table of date;
create or replace function get_dates(p_period varchar2,
p_block number,
p_day date,
p_week number,
p_mnth number,
p_year number) return t_dates pipelined
is
begin
for rec in
(
select p_day - case p_block
when 1 then 0
when 2 then 1
when 3 then 7
end as tran_date
from dual
where p_period = 'DAY'
union all
select tran_date
from shelfmon.v_dates d
where p_period ='WEEK'
and d.iw = p_week - case p_block
when 1 then 0
when 2 then 1
when 3 then 0
end
and d.year = p_year - case p_block
when 1 then 0
when 2 then 0
when 3 then 1
end
union all
select tran_date
from shelfmon.v_dates d
where p_period ='MONTH'
and d.month_num = p_mnth - case p_block
when 1 then 0
when 2 then 1
when 3 then 0
end
and d.year = p_year - case p_block
when 1 then 0
when 2 then 0
when 3 then 1
end
)
loop
pipe row (rec.tran_date);
end loop;
return;
end get_dates;
on db2 I have some_table with trandate DATE column
I also have dblink remote_db from db1 to db2
And when I run following query in db1, I get error ORA-05602
select 1 from dwh.some_table@remote_db where tree in ('1.7.26.','1.7.27.')
and trandate>sysdate-365
and trandate in
(select column_value from table(get_dates(p_period => 'MONTH',
p_block => 1,
p_day => null,
p_mnth=> 2,
p_week => null,
p_year => 2026
)
I created the same type and function on db2, and run the similar query
select 1 from dwh.some_table where tree in ('1.7.26.','1.7.27.')
and trandate>sysdate-365
and trandate in
(select column_value from table(get_dates(p_period => 'MONTH',
p_block => 1,
p_day => null,
p_mnth=> 2,
p_week => null,
p_year => 2026
)
And everything is okay!
What can be reason of the ORA-06502: PL/SQL: numeric or value error in db1?