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).

• hotfix: did you checked the REF function? could you also add samle data and examples what you a trying to do please
• blabla: @hotfix thanks, i've added sample data also i explained more what i want.

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)
;