|
|
|
|
| Re: Error: ORA-01722: invalid number [message #517535 is a reply to message #517490] |
Tue, 26 July 2011 09:01   |
 |
rhyamdale
Messages: 6
Registered: June 2011
Location: Philippines
|
Junior Member |
|
|
Yes, I believe that this might be something wrong with my db settings. Another info, when the value is = 2 or 1, it succeeded while if the query value is a double digit, it failed with the invalid number issue. Please consider below. Thanks
----- vIEW --------
SELECT lm.ltr_id,
lm.clnt_cde,
lm.ltr_grp,
lm.ltr_purp_id,
lm.ltr_cde,
lm.ltr_nme,
lm.ltr_desc,
lhtx.ltr_hdr_id,
lhtx.ltr_tmplt_id,
lm.ltr_typ,
lm.ask_fld_ind,
lm.defn_typ,
lm.evnt_oper_id,
lm.evnt_tme_stmp,
lm.stor_img_flg,
lhtx.ltr_seq,
lm.xml_based,
t1.ATTR_VAL AS email_ind,
t2.ATTR_VAL AS email_subject,
t3.ATTR_VAL AS email_body,
t4.ATTR_VAL AS merge_ind
FROM ltr_mstr lm
LEFT OUTER JOIN TABLE_ATTR t1
ON (t1.OWNER_ID =lm.ltr_id
AND t1.OWNER_TYPE='LTR_MSTR'
AND t1.ATTR_KEY ='EMAIL_IND')
LEFT OUTER JOIN TABLE_ATTR t2
ON (t2.OWNER_ID =lm.ltr_id
AND t2.OWNER_TYPE='LTR_MSTR'
AND t2.ATTR_KEY ='EMAIL_SUBJECT')
LEFT OUTER JOIN TABLE_ATTR t3
ON (t3.OWNER_ID =lm.ltr_id
AND t3.OWNER_TYPE='LTR_MSTR'
AND t3.ATTR_KEY ='EMAIL_BODY')
LEFT OUTER JOIN TABLE_ATTR t4
ON (t4.OWNER_ID =lm.ltr_id
AND t4.OWNER_TYPE='LTR_MSTR'
AND t4.ATTR_KEY ='MERGE_IND')
LEFT OUTER JOIN ltr_hdr_tmplt_xrf lhtx
ON (lm.ltr_id = lhtx.ltr_id)
WHERE lhtx.ltr_seq =
(SELECT MIN (lx.ltr_seq)
FROM ltr_hdr_tmplt_xrf lx
WHERE lx.ltr_id = lm.ltr_id
)
OR XML_BASED='Y';
Dependencies: LTR_MSTR, LTR_HDR_TMPLT_XRF, TABLE_ATTR
----- LTR_MSTR ------
LTR_ID NUMBER(10,0)
CLNT_CDE VARCHAR2(4 BYTE)
LTR_GRP VARCHAR2(1 BYTE)
LTR_PURP_ID NUMBER(10,0)
LTR_CDE VARCHAR2(10 BYTE)
LTR_NME VARCHAR2(20 BYTE)
LTR_DESC VARCHAR2(40 BYTE)
LTR_HDR_ID NUMBER(10,0)
LTR_TXT VARCHAR2(40 BYTE)
LTR_TYP VARCHAR2(1 BYTE)
ASK_FLD_IND VARCHAR2(1 BYTE)
DEFN_TYP VARCHAR2(1 BYTE)
EVNT_OPER_ID VARCHAR2(10 BYTE)
EVNT_TME_STMP DATE
STOR_IMG_FLG VARCHAR2(1 BYTE)
XML_BASED VARCHAR2(1 BYTE)
----- LTR_HDR_TMPLT_XRF ------
LTR_ID NUMBER(10,0)
LTR_TMPLT_ID NUMBER(10,0)
LTR_HDR_ID NUMBER(10,0)
LTR_SEQ NUMBER(10,0)
EVNT_OPER_ID VARCHAR2(10 BYTE)
EVNT_TME_STMP DATE
----- TABLE_ATTR ------
OWNER_ID VARCHAR2(50 BYTE)
OWNER_TYPE VARCHAR2(50 BYTE)
ATTR_KEY VARCHAR2(50 BYTE)
ATTR_VAL VARCHAR2(4000 BYTE)
EVNT_TME_STMP DATE
EVNT_OPER_ID VARCHAR2(10 BYTE)
------ DATA VALUE ------------
LTR_ID CLNT_CODE LTR_GRP LTR_P_ID LTR_CODE
1 PMIN L 1 LTRTST
2 PMIN A 2 IT000
26 PMIN L 1 TEST2
|
|
|
|
|
|
|
|
|
|
| Re: Error: ORA-01722: invalid number [message #517540 is a reply to message #517539] |
Tue, 26 July 2011 09:25 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
BlackSwan wrote on Tue, 26 July 2011 15:16When VARCHAR2 contains non-numeric characters, then ORA-01722 error is thrown.
Error might be thrown, it depends on the access path amongst other things. Which is why the OP isn't getting it consistently.
|
|
|
|
| Re: Error: ORA-01722: invalid number [message #517562 is a reply to message #517535] |
Tue, 26 July 2011 11:15 |
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
rhyamdale wrote on Tue, 26 July 2011 10:01
Yes, I believe that this might be something wrong with my db settings. Another info, when the value is = 2 or 1, it succeeded while if the query value is a double digit, it failed with the invalid number issue. Please consider below.
There is nothing wrong with database settings. It is your view. Condition OWNER_ID =lm.ltr_id, to be precise. OWNER_ID is a sting while ltr_id is a number. When comparing number to a string Oracle implicitly converts string to number. Obviously non-numeric OWNER_ID value will cause the error. And anticipating your next question " but OWNER_TYPE='LTR_MSTR' means OWNER_ID is numeric string" - there is no order in predicate execution. Optimizer decides which predicate apply first. If OWNER_TYPE='LTR_MSTR' is applied first, then OWNER_ID =lm.ltr_id will not cause ORA-01722. But if OWNER_ID =lm.ltr_id is applied first - it will. And it is possible that based on stats (most likely histograms in this case) for value = 2 or 1 optimizer decides to use one plan and for 25 a different one. But this is not that important. What is important - your view is unpredictable ticking bomb. You obviously have design issues. Meanwhile, change view definition and replace OWNER_ID =lm.ltr_id with OWNER_ID = to_char(lm.ltr_id).
SY.
[Updated on: Tue, 26 July 2011 11:15] Report message to a moderator |
|
|
|
| Re: Error: ORA-01722: invalid number [message #517563 is a reply to message #517562] |
Tue, 26 July 2011 11:21 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
syakobson wrote on Tue, 26 July 2011 17:15Meanwhile, change view definition and replace OWNER_ID =lm.ltr_id with OWNER_ID = to_char(lm.ltr_id).
Just bare in mind that that will prevent the usage of any indexes on lm.ltr_id
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
