Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

RE: to_number question

Stephen Lee

2004-07-14

Replies:
> -----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@(protected)
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
-----------------------------------------------------------------