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;
/
-------------------------------------------------------------------------------------
-- 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 ;
/
-------------------------------------------------------------------------------------
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;
/
-------------------------------------------------------------------------------------
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
