Type body created with compilation error

Question

customer_ty object I have created has a nested table including

CREATE TYPE deposit_ty as object(
depNo number,
depCategory ref depcategory_ty,
amount number,
period number
)
/

CREATE TYPE deposit_tbl as table of deposit_ty
/

CREATE TYPE customer_ty as object(
custId varchar2(4),
custName varchar2(10),
address address_ty,
dob date,
deposits deposit_tbl
)
/

I have written a code to compute the total amount deposited by each client. Here is the code I've written;

alter type customer_ty
add member function totDeposits return number cascade
/

create or replace type body customer_ty as
member function totDeposits 
return number is
total number;
BEGIN
    select sum(d.amount) into total
    from table(self.deposits) d;
    group by self.custId,self.custName
    return total;
END totDeposits;
END;
/

But I get a warning saying that the "type body created with compilation errors". What can I do to get rid of this?

Answer 1

If you do show errors immediately after you get the 'created with compilation errors' message, you'll see something like:

LINE/COL ERROR
-------- ------------------------------------------------------------------------------
8/5      PLS-00103: Encountered the symbol "GROUP" when expecting one of the following:

You can also query the user_errors or all_errors view to see the outstanding errors against any PL/SQL objects.

In this case it's a simple typo; you have the semicolon in the wrong place; instead of:

select sum(d.amount) into total
from table(self.deposits) d;
group by self.custId,self.custName

it should be:

select sum(d.amount) into total
from table(self.deposits) d
group by self.custId,self.custName;

In your version the ... d; is terminating that SQL statement - which would be invalid now because that truncated statement doesn't have a group by clause; but it doesn't get as far as complaining about that because it sees the group by ... as a separate statement, and that isn't the start of anything PL/SQL recognises as a query, statement, control loop etc., so it gives up.