CODEWITHSUNDEEP
sqloracle-databaseoracle11g
KMS
KMS

Reputation: 78

Insert into table with subtype column: Type Inheritance, Object Oriented SQL

when i try to insert data into following table.

create table Policies of policy_t(
pid primary key,
inscar references Cars
)nested table claims store as  claims_ntable;

using following query,

insert into policies
        select policy_t ('PLC1234','12 Mar 2017','12 Mar 2018',REF(C),175000,claim_t('CLAIM1234567','12 Feb 2017','56000',REF(T)))
        from cars C,customer T
        where C.regno = 'CAB3233' and T.cid = '123ABC'

and i get following error

Error starting at line : 1 in command -
insert into policies
select policy_t ('PLC1234','12 Mar 2017','12 Mar    2018',REF(C),175000,claim_t('CLAIM1234567','12 Feb 2017','56000',REF(T)))
from cars C,customer T
where C.regno = 'CAB3233' and T.cid = '123ABC'
Error at Command Line : 3 Column : 25
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

How to insert data into this table?

Other related Object types and tables,

Customer object type

create type Customer_t as object(
cid char(6),
cusname varchar(15),
birthdate date,
phone char(10),
address varchar(50)
);

Customers table

create table Customers of customer_t(
cid primary key
);

Claim Object type

create type claim_t as object(
claimno char(12),
cdate date,
amount number(8,2),
claimant ref customer_t
);

Claim nested table

create type Claim_ntab is table of claim_t;

Car Object type

create type Car_t as object(
regno char(9),
make varchar(12),
model varchar(10),
mdate date,
owner ref customer_t,
value number(8,2)
);

Car Table

create table Cars of car_t(
regno primary key,
owner references Customers
);

Policy object type

create type policy_t as object(
pid char(7),      
sdate date,
edate date,
inscar ref car_t,
premium number(6,2),
claims claim_ntab
)

Policies Table

create table Policies of policy_t(
pid primary key,
inscar references Cars
)nested table claims store as  claims_ntable;

Upvotes: 3

Views: 770

Answers (3)

William Robertson
William Robertson

Reputation: 16001

After correcting the table name (customer/customers), the next issue is that customers.claims is a claim_ntab but you are attempting to insert a claim_t into it. Try this:

insert into policies
select policy_t
       ( 'PLC1234'
       , date '2017-03-12'
       , date '2018-03-12'
       , ref(ca)
       , 175000
       , claim_ntab(claim_t
         ( 'CLAIM1234567'
         , date '2017-02-12'
         , '56000'
         , ref(cu) ) )
         )
from   cars ca
       join customers cu
            on ref(cu) = ca.owner
where  ca.regno = 'CAB3233'
and    cu.cid = '123ABC'

Edit: I changed your where clause join to ANSI style using an object ref. To be honest I've not used that syntax before and I suspect very few people have, so it will need some testing.

Upvotes: 1

Emily Harkness
Emily Harkness

Reputation: 315

INSERT INTO _table1_ (_field1_, _field2_, _field3_) name of fields, not values SELECT _field1_, _field2_, _field3_ FROM _table2_ WHERE ... ;

INSERT INTO _table1_ (_field1_, _field2_, _field3_) VALUES ('some text', 'some text', 'some text'); if you want to add some values

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

You named your table Customers, note the plural.

So change

from cars C,customer T

to

from cars C,customers T

or even better, while you're at it anyway, rewrite it using explicit JOIN syntax.

Upvotes: 0

Related Questions