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

Comments embedded.

And this cursor returns one record.

Bulk collect on one record?

Again, using BULK COLLECT on one record ...

And one final time makes three records you've fetched into three
separate PL/SQL tables.

You don't have 12 values in your table, you have one.

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.

David Fitzjarrell