ORACLE BULK COLLECT

One method of fetching data is an Oracle bulk collect.
With Oracle bulk collect, the PL/SQL engine tells the SQL engine to
collect many rows at once and place them in a collection. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them
into the collection and switches back to the PL/SQL engine.
When rows are retrieved using Oracle bulk collect,
they are retrieved with only two context switches.
The larger the number of rows you would like to collect with Oracle bulk collect,
the more performance improvement you will see using an Oracle bulk collect.

CREATE OR REPLACE PROCEDURE forall_errors IS

TYPE myarray IS TABLE OF tmp_target%ROWTYPE;
l_data myarray;

CURSOR c IS
SELECT table_name, num_rows
FROM all_tables;

errors PLS_INTEGER;

dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT 100;

-- SAVE EXCEPTIONS means don't stop if some DELETES fail
FORALL i IN 1..l_data.COUNT SAVE EXCEPTIONS
INSERT INTO tmp_target VALUES l_data(i);

-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXIT WHEN c%NOTFOUND;
END LOOP;
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of DELETE statements that
failed: ' || errors);

FOR i IN 1 .. errors
LOOP
dbms_output.put_line('Error #' || i || ' at '|| 'iteration
#' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
WHEN OTHERS THEN
RAISE;
END forall_errors;
/

DECLARE

lines dbms_output.chararr;

num_lines number;

BEGIN

-- enable the buffer with default size 20000

dbms_output.enable;

dbms_output.put_line('Hello Reader!');

dbms_output.put_line('Hope you have enjoyed the tutorials!');

dbms_output.put_line('Have a great time exploring pl/sql!');

num_lines := 3;

dbms_output.get_lines(lines, num_lines);

FOR i IN 1..num_lines LOOP

dbms_output.put_line(lines(i));

END LOOP;

END;

/