Re: ORA-01722

From: <soup_or_power_at_yahoo.com>
Date: 18 Jul 2005 09:46:16 -0700
Message-ID: <1121705176.810461.7540@o13g2000cwo.googlegroups.com>

DA Morgan wrote:
> 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)

Daniel
I am posting the edited sql. Most of the sql is checking for the domain names based on country codes. You can call it a bad schema but it is inevitable given the prolific list of country codes. Here is a brief sql that has the problem at s.C42=1155104. Thank you!

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 ...) )
AND s.C42 = 1155104) AND (srt.NUM_OPTOUT = 0 AND srt.NUM_FTD <= 3) AND s.block_id = '0') Received on Mon Jul 18 2005 - 11:46:16 CDT