Oracle 11g patching – predicate pushing

There are two environments with Oracle 11g i.e. DEV and LIVE.

There have been a few issues discovered with the code that has been developed e.g:

This code will work in the LIVE environment but not in DEV:

create table t_table as
select 'A' col1, 'F' is_number from dual
union
select '1' col1, 'T' is_number from dual
/

select * from (
select to_number(col1) c1 from t_table
where is_number='T')
where  to_number(c1)>0;

In test I get
ORA-01722: invalid number
01722. 00000 – “invalid number”
*Cause: The specified number was invalid.
*Action: Specify a valid number.

Why would the code work in one environment but not the other?

Is it patching levels?

Is it a predicate pushing issue?

Note:
The system we are working on was originally developed in another environment i.e. we have recently had new environments built for a project. So you can imagine the confusion when things start going wrong in the DEV when it worked without issue in the old environment and we notice the code works in the new LIVE environment.

We have a work around i.e. stick affected code into a case statement.

Issue code:

SELECT * FROM MD_PMNT_STG where   CHE='ALLEGRO' and critical_errors=0 ) p ,
   MD_tail_stg  t
where t.CHE=P.CHE
 and t.critical_errors=0 
 AND p.LIVE_TRN =t.F200
 AND p.sum_Q123_gbp=t.B111A  
 and p.JB_H_NUM = t.PMNT_ORDR_NUM
 and to_number(p.CHE_TRN_NUM)=to_number(substr(t.F300,-6));

Working code:

SELECT * FROM MD_PMNT_STG where   CHE='ALLEGRO' and critical_errors=0 ) p ,
   MD_tail_stg  t
    where t.CHE=P.CHE
   where t.FES=P.FES and  
     case when  t.critical_errors>0 then 'F'
          when  p.LIVE_TRN =t.F200
            AND p.sum_Q123_gbp=t.B111A             
                and p.JB_H_NUM = t.PMNT_ORDR_NUM
               and to_number(p.CHE_TRN_NUM)=to_number(substr(t.F300,-6))     then 'T'
     else 'F' end ='T';


Source: oracle

Leave a Reply