Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Use a sequence to bulk collect into a collection?
Can bulk collect be used with a sequence to populate a collection of unique
surrogate keys? I'm getting the following error:
ORA-01403 no data found
Cause: In a host language program, all records have been fetched. The return
code from the fetch was +4, indicating that all records have been returned
from
the SQL query.
Action: Terminate processing for the SELECT statement.
The code I've written is as follows (some code cut out):
CURSOR sqnc_csr IS
SELECT eqpmt_sqnc.NEXTVAL FROM dual;
TYPE ew_eqmt_asgn_id_t IS TABLE OF EW_EQMT_ASGN.ew_eqmt_asgn_id%TYPE
INDEX BY BINARY_INTEGER; TYPE eqpmnt_asgnmn_id_t IS TABLE OF EW_EQMT_ASGN.eqpmnt_asgnmn_id%TYPE INDEX BY BINARY_INTEGER;
TYPE work_item_id_t IS TABLE OF EW_EQMT_ASGN.work_item_id%TYPE INDEX BY BINARY_INTEGER;
tbl_ew_eqmt_asgn_id ew_eqmt_asgn_id_t; tbl_eqpmnt_asgnmn_id eqpmnt_asgnmn_id_t; tbl_work_item_id work_item_id_t; --------------------------------------------------------------------------
-- BEGIN OPEN ew_eqmt_asgn_csr; LOOP EXIT WHEN ew_eqmt_asgn_csr%NOTFOUND; FETCH ew_eqmt_asgn_csr BULK COLLECT INTO tbl_rowidReceived on Mon Jun 05 2006 - 22:45:17 CDT
, tbl_ew_eqmt_asgn_id
, tbl_eqpmnt_asgnmn_id
, tbl_work_item_id;
END LOOP; CLOSE ew_eqmt_asgn_csr; OPEN sqnc_csr; FETCH sqnc_csr BULK COLLECT INTO tbl_ew_eqmt_asgn_id; CLOSE sqnc_csr; OPEN sqnc_csr; FETCH sqnc_csr BULK COLLECT INTO tbl_eqpmnt_asgnmn_id; CLOSE sqnc_csr; OPEN sqnc_csr; FETCH sqnc_csr BULK COLLECT INTO tbl_work_item_id; CLOSE sqnc_csr; DBMS_OUTPUT.PUT_LINE ('tbl_ew_eqmt_asgn_id(12)= ' || TO_CHAR (tbl_ew_eqmt_asgn_id(12))); At this point the proc bombs with the message above. Same results whether I use an explicit cursor or an implicit one. Manual not too helpful in this respect. Any ideas? Thanks, Patrick Demets
![]() |
![]() |