Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Use a sequence to bulk collect into a collection?

Use a sequence to bulk collect into a collection?

From: Patrick Demets <notquiteclapton_at_HATESPAMshaw.ca>
Date: Tue, 06 Jun 2006 03:45:17 GMT
Message-ID: <hT6hg.244684$7a.117188@pd7tw1no>


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
Received on Mon Jun 05 2006 - 22:45:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US