Note. This article is a work-in-progress and is not finished ! 

Still we decided to include it into documentation, as it already contains helpful information. 

Object Extensions 

XML 

BFILE 

BLOB / CLOB / NCLOB 

PL/SQL Collections, Var Arrays 

AnyDAC supports Oracle PL/SQL tables as the parameters of PL/SQL anonymous blocks, stored procedures and functions. Note, that a PL/SQL associate table differs from VARRAY and collections. AnyDAC does not support the last two. 

To setup a parameter as a PL/SQL table specify TADParam.ArrayType = atPLSQLTable. Set ArraySize to a maximum table size before ExecProc call. When INOUT, OUT parameter ArraySize is less than the number of table elements assigned at server side, then exception will be raised: 

[AnyDAC][Phys][Ora] ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array
ORA-06512: at line 2

To read or write parameter values use TADParam.AsXxxs[<index>] properties. There index is zero-based. Although at server side the index is 1 based. The empty elements have NULL value and may be tested using TADParam.IsNulls[<index>]. 

For example the server side script: 

CREATE OR REPLACE PACKAGE ADQA_TestPack AS
  TYPE TVC2Tbl IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  PROCEDURE TestPLSQLArray(ATable in out TVC2Tbl);
END ADQA_testpack;
/

CREATE OR REPLACE PACKAGE BODY ADQA_TestPack AS
  PROCEDURE TestPLSQLArray(ATable IN OUT TVC2Tbl) IS
  BEGIN
    for i in ATable.First .. ATable.Last loop
      ATable(i) := '*' || ATable(i) || '*';
    end loop;
  END;
END ADQA_testpack;
/

And the client side code assigning memo lines to the table parameter, executing procedure, reading the table items and filling the memo: 

var
  i: Integer;

ADStoredProc1.PackageName := 'ADQA_TESTPACK';
ADStoredProc1.StoredProcName := 'TESTPLSQLARRAY';
ADStoredProc1.Prepare;

ADStoredProc1.Params[0].ArraySize := Memo1.Lines.Count;
for i := 0 to Memo1.Lines.Count - 1 do
  ADStoredProc1.Params[0].AsStrings[i] := Memo1.Lines[i];
Memo1.Lines.Clear;

ADStoredProc1.ExecProc;
for i := 0 to ADStoredProc1.Params[0].ArraySize - 1 do
  Memo1.Lines.Add(ADStoredProc1.Params[0].AsStrings[i]);

Also see AnyDAC\Samples\DBMS Specific\Oracle\PLSQLAssocArray demo. 

AnyDAC supports Oracle PL/SQL records as the parameters of stored procedures and functions. Note, that a PL/SQL record differs from objects. AnyDAC does not support the Oracle objects. 

It is possible to correctly setup such parameters only when fiMeta in TADStoredProc.FetchOptions.Items and ParamBindMode = pbByName. Then AnyDAC will expand record into a flat list of corresponding Params items. Where each item has a name <parameter name>$<record field name>. 

For example the server side script: 

CREATE OR REPLACE PACKAGE ClientPack IS
  TYPE t_clnt_data IS RECORD (
       client_id numeric,
       name varchar2(10),
       act boolean
  );
  PROCEDURE ClntProc(ARec IN t_clnt_data);
END ClientPack;
/

And the client side code assigning parameter values: 

ADStoredProc1.PackageName := 'MYPACK';
ADStoredProc1.StoredProcName := 'CLNTPROC';
ADStoredProc1.Prepare;
ADStoredProc1.ParamByName('AREC$CLIENT_ID').Value := 100;
ADStoredProc1.ParamByName('AREC$NAME').Value := 'Client 1';
ADStoredProc1.ParamByName('AREC$ACT').Value := True;
ADStoredProc1.ExecProc;

Also see AnyDAC\Samples\DBMS Specific\Oracle\PLSQLRecs demo. 

