Bulk Cursor MIT SAVE EXCEPTIONS
Tipp 62
Thema: Allgemeines
Datenbank-Version: RDBMS 8.x
Erstellt am 07.10.19
Bearbeitet am 07.10.19
DROP TABLE scott.objects;
DROP TABLE scott.tables;
CREATE TABLE scott.objects AS
SELECT object_name,owner FROM all_objects
WHERE owner='SCOTT' and object_type in ('TABLE','INDEX');
CREATE TABLE scott.tables AS
SELECT table_name,owner FROM all_tables WHERE owner='SCOTT';
ALTER TABLE scott.objects ADD PRIMARY KEY (owner,object_name);
SET SERVEROUTPUT ON
DECLARE
TYPE object_tab_type IS TABLE OF scott.objects.object_name%TYPE;
TYPE table_tab_type IS TABLE OF scott.tables.table_name%TYPE;
myobjects_tab object_tab_type;
mytables_tab table_tab_type;
error_count NUMBER;
BEGIN
SELECT object_name BULK COLLECT INTO myobjects_tab
FROM scott.objects;
SELECT table_name BULK COLLECT INTO mytables_tab
FROM scott.tables;
FORALL i IN mytables_tab.first .. mytables_tab.last SAVE EXCEPTIONS
INSERT INTO scott.objects (object_name,owner)
VALUES (mytables_tab(i),'SCOTT');
EXCEPTION
when others then
error_count := SQL%BULK_EXCEPTIONS.COUNT;
for i in 1.. error_count loop
dbms_output.put_line('** Oracle Fehler:' ||
sqlerrm(-SQL%BULK_EXCEPTIONS(I).ERROR_CODE)||':'||mytables_tab(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)||
' # Position:'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
END loop;
COMMIT;
END;
/