Results 1 to 11 of 11
Thread: Pl Sql Data Type

010306, 16:45 #1Registered User
 Join Date
 Dec 2005
 Posts
 10
Unanswered: Pl Sql Data Type
I have worked on this Algorithm to implement it in PL SQL for line intersection.
I NEED TO CREATE A NEW DATA TYPE and...
I need to implement this LINE CROSSOVER ALGORITHM as a MEMBER FUNCTION of the NEW DATA TYPE.
I have made a basic idea of the code and whole like to get help or ideas on how i can go further?
I have tried it as a procedure, but i need to work with is as a NEW DATA TYPE and MEMBER FUNCTION..
How do i declare variables which will be data type number  x1, y1, x2, y2, x3, y3, x4, y4,
xc, yc;
a1, a2, b1, b2, c1, c2; /* Coefficients of line eqns. */
 r1, r2, r3, r4; /* 'Sign' values */
 denom, num; /* Intermediate values */
 Bsign and Asign will have to be type boolean
a1:=y2y1;
b1:=x1x2;
c1:=x2*y1x1*y2;
r3:= a1*x3+b1*y3+c1;
r4:= a1*x4+b1*y4+c1;
asign:=r3<0;
bsign:=r4<0;
if ( (r3!=0) and (r4!=0) and (asign = bsign) ) then
return 'False';
 /* Check signs of r3 and r4. If both point 3 and point 4 lie on
 * same side of line 1, the line segments do not intersect.
 */
else
a2:= y4y3;
b2:= x3x4;
c2:= x4*y3x3*y4;
r1:=a2*x1+b2*y1+c2;
r2:=a2*x2+b2*y2+c2;
asign:=r1<0;
bsign:=r2<0;
if (r1 != 0) and (r2 != 0) and (asign =bsign) then
return 'False';
/* Check signs of r1 and r2. If both point 1 and point 2 lie
* on same side of second line segment, the line segments do
* not intersect.
*/
else
denom :=a1*b2a2*b1;
if(denom=0) then
return 'NULL';
they are collinear
else
num:=b1*c2b2*c1;
xc:= num/denom;
xc:=a2*c1a1*c2;
yc:= num/denom;
return 'True';
 This shows they intersect. HOW can i code to get output for
intersection points xc yx?
end if;
end if;
end if;

010406, 05:16 #2Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1> How do i declare variables which will be data type number  x1, y1, x2, y2, x3, y3, x4, y4,
>  Bsign and Asign will have to be type boolean
Code:function ... is x1 number; y1 number; x2 number; y2 number; x3 number; y3 number; x4 number; y4 number; BSign boolean; ASign boolean;  etc. begin
>  This shows they intersect. HOW can i code to get output for
> intersection points xc yx?
There are several ways you could do this:
1) Provide OUT parameters p_xc and p_yc:
Code:function ... (..., p_xc OUT number, p_yc OUT number) ... p_xc := xc; p_yc := yc; return 'True';
Tony Andrews
http://tinyurl.com/tonyandrews

010406, 17:37 #3Registered User
 Join Date
 Dec 2005
 Posts
 10
Data Type Error Help
I have created the following Data Type and Member function for calculating the algorithm.
I receive the error when executing the TYPE BODY:
LINE/COL ERROR
78/4 PLS00103: Encountered the symbol "endoffile" when expecting on e of the following: end not pragma final instantiable order o verriding static member constructor map
Is it my syntax? Please help
CODE:
create or replace type POINT_A as object
(x1 NUMBER, y1 NUMBER,
x2 NUMBER, y2 NUMBER,
x3 NUMBER, y3 NUMBER,
x4 NUMBER, y4 NUMBER,
p_xc NUMBER,
p_yc NUMBER,
p_returnval NUMBER,
member function Line_algorithm return number
);
Create type body POINT_A as
member function Line_algorithm return number is
xc NUMBER; yc NUMBER;
 coefficients of line eqns.
a1 NUMBER; b1 NUMBER; c1 NUMBER;
a2 NUMBER; b2 NUMBER; c2 NUMBER;
 signn values
r1 NUMBER; r2 NUMBER;
r3 NUMBER; r4 NUMBER;
 intermediate values
denom NUMBER;
num NUMBER;

asign BOOLEAN;
bsign BOOLEAN;
 return value: 1: no intersection, 0: collinear, 1: intersection
