procedure SDOGeometryDemo;
var Session: TOracleSession;
Query: TOracleQuery;
Polygon, ElemInfo, Ordinates: TOracleObject;
begin
// Create a session and logon as scott
Session := TOracleSession.Create(nil);
Session.LogonUsername := 'scott';
Session.LogonPassword := 'tiger';
Session.LogOn;
// Create a query to create/access/drop table
Query := TOracleQuery.Create(nil);
Query.Session := Session;
with Query do
begin
// Create the table
SQL.Text := 'create table cola_markets(mkt_id number, name varchar2(32), shape mdsys.sdo_geometry)';
Execute;
// Create a transient SDO_GEOMETRY object instance
Polygon := TOracleObject.Create(Session, 'mdsys.sdo_geometry', '');
// Set the type to a 2 dimensional polygon
Polygon.SetAttr('sdo_gtype', 2001);
// Define a rectangle
ElemInfo := Polygon.ObjAttr('sdo_elem_info');
ElemInfo.Elements[0] := 1;
ElemInfo.Elements[1] := 1003;
ElemInfo.Elements[2] := 3;
// Set 2 points at 1,1 and 5,7
Ordinates := Polygon.ObjAttr('sdo_ordinates');
Ordinates.Elements[0] := 1;
Ordinates.Elements[1] := 1;
Ordinates.Elements[2] := 5;
Ordinates.Elements[3] := 7;
// Insert a record with this object instance
SQL.Text := 'insert into cola_markets(mkt_id, name, shape) values (1, ''cola_a'', :polygon)';
DeclareVariable('polygon', otObject);
SetComplexVariable('polygon', Polygon);
Execute;
Session.Commit;
// Free the object
Polygon.Free;
// Requery the row with the previously inserted object instance
Clear;
SQL.Text := 'select * from cola_markets where mkt_id = 1';
Execute;
if not Eof then
begin
// Create a copy of the queried instance
Polygon := TOracleObject.Create(Session, 'mdsys.sdo_geometry', '');
Polygon.Assign(ObjField('shape'));
// Change the 2nd point from 5,7 to 6,8
Polygon.ObjAttr('sdo_ordinates').Elements[2] := 6;
Polygon.ObjAttr('sdo_ordinates').Elements[3] := 8;
// Update the record with this modified object instance
Clear;
SQL.Text := 'update cola_markets set shape = :polygon where mkt_id = 1';
DeclareVariable('polygon', otObject);
SetComplexVariable('polygon', Polygon);
Execute;
Session.Commit;
// Free the object
Polygon.Free;
end;
// Drop the table
Clear;
SQL.Text := 'drop table cola_markets';
Execute;
end;
// Free the query and session
Query.Free;
Session.Free;
end;