Objects

ODAC allows you to query and update columns of Oracle object type. You can access to attributes of object column as to fields of dataset using TDataSet.FieldByName or TDataSet.Fields. Dataset represents object attributes in two ways depending on the value of ObjectView property. If ObjectView is True attributes are stored hierarchically in the Fields property, that means any attributes of the object column are represented by child field of the object field and don't appear sequentially after the object field in the TFields.Fields array. When ObjectView is False, the attributes are stored sequentially in the Fields property, that means any child fields of the object field are stored after the object field as siblings in the Fields array.

For example we have such types and table

CREATE TYPE TAddress AS OBJECT (
  Country VARCHAR2(30),
  City VARCHAR2(30),
  Street VARCHAR2(30),
  Apartment NUMBER
);

CREATE TYPE TPerson AS OBJECT (
  Name VARCHAR2(30),
  Address TAddress,
  Phone VARCHAR2(20),
  BirthDate DATE
);

CREATE TABLE ODAC_Emp (
  Person TPerson,
  Job VARCHAR2(9),
  HireDate DATE,
  Sal NUMBER(7,2)
);

If you execute this query

SELECT * FROM ODAC_Emp

to learn the name of an employee you can write

Value:= Query.FieldByName('PERSON.NAME').AsString;

If ObjectView is True object column is represented by TADTField and to access the object attribute use child field by TADTField.Fields

Value:= TADTField(Query.FieldByName('PERSON')).
        Fields.FieldByName('NAME')).AsString;

Another way to get the value of an attribute is using TOraObject. Use TOraDataSet.GetObject method to get reference to the needed object.

So, the previous example may be rewritten this way

Value := Query.GetObject('PERSON').AttrAsString['NAME'];

Also ODAC supports object parameters. Use this feature when writing statements to update dataset rows. So, to insert a new row in ODAC_Emp table it is enough to assign this statement to SQLInsert property.

INSERT INTO ODAC_Emp
  (PERSON, JOB, HIREDATE, SAL)
VALUES
  (:PERSON, :JOB, : HIREDATE, :SAL)

To execute this INSERT statement only by TOraQuery or TOraSQL component use TOraParam.AsObject property to set attributes' value of PERSON parameter. But before you should assign dtObject to TOraParam.DataType property and allocate object handle by TOraParam.AllocObject method.

var
  OraSQL: TOraSQL;
. . .
  OraSQL.SQL.Text := 'INSERT INTO ODAC_Emp' +
                     '(PERSON, JOB, HIREDATE, SAL)' +
                     'VALUES (:PERSON, :JOB, : HIREDATE, :SAL)';

  with OraSQL.ParamByName('Person').AsObject do begin
    AllocObject(OraSession.OCISvcCtx, 'TPerson');
    AttrAsString['Name'] := 'JON';
    AttrAsString['Address.Country'] := 'USA';
    AttrAsString['Address.City'] := 'Boston';
    AttrAsInteger['Address.Apartment'] := 133;
    AttrAsDateTime['BirthDate'] := EncodeDate(1970, 7, 23);
  end;
  OraSQL.ParamByName('Job').AsString := 'MANAGER';
  OraSQL.ParamByName('HireDate').AsDateTime := EncodeDate(1998, 5, 14);
  OraSQL.ParamByName('Sal').AsInteger := 1700;
  OraSQL.Execute;

Objects

See Also