returnval NUMBER;
BEGIN
a1 := y2  y1;
b1 := x1  x2;
c1 := x2 * y1  x1 * y2;
r3 := a1 * x3 + b1 * y3 + c1;
r4 := a1 * x4 + b1 * y4 + c1;
asign := r3 < 0;
bsign := r4 < 0;
IF r3 <> 0 AND r4 <> 0 AND asign = bsign
THEN
/* check signs of r3 and r4. If both point 3 and point 4 lie on
the same side of line 1, the line segments do not intersect
*/
returnval := 1;
ELSE
a2 := y4  y3;
b2 := x3  x4;
c2 := x4 * y3  x3 *y4;
r1 := a2 * x1 + b2 *y1 + c2;
r2 := a2 * x2 + b2 *y2 + c2;
asign := r1 < 0;
bsign := r2 < 0;
IF r1 <> 0 AND r2 <> 0 AND asign = bsign
THEN
/* check signs of r1 and r2. If both point 1 and point 2 lie on
same side of second line segment, the line segments do not intersect
*/
returnval := 1;
ELSE
denom := a1 * b2  a2 * b1;
IF denom = 0
THEN
 they are collinear
returnval := 0;
ELSE
num := b1 * c2  b2 * c1;
/* why do you first calculate xc = num/denom, and in the next line as
a2 * c1  a1 * c2? What is the purpose of the first line*
*/
xc := num / denom;
xc := a2 * c1  a1 * c2;
yc := num / denom;
 this shows they intersect
returnval := 1;
END IF;
END IF;
END IF;
p_returnval := returnval;
p_xc := xc;
p_yc := yc;
END;

010506, 05:21 #4Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1You need another "END;" for the type body that contains the function.
Tony Andrews
http://tinyurl.com/tonyandrews

010506, 08:24 #5Registered User
 Join Date
 Dec 2005
 Posts
 10
PL SQL Data Type Error PLS00363
I need help with the error for the end expressions where i am assigning the variable in the type to the body:
LINE/COL ERROR
75/3 PL/SQL: Statement ignored
75/3 PLS00363: expression 'SELF.P_RETURNVAL' cannot be used as an ass ignment target
76/3 PL/SQL: Statement ignored
76/3 PLS00363: expression 'SELF.P_XC' cannot be used as an assignment target
77/3 PL/SQL: Statement ignored
77/3 PLS00363: expression 'SELF.P_YC' cannot be used as an assignment target
create or replace type POINT_A as object
(x1 NUMBER, y1 NUMBER,
x2 NUMBER, y2 NUMBER,
x3 NUMBER, y3 NUMBER,
x4 NUMBER, y4 NUMBER,
p_xc NUMBER,
p_yc NUMBER,
p_returnval NUMBER,
member function Line_algorithm return number
);
Create type body POINT_A as
member function Line_algorithm return number is
xc NUMBER; yc NUMBER;
 coefficients of line eqns.
a1 NUMBER; b1 NUMBER; c1 NUMBER;
a2 NUMBER; b2 NUMBER; c2 NUMBER;
 signn values
r1 NUMBER; r2 NUMBER;
r3 NUMBER; r4 NUMBER;
 intermediate values
denom NUMBER;
num NUMBER;

asign BOOLEAN;
bsign BOOLEAN;
 return value: 1: no intersection, 0: collinear, 1: intersection
returnval NUMBER;
BEGIN
a1 := y2  y1;
b1 := x1  x2;
c1 := x2 * y1  x1 * y2;
r3 := a1 * x3 + b1 * y3 + c1;
r4 := a1 * x4 + b1 * y4 + c1;
asign := r3 < 0;
bsign := r4 < 0;
IF r3 <> 0 AND r4 <> 0 AND asign = bsign
THEN
/* check signs of r3 and r4. If both point 3 and point 4 lie on
the same side of line 1, the line segments do not intersect
*/
returnval := 1;
ELSE
a2 := y4  y3;
b2 := x3  x4;
c2 := x4 * y3  x3 *y4;
r1 := a2 * x1 + b2 *y1 + c2;
r2 := a2 * x2 + b2 *y2 + c2;
asign := r1 < 0;
bsign := r2 < 0;
IF r1 <> 0 AND r2 <> 0 AND asign = bsign
THEN
/* check signs of r1 and r2. If both point 1 and point 2 lie on
same side of second line segment, the line segments do not intersect
*/
returnval := 1;
ELSE
denom := a1 * b2  a2 * b1;
IF denom = 0
THEN
 they are collinear
