Home » SQL & PL/SQL » SQL & PL/SQL » sys.anydata.ConvertClob (Oracle 11.1g)
sys.anydata.ConvertClob [message #448967] Fri, 26 March 2010 01:29 Go to next message
The Other Guy
Messages: 9
Registered: March 2010
Junior Member
Has anyone had success using the sys.anydata.ConvertClob member function when trying to insert data into a Anydata column? This function has been listed in Oracle documentation since 9i, but even in 11G I get the error : ORA-22370: incorrect usage of method AnyData Insert.

Code is pretty straight forward using a CLOB column in a after insert trigger :

Insert into TRACE_DETAIL(OBJ_ID, COLUMN_ID, OLD_VALUE, NEW_VALUE) values (logId, 73, null, sys.anydata.convertClob(:NEW.Req_Data));
Re: sys.anydata.ConvertClob [message #448971 is a reply to message #448967] Fri, 26 March 2010 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the type of NEW.Req_Data?

Regards
Michel
Re: sys.anydata.ConvertClob [message #448972 is a reply to message #448971] Fri, 26 March 2010 01:39 Go to previous messageGo to next message
The Other Guy
Messages: 9
Registered: March 2010
Junior Member
CLOB
Re: sys.anydata.ConvertClob [message #448979 is a reply to message #448972] Fri, 26 March 2010 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And (sorry forgot) NEW_VALUE?

Regards
Michel
Re: sys.anydata.ConvertClob [message #448983 is a reply to message #448967] Fri, 26 March 2010 02:32 Go to previous messageGo to next message
The Other Guy
Messages: 9
Registered: March 2010
Junior Member
anydata, here is that table's script without storage clauses:

create table TRACE_DETAIL
(
OBJ_ID number(16) not null,
Column_Id number(16) not null,
Old_Value anydata null,
New_Value anydata null
);
Re: sys.anydata.ConvertClob [message #448990 is a reply to message #448983] Fri, 26 March 2010 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10577/t_anydat.htm#i1000038

Quote:
Restrictions
Persistent storage of ANYDATA instances whose type contains embedded LOBs other than BFILEs is not currently supported.


Sorry, you cannot do it with this version, maybe 12g.

Regards
Michel
Re: sys.anydata.ConvertClob [message #448991 is a reply to message #448967] Fri, 26 March 2010 03:03 Go to previous message
The Other Guy
Messages: 9
Registered: March 2010
Junior Member
Thanks Michel, not sure how I missed that line (maybe because I wanted to) as I've been searching my eyes out on the net for some help. Odd that Oracle would have member functions that are completely useless for 3 whole versions already.

[Updated on: Fri, 26 March 2010 03:04]

Report message to a moderator

Previous Topic: SPOOL question: Adding quotes around field only if a comma exists within?
Next Topic: windows 7 and nls_lang
Goto Forum:
  


Current Time: Fri Sep 30 03:48:55 CDT 2016

Total time taken to generate the page: 0.05176 seconds