Re: Use a sequence to bulk collect into a collection?

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 Received on Tue Jun 06 2006 - 06:29:51 CDT