Problem with Oracle SDO_GEOMETRY (possibly all array fields)

LIX · Post by **LIX** » Tue 26 Jul 2011 05:15

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.

AlexP · Post by **AlexP** » Wed 27 Jul 2011 10:37

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;

LIX · Post by **LIX** » Thu 28 Jul 2011 07:12

Thanks.
I'm looking forward to that improvement.

LIX · Post by **LIX** » Fri 29 Jul 2011 08:56

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

Code: Select all

InsertQ.Execute;

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

AlexP · Post by **AlexP** » Wed 03 Aug 2011 13:32

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;