Title: Retrieve Multiple Recordsets from ORACLE 8i Stored Procedure
Question: A Faster and more resource efficient way to get data from the Database server (in this case - Oracle)
Answer:
Orace OLE DB supports Multiple Rowsets from version 8.1.7.0.0 and above.
// Oracle DB Preparations:
Create this package at the default "Scott" user.
--------------------------------------------------------------------
CREATE OR REPLACE PACKAGE TEST_PACKAGE AS
TYPE GenericCursor IS REF CURSOR;
PROCEDURE TEST_PROC (out_DEPT_TEST_cursor OUT GenericCursor,
out_EMP_TEST_cursor OUT GenericCursor);
END;
/
CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE AS
PROCEDURE TEST_PROC (out_DEPT_TEST_cursor OUT GenericCursor,
out_EMP_TEST_cursor OUT GenericCursor) AS
BEGIN
OPEN out_DEPT_TEST_cursor FOR SELECT * FROM DEPT;
OPEN out_EMP_TEST_cursor FOR SELECT * FROM EMP;
END;
END;
/
--------------------------------------------------------------------
The procedure "TEST_PROC" will open two cursors one by one :
first - All of DEPT table's records
second - All of EMP table's records
// Delphi Application :
Create new Application with DataModule and save it.
Add the Datamodule to the form's USES list.
Drop a button and two Memo Components into your form.
Drop ADOConnection, ADOCommand, ADODataSet Components into your DM.
Connect the ADOCommand to the ADOConnection using the ADOCommand's "Connection" property.
Build the ADOConnection's Connection String (Connect to the Scott Schema)
Your Connection string should look like that:
Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=scott;Data Source=MY_DataSource;Extended Properties="plsqlrset=1"
Pay Attention that the Extended Properties MUST contains the "plsqlrset=1"
You can either add it manually to the connection string or Add it like this :
Dbl Click on the ADOConnection Component, push the 'Build' button, Goto the 'All' Tab and put
there - plsqlrset=1 (no need for the quotation marks there)
Configure ADOCommand :
Set CommandType property to 'cmdStoredProc'
Set CommandText property to 'TEST_PACKAGE.TEST_PROC'
--------
This is the Button's Click procedure -
procedure TForm1.Button1Click(Sender: TObject);
begin
with DataModule1 do
begin
ADODataSet1.Recordset := ADOCommand1.Execute;
while NOT ADODataSet1.Eof do
begin
Memo1.Lines.Add(ADODataSet1.FieldByName('DNAME').AsString);
ADODataSet1.Next;
end;
ADODataSet1.Recordset := ADODataSet1.Recordset.NextRecordset(EmptyParam);
while NOT ADODataSet1.Eof do
begin
Memo2.Lines.Add(ADODataSet1.FieldByName('ENAME').AsString);
ADODataSet1.Next;
end;
end;
end;
--------
Memo1 will be filled with department names
Memo2 will be filled with employee names