How can i select columns from two tables using REF

Question

I'm new to Oracle's PL/SQL i'm using 11g. I have 2 Type Objects each one of them with has a table and third table where i keep references of this 2 objects, how can i retrieve values from objects tables using references in the third table.

First Type Object looks like:

CREATE OR REPLACE TYPE Teacher_T AS OBJECT
(
  teacher_id VARCHAR2(10),
  teacher_name VARCHAR2(20),
  teacher_address VARCHAR2(10),
  MEMBER PROCEDURE Insert_Teacher(new_teacher_id IN VARCHAR2,
                                  new_teacher_name IN VARCHAR2,
                                  new_teacher_address IN VARCHAR2), 
  MEMBER PROCEDURE Delete_Teacher
)
/

And its table:

CREATE TABLE Teacher OF Teacher_T
(
  teacher_id NOT NULL,
  PRIMARY KEY (teacher_id)
);

Procedures definition:

CREATE OR REPLACE TYPE BODY Teacher_T AS
  MEMBER PROCEDURE Insert_Teacher(new_teacher_id IN VARCHAR2,
                                  new_teacher_name IN VARCHAR2,
                                  new_teacher_address IN VARCHAR2) IS
  BEGIN
    INSERT INTO Teacher
     VALUES (new_teacher_id, new_teacher_name,new_teacher_address);
  END Insert_Teacher; 

  MEMBER PROCEDURE Delete_Teacher IS
  BEGIN
    DELETE FROM Teacher
     WHERE Teacher.teacher_id = self.teacher_id;
  END Delete_Teacher;
END; 
/

Example to insert data:

declare
  tea Teacher_T;
begin
  tea := new Teacher_T('','','');
  tea.Insert_Teacher('1','Jhon tea1','Cty');
end;

Second Type Object looks like:

CREATE OR REPLACE TYPE School_T AS OBJECT
(
  sch_id VARCHAR2(10),
  sch_name VARCHAR2(20),
  sch_address VARCHAR2(30),
  sch_type VARCHAR2(15),

  MEMBER PROCEDURE Insert_Sch(new_sch_id IN VARCHAR2,
                              new_sch_name IN VARCHAR2,
                              new_sch_address IN VARCHAR2,
                              new_sch_type IN VARCHAR2),
  MEMBER PROCEDURE Delete_Sch
)
/

And its table:

CREATE TABLE School OF School_T(
  sch_id NOT NULL,
  sch_type CHECK (sch_type IN ('Primary', 'Secondary', 'TechC')),
  PRIMARY KEY (sch_id)
);

Procedures definition:

CREATE OR REPLACE TYPE BODY School_T AS
  MEMBER PROCEDURE Insert_Sch(new_sch_id IN VARCHAR2,
                              new_sch_name IN VARCHAR2,
                              new_sch_address IN VARCHAR2,
                              new_sch_type IN VARCHAR2) IS
  BEGIN
    INSERT INTO School
    VALUES (new_sch_id, new_sch_name, new_sch_address, new_sch_type);
  END Insert_Sch;

  MEMBER PROCEDURE Delete_Sch IS
  BEGIN
    DELETE FROM Teach_In
    WHERE Teach_In.school IN (SELECT REF(a)
                                FROM School a
                               WHERE a.sch_id = self.sch_id);
    DELETE FROM School
    WHERE School.sch_id = self.sch_id;
  END Delete_Sch;
END;
/

Example to insert data:

declare
  sch School_T;
begin
  sch := new School_T('','','','');
  sch.Insert_Sch('2','schName','NYC','Secondary');
end;

Finally references table:

CREATE TABLE Teach_In
(
  teacher REF Teacher_T,
  school REF School_T
);

Procedure def:

CREATE OR REPLACE PROCEDURE Insert_Teach_In(new_teacher_id IN VARCHAR2,
                                            new_sch_id IN VARCHAR2) 
AS
  teacher_temp REF Teacher_T;
  school_temp REF School_T;
BEGIN
  SELECT REF(a) INTO teacher_temp
    FROM Teacher a
   WHERE a.teacher_id = new_teacher_id;

  SELECT REF(b) INTO school_temp
    FROM School b
   WHERE b.sch_id = new_sch_id;

  INSERT INTO Teach_In
  VALUES (teacher_temp, school_temp); 
END Insert_Teach_In;
/

Example to insert data:

begin
  Insert_Teach_In('1','2');
end;

So my goal from this example is to retrieve teacher_name,teacher_address,sch_name,sch_type,sch_address using Teach_In table(references table).

Answer 1

you can do a simple join

select t.*,s.*
from Teacher t
join Teach_In r on r.teacher = ref(t)
join School s on r.school = ref(s)
;