Oracle SQL - Table Create Issue

Question

i am currently trying to create a person table that allows a person to be either a client or a staff member. Currently this is my code :-

Create Or Replace Type Person As OBJECT
(
  person_id number(5) not null,
  firstname varchar2(15) not null,
  surname varchar2(15) not null,
  address1 varchar2(70) not null,
  address2 varchar2(70),
  address3 varchar2(70),
  postcode varchar2(9) not null
);
/

Create Or Replace Type Client Under Person
(
  marital_status varchar2(10),
  no_of_children number(2)
);
/

Create Or Replace Type Staff Under Person
(
  job_title varchar2(15) not null,
  salary number(4,2) not null,
  manager_id number(5) not null
);
/

Create Table Person Of Person
(
  person_id Primary Key
);

The object types all compile but when it goes to create the table i get the following error:-

SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

what is causing this error to occur and what can be done to rectify it. Any help is greatly appreciated.

********EDIT**********

I have changed the name of the table to person_tbl and still get the same error. For some reason, this error now appears in the compiler log:-

Error: PL/SQL: Compilation unit analysis terminated
Error(1,18): PLS-00905: object [ConnectionName].PERSON is invalid

I have no idea why it isn't letting me use the Person type as I have triede this method before successfully.

You need this. Create TYPE PersonTable as TABLE Of Person; You actually, cannot create a heap organised table with a type. — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Feb 11, 2015 at 14:41
@MaheswaranRavisankar: In Oracle, you can create an "object table" based on a type. The syntax in the question is valid, outside of the issues addressed in the answers. — Allan
– Allan, Commented Feb 11, 2015 at 15:52

tonirush · Accepted Answer · 2015-02-11 14:43:17Z

1

Don't know, why you get these error. You should get the error

00955. 00000 -  "name is already used by an existing object"

when trying to create a table with the same name as a type.

Try

Create Table Persons Of Person
(
  person_id Primary Key
);

answered Feb 11, 2015 at 14:43

tonirush

4301 gold badge6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Allan · Accepted Answer · 2015-02-11 16:07:14Z

You have a few problems. As @tonirush mentioned, you can't have more than one object in a database with the same name.

Additionally, the person type is compiling with errors (specifically PLS-00218: a variable declared NOT NULL must have an initialization assignment). Until you resolve these errors, you can't build an object table based on the object.

Your subtypes also have compilation errors: PLS-00590: attempting to create a subtype UNDER a FINAL type, but that's not relevant to the inability to create the object table.

As a footnote, the word "object" in this answer (and in Oracle databases, in general) is overloaded. In the first paragraph, I'm speaking of "database objects", which is pretty much anything that is created in the database with a create command. For the rest I'm speaking of "object types" which are object created by create type ... object specifically.

Collectives™ on Stack Overflow

Oracle SQL - Table Create Issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest