PLSQL önemli özellikler

PLSQL de dikkat edilmesi gerekenler.
1- Yazdığımız kodun etkiledikleri(depend) olanları tespit etmek.
SELECT name, type
FROM user_dependencies
WHERE referenced_name = UPPER(‘&object_name’)
AND referenced_type = UPPER(‘&object_type’)
ORDER BY name;
Çıktısı
NAME TYPE
———————- ————-
DBMS_ALERT PACKAGE
DBMS_ALERT PACKAGE BODY
DBMS_OUTPUT PACKAGE
DBMS_OUTPUT PACKAGE BODY
DBMS_SHARED_POOL PACKAGE
DBMS_SHARED_POOL PACKAGE BODY
Daha güzel bir depency örneği
CREATE OR REPLACE PACKAGE dependency_tree AS
— This package will traverse top-down(p_direction_txt = T)
— or bottom-up (p_direction_txt = B)
PROCEDURE find_dep
(p_direction_txt IN VARCHAR2,
p_object_name_txt IN VARCHAR2,
p_owner_txt IN VARCHAR2 :=USER);
PROCEDURE get_dep
(p_direction_txt IN VARCHAR2,
p_direction_msg2 IN VARCHAR2,
p_object_owner_txt IN VARCHAR2,
p_object_name_txt IN VARCHAR2,
p_object_type_txt IN VARCHAR2,
p_index_num IN PLS_INTEGER);
FUNCTION repeat_char
(p_repeat_num IN PLS_INTEGER,
p_repeat_txt IN VARCHAR2 := ‘-‘) RETURN VARCHAR2;
END dependency_tree;
/
Başka bir örnekte ise hazır paketimizi kullanabiliriz.
EXECUTE dependency_tree.find_dep(‘T’,’A’);
———————————————————
START OF DEPENDENCY TREE LISTING (TOP-DOWN)
———————————————————
Dependencies for PROCEDURE PLSQL_USER.A
———————————————————
—> References Object PLSQL_USER.B
—-> References Object PLSQL_USER.C
—–> References Object PLSQL_USER.D
——> References Object PLSQL_USER.E
———————————————————
END OF DEPENDENCY TREE LISTING (TOP-DOWN)
———————————————————
PL/SQL procedure successfully completed.
2-PLSQL compilation. Yazdığımız kodun compile edilmesinin sonuçları:
Standard Yöntem
1. Değiştirmek Stored Program Unit
2. Compile etmek Stored Program Unit
3. USER_OBJECTS for All INVALID objeleri bulalım
4. Execute Dynamic SQL çalıştırıp Build COMPILE Script
All INVALID Objects
5. Execute COMPILE scripti
6. tekrar 3 maddeye dönüp bütün hepsini compile edilmiş görmek
• Daha kolay bir yol varmıdır?
SELECT owner, object_type, object_name, status,
TO_CHAR(created, ‘MM/DD/YY HH24:MI:SS’) created,
TO_CHAR(last_ddl_time, ‘MM/DD/YY HH24:MI:SS’) modified
FROM dba_objects
WHERE object_type IN (‘PROCEDURE’, ‘FUNCTION’, ‘PACKAGE’,
‘PACKAGE BODY’, ‘TRIGGER’)
AND status = ‘INVALID’
AND owner = ‘PLSQL_USER’
ORDER BY owner, object_type, object_name;
OBJECT_TYPE OBJECT_NAME STATUS CREATED MODIFIED
———— ————- ——- —————– —————–
PACKAGE CLOSE INVALID 03/06/07 13:53:20 03/06/07 14:12:53
PACKAGE BODY CLOSE INVALID 03/06/07 13:55:04 03/06/07 14:12:53
PROCEDURE ADJUST_SALARY INVALID 02/13/07 15:22:02 02/15/07 12:21:18
DBMS_UTILITY.COMPILE_SCHEMA (schema VARCHAR2);
DBMS_UTILITY.COMPILE_SCHEMA (‘PLSQL_USER’);