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/TIGER@ORCL1020';
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/TIGER@ORCL1020";
OraSession->Connect();
PrintDept(OraSession);
system("pause");
}
__finally
{
OraSession->Free();
}
return 0;
}