Re: Use a sequence to bulk collect into a collection?
- From: "fitzjarrell@xxxxxxx" <fitzjarrell@xxxxxxx>
- Date: 6 Jun 2006 04:29:51 -0700
Comments embedded.
Patrick Demets wrote:
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;
And this cursor returns one record.
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_rowid
, 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;
Bulk collect on one record?
OPEN sqnc_csr;
FETCH sqnc_csr BULK COLLECT INTO tbl_eqpmnt_asgnmn_id;
CLOSE sqnc_csr;
Again, using BULK COLLECT on one record ...
OPEN sqnc_csr;
FETCH sqnc_csr BULK COLLECT INTO tbl_work_item_id;
CLOSE sqnc_csr;
And one final time makes three records you've fetched into three
separate PL/SQL tables.
DBMS_OUTPUT.PUT_LINE ('tbl_ew_eqmt_asgn_id(12)= ' || TO_CHAR
(tbl_ew_eqmt_asgn_id(12)));
You don't have 12 values in your table, you have one.
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?
You might think of creating a DUMMY table containing 12 rows (or more)
to fetch 12 sequence values (or more) at a time. BULK COLLECT won't do
you any good with the cursor you currently have since DUAL returns one
record:
SQL> create sequence testseq;
Sequence created.
SQL> create table testtbl as select rownum myval from user_objects
where rownum < 13;
Table created.
SQL> select testseq.nextval from testtbl;
NEXTVAL
----------
1
2
3
4
5
6
7
8
9
10
11
12
12 rows selected.
Your BULK COLLECT would then provide the results you were originally
expecting.
Thanks,
Patrick Demets
David Fitzjarrell
.
- References:
- Use a sequence to bulk collect into a collection?
- From: Patrick Demets
- Use a sequence to bulk collect into a collection?
- Prev by Date: Re: 10g win amd64 does not install
- Next by Date: Re: service name / invalid directory path
- Previous by thread: Use a sequence to bulk collect into a collection?
- Next by thread: Using Date Mask on ET Make No Rows Returned
- Index(es):
Relevant Pages
|
