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