ODAC

Cursors

Since Oracle 7.3 the REF CURSOR type has been available to allow recordsets to be returned from stored procedures and functions.

Oracle 9i introduced the predefined SYS_REFCURSOR type, meaning we no longer have to define our own REF CURSOR types.

Using Ref Cursors

The example below uses a ref cursor to return a subset of records in the EMP table.

The following procedure opens a query using a SYS_REFCURSOR output parameter. Notice the cursor is not closed in the procedure. It is up to the calling code to manage the cursor once it has been opened.


CREATE OR REPLACE PROCEDURE sp_get_emp (p_deptno    IN  emp.deptno%TYPE, p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END sp_get_emp;
/

Using cursors in ODAC

In ODAC work with cursors may be implemented using the following components: TOraQuery , TOraStoredProc , TOraSQL .

Below is a sample working with cursors in TOraStoredProc :

Delphi

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, Ora;

var
  OraSession: TOraSession;
  OraStoredProc: TOraStoredProc;

begin
  OraSession := TOraSession.Create(nil);
  try
    OraSession.ConnectString := 'scott/[email protected]';
    OraSession.Connect;
    WriteLn('Использование TOraStoredProc');
    OraStoredProc := TOraStoredProc.Create(nil);
    try
      OraStoredProc.Session := OraSession;
      OraStoredProc.StoredProcName := 'sp_get_emp';
      OraStoredProc.Prepare;
      OraStoredProc.ParamByName('p_deptno').AsInteger := 10;
      OraStoredProc.Execute;

      while not OraStoredProc.Eof do begin
        Writeln(OraStoredProc.FieldByName('ename').AsString);
        OraStoredProc.Next;
      end;
    finally
      OraStoredProc.Free;
    end;
  finally
    OraSession.Free;
    readln;
  end;
end.

C++Builder

#include <vcl.h>
#pragma hdrstop

#include <tchar.h>
#include <stdio.h>
#include <Ora.hpp>

#pragma argsused
int _tmain(int argc, _TCHAR* argv[])
{
	TOraSession *OraSession = new TOraSession(NULL);
	try
	{
		OraSession->ConnectString = "scott/[email protected]";
		OraSession->Connect();
		TOraStoredProc *OraStoredProc = new TOraStoredProc(NULL);
		try
		{
			OraStoredProc->StoredProcName = "sp_get_emp";
			OraStoredProc->Prepare();
			OraStoredProc->ParamByName("p_deptno")->AsInteger = 10;
			OraStoredProc->Session = OraSession;

			OraStoredProc->Execute();
			while (!OraStoredProc->Eof)
			{
				printf("%s\n", OraStoredProc->FieldByName("ename")->AsString.t_str());
				OraStoredProc->Next();
			}
		}
		__finally
		{
			OraStoredProc->Free();
		}
	}
	__finally
	{
		OraSession->Free();
		system("pause");
	}
	return 0;
}

If several output parameters in the procedure are cursors, then TOraStoredProc will work only with the first one as with a DataSet. To retrieve other DataSets, the asCursor method must be used:

OraQuery.Cursor := OraSession.ParamByName('Cur2').AsCursor;
OraQuery.Open;
© 1997-2022 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback