Page 1 of 1
Problem with Oracle SDO_GEOMETRY (possibly all array fields)
Posted: Tue 26 Jul 2011 05:15
by LIX
Hello.
I'm trying to fetch an object of Oracle's SDO_GEOMETRY type from database.
For this I set ObjectView:=True for query, and I'm afraid turning it to False is not an option. So UniDAC fetches geometry as an TADTField (I'm using Delphi). Problem is this object has two attributes which are array fields (TArrayField) - SDO_ORDINATES and SDO_ELEM_INFO. Their lengths are always set to 100 so if geometry has more ordinates than that, it is trimmed.
Posted: Wed 27 Jul 2011 10:37
by AlexP
Hello,
This behaviour is connected with the fact that we force cutting of the array to 100 elements, because sdo_elem_info_array is declared as:
CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER
and its size is 1048576 (it's impossible to create such number of fields).
We will try to change this behaviour to make working with such data types more convenient and correct.
You can get all data from your array in the following way: you can select data in the main query without the SDO_GEOMETRY field and retrieve the needed data from this dataset when scrolling using the following parametrized query:
Code: Select all
procedure TForm1.OraQuery2AfterScroll(DataSet: TDataSet);
Var
tip: TOraType;
obj: TOraObject;
i: integer;
begin
tip:= TOraType.Create(OraSession1.OCISvcCtx,'MDSYS.SDO_GEOMETRY');
obj:= TOraObject.Create(tip);
OraQuery1.SQL.Text:= 'begin Select SHAPE into :val from alexp_geometry where id = :id; end;';
OraQuery1.ParamByName('id').DataType := ftInteger;
OraQuery1.ParamByName('id').ParamType := ptInput;
OraQuery1.ParamByName('id').AsInteger := DataSet.FieldByName('id').AsInteger;
OraQuery1.ParamByName('val').DataType := ftObject;
OraQuery1.ParamByName('val').AsObject:=obj;
OraQuery1.Execute;
for i:= 0 to OraQuery1.ParamByName('val').AsObject.AttrAsArray['SDO_ORDINATES'].Size - 1 do
begin
ShowMessage(IntToStr(OraQuery1.ParamByName('val').AsObject.AttrAsArray['SDO_ORDINATES'].ItemAsInteger));
end;
end;
Posted: Thu 28 Jul 2011 07:12
by LIX
Thanks.
I'm looking forward to that improvement.
Posted: Fri 29 Jul 2011 08:56
by LIX
Hello, it's me again!
I tried to run your code with some alterations and it didn't work. Well, first of all I assumed you had used ODAC components such as TOraSession and TOraQuery which doesn't suit me cause I have to do with TUniConnection and TUniQuery instead. So I changed line
Code: Select all
tip:= TOraType.Create(OraSession1.OCISvcCtx,'MDSYS.SDO_GEOMETRY');
to
Code: Select all
tip:= TOraType.Create(TUniUtils.GetCRConnection(UniConnectionOracle) ,'MDSYS.SDO_GEOMETRY');
I left everything else pretty much the same, except I didn't use AfterScroll event.
However execution of this line
fails with exception
First chance exception at $765F42EB. Exception class EConvertError with message 'Unknown data type'. Process Project2.exe (5600)
I tried then to change type of the "val" parameter to ftADT, but the only result I got of it was an access violation
First chance exception at $765F42EB. Exception class EAccessViolation with message 'Access violation at address 006806BD in module 'Project2.exe'. Read of address 00000000'. Process Project2.exe (3836)
Also I tried setting val ParamType to ptOutput and ptResult. It still didn't work but gave me the following error:
First chance exception at $765F42EB. Exception class EOraError with message
'ORA-06550: Line 1, Column 14:
PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER, got MDSYS.SDO_GEOMETRY
ORA-06550: Line 1, Column 7:
PL/SQL: SQL Statement ignored
'.
Process Project2.exe (1812)
You can take a look at my code here:
http://paste2.org/p/1549496
Posted: Wed 03 Aug 2011 13:32
by AlexP
Hello,
Unfortunately, UniDAC does not work with OBJECT types, and you can use the following code to get data from SDO_GEOMETRY:
Code: Select all
var SelectIdQ, SelectGeomQ: TUniQuery;
begin
UniConnectionOracle.Connect;
SelectIdQ:=TUniQuery.Create(nil);
try
SelectIdQ.Connection:=UniConnectionOracle;
SelectIdQ.ObjectView:=True;
SelectIdQ.Sql.Text:='SELECT ID FROM GeomTable';
SelectIdQ.Open;
SelectGeomQ:=TUniQuery.Create(nil);
try
SelectGeomQ.Connection:=UniConnectionOracle;
SelectGeomQ.Sql.Text:='SELECT T.* FROM GeomTable GEOM, TABLE(GEOM.GMTRY.SDO_ORDINATES) t where GEOM.ID = :id';
SelectGeomQ.ParamByName('id').DataType := ftInteger;
SelectGeomQ.ParamByName('id').ParamType := ptInput;
while not SelectIdQ.eof do
begin
SelectGeomQ.ParamByName('id').AsInteger := SelectIdQ.Fields[0].AsInteger;
SelectGeomQ.Open;
while not SelectGeomQ.Eof do
begin
SelectGeomQ.Next;
end;
SelectIdQ.Next;
end;
finally
SelectGeomQ.Free;
end;
finally
SelectIdQ.Free;
end;