ODAC

Using Stored Procedures via the TOraStoredProc Class

This topic describes how to use Oracle stored procedures and functions with ODAC by the help of TOraStoredProc class.

The following sample demonstrates the work with an Oracle stored procedure using the get_all_depts_proc procedure from the previous section. Since the out parameter of the procedure is the cursor, it is possible to work with the procedure as with a simple DataSet.

Note: If several out parameters in the procedure are cursors, then TOraStoredProc will work only with the first one of them as with a DataSet.

[Delphi]


program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  DB,
  DBAccess,
  Ora;

procedure PrintDept(OraSession: TOraSession);
var
  OraStoredProc: TOraStoredProc;
  i: integer;
begin
  //procedure creation
  OraStoredProc := TOraStoredProc.Create(nil);
  OraStoredProc.Session := OraSession;
  //setting the stored procedure name
  OraStoredProc.StoredProcName := 'get_all_depts_proc';
  //The ParamCheck property must be set to True for automatic
  //definition of parameters used in the stored procedure
  OraStoredProc.ParamCheck := True;
  try
    //execution of the stored procedure
    OraStoredProc.Execute;
    //retrieving data from the cursor returned by the procedure
    for i:= 0 to OraStoredProc.FieldCount - 1 do
      Write(OraStoredProc.Fields[i].DisplayName+#09);
    WriteLn;
    while not OraStoredProc.Eof do
    begin
      for i:= 0 to OraStoredProc.Fields.Count - 1 do
        Write(OraStoredProc.Fields[i].AsString+#09);
      WriteLn;
      OraStoredProc.Next;
    end;
  finally
    OraStoredProc.close;
    OraStoredProc.Free;
  end;
end;

var
  OraSession: TOraSession;

begin
  OraSession := TOraSession.Create(nil);
  OraSession.ConnectString := 'SCOTT/[email protected]';
  try
    OraSession.Connect;
    PrintDept(OraSession);
    readln;
  finally
    OraSession.Free;
  end;
end.

[C++ Builder]


#include <vcl.h>
#pragma hdrstop

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

#pragma argsused

void PrintDept(TOraSession *OraSession)
{
  TOraStoredProc *OraStoredProc = new TOraStoredProc(NULL);
  OraStoredProc->StoredProcName = "get_all_depts_proc";
  OraStoredProc->Session = OraSession;
  int i;
  try
  {
	OraStoredProc->Execute();
	for(i = 0; i< OraStoredProc->Fields->Count; i++)
	  printf("%s\t",OraStoredProc->Fields->operator [](i)->DisplayName.t_str());
	printf("\n");
	while (!OraStoredProc->Eof)
	{
		for(i = 0; i< OraStoredProc->Fields->Count; i++)
			printf("%s\t",OraStoredProc->Fields->operator [](i)->AsString.t_str());
		printf("\n");
		OraStoredProc->Next();
	}
  }
  __finally
  {
	OraStoredProc->Free();
  }
}

int _tmain(int argc, _TCHAR* argv[])
{
	TOraSession *OraSession = new TOraSession(NULL);
	int i;
	try
	{
		OraSession->ConnectString = "SCOTT/[email protected]";
		OraSession->Connect();
		PrintDept(OraSession);
		system("pause");
	}
	__finally
	{
		OraSession->Free();
	}
	return 0;
}

See Also

© 1997-2022 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback