参考:
CREATE TABLE tab1 (a SYS.AnyData);
/
-- Insert a built-in type value after explicit conversion to an AnyData.
insert into tab1 values (SYS.AnyData.Convert(5));
/
-- Insert a user-defined type value after explicit conversion to an AnyData.
insert into tab1 values(SYS.AnyData.Convert(employee(5555, "John"));
/
declare
a SYS.AnyData;
t SYS.AnyType;
CURSOR C1 is SELECT a FROM tab1;
n NUMBER;
e EMPLOYEE;
tc PLS_INTEGER;
prec, scale, len, csid, csfrm, count, rval PLS_INTEGER;
sch, tname, version VARCHAR2(50);
begin
OPEN C1;
LOOP
FETCH C1 INTO a;
EXIT WHEN C1%NOTFOUND;
t := a.AnyDataGetType();
tc := t.AnyTypeGetInfo(prec, scale, len, csid. csfrm, sch, tname, version, count
);
/* Dynamically describe the type and do appropriate explicit casts. */
if (tc == DBMS_TYPES.TYPECODE_NUMBER) THEN
rval := a.AnyDataGetValue(n);
DBMS_OUTPUT.PUT_LINE(n);
ELSE if ((tc == DBMS_TYPES.TYPECODE_OBJECT) AND (sch == ’SCOTT’) AND
(tname == ’EMPLOYEE’)) THEN
rval := a.AnyDataGetValue(e);
DBMS_OUTPUT.PUT_LINE(’EMPLOYEE ( ’ || e.empno || ’,’ || e.ename || ’)’);
END IF;
END LOOP;
EXCEPTION
WHEN DBMS_TYPES.TYPE_MISMATCH
DBMS_OUTPUT.PUT_LINE(’This operation is not allowed on this type.’);
END;
/
CREATE TABLE tab1 (a SYS.AnyData);
/
-- Insert a built-in type value after explicit conversion to an AnyData.
insert into tab1 values (SYS.AnyData.Convert(5));
/
-- Insert a user-defined type value after explicit conversion to an AnyData.
insert into tab1 values(SYS.AnyData.Convert(employee(5555, "John"));
/
declare
a SYS.AnyData;
t SYS.AnyType;
CURSOR C1 is SELECT a FROM tab1;
n NUMBER;
e EMPLOYEE;
tc PLS_INTEGER;
prec, scale, len, csid, csfrm, count, rval PLS_INTEGER;
sch, tname, version VARCHAR2(50);
begin
OPEN C1;
LOOP
FETCH C1 INTO a;
EXIT WHEN C1%NOTFOUND;
t := a.AnyDataGetType();
tc := t.AnyTypeGetInfo(prec, scale, len, csid. csfrm, sch, tname, version, count
);
/* Dynamically describe the type and do appropriate explicit casts. */
if (tc == DBMS_TYPES.TYPECODE_NUMBER) THEN
rval := a.AnyDataGetValue(n);
DBMS_OUTPUT.PUT_LINE(n);
ELSE if ((tc == DBMS_TYPES.TYPECODE_OBJECT) AND (sch == ’SCOTT’) AND
(tname == ’EMPLOYEE’)) THEN
rval := a.AnyDataGetValue(e);
DBMS_OUTPUT.PUT_LINE(’EMPLOYEE ( ’ || e.empno || ’,’ || e.ename || ’)’);
END IF;
END LOOP;
EXCEPTION
WHEN DBMS_TYPES.TYPE_MISMATCH
DBMS_OUTPUT.PUT_LINE(’This operation is not allowed on this type.’);
END;
/
在oracle DBA Studio中定义了一个对象类型T_NAME,它只有一个属性T_NAME_ATTR,是VARCHAR2(255),然后想生成建表脚本和插值脚本如下
1)建表脚本:FHS是方案,USERS是表空间,tab1是表名,
CREATE TABLE "FHS"."tab1"(
NAME T_NAME, NOT NULL
PRIMARY KEY("NAME") USING
INDEX
TABLESPACE "USERS"
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS
2 MAXTRANS 255,
UNIQUE("NAME"))
TABLESPACE "USERS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
在sqlplus worksheet运行,提示数据类型ADT的列不能是唯一关键字或主关键字,指示PRIMARY
KEY("NAME") USING出错 (2)如何编写插入脚本:
INSERT INTO "FHS"."tab1"
(ModelAttrIID,ModelIID,AttrId,AttrName,AttrDataType,AttrType,AttrLookupTable,AttrIsIndex,AttrIsNull,AttrIsUnique)
Values(37918cc325375faf,37917226019e7d95,grandfaBasicPropertyId,grandfaBasicPropertyName1,1,1,,0,0,1); 提示数据类型不符,请大侠帮忙。
在oracle DBA Studio中定义了一个对象类型T_NAME,它只有一个属性T_NAME_ATTR,是VARCHAR2(255),然后想生成建表脚本和插值脚本如下
1)建表脚本:FHS是方案,USERS是表空间,tab1是表名,
CREATE TABLE "FHS ". "tab1 "(
NAME T_NAME, NOT NULL
PRIMARY KEY( "NAME ") USING
INDEX
TABLESPACE "USERS "
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS
2 MAXTRANS 255,
UNIQUE( "NAME "))
TABLESPACE "USERS " PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
在sqlplus worksheet运行,提示数据类型ADT的列不能是唯一关键字或主关键字,指示PRIMARY
KEY( "NAME ") USING出错
(2)如何编写插入脚本:
INSERT INTO "FHS ". "tab1 "
(NAME)Values('testname');
在sqlplus worksheet提示数据类型不符.
开始可能是我没说清楚,说的比较复杂,请大侠见量,并请各位大侠帮帮小弟!不胜感激!