Hello all,

Say I have a worst case scenario in my development environment where
our production
server and our version control are out of sync. We know that the
production server code
is complete and accurate, and what needs to be in version control.
Does anyone
have any suggestions for automating the export of the source code,
triggers, tables, functions, and packages
that consist of the applicaiton I'm trying to sync up into version
control?

--Thanks in advance,
CW

Redrobot5050 wrote:
I would highly recommend, even though it is slow and somewhat painful,
using DBMS_METADATA.GET_DDL.

You will find a working demo in Morgan's Library at www.psoug.org.

Use it to reverse engineer everything ... even the tablespaces.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 03.12.2007, Redrobot5050 <redrobot5050 (AT) gmail (DOT) com> wrote:
Hi,

We keep current DDL of PL/SQL code (procedures, functions, packages)
in SVN repository through a small utility that queries USER_SOURCE
and writes PL/SQL code to the current directory (one file per
procedure or package). Changes are then commited to SVN. Of course
it has to be done after every set of changes in a database and
requires some self-discipline.
Production database has to be in sync with current release branch,
test database with a test branch and development database with
trunk.

We do not version DDL of schema although it could be done as Daniel
suggested with DBMS_METADATA.

Of course all DDL that goes to test and production servers is first
recorded in scripts (that we call changesets) and then deployed.

I hope it helps.

--
Wiktor Moskwa

Redrobot5050 wrote:
Oracle tools like Designer are quite
useful when reverse engineering Oracle.
Setting up Designer is quite a task, but it does
a good job in reverse engineering database objects.

Depending on how large the effort is estimated, it
may be a valid choice.

Designer even intergrates with tools like CVS, ClearCase
and VSS
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

On Dec 3, 9:43 am, Redrobot5050 <redrobot5... (AT) gmail (DOT) com> wrote:
I don't know if Daniel's notes cover this but for tracking purposes, I
have found adding a WHOAMI() function to every package to be useful.
That way rather than trying to compare test files, you query the
function and get a version control identifier like

SELECT MTP_package.WHOAMI() FROM DUAL;

returns something like this:
$Id: packagename 1.15 2007/11/12 15:13:12 ejp Exp ejp $
(if you happen to use RCS)

You can have it return a string with just the package version info, or
version info for all the components. It is totally up to you.

HTH,
Ed

Ed Prochak wrote:
My notes DID'T ... past tense ... Thanks.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org