Hello everyone, this is my second post, I hope I do understand and I can give an idea of how to solve this problem since I'm (for now) inexperienced in oracle.
I wish to make a fully dynamic bulk collect into (insert); where they enter as parameters table, and query limit.
This is my code:
CREATE OR REPLACE
PROCEDURE pb_bulk_collect(vctable IN VARCHAR2,
inarray_size IN PLS_INTEGER DEFAULT 100,
vcquery IN CLOB) IS
TYPE t_cursor IS REF CURSOR;
v_cursor t_cursor;
TYPE t_array_elements IS TABLE OF vctable%rowtype INDEX BY BINARY_INTEGER;
l_data t_array_elements;
BEGIN
OPEN v_cursor FOR vcquery;
LOOP
FETCH v_cursor BULK COLLECT INTO l_data LIMIT inarray_size;
FORALL i IN 1..l_data.COUNT
INSERT INTO vctabla VALUES l_data(i);
EXIT WHEN v_cursor%notfound;
END LOOP;
CLOSE v_cursor;
COMMIT;
END;
The above procedure is executed with an error that can not create a table in a dynamic type because it is not a valid rowtype%, on everything else fails.
Is there any way to do better?
Thank you!