AnyDAC supports Oracle REF CURSOR, returned by Oracle PL/SQL anonymous blocks, stored procedures and functions. To open first cursor call Open method, to switch to subsequent cursors use NextRecordSet method. After switching to next cursor the previous one is not more accessible. See Command Batches chapter for details. 

For example: 

CREATE PROCEDURE TestRefCrs (ACrs1 IN OUT SYS_REFCURSOR, ACrs2 IN OUT SYS_REFCURSOR) AS
BEGIN
  OPEN ACrs1 FOR SELECT * FROM "Orders";
  OPEN ACrs2 FOR SELECT * FROM "Order Details";
END;

Using TADStoredProc: 

ADStoredProc1.FetchOptions.AutoClose := False;
ADStoredProc1.StoredProcName := 'TESTREFCRS';
ADStoredProc1.Open;
// work with "Orders" table data
ADStoredProc1.NextRecordSet;
// work with "Order Details" table data
ADStoredProc1.Close;

Using TADQuery: 

ADQuery1.FetchOptions.AutoClose := False;
ADQuery1.Open('BEGIN TestRefCrs(:p1, :p2); END;');
// work with "Orders" table data
ADQuery1.NextRecordSet;
// work with "Order Details" table data
ADQuery1.Close;

Note, if REF CURSOR is opened using dynamic SQL command text, then before subsequent Open calls, you should call Disconnect method. This is because AnyDAC keeps dataset prepared and expects the same cursor structure as it was on first Open call. For example: 

CREATE PROCEDURE TestDynCrs (ASQL IN VARCHAR2, ACrs OUT SYS_REFCURSOR) AS
BEGIN
  OPEN ACrs FOR ASQL;
END;

Using TADQuery: 

ADQuery1.FetchOptions.AutoClose := False;
ADQuery1.SQL.Text := 'BEGIN TestDynCrs(:p1, :p2); END;';

ADQuery1.Params[0].AsString := 'SELECT * FROM "Orders"';
ADQuery1.Open;
// work with "Orders" table data
ADQuery1.Close;

ADQuery1.Params[0].AsString := 'SELECT * FROM "Order Details"';
ADQuery1.Disconnect;
ADQuery1.Open;
// work with "Order Details" table data
ADQuery1.Close;

AnyDAC supports CURSOR type columns in a SELECT lists. There may be multiple CURSOR's in the list. But a CURSOR nested into a CURSOR is not supported. Such columns AnyDAC defines as dtRowSetRef and creates for them TDataSetField. To process their row sets, application should use TADMemTable, and set its DataSetField property to a TDataSetField reference. 

While application navigates through the main dataset, the nested datasets will be automatically open and refreshed to provide the nested cursor records for a current record of the main dataset. 

For example see AnyDAC\Samples\DBMS Specific\Oracle\NestedCursors demo. 

To refresh REF CURSOR or nested cursor records, application must reexecute main query. 

To edit REF CURSOR or nested cursor record, application must override updates posting. 

Oracle application backend may send feedback to an application frontend using DBMS_OUTPUT package. AnyDAC allows to automatically receive the DBMS_OUTPUT content. 

Optionally set TADConnection.ResourceOptions.ServerOutputSize to the maximum buffer size. To enable DBMS_OUTPUT set TADConnection.ResourceOptions.ServerOutput to True. After a SQL command execution, application may process DBMS_OUTPUT feedback using TADConnection.Messages. Note, DBMS_OUTPUT processing affects performance, so normally it must be disabled. 

For example: 

var
  i: Integer;
...
ADConnection1.ResourceOptions.ServerOutput := True;
with ADQuery1.SQL do begin
  Clear;
  Add('begin');
  Add('  dbms_output.put_line(''Hello World !'');');
  Add('end;');
end;
ADQuery1.ExecSQL;
if ADConnection1.Messages <> nil then begin
  Memo1.Lines.Clear;
  for i := 0 to ADConnection1.Messages.ErrorCount - 1 do
    Memo1.Lines.Add(ADConnection1.Messages[i].Message);
end;

Connect to Oracle Server, uADPhysOracle Namespace