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
