See chapt 221 ANYDATA TYPE in the PL/SQL Packages and Types doc
create table anydata_test (colA number, colB anydata); insert into anydata_test values (1, anydata.ConvertNumber(5))
Your post raises this question: Why you are using ANYDATA if you don't know what it is or how to work with it? A simple web search for 'oracle 11g anydata' produces the doc link above as the very first item.LOL. You should always read the question before you are trying to answer it.
You should always review and become familiar with the relevant Oracle docs before using any technology.
But this is quite some work and I am wondering why a product with a 3 at the major version does not support all of its own data types. I would expect this feature in version 1.0.
CREATE OR REPLACE FUNCTION ANYDATA_TO_VARCHAR2 ( ANYVAL IN SYS.ANYDATA ) RETURN VARCHAR2 AS lv_number number; lv_varchar2 varchar2(4000); BEGIN case anyval.gettypeName when 'SYS.NUMBER' then if (anyval.getNumber(lv_number) = dbms_types.success) then lv_varchar2 := lv_number; end if; when 'SYS.VARCHAR2' then if (anyval.getVarchar2(lv_varchar2) = dbms_types.success) then null; end if; else lv_varchar2 := 'SYS.ANYDATA'; end case; return lv_varchar2; END ANYDATA_TO_VARCHAR2;
That generates java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype
alter table anydata_test add (colc public.anydata)
I haven't researched to see what JDeveloper does but since you share their code base I would expect the same behavior with them.
alter table anydata_test add (colc anydata)
Yes it does. But it is listed as 'public.anydata'Interesting -- that does not show up in my Type list. I checked on 3.2.2 and 3.0.4 against Oracle EE 126.96.36.199 and XE 10g.
Gary Graham wrote:I created the table with SQL Developer Data Modeler. The name of the logical type is SYS_ANYDATA. It can be engineered into a 10gR2 relational model, which names the type SYS.ANYDATA. The DDL created from the relational model creates the table correctly. From the engineering perspective there is no reason, why it should not be supported.
Even the Advanced mode of New Table from the Connections view does not list anydata as an available data type.