ora-12801 and ora-01722 when using PARALLEL hint
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ora-12801 and ora-01722 when using PARALLEL hint

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    ora-12801 and ora-01722 when using PARALLEL hint

    Hi all,

    I am getting foll. error when using PARALLEL hint and works fine without the hint.

    ora-12801: error signaled in parallel query server P005
    ora-01722: invalid number

    This error comes on the foll. line in SQL statement.

    AND papfa.person_id = DECODE(haaif.attribute3,NULL,papfss.person_id,TO_NUMBER(haaif.attribute3))

    What could be the reason?

    Thanks in Adv.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    haaif.attribute3 contains strings that cannot be converted to numbers
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by slimdave
    haaif.attribute3 contains strings that cannot be converted to numbers
    Most obvous answer. However: how come the same SQL executes fine without PARALLEL hint, as the original poster indicates (if this is actualy true)? It smells more as a bug to me, if what original poster states is true....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Folks,

    I figured it out. In one of the table, attribute3 column has 'NA' value in it, which it is not able to convert into number. So, I used DECODE and now it is not giving the error.

    This is true that it is giving error only if I use PARALLEL hint. This certainly looks like a bug. We are using 8.1.7.4.13 on Windows platform.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Most obvous answer. However: how come the same SQL executes fine without PARALLEL hint, as the original poster indicates (if this is actualy true)? It smells more as a bug to me, if what original poster states is true....
    Or it could be something like.. the order in which the tabs are accessed & joins and so.

    What i mean is something like..

    Case I ( Without parallel hint ) :

    lets assume the table haaif is the driving table (& in exec plan too)and is driven by some other condition & not 'attribute3' (or even 'attribute3' also as we dont know what the other conditions are).. and all the values returned for 'attribute3' is only Numerals..

    Case II ( With parallel hint )

    lets assume here plan is slightly diff from above one.. & haaif is not the driving table.. we will assume papfa is the driving table & for some person id there is a value in haaif where attribute3 is non numeral.. so for that Val, it suerly errors.

    So not necessarly a bug ..

    PS jurij, you can blast if i have some where mistaken in concepts

    Abhay.
    Last edited by abhaysk; 04-07-2004 at 11:53 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    I think, you are right. Because, in following case, I am running the 1st select statement without parallel in it and it still failing with invalid number error. but in second, it is not failing when using decode statement.

    select attribute3,to_number(attribute3) from hxc_time_attributes

    select attribute3,to_number(decode(attribute3,'NA',NULL,attribute3)) from hxc_time_attributes

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width