如何创建包含嵌套表的对象的Oracle表? [英] How do I create an Oracle table of objects containing nested tables?
问题描述
我的问题类似于 9454933 ,但是我的嵌套表位于对象内.
我希望创建一个用户定义类型的表,其中定义的类型包含一个对象,我的简化设置如下所示:
CREATE TYPE DEMO_TYPE1 AS OBJECT (A1 NUMBER, A2 NUMBER);
CREATE TYPE DEMO_TAB_TYPE1 AS TABLE OF DEMO_TYPE1;
CREATE TYPE DEMO_TYPE2 AS OBJECT (B1 NUMBER, B2 DEMO_TAB_TYPE1);
CREATE TABLE DEMO_TAB1 (C1 NUMBER, C2 DEMO_TYPE2);
如果运行以上命令,则会出现以下错误
The storage clause is not specified for the nested table column or attribute.
这很有意义,但我无法找出正确的语法来解决我尝试过的问题
CREATE TABLE DEMO_TAB1 (C1 NUMBER, C2 DEMO_TYPE2)
NESTED TABLE B2 STORE AS B2_TAB;
但是我尝试过的这个和所有其他变体导致 ORA-00922:选项丢失或无效
那我该如何解决呢?
错误的原因是C2不是嵌套表.嵌套表是C2中的B2,因此您应该尝试以下操作:
CREATE TABLE DEMO_TAB1 (C1 NUMBER, C2 DEMO_TYPE2)
NESTED TABLE C2.B2 STORE AS B2_TAB;
请参阅sqlfiddle: sqlfiddle.com/#!4/6c662/1 >
My question is similar to 9454933 but my nested table is within the object.
I wish to create a table of user defined types where the defined type contains an object, my simplified setup looks like this
CREATE TYPE DEMO_TYPE1 AS OBJECT (A1 NUMBER, A2 NUMBER);
CREATE TYPE DEMO_TAB_TYPE1 AS TABLE OF DEMO_TYPE1;
CREATE TYPE DEMO_TYPE2 AS OBJECT (B1 NUMBER, B2 DEMO_TAB_TYPE1);
CREATE TABLE DEMO_TAB1 (C1 NUMBER, C2 DEMO_TYPE2);
If I run the above I get the following error
The storage clause is not specified for the nested table column or attribute.
This makes sense but I can't work out the correct syntax to fix this I've tried
CREATE TABLE DEMO_TAB1 (C1 NUMBER, C2 DEMO_TYPE2)
NESTED TABLE B2 STORE AS B2_TAB;
but this and every other variation I've tried result in ORA-00922: missing or invalid option
So how do I resolve this?
The reason of the error is that the C2 is not a nested table. The nested table is the B2 inside C2, so you should try this:
CREATE TABLE DEMO_TAB1 (C1 NUMBER, C2 DEMO_TYPE2)
NESTED TABLE C2.B2 STORE AS B2_TAB;
See sqlfiddle: sqlfiddle.com/#!4/6c662/1
这篇关于如何创建包含嵌套表的对象的Oracle表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!