You Asked
--------------------creating types--------------
create type currency_vaty as varray(5) of char(3)
/
create type depcatogory_ty as object(
depName char(5),
intRate number(2,2),
minDeposit number(8),
currencies currency_vaty
)
/
create type deposit_ty as object(
depNo char(9),
depCatagory depcatogory_ty,
amount number(8),
period number
)
/
create type deposit_ntty as table of deposit_ty
/
create type addres_ty as object(
homeNo char(4),
street varchar(8),
city varchar(6)
)
/
create type customer_ty as object(
custId varchar(6),
custName char(8),
address addres_ty,
dob varchar(9),
deposits deposit_ntty
)
/
----------creating tables-----------
create table depcatagory_tab of depcatogory_ty
/
create table customer_tab of customer_ty(
custId primary key
)
nested table deposits store as custdeposits
/
-----insert depcatagory_tab----------
insert into depcatagory_tab values(depcatogory_ty('MLN',0.10,10000,currency_vaty('LKR','USD')))
/
insert into depcatagory_tab values(depcatogory_ty('PRO',0.12,25000,currency_vaty('LKR','USD')))
/
insert into depcatagory_tab values(depcatogory_ty('SPR',0.15,50000,currency_vaty('LKR')))
/
insert into depcatagory_tab values(depcatogory_ty('VIP',0.20,100000,currency_vaty('LKR','USD','AUS','GBP')))
/
------
------ insert data to customer table---------
insert into customer_tab values(customer_ty('C001','Amal',addres_ty('30','Flower road','Malabe'),'01-11-1990',deposit_ntty('123123',(select ref(s) from depcatagory_tab s where s.depName='MLN'),20000,3)))
/
and Chris said...
You're reading the error message the wrong way around. As you said, you've supplied a string. But Oracle
expects a UDT. More precisely a deposit_ty.
deposit_ntty is a table of deposit_ty. So the arguments to this must be of type deposit_ty.
Wrap this around your parameters to solve the error:
insert into customer_tab values
(
customer_ty (
'C001',
'Amal',
addres_ty ( '30','Flower','Malabe'
) ,
'01111990',
deposit_ntty (
deposit_ty(
'123123',
( select depcatogory_ty (depname, intrate, mindeposit, currencies)
from depcatagory_tab s where s.depname='MLN'
) ,
20000,
3 )
)
)
)
/
Note you also need to change the ref(s) in the subquery. This returns a "ref depcatogory_ty". You need an "actual" depcatogory_ty. So again, place this in the select, passing the columns as appropriate.
And shorten the strings which are too long for their target data type ;)