Keeping History of PL/SQL code changes

-- Create a Table for logging the modifications
CREATE TABLE source_hist 
    AS SELECT SYSDATE change_date ,user_source.* 
        FROM user_source WHERE 1=2;

--Trigger for tracking the changes
CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON scott.schema -- Change SCOTT to your schema name
DECLARE 
                BEGIN
                    IF DICTIONARY_OBJ_TYPE
  IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE') THEN
                        INSERT INTO source_hist
                                SELECT sysdate,user_source.*
                            FROM user_source
                                WHERE TYPE=DICTIONARY_OBJ_TYPE
                                AND NAME=DICTIONARY_OBJ_NAME;
                    END IF;
                EXCEPTION
                WHEN OTHERS THEN 
                    raise_application_error(-20000,SQLERRM);
                END;

PINNING THE OBJECTS IN THE SHARED POOL

Shared Pool is a part of SGA which contains p-code of compiled subprograms.
First time a stored subprograms is called , p-code is loaded from the disk to the shared pool.
Once the object is no longer referenced, it is free to be aged out from the shared pool.

DBMS_SHARED_POOL package allows us to pin objects in the shared pool. When an object is pinned,
it will never be aged out until we request it.

* Pinning the objects can improve performance,as it takes time to reload a package from disk.
* Helps in minimizing fragmentation of shared pool.

DBMS_SHARED_POOL package has 4 procedures : KEEP
                                                                         UNKEEP
                                                                         SIZES
                                                                         ABORTED_REQUEST_THRESHOLD

KEEP :- This helps in pinning the objects in the shared pool
             PROCEDURE KEEP(name VARCHAR2,
                                      flag CHAR  DEFAULT 'P');
                                      name : Name of the object
                                      flag    : Determines the type of the object

UNKEEP :- If the object is pinned in the shared pool, only possible way to remove the object is to make use of procedure UNKEEP.
                PROCEDURE UNKEEP(name VARCHAR2,
                                              flag CHAR DEFAULT 'P');

SIZES :- This procedure will echo the contents of the shared pool to the screen.
            PROCEDURE SIZES(minsize NUMBER);
                                       minsize :object with a size greater that minsize will be returned. 

ABORTED_REQUEST_THRESHOLD :- When the database determines that there is not enough memory in the shared pool,
it will begin aging objects out until there is enough memory. If objects are aged out, this can have a performance impact on other
database sessions.

ABORTED_REQUEST_THRESHOLD can be used for remedy.
                                   PROCEDURE ABORTED_REQUEST_THRESHOLD(threshold_size NUMBER);

Once the procedure is called, Oracle will not start aging objects from the pool unless atleast threshold_size is needed.

*Terminologies/Words  marked in Blocked red color requires certain references for understanding.

How can you Track the modifications done to DB objects

SELECT object_name AS "Object Name",
object_type AS "Object Type",
status AS "Status",
created AS "Created",
last_ddl_time AS "Last Modified"
FROM user_objects
WHERE last_ddl_time >= :p_check_date


The above query will retrieve all the object(s) modified on or after the date provided (p_check_date)

Concept of Polymorphism implemented with 'NULL'

In PL/SQL Syntax for basic IF-THEN-ELSE is as follows

IF(expression) THEN
Statement 1;
ELSE
local_variable:= Rvalue;
END IF;

Cosidering the above syntax to appreciate the concept of Polymorphism implemented through "NULL"

NULL AS STATEMENT
--------------------------
IF(condition is true) THEN
NULL; -- Null acts as a Statement
ELSE
execute this statement;
END IF;


NULL AS RVALUE
----------------------
IF(condition is true) THEN
local_variable := NULL; -- Null acts as a Rvalue
ELSE
execute this statement;
END IF;

The following example is only to appreciate the concept of Polymorphism

NULL AS AN EXPRESSION
-------------------------------
BEGIN
IF(NULL) THEN -- NULL acts as an expression
DBMS_OUTPUT.PUT_LINE('I am Null');
ELSE
DBMS_OUTPUT.PUT_LINE('I am not Null');
END IF;
END;

If the above autonomous procedure is executed it will always provide an o/p : I am not Null

Query to Retrieve 'n' th row from a table

SELECT field_name1,field_name2

FROM table_name

WHERE ROWID = (SELECT ROWID

FROM table_name

WHERE ROWNUM <= :nthrow

MINUS

SELECT ROWID

FROM table_name

WHERE ROWNUM < :nthrow)

The above shown query is one of the way to retrieve the 'n' th row from a table. The same query can be modified in order to provide the rows provided with upper limit and lower limit. say, rows from 5-10.