Basic reference pointer problem

 I am using SQL* Plus on Oracle 9i and Windows XP. I am trying to complete some work on pointers and am finding it difficult. I have the following types:

CREATE OR REPLACE TYPE student_type AS OBJECT
(FirstName varchar2(15),

LastName varchar2(15),
Address address_type,
Contact contact_type,
DateOfBirth date,
Nationality varchar2(15),
AttendanceMode varchar2(15),
StudentNo varchar2(15),
Userid varchar(15),
FeeStatus varchar2(25))
/

CREATE OR REPLACE TYPE staff_type AS OBJECT
(FirstName varchar2(15),

LastName varchar2(15),
Address address_type,
Contact contact_type,
DateOfBirth date,
Nationality varchar2(15),
Position varchar2(20),
DateStarted date,
TeachingHrs number(4),
StaffNo varchar2(15),
Userid varchar2(15))
/

and have created the following table to contain pointers:

create table mentors_table (
student_pointer ref student_type,
staff_pointer ref staff_type);

I have inserted data into the mentors_table using statements such as:

insert into mentors_table values ((select ref(stud) from student_table stud where userid = '5548223'),
(select ref(st) from staff_table st where userid = '029748457'));

I want to retrieve the members of staff who tutor students I also want to make sure that the staff objects and displayed once. I also have to output just the name.

To just output the name, I have the method getname() for both the staff and student tables. To output the staff object instance for a specified student, I have the line:

select DEREF(staff_pointer) from mentors_table where student_pointer = (select ref(s) from student_table s where userid = '2387387');

Any help with this would be great.
James Received on Wed Nov 26 2003 - 10:13:04 CST