Oracle PL/SQL: Type Body created with compilation errors.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidtube
Forum Commoner
Posts: 79
Joined: Sun Mar 25, 2007 8:42 pm

Oracle PL/SQL: Type Body created with compilation errors.

Post by davidtube »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi. I can't work out the problem with this code but I'm getting the error Warning: Type Body created with compilation errors.

[syntax="sql"]
CREATE OR REPLACE TYPE sales_person_t UNDER person_t (
  makes			appointment_list_t,
  MEMBER FUNCTION	commission RETURN NUMBER
    ) FINAL;
/



CREATE OR REPLACE TYPE BODY sales_person_t AS
MEMBER FUNCTION commission RETURN NUMBER IS
i	INTEGER;
pp	property_t;
ao	appointment_t;
total	NUMBER := 0;
price	NUMBER;
BEGIN
FOR i IN 1..SELF.makes.COUNT LOOP
ao :=SELF.makes(i);
SELECT DEREF(ao.held_at) INTO pp FROM DUAL;
total := total + pp.price;
END LOOP;
RETURN total;
END;
END;
/
Here's the full code:

Code: Select all

--Drop types

DROP TYPE appointment_t FORCE;
DROP TYPE appointment_list_t FORCE;
DROP TYPE address_t FORCE;
DROP TYPE person_t FORCE;
DROP TYPE sales_person_t FORCE;
DROP TYPE phone_list_t FORCE;
DROP TYPE applicant_t FORCE;
DROP TYPE property_t FORCE;




--Drop tables

DROP TABLE property_tab FORCE;
DROP TABLE person_tab FORCE;
DROP TABLE sales_person_tab FORCE;
DROP TABLE applicant_tab FORCE;
DROP TABLE appointment_tab FORCE;




--Create types

CREATE TYPE appointment_t;
/
CREATE TYPE address_t;
/
CREATE TYPE person_t;
/
CREATE TYPE sales_person_t;
/
CREATE TYPE applicant_t;
/
CREATE TYPE property_t;
/

CREATE OR REPLACE TYPE appointment_t AS OBJECT (
  appointment_no 	NUMBER,
  appointment_date	DATE,
  appointment_time	NUMBER(4),
  appointment_type 	VARCHAR2(200),
  level_of_interest	NUMBER, 
  offer_made		NUMBER,
  offer_accepted	VARCHAR2(3),
  is_made_by		REF sales_person_t,
  is_attended_by	REF applicant_t,
  held_at		REF property_t
  ) ;
/

CREATE TYPE appointment_list_t AS TABLE OF REF appointment_t ;
/

CREATE OR REPLACE TYPE address_t AS OBJECT (
  street   VARCHAR2(200),
  town     VARCHAR2(200),
  county   CHAR(30),
  postcode VARCHAR2(20)
  ) ;
/


/*
For simplicity, you should assume that Person has a unique identifier person# which can be used in place of applicant# in Applicant 
and in place of salesperson# in SalesPerson. SalesPerson and Applicant objects should be stored in a Person table and not in their own tables.
*/

CREATE TYPE person_t AS OBJECT (
  person_no	 NUMBER,
  surname        VARCHAR2(200),
  forename       VARCHAR2(200),
  date_of_birth	 DATE,
  address   	 address_t
  ) NOT FINAL;
/


/*
In the applicant entity, the first contact telephone number is the daytime number Monday to Friday and the second is for weekday evenings and weekends.
*/
CREATE TYPE phone_list_t AS VARRAY(3) OF VARCHAR2(20) ;
/

CREATE OR REPLACE TYPE applicant_t UNDER person_t (
  attends		appointment_list_t,
  phone_list 		phone_list_t,
  max_price		NUMBER,
  desired_area		VARCHAR2(200)
    ) FINAL;
/


CREATE OR REPLACE TYPE property_t AS OBJECT (
  property_no	 	NUMBER,
  date_of_registration	Date,
  type			VARCHAR2(13),
  bedrooms		NUMBER, 
  reception_rooms	NUMBER, 
  bathrooms		NUMBER,
  garage		VARCHAR2(3),
  garden		VARCHAR2(5),	
  region_area		VARCHAR2(200),
  price			NUMBER,
  address   		address_t,
  relates_to		appointment_list_t  
  ) ;
/


CREATE OR REPLACE TYPE sales_person_t UNDER person_t (
  makes			appointment_list_t,
  MEMBER FUNCTION	commission RETURN NUMBER
    ) FINAL;
/



CREATE OR REPLACE TYPE BODY sales_person_t AS
MEMBER FUNCTION commission RETURN NUMBER IS
i	INTEGER;
pp	property_t;
ao	appointment_t;
total	NUMBER := 0;
price	NUMBER;
BEGIN
FOR i IN 1..SELF.makes.COUNT LOOP
ao :=SELF.makes(i);
SELECT DEREF(ao.held_at) INTO pp FROM DUAL;
total := total + pp.price;
END LOOP;
RETURN total;
END;
END;
/


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Is Warning: Type Body created with compilation errors the complete error message?
No ERROR at line #: PLS-#####: text text text ?
davidtube
Forum Commoner
Posts: 79
Joined: Sun Mar 25, 2007 8:42 pm

Post by davidtube »

I gave up on it and started again. Thanks though. :)
Post Reply