PROCEDURE load_my_recs() IS
rec_count PLS_INTEGER := 0;
loop_count PLS_INTEGER := 0;
CURSOR source_cur IS
SELECT * FROM my_table;
TYPE source_coll_t IS TABLE OF my_table%ROWTYPE
INDEX BY PLS_INTEGER;
local_coll source_coll_t;
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
BEGIN
OPEN source_cur;
LOOP
FETCH source_cur BULK COLLECT
INTO local_coll LIMIT 100;
EXIT WHEN local_coll.COUNT = 0;
loop_count := 0;
/* ****** Your extra processing here ****** */
FOR idx IN 1 .. local_coll.COUNT
LOOP
loop_count := loop_count + 1;
source_coll(rec_count + idx) := local_coll(idx);
END LOOP;
/* ****** Your extra processing here ****** */
/* ****** Column definitions must match exactly! ****** */
FORALL idx IN 1 .. local_coll.COUNT
INSERT INTO my_table VALUES local_coll(idx);
rec_count := rec_count + loop_count;
END LOOP;
CLOSE source_cur;
DBMS_OUTPUT.PUT_LINE('Last loop count: ' || loop_count);
DBMS_OUTPUT.PUT_LINE('Total count: ' || rec_count);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('load_my_recs - NO_DATA_FOUND');
RAISE no_data_found;
WHEN bulk_errors
THEN
dbms_output.put_line('EXCEPTIONS in FORALL, count: ' || SQL%BULK_EXCEPTIONS.COUNT)
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
/* The index value in the binding array that caused the error. */
dbms_output.put_line(' Error Index: ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
/* The error code that was raised.
Warning! Oracle stores this as a positive, not negative value.
*/
dbms_output.put_line(' SQLCODE: ' || SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
/* ADD A BETTER ERROR-HANDLER HERE SO THEY ACTUALLY GET SEEN! */
END LOOP;
WHEN INVALID_CURSOR THEN
dbms_output.put_line('INVALID CURSOR. loop count: ' || loop_count || '. Total count: ' ||
rec_count);
RAISE;
WHEN OTHERS THEN
dbms_output.put_line('OTHER EXCEPTION IN load_my_recs. loop count: ' || loop_count || '. Total count: ' ||
rec_count);
dbms_output.put_line(' EXCEPTION - ' || SQLCODE || ': ' ||
SQLERRM);
RAISE;
END load_my_recs;
SELECT REGEXP_REPLACE(
'string'
, '^([[:space:]]|'||CHR(9)||'|'||CHR(10)||'|'||CHR(13)||')+|([[:space:]]|'||CHR(9)||'|'||CHR(10)||'|'||CHR(13)||')+$', '*') AS result
FROM dual;
-- drop all tables in current schema
declare
table_name varchar2(30);
cursor usertables is select * from user_tables where table_name not like 'BIN$%';
begin
for next_row in usertables
loop
execute immediate 'drop table ' || next_row.table_name || ' cascade constraints';
end loop;
end;
/