Backup of Source Code (effective versioning without SVN)

PL SQL versioning tutorial without SVN

Have been busy lately with MSAccess coding and had no time to write something useful....
But now I manage to take some time and write ....

I had some bad experience with "loosing source code"  day after I wrote it...
Scenario: You write the code test it and Compile package
Someone else has that package opened and hit compile on it....
Next day you open your source and you see that there are no changes that you made day before...

So go with coding all over again.... :)

I decide to make some table and Trigger that will automatically execute and save my package before someone compiles it....

So let's make table where to store our changes...

CREATE TABLE "MYBASE"."BACKUP_SOURCE"
   (    "CHANGE_DATE" TIMESTAMP (6),
    "OWNER_OF" VARCHAR2(4000 BYTE),
    "PKG_NAME" VARCHAR2(4000 BYTE),
    "PKG_TYPE" VARCHAR2(4000 BYTE),
    "CONTENT" CLOB,
    "CHANGED_BY" VARCHAR2(100 BYTE)
     );

In this table we have change date, name of shema (owner of package), name of package, package type, full content backup and username of person who compiled last...

Let's write Trigger that will do some BACKING-UP....
We will make AFTER CREATE trigger...

If you cannot create trigger read this (or just curious).... otherwise skip on trigger creation...

Before a trigger can be created, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.
To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER system privilege.
To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.
In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.
If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles.
What is after trigger....

AFTER
Specify AFTER to cause the database to fire the trigger after executing the triggering event. For row triggers, the trigger is fired after each affected row is changed. Restrictions on AFTER Triggers AFTER triggers are subject to the following restrictions: You cannot specify an AFTER trigger on a view or an object view. You cannot write either the :OLD or the :NEW value.
CREATE 
Specify CREATE to fire the trigger whenever a CREATE statement adds a new database object to the data dictionary.
Trigger CODE

create or replace trigger mybase.trg_backup_source
AFTER CREATE ON mybase.SCHEMA
declare
l_owner varchar2(1000) := ' ';
l_name varchar2(1000) := ' ';
l_type varchar2(1000) := ' ';
l_clob clob;
l_user varchar2(100) :=' ';

BEGIN
IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
       'PACKAGE', 'PACKAGE BODY',
        'TYPE', 'TYPE BODY')
THEN
for c1 in
    (SELECT sysdate, all_source.* FROM ALL_SOURCE
                    where type = ORA_DICT_OBJ_TYPE
                    and name = ORA_DICT_OBJ_NAME
    )
 loop
        l_clob := l_clob||' '||c1.text;

end loop;

select
    all_source.owner,
    all_source.name,
    all_source.type
into
    l_owner,
    l_name,
    l_type
from
    all_source
where type = ORA_DICT_OBJ_TYPE
    and name = ora_dict_obj_name
    and rownum <2;
-- get username
begin
    select
       osuser
    into
       l_user 
   from
      v$session
   where
      audsid = userenv('sessionid');

exception
    when no_data_found then
         l_user := 'Unknown';
    when others then
         null;
end;

insert into backup_source values
     (systimestamp,
       l_owner,
       l_name,
       l_type,
       l_clob,
       l_user);

end if;
exception
    when no_data_found then
         null;
when others then
      raise_application_error(-20000,' ERROR IN                TRG_BACKUP_SOURCE_mybase sqlerr:'|| sqlerrm||' NUM:'||SQLcode);
end;

After that just Compile and Enable trigger and you're ready to go ....
do some compiling of some package and then select from table

SELECT * FROM BACKUP_SOURCE ORDER BY CHANGE_DATE DESC;

Every time you hit Compile it will make backup...


Enjoy
P.S.

will add how to delete source every 10 days into code when I get time.....


Happy coding

Read More

PLSQL backup time...

PL SQL package backup tutorial

I started this project because i needed program that will easily back up my files from developer without clicking on package and exporting it to save my work...
Second i wrote it because there is Bug in SQL DEVELOPER that causes lines to be messed up  when you have large code...
Example:

You have large code and export it with right click on package name and select save specs and body

Real code is written like this...

1 line - BEGIN
2 LINE - SELECT RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))
3 line - INTO L_INPUTNAME
4 line - FROM V$INSTANCE ;
5 line - EXCEPTION WHEN OTHERS THEN
6 LINE - NULL;
7 line - END;

but sometimes export does the weird thing and saves it messed up
switching the line order....

1 line - BEGIN
2 line - SELECT RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))
4 line - FROM V$INSTANCE ;
3 line - INTO L_INPUTNAME
5 line - EXCEPTION WHEN OTHERS THEN
6 line - NULL;
7 line - END;

I googled this program somewhere on the web two years ago and manage to rewrite it for my needs...
So without any chit chat let's see the code

CREATE OR REPLACE
PROCEDURE BACKUP_OF_PL_FILES
    (I_TIPE_OF_OBJECT    VARCHAR2
    ,I_NAME_OF_OBJECT    VARCHAR2 DEFAULT '%')
