Re: ORA-01722

soup_or_power_at_yahoo.com wrote:
> Can someone please explain why ORA-01722 occurs in the following sql
> near s.C42=115504? s.C42 is a number in the database. Many thanks!
>
>
> Fri Jul 15 19:36:28 2005 DBD::Oracle::st execute failed: ORA-01722:
> invalid number (DBD ERROR: error possibly near <*>
> indicator at char 2161 in
> 'INSERT INTO sendq_tbl ( profile_id, slist_id, block_id )
> SELECT s.profile_id, 58492488, 0 FROM subscriber_tbl s, sub_report_tbl
> srt WHERE s.plist_id = 16131237
> AND srt.profile_id = s.profile_id AND srt.plist_id = 16131237 AND
> ((s.C11 LIKE '%PROSPECT%' OR s.C11 LIKE '%UNKNOWN%' OR s.C11 IS NULL OR
> s.C11 = 'PARTNERSHIPS' OR s.C11 =
> 'MAJOR DONORS' OR s.C11 = 'SPONSORS' OR s.C11 = 'PLEDGERS' OR s.C11 =
> 'SINGLEGIFT' OR
> s.C11 = 'ORGANIZATIONS') AND NOT (s.C36 = '20050408_KINTERA_GT5000' AND
> s.C36 IS NOT NULL) AND NOT
> (s.C36 IN
> ('20050303_TSUNAMI_DV_NEW_DONORS','20050105_KINTERA_NEWDONOR') AND
> s.C36 IS NOT NULL) AND NOT
> (s.PROFILE_KEY LIKE '%.to' OR s.PROFILE_KEY LIKE '%.lk' OR
> s.PROFILE_KEY LIKE '%.lu' OR s.PROFILE_KEY LIKE '%.mm' OR s.PROFILE_KEY
> LIKE '%.mx' OR s.PROFILE_KEY LIKE '%.nl' OR s.PROFILE_KEY LIKE '%.no'
> OR s.PROFILE_KEY L!
> IKE '%.nu' OR s.PROFILE_KEY LIKE '%.nz' OR s.PROFILE_KEY LIKE '%.pe'
> OR s.PROFILE_KEY LIKE '%.pl' OR s.PROFILE_KEY LIKE '%.pt' OR
> s.PROFILE_KEY LIKE '%.se'
> OR s.PROFILE_KEY LIKE '%.sg' OR s.PROFILE_KEY LIKE '%.br' OR
> s.PROFILE_KEY LIKE '%.sk' OR s.PROFILE_KEY LIKE '%.st' OR s.PROFILE_KEY
> LIKE '%.tc' OR s.PROFILE_KEY LIKE '%.tj' OR s.PROFILE_KEY LIKE '%.at'
> OR s.PROFILE_KEY LIKE '%.tr' OR s.PROFILE_KEY LIKE '%.tw' OR
> s.PROFILE_KEY LIKE '%.uk' OR s.PROFILE_KEY LIKE '%.za' OR s.PROFILE_KEY
> LIKE '%.es' OR s.PROFILE_KEY LIKE '%.fi' OR s.PROFILE_KEY LIKE '%.ac'
> OR s.PROFILE_KEY LIKE '%.am' OR s.PROFILE_KEY LIKE '%.as' OR
> s.PROFILE_KEY LIKE '%.is' OR s.PROFILE_KEY LIKE '%.au' OR s.PROFILE_KEY
> LIKE '%.be' OR s.PROFILE_KEY LIKE '%.ca' OR s.PROFILE_KEY LIKE '%.cc'
> OR s.PROFILE_KEY LIKE '%.ch' OR s.PROFILE_KEY LIKE '%.ck' OR
> s.PROFILE_KEY LIKE '%.cl' OR s.PROFILE_KEY LIKE '%.cn' OR s.PROFILE_KEY
> LIKE '%.cr' OR s.PROFILE_KEY LIKE '%.cz' OR s.PROFILE_KEY LIKE '%.de'
> OR s.PROFILE_KEY LIK!
> E '%.dk' OR s.PROFILE_KEY LIKE '%.ee' OR s.PROFILE_KEY LIKE '%.kz' OR
> s.PROFILE_KEY LIKE '%.fr' OR s.PROFILE_KEY LIKE '%.hk' OR s.PROFILE_KEY
> LIKE '%.hm' OR s.PROFILE_KEY LIKE '%.in' OR s.PROFILE_KEY LIKE '%.sh'
> OR s.PROFILE_KEY LIKE '%.it' OR s.PROFILE_KEY LIKE '%.jp' OR
> s.PROFILE_KEY LIKE '%.kg' OR s.PROFILE_KEY LIKE '%.kr')
> AND <*>s.C42 = 1155104) AND (srt.NUM_OPTOUT = 0 AND srt.NUM_FTD <= 3)
> AND s.block_id = :p1') [for Statement "INSERT INTO sendq_tbl (
> profile_id, slist_id, block_id ) SELECT s.profile_id, 58492488, 0 FROM
> subscriber_tbl s, sub_report_tbl srt WHERE s.plist_id = 16131237 AND
> srt.profile_id = s.profile_id AND srt.plist_id = 16131237 AND ((s.C11
> LIKE '%PROSPECT%' OR s.C11 LIKE '%UNKNOWN%' OR s.C11 IS NULL OR s.C11 =
> 'PARTNERSHIPS' OR s.C11 = 'MAJOR DONORS' OR s.C11 = 'SPONSORS' OR s.C11
> = 'PLEDGERS' OR s.C11 = 'SINGLEGIFT' OR s.C11 = 'ORGANIZATIONS') AND
> NOT (s.C36 = '20050408_KINTERA_GT5000' AND s.C36 IS NOT NULL) AND NOT
> (s.C36 IN
> ('20050303_TSUNAMI_DV_NEW_DONORS','20050105_KINTERA_NEWDONOR') AND
> s.C36 IS NOT NULL) AND NOT !
> (s.PROFILE_KEY LIKE '%.to' OR s.PROFILE_KEY LIKE '%.lk' OR
> s.PROFILE_KEY LIKE '%.lu' OR s.PROFILE_KEY LIKE '%.mm' OR s.PROFILE_KEY
> LIKE '%.mx' OR s.PROFILE_KEY LIKE '%.nl' OR s.PROFILE_KEY LIKE '%.no'
> OR s.PROFILE_KEY LIKE '%.nu' OR s.PROFILE_KEY LIKE '%.nz' OR
> s.PROFILE_KEY LIKE '%.pe' OR s.PROFILE_KEY LIKE '%.pl' OR s.PROFILE_KEY
> LIKE '%.pt' OR s.PROFILE_KEY LIKE '%.se' OR s.PROFILE_KEY LIKE '%.sg'
> OR s.PROFILE_KEY LIKE '%.br' OR s.PROFILE_KEY LIKE '%.sk' OR
> s.PROFILE_KEY LIKE '%.st' OR s.PROFILE_KEY LIKE '%.tc' OR s.PROFILE_KEY
> LIKE '%.tj' OR s.PROFILE_KEY LIKE '%.at' OR s.PROFILE_KEY LIKE '%.tr'
> OR s.PROFILE_KEY LIKE '%.tw' OR s.PROFILE_KEY LIKE '%.uk' OR
> s.PROFILE_KEY LIKE '%.za' OR s.PROFILE_KEY LIKE '%.es' OR s.PROFILE_KEY
> LIKE '%.fi' OR s.PROFILE_KEY LIKE '%.ac' OR s.PROFILE_KEY LIKE '%.am'
> OR s.PROFILE_KEY LIKE '%.as' OR s.PROFILE_KEY LIKE '%.is' OR
> s.PROFILE_KEY LIKE '%.au' OR s.PROFILE_KEY LIKE '%.be' OR s.PROFILE_KEY
> LIKE '%.ca' OR s.PROFILE_KEY LIKE '%.cc' OR s!
> .PROFILE_KEY LIKE '%.ch' OR s.PROFILE_KEY LIKE '%.ck' OR s.PROFILE_KEY
> LIKE '%.cl' OR s.PROFILE_KEY LIKE '%.cn' OR s.PROFILE_KEY LIKE '%.cr'
> OR s.PROFILE_KEY LIKE '%.cz' OR s.PROFILE_KEY LIKE '%.de' OR
> s.PROFILE_KEY LIKE '%.dk' OR s.PROFILE_KEY LIKE '%.ee' OR s.PROFILE_KEY
> LIKE '%.kz' OR s.PROFILE_KEY LIKE '%.fr' OR s.PROFILE_KEY LIKE '%.hk'
> OR s.PROFILE_KEY LIKE '%.hm' OR s.PROFILE_KEY LIKE '%.in' OR
> s.PROFILE_KEY LIKE '%.sh' OR s.PROFILE_KEY LIKE '%.it' OR s.PROFILE_KEY
> LIKE '%.jp' OR s.PROFILE_KEY LIKE '%.kg' OR s.PROFILE_KEY LIKE '%.kr')
> AND s.C42 = 1155104) AND (srt.NUM_OPTOUT = 0 AND srt.NUM_FTD <= 3) AND
> s.block_id = ?" with ParamValues: :p1='459952'] at
> /export/home/bfiapps/cvs/dream3x/common/Segment.pm line 158.
> Fri Jul 15 19:49:10 2005 DBD::Oracle::st execute failed: ORA-01722:
> invalid number (DBD ERROR: error possibly near <*> indicator at char
> 543 in 'SELECT COUNT(*) FROM subscriber_tbl s, sub_report_tbl srt WHERE
> s.plist_id = 16131237 AND srt.profile_id = s.profile_id AND
> srt.plist_id = 16131237 AND ((s.C11 LIKE '%PROSPECT%' OR s.C11 LIKE
> '%UNKNOWN%' OR s.C11 IS NULL OR s.C11 = 'PARTNERSHIPS' OR s.C11 =
> 'MAJOR DONORS' OR s.C11 = 'SPONSORS' OR s.C11 = 'PLEDGERS' OR s.C11 =
> 'SINGLEGIFT' OR s.C11 = 'ORGANIZATIONS') AND NOT (s.C36 =
> '20050408_KINTERA_GT5000' AND s.C36 IS NOT NULL) AND NOT (s.C36 IN
> ('20050303_TSUNAMI_DV_NEW_DONORS','20050105_KINTERA_NEWDONOR') AND
> s.C36 IS NOT NULL) AND <*>s.C42 = 1155104) AND (srt.NUM_OPTOUT = 0 AND
> srt.NUM_FTD <= 3) AND s.block_id = :p1') [for Statement "SELECT
> COUNT(*) FROM subscriber_tbl s, sub_report_tbl srt WHERE s.plist_id =
> 16131237 AND srt.profile_id = s.profile_id AND srt.plist_id = 16131237
> AND ((s.C11 LIKE '%PROSPECT%' OR s.C11 LIKE '!
> %UNKNOWN%' OR s.C11 IS NULL OR s.C11 = 'PARTNERSHIPS' OR s.C11 =
> 'MAJOR DONORS' OR s.C11 = 'SPONSORS' OR s.C11 = 'PLEDGERS' OR s.C11 =
> 'SINGLEGIFT' OR s.C11 = 'ORGANIZATIONS') AND NOT (s.C36 =
> '20050408_KINTERA_GT5000' AND s.C36 IS NOT NULL) AND NOT (s.C36 IN
> ('20050303_TSUNAMI_DV_NEW_DONORS','20050105_KINTERA_NEWDONOR') AND
> s.C36 IS NOT NULL) AND s.C42 = 1155104) AND (srt.NUM_OPTOUT = 0 AND
> srt.NUM_FTD <= 3) AND s.block_id = ?" with ParamValues: :p1='459952']
> at /export/home/bfiapps/cvs/dream3x/common/Segment.pm line 158.

I truly wish I could be more helpful ... but the truth is that after looking at your INSERT statement twice the only thing that occurs to me is that someone needs to reexamine the design of this schema.

I have built applications that control things as complex as the manufacture of airplanes with 1.4 million parts and never once seen anything like the above.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)