CODEWITHSUNDEEP
sqloracle-database
Chamal
Chamal

Reputation: 33

PL/SQL With Object Types PRIMARY KEY

I have create object relation type as below. 

CREATE OR REPLACE TYPE familycar_t AS OBJECT (
make VARCHAR (10),
model VARCHAR(10),
year NUMBER (4),
fuel_type VARCHAR (10));
/

CREATE OR REPLACE TYPE sedan_t AS OBJECT (
familycar ref familycar_t,
cylinder_Size  number(10));
/

CREATE TABLE familycar OF familycar_t (PRIMARY KEY (make,model));

Now I want to set primary for "Sedan" table as follow.

CREATE TABLE sedan OF sedan_t (PRIMARY KEY (familycar_t.make,familycar_t.model));

But Error occur like,

Error starting at line : 4 in command - CREATE TABLE sedan OF sedan_t (PRIMARY KEY (familycar_t.make,familycar_t.model)) Error report - SQL Error: ORA-00904: "FAMILYCAR_T"."MAKE": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action:

What is the solution for it?

Upvotes: 2

Views: 1777

Answers (2)

0xdb
0xdb

Reputation: 3697

You cannot create an index on attributes whose type is REF (see here). You can only define indexes on REF attributes or columns if the REF is scoped. 

CREATE OR REPLACE TYPE sedan_t AS OBJECT (
    familycar familycar_t,
    cylinder_Size  number(10)
    );
/
CREATE TABLE sedan OF sedan_t (PRIMARY KEY (familycar.make, familycar.model));

Table SEDAN created.

Upvotes: 1

MT0
MT0

Reputation: 168613

An alternative to using references is to use inheritance:

CREATE OR REPLACE TYPE familycar_t AS OBJECT (
  make VARCHAR (10),
  model VARCHAR(10),
  year NUMBER (4),
  fuel_type VARCHAR (10)
) NOT FINAL;
/

CREATE OR REPLACE TYPE sedan_t UNDER familycar_t (
  cylinder_Size  number(10)
);
/

CREATE TABLE familycar OF familycar_t (PRIMARY KEY (make,model));

CREATE TABLE sedan OF sedan_t (PRIMARY KEY (make,model));

However, you don't really need the sedan table in this case:

INSERT INTO familycar
SELECT sedan_t( 'Ford', 'Model-T', 1908, 'Petrol', 4 ) FROM DUAL UNION ALL
SELECT familycar_t( 'Ford', 'Model-A', 1903, 'Petrol' ) FROM DUAL;

SELECT f.*,
       TREAT( VALUE(f) AS sedan_t ).cylinder_size AS cylinder_size
FROM   familycar f;

Output:

MAKE       MODEL            YEAR FUEL_TYPE  CYLINDER_SIZE
---------- ---------- ---------- ---------- -------------
Ford       Model-T          1908 Petrol                 4 
Ford       Model-A          1903 Petrol            (null)

Upvotes: 2

Related Questions