Using Oracle with FireDAC

From Appmethod Topics
Jump to: navigation, search

Go Up to Working with DBMS (FireDAC)


Oracle Advanced Data Types

PL/SQL Tables

FireDAC 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. FireDAC does not support the last two.

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

[FireDAC][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 TFDParam.AsXxxs[<index>] properties. The index is zero-based, while at server side the index is 1 based. The empty elements have a NULL value and may be tested using TFDParam.IsNulls[<index>].

See an example of the server side script below:

CREATE OR REPLACE PACKAGE FDQA_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 FDQA_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 FDQA_testpack;
/

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

var
  i: Integer;

FDStoredProc1.PackageName := 'FDQA_TESTPACK';
FDStoredProc1.StoredProcName := 'TESTPLSQLARRAY';
FDStoredProc1.Prepare;

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

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

Also see the FireDAC\Samples\DBMS Specific\Oracle\PLSQLAssocArray demo.

PL/SQL Records

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

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

See an example of the server side script below:

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 of the client side code assigning parameter values:

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

Also see the FireDAC\Samples\DBMS Specific\Oracle\PLSQLRecs demo.

Oracle Advanced Cursors

Working with Oracle REF CURSOR

FireDAC supports Oracle REF CURSOR, returned by Oracle PL/SQL anonymous blocks, stored procedures and functions. To open the first cursor, call the Open method, and to switch to subsequent cursors, use the NextRecordSet method. After switching to the next cursor, the previous one is not accessible anymore. See the 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 TFDStoredProc:

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

Using TFDQuery:

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

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

For example:

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

Using TFDQuery:

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

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

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

Working with Oracle Nested Cursors

FireDAC supports CURSOR type columns in SELECT lists. There may be multiple CURSORs in the list. But a CURSOR nested into a CURSOR is not supported. FireDAC sets such columns to dtRowSetRef and creates a TDataSetField for them. To process their row sets, the application should use the TFDMemTable, and set its DataSetField property to a TDataSetField reference.

While the 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 examples, see the FireDAC\Samples\DBMS Specific\Oracle\NestedCursors demo.

Updating Cursors Data

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

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

Providing Server Feedback

Oracle backend applications may send feedback to a frontend application using the DBMS_OUTPUT package. FireDAC allows to automatically receive the DBMS_OUTPUT content.

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

For example:

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

See Also