Oracle PL/SQL-Tipps

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