Hi all,
I am using cursor attribute %ROWCOUNT and I would like to insert its value into a table. Whenever I try to do this directly, I get the ORA-00911 Invalid character error. However inserting over intermediate variable works. Any idea why?
Consider I have a table called SOME_TABLE with e.g. two rows and SOME_TABLE2 with one column of type e.g. NUMBER(10). Then I try to run code like this:
DECLARE
CURSOR cur_Test IS
SELECT * FROM SOME_TABLE;
BEGIN
FOR c IN cur_Test LOOP
DBMS_OUTPUT.PUT_LINE(cur_Test%ROWCOUNT); -- this line is ok
INSERT INTO SOME_TABLE2(COL1) VALUES(cur_Test%ROWCOUNT); -- here it fails
END LOOP;
END;
However when I run it like this:
DECLARE
i NUMBER;
CURSOR cur_Test IS
SELECT * FROM SOME_TABLE;
BEGIN
FOR c IN cur_Test LOOP
DBMS_OUTPUT.PUT_LINE(cur_Test%ROWCOUNT); -- this line is ok
i := cur_Test%ROWCOUNT;
INSERT INTO SOME_TABLE2(COL1) VALUES(i); -- now it works
END LOOP;
END;
I suppose that the '%' character causes the problem. Maybe there is some way how to backslash it or something?
Of cource I can use ROWNUM value or other solution as well, I am just curious why this doesn't work.
Thanks for help