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;
/
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]