Note: I have transferred this blog from my old blog site: http://dcarapic.blogspot.com/ so I am reposting most of the stuff from there
If you have ever created applications that use a database to store data you must have run into a problem how to do updates on the database once the application goes into production and you need to update the production database structure and/or data with new functionality or bug fixes made on your development database. Some database tools (Oracle SQL developer) help you in this by having an integration with SVN. This means that you have a history of various database versions that you have been working on, but it takes some work to use that data to create update scripts which you can apply on your production system.
If you are using Oracle as your database solution then this problem becomes much easier because Oracle supports DDL triggers. DDL triggers enable you to do some work once a DDL command is executed. Combining this trigger with some logging can enable you to quickly produce database update scripts. Note: Everything done on the "SYS" schema is done as a SYS user. Everything else is done by 'normal' db users.To start with, we are going to need a database table to log our changes:
CREATE TABLE "SYS"."DDL_LOG" (
"ID" NUMBER(38,0) DEFAULT 0 NOT NULL ENABLE,
"USER_NAME" VARCHAR2(30 BYTE),
"DDL_DATE" DATE,
"DDL_TYPE" VARCHAR2(30 BYTE),
"OBJECT_TYPE" VARCHAR2(18 BYTE),
"OWNER" VARCHAR2(30 BYTE),
"OBJECT_NAME" VARCHAR2(128 BYTE),
"SQL_TEXT" CLOB,
"EXECUTE_TEXT" CLOB
);
Here is the explanation of the columns:
- ID - id of the log (I'll explain later why we need it)
- USER_NAME - name of the Oracle user that made the DDL command
- DDL_DATE - date and time of the modification
- DDL_TYPE - type of modification (ALTER, CREATE etc.)
- OBJECT_TYPE - type of object on which the modification was made (TABLE, TRIGGER etc.)
- OWNER - schema owner
- OBJECT_NAME - name of the object that was modified (created, altered dropped etc.)
- SQL_TEXT- actual command that was executed
- UPDATE_TEXT -I'll explain this later
Here is the actual trigger that will do the logging:
CREATE OR REPLACE TRIGGER "SYS"."DDL_TRIG"
AFTER DDL
ON DATABASE
DECLARE
v_date varchar2(100);
v_id number;
v_stmt clob;
BEGIN
IF ora_dict_obj_owner = 'DEVELOPMENT' THEN
v_date := TO_CHAR(sysdate, 'YYYY-MM-DD');
SELECT ddl_log_seq.nextval INTO v_id FROM dual;
v_stmt := REPLACE(last_sql_text(), '''', '''''');
INSERT INTO DDL_LOG (
id,
user_name,
ddl_date,
ddl_type,
object_type,
owner,
object_name,
sql_text,
execute_text
) VALUES (
v_id,
ora_login_user,
SYSDATE,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_dict_obj_name,
last_sql_text(),
'execute_if_not_executed(''' || ora_login_user || ''', ''' || v_date
|| ''', ' || v_id || ', ''' || v_stmt || ''');'
);
END IF;
END;
The trigger is pretty straightforward, it just fills the DDL_LOG table if the DDL commands were made on the DEVELOPMENT schema. What is interesting are the fields ID and EXECUTE_TEXT.
Why do we need ID field? This is because, later on, we need to create some sort of a script from the records in the DDL_LOG table. This script should have all the commands executed in the same order as the developers have executed them on the development database. While we could use DDL_DATE field for this, it is not a good solution when you are using database management tools.
The tools (such as PL/SQL developer or Oracle SQL developer) usually execute DDL commands in a batch so you get a lot of commands within the same millisecond. Ordering by DDL_DATE field does not help you much in those cases, but if you have ID then you can be certain of the order in which the DDL commands were executed.
UPDATE_TEXT field also serves to help us create an update script. This field will be filled with a procedure call ('execute_if_not_executed') that can be executed on the production server. Why would we need such an elaborate system? Why not just take the SQL_TEXT and run it on the production? This is certainly possible, but one of the things that might occur (and does occur) on the production database (and even more so if you have multiple production databases) is that a DDL command or a group of commands never get executed!
You may simply overlook a system because you forgot about it, or the guy responsible for updates got sick and someone else does a bad job out of it. If such an error occurs frequently it would be nice to have a log of updates (DDL commands) that have been made on the database. It would be even nicer if you could automatically check if an update was executed and execute it if necessary. This is exactly what the 'execute_if_not_executed' function does: Checks if we have already executed a command If not then executes it Records that the update has been made so that future calls will not do an update again The update records are kept in a database table for each production database.
CREATE TABLE "PRODUCTION"."SCHEMA_VERSION" (
"VERSION_USER" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"VERSION_DATE" DATE NOT NULL ENABLE,
"VERSION_NUMBER" NUMBER NOT NULL ENABLE,
CONSTRAINT "SCHEMA_VERSION_UK" UNIQUE
("VERSION_USER", "VERSION_DATE", "VERSION_NUMBER") ENABLE
);
Here is the EXECUTE_IF_NOT_EXECUTED procedure.
CREATE OR REPLACE PROCEDURE "PRODUCTION"."EXECUTE_IF_NOT_EXECUTED"
(
p_user in VARCHAR2,
p_date in VARCHAR2,
p_order_no in NUMBER,
p_command in VARCHAR2
)
AUTHID CURRENT_USER
AS
BEGIN
IF EXISTS_VERSION(p_user, p_date, p_order_no) = 0 then
EXECUTE IMMEDIATE p_command;
insert_version(p_user, p_date, p_order_no);
commit;
end if;
END;
The EXECUTE_IF_NOT_EXECUTED procedure is relatively simple. It takes parameters which serve to uniquely identify the update command (user, date and id) and the actual DDL command text. One thing that is specific is that the procedure has AUTHID CURRENT_USER option set. This gives the procedure will execute the DDL commands with the same rights as the user that is executing the procedure. You may remove this option which means that the rights of the user that created the procedure will be used when executing the DDL commands.
This depends on how you have set up your update process. Once you have everything in place, all DDL commands that you are making on developer database will be recorded into the DDL_LOG table. When you want to create an update script just browse the table and copy/paste the update commands into a file, surround them with begin/end; and you are good to go.
Appendix: Several various functions and procedures have also been used but I will not explain them since they are pretty much straightforward. I am just going to list them here.
CREATE OR REPLACE FUNCTION "PRODUCTION"."LAST_SQL_TEXT"
RETURN CLOB IS
v_sql_text ORA_NAME_LIST_T;
v_stmt CLOB;
n INTEGER;
i INTEGER;
BEGIN
n := ORA_SQL_TXT(v_sql_text);
FOR i IN 1..n LOOP
v_stmt := v_stmt || REPLACE(SQL_TEXT(i), chr(0));
END LOOP;
RETURN v_stmt;
END;
CREATE OR REPLACE FUNCTION "PRODUCTION"."EXISTS_VERSION"
(
p_username in VARCHAR2,
p_isoDate in VARCHAR2,
p_versionNum in NUMBER
)
RETURN NUMBER
IS
v_counter NUMBER;
BEGIN
SELECT COUNT(1) INTO v_counter FROM SCHEMA_VERSION
WHERE VERSION_USER = p_username AND
VERSION_DATE = parse_iso_date(p_isoDate) AND
VERSION_NUMBER = p_versionNum;
IF counter = 0 then
RETURN 0;
ELSE
RETURN 1;
END IF;
END;
CREATE OR REPLACE PROCEDURE "PRODUCTION"."INSERT_VERSION"
(
p_username in VARCHAR2,
p_isoDate in VARCHAR2,
p_versionNum in NUMBER
)
IS
BEGIN
INSERT INTO SCHEMA_VERSION (VERSION_USER, VERSION_DATE, VERSION_NUMBER)
values (p_username, parse_iso_date(p_isoDate), p_versionNum);
END;
CREATE OR REPLACE FUNCTION "PRODUCTION"."PARSE_ISO_DATE"
(
p_isoDate in varchar2
)
RETURN DATE
IS
BEGIN
RETURN TO_DATE(p_isoDate, 'YYYY-MM-DD');
END;