IS
    PL_OUTPUT    UTL_FILE.FILE_TYPE;
    L_READ UTL_FILE.FILE_TYPE;
    L_BUFFER    VARCHAR2(4000);
    G_SESSONID    VARCHAR2(80);
    L_LINE        VARCHAR2(80);
    L_COUNTER        NUMBER(30) :=1 ;
    L_SAVE VARCHAR2(32767);
    L_INPUTNAME    VARCHAR2(20);
    L_ERRNUM    NUMBER := 0;
    L_DIRNAME VARCHAR2(100) := ' ';
BEGIN
  
   G_SESSONID := USERENV('sessionid');
   L_DIRNAME := 'BACKUP_PL/'||I_NAME_OF_OBJECT||'/'||TO_CHAR(TO_DATE(SYSDATE,'dd.mm.yy'),'yyyymmdd')||'/';
   -----------------------------------------------------------------------------
  
    BEGIN
        SELECT    RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))
        INTO    L_INPUTNAME
        FROM    V$INSTANCE ;
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;

    IF I_NAME_OF_OBJECT='%' THEN
        G_SESSONID    := 'BAK_'||INITCAP(I_TIPE_OF_OBJECT)||'_'||L_INPUTNAME||'_'||TO_CHAR(SYSDATE, 'DDMMYY_HH24MI') || '.pls';
    ELSE
        G_SESSONID    := 'BAK_'||INITCAP(I_NAME_OF_OBJECT)||'_'||L_INPUTNAME||'_'||TO_CHAR(SYSDATE, 'DDMMYY_HH24MI') || '.pls';
    END IF;   
  
   PL_OUTPUT := UTL_FILE.FOPEN('c:\BACKUP', G_SESSONID,'A',4000);      
   UTL_FILE.PUT_LINE(PL_OUTPUT, 'c:\BACKUP' || L_DIRNAME || G_SESSONID);

    FOR RS_TYPE IN (SELECT    DISTINCT TYPE, NAME
            FROM    USER_SOURCE
            WHERE    TYPE LIKE UPPER(I_TIPE_OF_OBJECT)||'%'
            AND    NAME LIKE UPPER(I_NAME_OF_OBJECT)
            ORDER BY 2,1 )
    LOOP
        FOR RS_NAME IN (SELECT    TEXT
                FROM    USER_SOURCE
                WHERE    TYPE = RS_TYPE.TYPE
                AND    NAME = RS_TYPE.NAME )
        LOOP
            BEGIN
                IF (L_COUNTER = 1 OR UPPER(RS_NAME.TEXT) LIKE '%PACKAGE BODY%') THEN
                    L_BUFFER := 'CREATE OR REPLACE '||RS_NAME.TEXT ;
                ELSE
                    L_BUFFER := RTRIM(RS_NAME.TEXT,' ');
                END IF;
                IF SUBSTR(L_BUFFER,-1,1) = ';' THEN
                    UTL_FILE.PUT_LINE(PL_OUTPUT,RTRIM(L_BUFFER));
                ELSE
                    UTL_FILE.PUT_LINE(PL_OUTPUT,RTRIM(L_BUFFER,SUBSTR(L_BUFFER,-1,1)));
                END IF;
                L_COUNTER := L_COUNTER + 1 ;
           
            EXCEPTION WHEN OTHERS THEN
                L_ERRNUM :=1 ;
                DBMS_OUTPUT.PUT_LINE('error 2nd loop...'||SQLERRM);
            END ;
        END LOOP;
        L_COUNTER := 1;
        UTL_FILE.PUT_LINE(PL_OUTPUT,'/');              
        IF RS_TYPE.TYPE NOT LIKE '%PACKAGE%' OR RS_TYPE.TYPE LIKE '%PACKAGE BODY%' THEN
          UTL_FILE.PUT_LINE(PL_OUTPUT,L_LINE);   
        END IF;
    END LOOP;
    UTL_FILE.FCLOSE(PL_OUTPUT);
   L_READ := UTL_FILE.FOPEN('PL_OUT', G_SESSONID,'A',4000);
   ----------------------
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error... '||SQLCODE);
    UTL_FILE.FCLOSE(PL_OUTPUT);
END BACKUP_OF_PL_FILES;

 
Basically this procedure is searching database same as export and write package in your local directory c:\BACKUP

Calling is easy. All you have to do is call it from anonymous block with specification(package,procedure,function...) and name of the Package/Procedure... like this

BEGIN
   BACKUP_OF_PL_FILES('package','MY_PACKAGE_NAME_PKG');
   BACKUP_OF_PL_FILES('package','MY_PACKAGE_NAME_PKG');
   BACKUP_OF_PL_FILES('procedure','MY_PROCEDURE_NAME);
   BACKUP_OF_PL_FILES('function','MY_FUNCTION_NAME');
END;

Read More