TRUNC(date)
PLW-07204 is also caused if
trunc()
is applied on a
date
column:
create table tq84_tab (
val number,
txt varchar2(10),
dt date not null
);
create or replace function tq84_func(p_dt date) return number
authid definer
as
ret number;
begin
select
val into ret
from
tq84_tab
where
trunc(dt, 'mm') = p_dt;
return ret;
end tq84_func;
/
show errors
explain plan for
select
val
from
tq84_tab
where
trunc(dt, 'mm') = trunc(sysdate, 'mm');
select * from table(dbms_xplan.display(format=>'basic predicate'));
--
-- --------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------
-- | 0 | SELECT STATEMENT | |
-- |* 1 | TABLE ACCESS FULL| TQ84_TAB |
-- --------------------------------------
--
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--
-- 1 - filter(TRUNC(INTERNAL_FUNCTION("DT"),'fmmm')=TRUNC(SYSDATE@!,'fmmm'))
drop table tq84_tab;
drop function tq84_func;