Thanks for the question, Subo.
Asked: September 02, 2016 - 12:42 pm UTC
Last updated: September 02, 2016 - 2:53 pm UTC
Version: 11.2
Viewed 1000+ times
You Asked
create type deposit_ty2 as object(
depNo number,
depCatagory ref depcatagoey_ty2,
amount number,
period number
)
/
create type deposit_ntty2 as table of deposit_ty2
/
create type address_tyy as object(
homeNo number,
street char(14),
city char(8)
)
/
create type customer_ty2 as object(
custID char(6),
custName char(8),
address address_tyy,
dob char(12),
deposits deposit_ntty2
)
/
alter type customer_ty2
add member function totalAmount return float cascade
/
create or replace type body customer_ty2 as
member function totalAmount return float is
begin
a integer;
select self.custName,sum(p.amount) into a
from table(self.deposits)p;
group by c.custName;
return a;
end;
end;
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PLS-00103: Encountered the symbol "INTEGER" when expecting one of
the following:
:= . ( @ % ;
and Chris said...
You know, all this OO stuff in the database isn't really the best idea. It's easier to stick with plain tables and PL/SQL!
Anyway, you need to put the variable a in the declaration section. i.e. above the begin statement. And sort out your SQL statement:
SQL> create type deposit_ty2 as object (
2 depno number,
3 amount number,
4 period number );
5 /
Type created.
SQL> create type deposit_ntty2 as table of deposit_ty2;
2 /
Type created.
SQL> create type customer_ty2 as object (
2 custid char ( 6 ) ,
3 custname char ( 8 ) ,
4 deposits deposit_ntty2,
5 dob char ( 12 ) ) ;
6 /
Type created.
SQL> alter type customer_ty2 add member function totalamount return float cascade
2 /
Type altered.
SQL> create or replace type body customer_ty2
2 as
3 member function totalamount
4 return float is
5 a integer;
6 begin
7
8 select sum ( p.amount )
9 into a
10 from table ( self.deposits ) p
11 group by self.custname;
12 return a;
13
14 end;
15 end;
16 /
Type body created.