I don't know squat about the ANYDATA type but I wonder if there is a restriction that you can only update an ANYDATA column with a new value of the same type. For example, if you initially put a '101' VARCHAR2 into the ANYDATA column and then attempted to update it to 102, where 102 is a NUMBER datatype it might fail whereas if you tried to update it to '102', where 102 is a VARCHAR2 then it might work.
Could this be causing your dilemma? I notice there is a GETTYPENAME member function that will return the type name of AnyData: MEMBER FUNCTION GetTypeName( self IN AnyData) RETURN VARCHAR2; The function will return NUMBER, etc. (the type stored in the ANYDATA record) This would let you know what type is stored in the ANYDATA column for a particular row and you could Make sure your updating with the same type. Maybe do an explicit type conversion of the new value before using it with UPDATE. Just a shot in the dark. HTH Ed -----Original Message----- Sent: Friday, October 04, 2002 12:05 PM To: Multiple recipients of list ORACLE-L Rachel, First time I hear about the ANYDATA type but I like to share my ignorance and I guess it must be something akin to a C 'void *' - ie a pointer to 'something'. To bind properly, Oracle needs two things : a) a pointer to the start of the memory area b) something to tell how big this memory area is. Either it's a 'well known' type, or you must use an end marker (typically, a '0' with character strings), or you must explicitly give a size. IMHO Oracle blows up because b) is missing. If you can insert, there must be some way of telling it how large the variable is. I can't see why it would be specific to an update (except if the PL/SQL engine is buggy, which obviously it is, but even more so than appears to the eye). Are you sure that there is not some obscure new function ... ? HTH >----- Original Message ----- >From: Rachel Carmichael <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Sent: Fri, 04 Oct 2002 05:33:23 > >the subject line pretty much describes it. > >9.2.0.1, Solaris 2.8 > >We are using the ANYDATA datatype and while we have >no problems with >insert or select or delete, the process blows up >(ora-7445, coredump) >when we try to update the ANYDATA column. Within a >PL/SQL process, >using aliased tablenames and bind variables for all >values: > >UPDATE MI.T_IN03_ObjPrpty > SET IN03_Value_AD = :b7 > ,IN03_Seq_NO = :b6 > ,RF01_Publisher_KY = :b5 > ,IN03_Amend_DT = :b4 > ,RF02_Status_KY = :b3 > ,IN03_Status_DT = :b2 > WHERE IN03_ObjPrpty_KY = :b1 > >IN03_Value_AD is the ANYDATA column. Statement >works fine if we remove >that column. Statement blows up if we remove all >OTHER columns or if we >run it as is. > >We've posted an iTAR and are waiting. I've searched >MetaLink and the >docs. Nothing useful. > >But the search of the docs left me with a suspicion >that you can't >update an ANYDATA column. > >Has anyone either successfully updated an ANYDATA >column or found >documentation somewhere that says you can't? > >this is stopping development on a critical system. >I'm not the primary >DBA on it, but the consultant DBA doesn't have >access to MetaLink and >isn't on this list so I'm helping out. > >Suggestions? Worst case I suppose we could delete >the original row and >insert the new one but that's kludgy and messy and >an additional >performance hit on a system that needs to "fly like >the wind". I'd >rather fix this properly... of course Oracle is >capable of saying that >the delete and insert IS the workaround and/or >standard procedure for >this. > >Rachel > Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Edward INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
