Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: to_number question

RE: to_number question

From: <Stephen.Lee_at_DTAG.Com>
Date: Wed, 14 Jul 2004 22:13:22 -0500
Message-ID: <D6339830FC73944E889CC3CEADDB205B0790951B@bu-dtagpo1.tracs.com>


> -----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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Jul 14 2004 - 22:10:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US