Pages

Sunday 24 February 2013

How to deploy the database changes



We need to deploy changes in the database either for a full version release or a bug fix.

I had seen in many projects we didn't provide much attention to this step & thinking database a just container for the data. But if pay attention to this from the start of development and test our scripts in a testing environment we can avoid lot of implementation issues in the production.

When you are planning to implement changes think following points and judge where your implementation Strategy stands.

1. What efforts require to run the database changes
Ideal Scenario It should run from a mouse click in window’s environment or just calling a single file In UNIX or Linux operating system.

2. Are your scripts providing logs for errors and messages for success or failure?
Scripts must provide the logs for each and every activity..

3 Is your scripts are robust.
How many time you run your scripts they should not fail.

4.If you face errors, during the implementation how easily you can track them and fix them.
Scripts with logs can make this process easy to track any issues.

To explain the process here we are creating the following objects in SCOTT schema.
TABLEX
one Primary key CONSTRAINTX
one sequence SEQ_X

Create all this objects in separate files so that we can debug them easily, in case of any error.

Let’s see how to create a robust script for table creation.

Check first table  exist in the database from user_tables data dictionary view if no then create the table else give the message
.-------------------------------------------------------------------------------------
Prompt Creating table ...
-- Create table
DECLARE
       fCOUNT INTEGER;
       fString Varchar2(
1000):=NULL;
BEGIN
       SELECT COUNT(*) INTO FCOUNT FROM USER_TABLES
                     WHERE TABLE_NAME=
'TABLEX';
       IF FCOUNT =
0        THEN
              fString:=
'create table TABLEX (
                      col1  VARCHAR2(5) not null,
                     col2    NUMBER(10) not null )'
;
              EXECUTE IMMEDIATE fString;
              DBMS_OUTPUT.PUT_LINE(
'TABLEX CREATED SUCCESSFULLY');
       ELSE
              DBMS_OUTPUT.PUT_LINE(
'TABLEX ALREADY EXISTS');
       END IF;
END;
/
-------------------------------------------------------------------------------------
Save this text as table.sql
-------------------------------------------------------------------------------------
Example how to create a Sequence .
Check first sequence exist in the database from user_sequences data dictionary view if no then create the sequence else give the message.
-------------------------------------------------------------------------------------
declare
       fcount number;
  fstring varchar2(
1000);
begin
           select count(*) into fcount from user_sequences where sequence_name=
'SEQ_X';
              if fcount =
0 then
   
    fstring:=
'CREATE SEQUENCE SEQ_X
                           MINVALUE 1 MAXVALUE 99999
                           INCREMENT BY 1 START WITH 1
                           NOCACHE NOORDER
                           NOCYCLE'
;
   
                     execute immediate fstring;

                     dbms_output.put_line (
'Sequence SEQ_X created successfully');
              else
                     dbms_output.put_line (
'Sequence SEQ_X allready exists');
              end if;
end ;
/
-------------------------------------------------------------------------------------
Save this text as seq.sql
-------------------------------------------------------------------------------------
Example script to create a Primary Key
Check first constraint  exist on the table from user_constraints data dictionary view if no then create the Constraint  else give the message.
-------------------------------------------------------------------------------------
DECLARE
    fCount Integer;
    fString varchar2(
250):=NULL;
BEGIN
              select COUNT(*) INTO FCOUNT  from user_constraints
              where table_name=
'TABLEX' and constraint_type='P'
    and constraint_name=
'CONSTRAINTX';
       IF FCOUNT =
0        THEN
              fString:=
'alter table TABLEX add constraint CONSTRAINTX primary key (col1)';
              EXECUTE IMMEDIATE fString;
              DBMS_OUTPUT.PUT_LINE(
'PRIMARY Key CONSTRAINT CONSTRAINTX ON TABLE TABLEX CREATED SUCCESSFULLY');
       ELSE
              DBMS_OUTPUT.PUT_LINE(
'PRIMARY Key CONSTRAINT CONSTRAINTX ON TABLE TABLEX allready EXISTS');
       END IF;
End
/
-------------------------------------------------------------------------------------
Save this text as PK.sql
-------------------------------------------------------------------------------------
Now to create the one file which call all this files .Set session settings accordingly using SQL*PLUS commands. spool is create the logs.
-------------------------------------------------------------------------------------
Set echo off
set autoprint off
set heading off
set feedback off
Set Define off
set serveroutput on SIZE 10000
SPOOL RUN_ME.LOG
@table.sql
@pk.sql
@seq.sql
PROMPT Task completed successfully
Set echo oN
set autoprint oN
set heading oN
set feedback oN
Set Define oN
set serveroutput oFF
SPOOL OFF

Exit  
-------------------------------------------------------------------------------------
Save this text as RUN_ME.SQL
-------------------------------------------------------------------------------------
How to create the batch file to run this RUN_ME.sql from window’s environment with one click
We are prompting the user are you sure you want to execute the scripts if yes then check for run_me.log file exist if yes delete it then open sqlplus in silent mode and connect to user and
Run the run_me.sql file. And for checking the logs open run_me.log file.
-------------------------------------------------------------------------------------
@echo off
cls
set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?
if '%choice%'=='y' goto begin
goto end

:begin
if exist Run_ME.log  then del RUN_ME.log
@echo on
sqlplus -S scott/tiger@test @run_me.sql
@notepad run_me.log
:end

Exit
-------------------------------------------------------------------------------------
Save this file as execute_me.bat And make sure file extension is a batch file.
-------------------------------------------------------------------------------------
And after clicking on execute_me.bat check the logs from RUN_ME.log file

Sample of logs 

Creating table ...
TABLEX ALREADY EXISTS                                                           
PRIMARY Key CONSTRAINT CONSTRAINTX ON TABLE TABLEX allready EXISTS              
SEQUENCE SEQ_X ALLREADY EXIST   



Your Comments & Suggestions are always welcome



3 comments:

  1. Thanks! I'm currently working on a custom database development project and deploying some changes have really been confusing. Thanks for some great advice!

    ReplyDelete
  2. This info is very useful, but i have a doubt...it is processed in sql*plus tool..right.
    my question is...How to create this one in Sql Developer tool.Is it possible ?

    ReplyDelete