Hi,

I've got a procedure which returns a ref cursor back to a VB test harness.

Here's the bones of it:-

I've got an object declared:-


CREATE TYPE MyRow as object(
ID NUMBER,
Forename VARCHAR2(30),
Family_name VARCHAR2(30),
Postcode VARCHAR2(100));


Then I have a TABLE type declared:-

CREATE TYPE MyRowArray as table of MyRow;


In the package I have a record declared:-

TYPE RowRec is RECORD( ID NUMBER,
Forename VARCHAR2(30),
Family_name VARCHAR2(30),
Postcode VARCHAR2(100));

And I have a another type for the ref cursor:-

TYPE return_cur is REF CURSOR;

Then the proc....

PROCEDURE search_by_address(pForeName VARCHAR2, pPostCode VARCHAR2, p_cursor OUT return_cur) IS
results_tab MyRowArray := MyRowArray();
search_rec RowRec;
clear_rec RowRec;
i NUMBER;
BEGIN
i := 0;

-- Cursor which returns PK's of rows matching input criteria...
FOR app_rec in get_ApplicationsByAddress(pForeName, pPostCode)
LOOP

search_rec := clear_rec; -- clear out the record
i := i+1;
-- pass in the PK and the record, which is returned populated
GetApplicationRec(app_rec.application_id, search_rec);

results_tab.extend;
-- Now populate the table
results_tab(i) := MyRow( search_rec.ID,
search_rec.strForename,
search_rec.strFamily_name,
search_rec.strPostcode
);
END LOOP;


OPEN p_cursor FOR
select
from TABLE (cast(results_tab as MyRowArray));

END;

Now, when I run this from VB, I don't get any errors, but I don't get a recordset back either. If I change the OPEN p_cursor call to:-

OPEN p_cursor FOR
select * from mytable;

Then I get back the three rows from MyTable.

Any ideas what's going wrong?
Thanks,

Paul.