> -----Original Message----- > I added few hints into your query. Hey Moe! It woiks! Nyuk Nyuk Nyuk. In case you are wondering, here is what I was working on. Without the hint, I get all 'YEEEEEHAAAA'. With the hint, life is good. If there are any of you who are dealing with Sardines Oxley stuff -- you know, like what credit card numbers you shove into a test and/or development database -- this might be of use to you. And the YEEEEHAAAA in the exceptions part should actually be p_corigid too (I think). I'm still debating about what to do about the when others part. (And the dummy numbers have been changed to protect the innocent.) -------------------------- create or replace function doink(p_corigid in varchar2) return varchar2 as v_fop varchar2(10); BUM_NUMBER exception; pragma exception_init(BUM_NUMBER, -1722); begin select /*+ NO_MERGE(Z) NO_PUSH_PRED(Z) PUSH_SUBQ(Z) */ distinct nbr_cc_fop_name into v_fop from (select nbr_cc_fop_name, nbr_cc_fop_high, nbr_cc_fop_low from nbr_cc where nbr_cc_fop_name in ('AX','MC','VI','DS')) Z where to_number(trim(substr(p_corigid,1,20))) between to_number(trim(nbr_cc_no_low)) and to_number(trim(nbr_cc_no_high)); case v_fop when 'AX' then return '123412341234123 '||SUBSTR(p_corigid,21); when 'MC' then return '1234123412341234 '||SUBSTR(p_corigid,21); when 'VI' then return '2345234523452345 '||SUBSTR(p_corigid,21); when 'DS' then return '3456345634563456 '||SUBSTR(p_corigid,21); end case; exception when no_data_found then return p_corigid; when BUM_NUMBER then return 'YEEHAAAAAAAAA'; when others then return p_corigid; end; / ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------