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');