All,
We are using Oracle 10g. I have already created an object TYPE and would like to reference it and create a table. When I run the script that creates a table, I am getting ORA-00904: invalid identifier. It might be that I am prefixing the type name with the schema owner name and column name or in this case TYPE name can not contain special characters such as a dot. I am getting the same error message, if I remove the schema owner name.
Thank you for your help,
Seyed
CREATE OR REPLACE TYPE ABCD."TEMP_O_META_JOIN" AS OBJECT ( "COLUMN_NAME" VARCHAR2(30),
"FOREIGN_DATABASE_NAME" VARCHAR2(30),
"FOREIGN_DATABASE_OWNER" VARCHAR2(30),
"FOREIGN_TABLE" VARCHAR2(30),
"FOREIGN_COLUMN" VARCHAR2(30) );
/
CREATE OR REPLACE TYPE ABCD."TEMP_OT_META_JOINS" AS TABLE OF "ABCD"."TEMP_O_META_JOIN";
/
ALTER TABLE ABCD.TEMP_TABLE_METADATA
DROP PRIMARY KEY CASCADE;
DROP TABLE ABCD.TEMP_TABLE_METADATA CASCADE CONSTRAINTS;
CREATE TABLE ABCD.TEMP_TABLE_METADATA
(
OWNER_NAME VARCHAR2(30 BYTE) NOT NULL,
TABLE_NAME VARCHAR2(30 BYTE) NOT NULL,
TABLE_USER_ALIAS VARCHAR2(30 BYTE) NOT NULL,
TABLE_DESC VARCHAR2(4000 BYTE),
DATABASE_NAME VARCHAR2(30 BYTE),
META_JOINS_NT ABCD.SEYED_OT_META_JOINS,
LAST_UPDATE DATE,
META_MODEL VARCHAR2(200 BYTE),
SOURCE_DATABASE VARCHAR2(50 BYTE),
PS2_NAME VARCHAR2(50 BYTE),
TABLE_TECH_DESC VARCHAR2(4000 BYTE)
)
NESTED TABLE ABCD.TEMP_META_JOINS_NT STORE AS TEMP_META_JOINS_NT_TAB
TABLESPACE DWUSER
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;