If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Problem with CAST to REF CURSOR from PL/SQL TABLE

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 16
Problem with CAST to REF CURSOR from PL/SQL TABLE

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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On