# Thread: Pl Sql Data Type

1. Registered 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:=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
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:= 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
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*b2-a2*b1;
if(denom=0) then
return 'NULL';
--they are collinear
else

num:=b1*c2-b2*c1;
xc:= num/denom;
xc:=a2*c1-a1*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;

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
> 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```
> return 'True';
> ---- 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';```
2) Define a POINT data type with attributes x and y, and change the function to return a POINT value (null if no intersection) instead of the string 'True'. This would seem to fit in better with your OO design anyway.

3. Registered 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 PLS-00103: Encountered the symbol "end-of-file" when expecting on e of the following: end not pragma final instantiable order o verriding static member constructor map

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;

4. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
You need another "END;" for the type body that contains the function.

5. Registered User
Join Date
Dec 2005
Posts
10

## PL SQL Data Type Error PLS-00363

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 PLS-00363: expression 'SELF.P_RETURNVAL' cannot be used as an ass ignment target
76/3 PL/SQL: Statement ignored
76/3 PLS-00363: expression 'SELF.P_XC' cannot be used as an assignment target
77/3 PL/SQL: Statement ignored
77/3 PLS-00363: 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;

6. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
To 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.

7. Registered User
Join Date
Dec 2005
Posts
10

## PL SQL Data Type Error PLS-00363

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.

8. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
I 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 (pre-defined) 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;```
Then you could use it like this:
Code:
```select intersection( line(point(1,1), point(4,4)),
line(point(1,3), point(4,1)) )
from dual;```

9. Registered 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?

10. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
It 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 stand-alone 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);
...```
Really, you don't need to be working with "create type" at all if you don't want to (this OO stuff is hard to get to grips with). You can just create a procedure that takes in 8 numbers (x1,y1,x2,y2,...) and outputs 2 (xc,yc).

11. Registered User
Join Date
Sep 2004
Location
London, UK
Posts
565
Originally Posted by andrewst
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:
Following Tony's example, I suppose if pairs of lines were something your application used a lot, you could also

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•