RE: RE: Scripting for change with Team Coding

> It is our
> responsibility to manage the change in Dev and then schedule a 
> deployment along with an appropriate script to execute in QA or 
> Production via the DBA team. What I need to be able to do is find all 
> changes within the Dev database since a given version / revision in 
> the repository and create a script to give the DBA's for execution

My solution to this is that I build my implementation script as I am doing
development. Any DDL changes I do get put into this implementation script.
If I back changes out, I back them out of the implementation script. I use
the project manager to show the files I am working on, broken out by schema,
and one of those files is the implementation script. I also have an
implementation plan that references what I (or someone else) has to do in
order to implement my changes, and at least one of those items is to run the
implementation script.

At the top of my script I usually always contains the following SQL at the
top:-

>
SET TERMOUT OFF
COLUMN FNAME NEW_VALUE _SPOOL_FILE

SELECT    'ttpa_impl_&_CONNECT_IDENTIFIER'
       || '_'
       || TO_CHAR (SYSDATE, 'yyyymmdd_hh24miss')
       || '.log'
          FNAME
  FROM DUAL;

SET TERMOUT ON;
set verify off
set echo on
spool &_SPOOL_FILE
prompt $Header: tpc_er_ttpa_impl.sql, 4, 8/23/2010 9:22:08 AM, Michael
McAllister$
>

The first part of the SELECT statement (in this case 'ttpa_impl') changes
from implementation script to implementation script. Running this at the
start of my script makes sure that my instance name and the date/time of the
script being run becomes a part of the log filename that SQL*Plus (or TOAD)
outputs to. I also make sure I have $Header$ at the top of the script
(explanded in the example above) so that I can see what version of my
implementation script was run. The log file gets checked into our version
control system so that we have a record of what was run where, and the
results of that implementation.

Executing the implementation plan, and running the implementation script in
the test instance becomes a test of my implementation. If there are any
problems with it, we restore the DB back to the point prior to running the
implementation, fix the problems with the implementation in dev, and then
run in test again. The implementation CANNOT be run in production until it
has run successfully in test.

Regards,

Mike McAllister 
Principal Systems Engineer
Decypher
DSN: 		487-3751
Commercial: (210) 652-3751
Cell: 	(512) 423-7447
Email: 	[email protected]


-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Norman
Dunbar
Sent: Monday, January 09, 2012 10:25 AM
To: [email protected]
Subject: Re: [toad] RE: Scripting for change with Team Coding

  

Hi Timothy,

> When you say "DBA Option", what are you referring to?
Help->About. That lists all the add-ons that your license for Toad 
covers. The DBA option has the ability in it to create a script from the 
output of Schama Compare. Without the DBA option all you can do is look 
at the differences and try to manually resolve them.

Mine says "Add-ons: DB Admin Module, SQL Optimizer".

> We do not have the ability to run anything against QA or Production, 
only dev.
That makes sense to be honest. But it will make running the schema 
compare difficult if you are not able to access production. :-(

> It is our
> responsibility to manage the change in Dev and then schedule a
> deployment along with an appropriate script to execute in QA or
> Production via the DBA team. What I need to be able to do is find all
> changes within the Dev database since a given version / revision in the
> repository and create a script to give the DBA's for execution to QA and
> later to Production.
I understand. You mentioned that you use Subversion as well as team 
Coding (or are you using Subversion as your Legacy team Coding system?).

I would think about looking at your Subversion logs to see what changed 
"recently" and maybe, the "svn diff" command will enable you to do some 
scripting work?

It's a bit difficult to find out exactly what has changed between fixes 
if you don't have a schema still at the base level to compare it with.

-- 
Cheers,
Norm. [TeamT]