How do I insert values into a type table with references

Question

I've tried what seems like a million different ways to try and get values to be inserted into this table.

I have a Line object, this is the code:

CREATE OR REPLACE TYPE LINE AS OBJECT
(EP1 REF Point,
 EP2 REF Point,
 MEMBER FUNCTION getOrientation RETURN Char,
 MEMBER FUNCTION getLen Return Number,
 CONSTRUCTOR FUNCTION Line
 (EP1 REF Point,
 EP2 REF Point) RETURN SELF as RESULT);

create or replace type body line as
member function getLen return number
is
length Integer;
begin
if getOrientation = 'V'
then
Select Deref(EP1).getY() - Deref(EP2).getY()
into length
from Dual;
else
Select Deref(EP1).getX() - Deref(EP2).getX()
into length
from Dual;
end if;
return length;
end;
member function getOrientation return char
is
ep1x Integer;
ep2x Integer;
ep1y Integer;
ep2y Integer;
begin
select Deref(EP1).getX(), Deref(EP1).getY(),Deref(EP2).getX(),Deref(EP2).getY()
into ep1x, ep1y, ep2x, ep2y
from dual;
if ep1x = ep2x
then return 'V';
else return 'H';
end if;
end;
constructor function line
(EP1 REF Point,
EP2 REF Point) return self as result
is
begin
self.ep1 := ep1;
self.ep2 := ep2;
return;
end;
end;
/

I suppose you would also need the Point object as well so here's that too:

CREATE OR REPLACE TYPE POINT AS OBJECT
(label Char,
X Number,
Y Number,
MEMBER FUNCTION getX RETURN Number,
MEMBER FUNCTION getLABEL RETURN Char,
MEMBER FUNCTION getY RETURN Number,
CONSTRUCTOR FUNCTION POINT
(label Char,
X Number,
Y Number) RETURN SELF as RESULT);

CREATE TABLE POINTS OF POINT
   (unique(label),
   unique(X,Y))

create or replace type body point as
member function getX return Number
is
begin
return X;
end;
member function getY return Number
is
begin
return Y;
end;
member function getLABEL return Char
is
begin
return Label;
end;
constructor function point
(Label Char,
X Number,
Y Number) return self as result
is
begin
if X<=0 then
raise_application_error(-20001, 'Invalid X');
end if;
if Y<=0 then
raise_application_error(-20002, 'Invalid Y');
end if;
self.X := X;
self.Y := Y;
self.Label := Label;
return;
end;
end;
/

So what I did was create a table like so:

Create table LINES of line;

In the points table I have some points (A, B, C, D, and E) and I want to create a line out of two points and store them in this table. In the end, I need to create a table called BOXES and insert a rectangle into it (simply 4 lines with a bunch of constraints). I have that object created but the code is way to long to post here. I feel like if I can understand how to put a LINE into a table I can get 4 of them into one, I just want to start off simple.

Now I can get points in the type table just fine, it's pretty straightforward because there's no references. However, with this LINE, I just cant figure out how to get it into the table since it's just two endpoints referencing a point. Do I have to do some de-referencing then put it in the table? If so, how would I do that? Did I create the LINES table wrong? Should it be different since it does have references? I'm just unsure, and I can't seem to find any examples online. If anyone could provide some input I would greatly appreciate it. If you need any additional information, please let me know, thank you.

Answer 1

insert into boxes
values(RECT(
line((select ref(A) --left ep1
from points A
where Label='B'),
(select ref(B) --left ep2
from points B
where Label='A')),
line((select ref(C) --bottom ep1
from points C
where Label='A'),
(select ref(D) --bottom ep2
from points D 
where Label='C')),
line((select ref(E) --right ep1
from points E
where Label='D'),
(select ref(F) --right ep2
from points F 
where Label='C')),
line((select ref(G) --top ep1
from points G 
where Label='B'),
(select ref(H) --top ep2
from points H 
where Label='D'))));

After much struggling, this was what I came up with, and it seems to work. Constraints are applied, the only thing is that it returns a REF which is a bit annoying.