Re: ORA-01722

On 2005-07-18, soup_or_power_at_yahoo.com <soup_or_power_at_yahoo.com> wrote:
>
>
> 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')
>

ORA-01722 invalid number

    Cause: The attempted conversion of a character string to a number failed     because the character string was not a valid numeric literal. Only numeric     fields or character fields containing numeric data may be used in     arithmetic functions or expressions. Only numeric fields may be added to or     subtracted from dates.

    Action: Check the character strings in the function or expression. Check     that they contain only numbers, a sign, a decimal point, and the character     "E" or "e" and retry the operation.

So, I'd assume that the problem is not at the s.C42 = 1155104 location, but somewhere around it whereit tries to compare a numeric field with a string.

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/