returnval := 0;
ELSE
num := b1 * c2  b2 * c1;
/* why do you first calculate xc = num/denom, and in the next line as
a2 * c1  a1 * c2? What is the purpose of the first line*
*/
xc := num / denom;
xc := a2 * c1  a1 * c2;
yc := num / denom;
 this shows they intersect
returnval := 1;
END IF;
END IF;
END IF;
p_returnval := returnval;
p_xc := xc;
p_yc := yc;
END;

010506, 08:57 #6Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1To be honest, I don't really understand what you are doing here. You have declared a type called POINT_A, but what does it represent? Normally, points have about 2 attributes (e.g. x and y), but yours has about a dozen.
Maybe what you really wanted to create was a package, not a type?
I don't use types much myself, so I can't help with resolvng the specific issue  but it really doesn't look like you want a type anyway.Tony Andrews
http://tinyurl.com/tonyandrews

010506, 12:04 #7Registered User
 Join Date
 Dec 2005
 Posts
 10
PL SQL Data Type Error PLS00363
In the Point_a Type created, i am creating Points for 2 Lines (hence 8 Points)
This has a member function (which is the algorithm).
In the body, i have created the variables required for the algorithm, and then the member function works through the algorithm.
Any i making any mistakes through this?
I need to create a new data type and test the algorithm as a member function of the new data type.

010506, 16:13 #8Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1I don't think what you are doing makes sense. If you want a procedure that takes 8 values as parameters and returns the point of intersection why don't you just write one (a procedure)  why create a strange TYPE called POINT_A?
TYPEs are meant to represent (complex) data types that variables can be based on: simple (predefined) types are things like integer, varchar2; complex types could be things like POINT (a pair of x, y values), LINE (a pair of points).
You might define types like this:
CREATE TYPE point AS OBJECT (x number, y number);
CREATE TYPE line as OBJECT (start point, end point);
Then you might write a function something like this:
Code:create or replace function intersection ( line1 line, line2 line) return point as p point; begin  Work out the intersection ... return p; end;
Code:select intersection( line(point(1,1), point(4,4)), line(point(1,3), point(4,1)) ) from dual;
Tony Andrews
http://tinyurl.com/tonyandrews

010606, 11:51 #9Registered User
 Join Date
 Dec 2005
 Posts
 10
Data Type
I am trying to work through the above method
Therefore I have created type point:
create or replace type POINT as object
(xval number, yval number,
member function x return number,
member function y return number
)
/
create or replace type body point as
member function X return number is
begin
return xval;
end;
member function y return number is
begin
return yval;
end;
end; /
Creating Type Line
Create or replace type Line as object
(start point, end point
member function Intersection( aPoint Point) return number
The member function is to go through the intersection algorithm and give
the point where the 2 intersect. But how do i create the 2 lines?
);
How can i create the point for the line x1,x2 etc. And wil the member function Intersection work in the way i have created it?

010606, 16:29 #10Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1It doesn't really make sense for Intersection to be a member of the Line type, because it doesn't return information about or act upon a line, it takes two lines as parameters and returns a point. It could be created as a standalone function (create or replace function intersect...) or as part of a package (create package geom_pkg as ...).
To "create a point" in PL/SQL you do this:
Code:declare p point := point(1,2); ...
Tony Andrews
http://tinyurl.com/tonyandrews

010706, 14:32 #11Registered User
 Join Date
 Sep 2004
 Location
 London, UK
 Posts
 565
Originally Posted by andrewst
CREATE TYPE line_pair AS OBJECT (line1 LINE, line2 LINE, intersection POINT);
Then you could give it a constructor function that took two LINE objects and figured out the intersection point, so you could code something like
v_linepair LINE_PAIR := NEW LINE_PAIR(v_someline, v_otherline);
and you would automagically have a LINE_PAIR object with an intersection attribute populated.
This is the whole strength of OO in PL/SQL  you define types that represent something meaningful and give them useful methods, thus breaking down the problem, and the whole thing makes some intuitive sense. I'm in favour of this approach.
Where it gets hard is when you create tables with OO attributes nested and subtyped in funky ways, thus giving yourself all kinds of headaches when you come to query/load/copy/update/delete the data or change the type definition, at which point you wish you'd created OO views of relational tables.