create or replace package Geom
AUTHID CURRENT_USER
Is
...

  Function Rotate (
    p_geometry in MDSYS.SDO_Geometry,
    p_dimarray in MDSYS.SDO_Dim_Array,
    p_X        in number,
    p_Y        in number,
    p_rotation in number := 0)
    return MDSYS.SDO_Geometry deterministic;

  /**
  * Overloads of main Rotate function
  **/
  Function Rotate( p_geometry  IN MDSYS.SDO_GEOMETRY,
                   p_tolerance IN number,
                   p_rotation  IN number := 0)  -- 0 to 360 degrees
    Return MDSYS.SDO_GEOMETRY Deterministic;

  Function Rotate( p_geometry  IN MDSYS.SDO_GEOMETRY,
                   p_tolerance IN number,
                   p_X         IN number,
                   p_Y         IN number,
                   p_rotation  IN number := 0)  -- 0 to 360 degrees
    Return MDSYS.SDO_GEOMETRY Deterministic;

  Function Rotate( p_geometry IN MDSYS.SDO_GEOMETRY,
                   p_dimarray IN MDSYS.SDO_DIM_ARRAY,
                   p_rotatePt IN MDSYS.SDO_Point_Type,
                   p_rotation IN number := 0)  -- 0 to 360 degrees
    Return MDSYS.SDO_Geometry Deterministic;

Let’s explore rotation.

Firstly, let’s define a rectangular polygon whose WKT is:

“POLYGON ((2 2, 2 7, 12 7, 12 2, 2 2))”

Original Rectangle Polygon before Rotation

Then, let’s rotate it about itself, 45 degrees. In the following call, the false orig rotation parameters (p_x and p_y) are set to NULL. The Rotate function then gets the Minumum Bounding Rectangle (MBR) of the rectangle, extracts its centre, and then rotates about that point.

select Geom.Rotate(mdsys.sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL),0.05,45) 
  from dual; 

GEOM45
-------------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(5.2,-0.8,1.7,2.7,8.8,9.8,12.3,6.3,5.2,-0.8))

1 rows selected

And this looks like:

Original Rectangle after Rotation 45 degrees

(Now, it appears that the Rotate function in PostGIS only rotates around the origin of the co-ordinate system. Thus to achieve the above one must translate the geometry to the origin, rotate it, then translate it back. Here is a blog on how to do this in PostGIS. Even though my Rotate function has all this built in, this is how one would do what my PostGIS friends did:
SELECT geom.st_translate(
       geom.rotate(
       geom.st_translate(the_geom,-x,-y),
       0.005,null,null,45),
       x,y) as GEOM45
  FROM ( SELECT the_geom, 
                geom.sdo_centroid(the_geom,0.005).sdo_point.x as x,
                geom.sdo_centroid(the_geom,0.005).sdo_point.y as y
           FROM (SELECT mdsys.sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',null) as the_geom 
                   FROM dual )
        );

GEOM45
--------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(5.23,-0.8,1.7,2.73,8.77,9.8,12.3,6.27,5.23,-0.8))

1 rows selected
I think having it all inside the Rotate function is neater but less “orthogonal”.)

Now, let’s rotate about the 0,0 axis.

select Geom.Rotate(b.the_geom,0.05,0,0,45 ) as geom00
  from (select mdsys.sdo_geometry('POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))',NULL) as the_geom
         from dual
       ) b;

GEOM00
--------------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(0,2.8,-3.5,6.4,3.5,13.4,7.1,9.9,0,2.8))

1 rows selected

Which looks like:

Original rectangle rotated 45 degrees around origin 0,0

We can do some cool things.

First, let’s take a single point (0,10) and rotate it around 359 degrees.

select a.column_value as oid,
       CONCAT('degrees = ',to_char(a.column_value,'999')) as descptn,
       geom.rotate(mdsys.sdo_geometry(2001,null,sdo_point_type(0,10,Null),null,null),0.0005,0,0,a.column_value) as geom
  from table(geom.generate_series(1,359,1)) a;

oid	descptn	geom
1	degrees = 1	MDSYS.SDO_GEOMETRY(2001,null,MDSYS.SDO_POINT_TYPE(-0.175,9.998,null),null,null)
2	degrees = 2	MDSYS.SDO_GEOMETRY(2001,null,MDSYS.SDO_POINT_TYPE(-0.349,9.994,null),null,null)
…	…	…
359	degrees = 359	MDSYS.SDO_GEOMETRY(2001,null,MDSYS.SDO_POINT_TYPE(0.175,9.998,null),null,null)

Visually…

Point 0,10 rotated 359 times

Finally, let’s take a single slice of a 2 degree pie and rotate it fully around the circle.

Single 2 degree sector of a pie

select a.column_value as oid,
       CONCAT('Sector = ',to_char(a.column_value,'999')) as descptn,
       geom.rotate(MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,2,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(-0.175,9.998,-0.349,9.994,-0.523,9.986,0,0,-0.175,9.998)),0.0005,0,0,a.column_value) as geom
  from table(geom.generate_series(1,360,2)) a;

oid	descptn	geom
1	Sector = 1	MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,2,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(-0.349,9.993,-0.523,9.986,-0.697,9.975,0,0,-0.349,9.993))
3	Sector = 3	MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,2,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(-0.698,9.975,-0.872,9.962,-1.045,9.945,0,0,-0.698,9.975))
…	…	…
359	Sector = 359	MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,2,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(0,10,-0.175,9.999,-0.349,9.994,0,0,0,10))