SİLİNEN VE OLUŞTURULAN NESNELERİ LİSTELEME

--TABLOLARıMıZı YARATALıM.

--karşilaştırma yapmak için aşağıdaki tabloyu kullanıyoruz.

CREATE TABLE OBJECT_TAKIP
(
OWNER VARCHAR2(30 BYTE),
OBJECT_ID NUMBER,
OBJECT_NAME VARCHAR2(128 BYTE),
OBJECT_TYPE VARCHAR2(19 BYTE)
)


--silinen yada oluşturulan nesneler de burada

CREATE TABLE CRE_OR_DEL_OBJECT
(
OWNER VARCHAR2(30 BYTE),
OBJECT_ID NUMBER,
OBJECT_NAME VARCHAR2(128 BYTE),
OBJECT_TYPE VARCHAR2(19 BYTE),
STATUS VARCHAR2(10 BYTE)
)



CREATE OR REPLACE PROCEDURE object_takip_proc
IS
v_dynsql VARCHAR2 (1000);
v_owner VARCHAR2 (150);
BEGIN
v_owner := '''HR''';
-- Mail ile gönderecegimiz verileri tabloya aktariyorum. Silinenler ve olusturulanlar bu tabloda tutuluyor.
v_dynsql := 'TRUNCATE TABLE CRE_OR_DEL_OBJECT';

EXECUTE IMMEDIATE v_dynsql;

--SILINEN OBJELER
--OBJECT_TAKIP tablosu doldurulduktan sonra silinen objeleri yakaliyor.
v_dynsql :=
'INSERT INTO CRE_OR_DEL_OBJECT
(OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS)
SELECT o.owner, o.object_name, o.object_type, ''SILINDI''
FROM object_takip o LEFT OUTER JOIN all_objects a
ON o.object_type = a.object_type
AND o.object_name = a.object_name
WHERE a.object_name IS NULL
AND o.owner IN (''HR'', ''SCOOT'')
AND o.object_type IN ( ''TABLE'',''PROCEDURE'',''FUNCTION'',''PACKAGE'',''VIEW'')';

-- DBMS_OUTPUT.PUT_LINE (V_DYNSQL);
EXECUTE IMMEDIATE v_dynsql;

--OBJECT_TAKIP tablosu doldurulduktan sonra yeni yaratilan objeleri yakaliyor.
v_dynsql :=
'INSERT INTO CRE_OR_DEL_OBJECT
(OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS)
SELECT A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE, ''OLUSTU''
FROM ALL_OBJECTS A LEFT OUTER JOIN OBJECT_TAKIP O
ON O.OBJECT_TYPE = A.OBJECT_TYPE
AND O.OBJECT_NAME = A.OBJECT_NAME
WHERE O.OBJECT_NAME IS NULL
AND a.owner IN (''HR'', ''SCOOT'')
AND a.object_type IN (''PROCEDURE'', ''PACKAGE'', ''TABLE'', ''VIEW'',''FUNCTION'')';

--YENI YARATILAN TABLOALRI ASAGIDAKI SQL ILE DE BULABILIRIZ.
/* SELECT a.owner, a.object_name, a.object_type, ''OLUSTU''
FROM all_objects a
WHERE a.owner IN (''HR'', ''SCOOT'')
AND a.object_type IN (''TABLE'', ''PROCEDURE'', ''FUNCTION'', ''PACKAGE'')
MINUS
SELECT o.owner, o.object_name, o.object_type, ''OLUSTU''
FROM object_takip o
WHERE o.owner IN (''HR'', ''SCOOT'')
AND o.object_type IN (''TABLE'', ''PROCEDURE'', ''FUNCTION'', ''PACKAGE'')
*/
-- DBMS_OUTPUT.PUT_LINE (V_DYNSQL);
EXECUTE IMMEDIATE v_dynsql;

COMMIT;
--mail atan procedureyi çagiralim.
smtp_att_mail ('CRE_OR_DEL_OBJECT');

--Mail atma islemini tablodan yaptigimiz için gerek kalmadi.
--Burada tabloyu text dosyaya yazan procedureyi cagiriyorum.
-- EXTRACT_TABLE ('CRE_OR_DEL_OBJECT', --tablo adi
-- ',', --ayracimiz
-- 'USERDATA_DB', --directory
-- 'DELETE_AND_CREATE.TXT' --dosya adi
-- );
EXECUTE IMMEDIATE 'TRUNCATE TABLE OBJECT_TAKIP';

INSERT INTO object_takip
(owner, object_name, object_type)
SELECT owner, object_name, object_type
FROM all_objects
WHERE owner IN ('HR', 'SCOOT')
AND object_type IN
('PROCEDURE', 'PACKAGE', 'TABLE', 'VIEW', 'FUNCTION');

COMMIT;
END;
/