Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-00904: invalid identifier

Seyed_GAug 31 2009 — edited Aug 20 2010
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;
This post has been answered by Anurag Tibrewal on Aug 31 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 17 2010
Added on Aug 31 2009
3 comments
2,618 views