CODEWITHSUNDEEP
sqloracle-databaseuser-defined-types
Angel Pensive
Angel Pensive

Reputation: 137

How to create a table with an object type?

I can not understand the error in the code:

create table insured_employee (of insured_employee_type scope for (employee_ref) is employee);

Table:

create type insured_employee_type as object (
control_number number(9),
contract CLOB,
employee_ref REF employee_type );

Type:

create type employee_type as object(
ssn number(9),
name varchar2(35),
address varchar2(70),
resume CLOB);

Error:

Error starting at line : 32 in command -
create table insured_employee (of insured_employee_type scope for (employee_ref) is employee)
Error report -
ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Upvotes: 0

Views: 249

Answers (1)

MT0
MT0

Reputation: 167981

You want:

create table insured_employee of insured_employee_type (
  control_number PRIMARY KEY,
  employee_ref   SCOPE IS employees
);

Oracle Setup:

CREATE TYPE employee_type IS OBJECT(
  id         NUMBER(8,0),
  first_name VARCHAR2(100),
  last_name  VARCHAR2(100)
);

CREATE TABLE employees OF employee_type(
  id PRIMARY KEY
);

create type insured_employee_type as object (
  control_number number(9),
  contract       CLOB,
  employee_ref   REF employee_type
);

db<>fiddle here

Upvotes: 2

Related Questions