세 번째로 변경된 객체 정보를 저장하는 프로시저인 ch15_make_objects_prc를 만들어 보자.

입력

    CREATE OR REPLACE PROCEDURE ch15_make_objects_prc
    IS
    BEGIN
      INSERT INTO ch15_changed_object (
      object_name, object_type,
      created,     last_ddl_time,
      status,      creation_date )
      SELECT object_name, object_type,
             created,     last_ddl_time,
             status,      SYSDATE
        FROM  USER_OBJECTS a
       WHERE LAST_DDL_TIME BETWEEN SYSDATE - 7
                             AND SYSDATE
         AND NOT EXISTS ( SELECT 1
                            FROM ch15_changed_object b
                           WHERE a.object_name = b.object_name);

      COMMIT;

    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      RAISE_APPLICATION_ERROR(-20002, SQLERRM);
      ROLLBACK;
    END;

결과

    PROCEDURE CH15_MAKE_OBJECTS_PRC이(가) 컴파일되었습니다.