Oracle PL/SQL Einzel-Tipp ansehen

Bulk Cursor MIT SAVE EXCEPTIONS

Tipp 62

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;
/