PLSQL bilinmesi gerekenler-6

12-PLSQL source code versioning:

ilk önce history bilgilerimiz tutacak bir tablo yaratalım.


CREATE TABLE source_history
(change_date DATE NOT NULL,
owner VARCHAR2(30) NOT NULL,
name VARCHAR2(30) NOT NULL,
type VARCHAR2(20),
line NUMBER NOT NULL,
text VARCHAR2(4000));


Daha sonra


CREATE OR REPLACE trigger source_history
AFTER CREATE ON DATABASE
BEGIN
INSERT INTO source_history
SELECT SYSDATE, owner, name, type, line, text
FROM dba_source
WHERE owner = ORA_DICT_OBJ_OWNER
AND name = ORA_DICT_OBJ_NAME
AND type = ORA_DICT_OBJ_TYPE;
END source_history;
/



COLUMN owner FORMAT a12
COLUMN name FORMAT a11
COLUMN line FORMAT 9999
COLUMN text FORMAT a60 WORD_WRAPPED
SELECT change_date, owner, name, type, line, text
FROM source_history
WHERE name = 'PIN_OBJECTS'
order by change_date, owner, name, type, line;

historymiz asağıdaki şekilde oluşacaktır.


CHANGE_DA OWNER NAME TYPE LINE TEXT
--------- ------------ ----------- ---------- ----- ------------------------------------------------------------
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 1 PROCEDURE pin_objects
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 2 (p_pin_flag_txt IN VARCHAR2 := 'P') IS
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 3 -- The p_pin_flag_txt is either 'P' for pin
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 4 -- or 'U' for unpin.
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 5 CURSOR cur_pin_objects IS
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 6 SELECT owner '.' owner,
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 7 object
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 8 FROM objects_to_pin
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 9 ORDER BY owner, object;
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 10 BEGIN
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 11 FOR cur_pin_objects_rec IN cur_pin_objects LOOP
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 12 IF p_pin_flag_txt = 'U' THEN
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 13 DBMS_SHARED_POOL.UNKEEP(cur_pin_objects_rec.owner
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 14 cur_pin_objects_rec.object, 'P');
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 15 ELSE
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 16 DBMS_SHARED_POOL.KEEP(cur_pin_objects_rec.owner
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 17 cur_pin_objects_rec.object, 'P');
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 18 END IF;
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 19 END LOOP;
27-JAN-07 TRIGGER_TEST PIN_OBJECTS PROCEDURE 20 END pin_objects;