Use a sequence to bulk collect into a collection?

[Patrick Demets]

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_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;

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

[fitzj...@cox.net]

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.

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