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[2]: to_number question

Jonathan Gennick

2004-07-15

Replies:
Something is not right here. Stephen. Here is your query:

SLDC>   select /*+ NO_MERGE(Z) NO_PUSH_PRED(Z) PUSH_SUBQ(Z) */ distinct
SLDC> nbr_cc_fop_name into v_fop from
SLDC>     (select nbr_cc_fop_name, nbr_cc_fop_high, nbr_cc_fop_low
SLDC>      from nbr_cc where nbr_cc_fop_name in ('AX','MC','VI','DS')) Z
SLDC>     where to_number(trim(substr(p_corigid,1,20))) between
SLDC> to_number(trim(nbr_cc_no_low))
SLDC>     and to_number(trim(nbr_cc_no_high));

How can this even work? Your outer WHERE clause references
columns that do not exist in the table returned by your
subequery.

I built an nbr_cc table and ran your query. Below are the
results I get:

SQL> select distinct nbr_cc_fop_name into v_fop
2 from ( select nbr_cc_fop_name, nbr_cc_fop_high, nbr_cc_fop_low
3      from nbr_cc
4      where nbr_cc_fop_name in ('AX','MC','VI','DS')) Z
5 where to_number(trim(substr(p_corigid,1,20))) between
6       to_number(trim(nbr_cc_no_low))
7       and to_number(trim(nbr_cc_no_high));
     and to_number(trim(nbr_cc_no_high))
                  *
ERROR at line 7:
ORA-00904: "NBR_CC_NO_HIGH": invalid identifier

While NBR_CC_NO_HIGH *does* exist in nbr_cc, it does not
exist in the table Z that is returned from the subquery.

What am I missing here? So far as I can tell, the above
query should not even execute for you.


SQL> describe nbr_cc
Name                         Null?   Type
----------------------------------------- -------- --------------------
NBR_CC_FOP_NAME                        VARCHAR2(2)
NBR_CC_FOP_HIGH                        VARCHAR2(10)
NBR_CC_FOP_LOW                         VARCHAR2(10)
P_CORIGID                            VARCHAR2(30)
NBR_CC_NO_LOW                         VARCHAR2(20)
NBR_CC_NO_HIGH                         VARCHAR2(20)




Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:jonathan@(protected)

Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article,
or send email to Oracle-article-request@(protected)
include the word "subscribe" in either the subject or body.


Wednesday, July 14, 2004, 11:13:22 PM, Stephen.Lee@(protected):
>> -----Original Message-----
>> I added few hints into your query.

SLDC> Hey Moe! It woiks! Nyuk Nyuk Nyuk.

SLDC> In case you are wondering, here is what I was working on. Without the hint,
SLDC> I get all 'YEEEEEHAAAA'. With the hint, life is good.  If there are any of
SLDC> you who are dealing with Sardines Oxley stuff -- you know, like what credit
SLDC> card numbers you shove into a test and/or development database -- this might
SLDC> be of use to you. And the YEEEEHAAAA in the exceptions part should actually
SLDC> be p_corigid too (I think). I'm still debating about what to do about the
SLDC> when others part. (And the dummy numbers have been changed to protect the
SLDC> innocent.)

SLDC> --------------------------

SLDC> create or replace function doink(p_corigid in varchar2) return varchar2 as

SLDC>   v_fop varchar2(10);
SLDC>   BUM_NUMBER exception;
SLDC>   pragma exception_init(BUM_NUMBER, -1722);

SLDC> begin

SLDC>   select /*+ NO_MERGE(Z) NO_PUSH_PRED(Z) PUSH_SUBQ(Z) */ distinct
SLDC> nbr_cc_fop_name into v_fop from
SLDC>     (select nbr_cc_fop_name, nbr_cc_fop_high, nbr_cc_fop_low
SLDC>      from nbr_cc where nbr_cc_fop_name in ('AX','MC','VI','DS')) Z
SLDC>     where to_number(trim(substr(p_corigid,1,20))) between
SLDC> to_number(trim(nbr_cc_no_low))
SLDC>     and to_number(trim(nbr_cc_no_high));

SLDC>   case v_fop
SLDC>     when 'AX' then return '123412341234123   '||SUBSTR(p_corigid,21);
SLDC>     when 'MC' then return '1234123412341234   '||SUBSTR(p_corigid,21);
SLDC>     when 'VI' then return '2345234523452345   '||SUBSTR(p_corigid,21);
SLDC>     when 'DS' then return '3456345634563456   '||SUBSTR(p_corigid,21);
SLDC>   end case;

SLDC> exception
SLDC>   when no_data_found
SLDC>     then return p_corigid;
SLDC>   when BUM_NUMBER
SLDC>     then return 'YEEHAAAAAAAAA';
SLDC>   when others
SLDC>     then return p_corigid;

SLDC> end;
SLDC> /

SLDC> ----------------------------------------------------------------
SLDC> Please see the official ORACLE-L FAQ: http://www.orafaq.com
SLDC> ----------------------------------------------------------------
SLDC> To unsubscribe send email to: oracle-l-request@(protected)
SLDC> put 'unsubscribe' in the subject line.
SLDC> --
SLDC> Archives are at http://www.freelists.org/archives/oracle-l/
SLDC> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
SLDC> -----------------------------------------------------------------

----------------------------------------------------------------
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
-----------------------------------------------------------------