RE: anydata datatype update help

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 <wisernet100_at_yahoo.com>
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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: sfaroult_at_oriolecorp.com

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: ListGuru_at_fatcity.com (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: shermanej_at_eccic.com

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: ListGuru_at_fatcity.com (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).