Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Selecting the contents of a table(collection) into a strong REF Cursor

675129Dec 11 2008 — edited Dec 19 2008
I'm trying to roll some data into a table collection and return it as a strong named cursor.
I have not been able to do this successfully yet.
I have tried casting the table and I couldn't get that to work either.

I have included the whole procedure but here is the line I am getting errors on:

SELECT * bulk collect into o_response_data_cur from table (response_data_tbl);

Any help on this would be great.

P.S. - As this is being picked up by BizTalk I can't return a table.

Thanks,

Todd M
-------------------------------------------------------------------

PROCEDURE create_customer (
i_interface_hdr IN BizTalk_TestCustomer.interface_hdr_rec,
i_customer_rec IN BizTalk_TestCustomer.customer_rec,
i_address_cur IN BizTalk_TestCustomer.CUR_Addresses,
i_contact_cur IN BizTalk_TestCustomer.CUR_Contact,
o_interface_status OUT varchar2,
o_response_data_cur OUT BizTalk_TestCustomer.CUR_CreateCustResponse)
IS
l_response_rec create_cust_response_rec;
response_data_tbl create_cust_response_tbl;
BEGIN
FOR i IN 1 .. 10

LOOP
l_response_rec.ERROR_TYPE := 'Pre-Validation Error';
l_response_rec.ERROR_CODE := 'DUMMY-' || i;
l_response_rec.error_message := 'Test Error Message-' || i;
response_data_tbl (i) := l_response_rec;
END LOOP;


SELECT * bulk collect into o_response_data_cur from table (response_data_tbl);
o_interface_status := 'FAILURE';

END create_customer;

END BizTalk_TestCustomer;

------------------------
Here is the important Spec info:

TYPE create_cust_response_rec
IS
RECORD (
orig_system_party_ref varchar2 (240),
orig_system_cust_acct_ref varchar2 (240),
orig_system_site_ref varchar2 (240),
oracle_party_id number,
oracle_customer_id number,
oracle_site_id number,
ERROR_TYPE strar_cust_intf_err.ERROR_TYPE%TYPE,
ERROR_CODE strar_cust_intf_err.ERROR_CODE%TYPE,
error_message strar_cust_intf_err.error_message%TYPE
);

TYPE CUR_Addresses IS REF CURSOR RETURN BizTalk_TestCustomer.address_rec;
TYPE CUR_Contact IS REF CURSOR RETURN BizTalk_TestCustomer.contact_rec;
TYPE CUR_CreateCustResponse IS REF CURSOR RETURN BizTalk_TestCustomer.create_cust_response_rec;

TYPE create_cust_response_tbl
IS
TABLE OF create_cust_response_rec
INDEX BY binary_integer;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 16 2009
Added on Dec 11 2008
9 comments
855 views