Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Assigning a value to an array cell populated [BULK]

920249Mar 2 2012 — edited Mar 2 2012
Hi all,
I've got a problem in assigning a value to an array populated with BULK COLLECT INTO .
I can reproduce the problem with this
CREATE TABLE TEST_TABLE (
value1 NUMBER,
value2 NUMBER
);

INSERT INTO test_table VALUES(1,1);
INSERT INTO test_table VALUES(1,1);
INSERT INTO test_table VALUES(1,1);
INSERT INTO test_table VALUES(1,1);
INSERT INTO test_table VALUES(1,1);
INSERT INTO test_table VALUES(1,1);
INSERT INTO test_table VALUES(1,1);
INSERT INTO test_table VALUES(1,1);
INSERT INTO test_table VALUES(1,1);
INSERT INTO test_table VALUES(1,1);
And this is the PL/SQL anonymous block that gives me problems:
DECLARE
  SUBTYPE t_rec IS TEST_TABLE%ROWTYPE;
TYPE records_table IS TABLE OF t_rec;
  elist RECORDS_TABLE;
  CURSOR table_cursor IS SELECT * FROM TEST_TABLE WHERE rownum <= 20;
BEGIN
  OPEN table_cursor;
  FETCH table_cursor BULK COLLECT INTO elist;
    FOR j IN 1..elist.COUNT
    LOOP
      elist(j)(1) := elist(j)(1) +1;
    END LOOP;
  CLOSE table_cursor;
END; 
The error is
ORA-06550: line 13, column 7:
PLS-00308: this construct is not allowed as the origin of an assignment
ORA-06550: line 13, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
So it doesn't compile because of this line of code:
elist(j)(1) := elist(j)(1) +1;

Why doesn't it work?

If I try to do this, works perfectly:
DECLARE
TYPE v_num_table
IS
  TABLE OF NUMBER;
TYPE v_2_num_table
IS
  TABLE OF v_num_table;
  v_nums V_2_NUM_TABLE := V_2_NUM_TABLE();
BEGIN
  v_nums.EXTEND;
  v_nums(1) := v_num_table();
  v_nums(1).EXTEND;
  v_nums(1)(1) := 1;
  v_nums(1)(1) := v_nums(1)(1) +1;
  dbms_output.put_line(v_nums(1)(1) );
END;
Edited by: user10396517 on 2-mar-2012 2.35
This post has been answered by odie_63 on Mar 2 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 30 2012
Added on Mar 2 2012
3 comments
225 views