CODEWITHSUNDEEP
sqloracle-databaseoracle11gsqlplus
dgcharitha
dgcharitha

Reputation: 345

ORA-00936: missing expression error when inserting values

I spend lot of time searching where i made the mistake but i was unable to find it when its going to insert the last record there is a error message showing "ORA-00936: missing expression" How to solve this please help me 

create type pearson_types as object(
name varchar2(50),
sysID char(6)
)NOT FINAL;

create type doctor_types under pearson_types(
regNo char(10),
specialization varchar2(25)
)

create table doctor of doctor_types(
regNo primary key 
)

create type hospVisits_types as object(
hosChg float,
vDate varchar2(20),
refDoc REF doctor_types,
docChg float
)

create type hospvisits_tbl_types as table of hospVisits_types

create type phone_arr as VARRAY(3) of char(10)

create type patient_types under pearson_types
(
id char(10),
dob varchar(20),
phone phone_arr,
hospVisits hospvisits_tbl_types
)

create table patients of patient_types(
id primary key
)nested table hospVisits store as Hospital_tables

alter table Hospital_tables add scope for (refDoc) is doctor

insert into doctor values ('Dr.k.perera','D001','1223441234','Gynecologist');
insert into doctor values ('Dr.p.weerasingha','D002','1234421131','Dermatalogist');
insert into doctor values ('Prof .S. Fernando','D003','2342111322','Pediatrician');
insert into doctor values ('Dr.k.Sathgunanathan','D004','2344114344','Pediatrician');


insert into patients values('Sampath Weerasingha','P001','732821122V','23-JAN-73',phone_arr('0332124222'),hospvisits_tbl_types(hospVisits_types(50.00,'24-MAY-06',select ref (a) from doctor a where a.regNo='1223441234',500.00)))

Upvotes: 4

Views: 10232

Answers (1)

Jon Heller
Jon Heller

Reputation: 36808

Add parentheses to SELECT statements inside a SQL statement:

insert into patients values(
    'Sampath Weerasingha','P001','732821122V','23-JAN-73',phone_arr('0332124222'),
    hospvisits_tbl_types(hospVisits_types(50.00,'24-MAY-06',
        ( -- ADD ME
            select ref (a) from doctor a where a.regNo='1223441234'
        ) -- ADD ME
    ,500.00))
);

Upvotes: 2

Related Questions