version control

[cappmgr]

Our version control does not seem correct to me. We are contractors that build applications for a company. The company has their own dbas and they run the dml scripts that we ask them to. We keep our scripts in VSS and when we do a migration from dev to qa and also from qa to prod, the dba on our team builds scripts from VSS using a tool called embarcadero and toad. The schema is rebuilt every time excluding tables unless they need to be altered. This does not make sense to me. Also the dml scripts have not run correctly quite a few times and this is not good. Their dba has asked many times that the scripts be tested using sql/plus. I believe he is 100% correct. This is an email from the companies dba after our last migration:

'I thought there was going to be some sort of cleanup of the scripts - the 1st script does not work in SQLPlus or DBArtisan - I had to install TOAD to get the script to load. I would appreciate it if the scripts were tested with SQLPlus to see if they work!'

The prior gig I was only used sq/plus and and cvs(open source version control software) and had no dba on our team. We built the scripts and gave them to the company's dba and asked that they please be run them. The scripts did not alter any objects unless they were changed or a dependency changed.

In short I am looking for a reference (links) suggestions on database object version control.

I am in charge of the migration of the code but have no say in the db object migration. I am also in charge of the application and would like it done right.
Thank you,
Marty

 

[Thargy]

Marty,

I've been wrestling with almost exactly this problem for a while. Although there must be many takes on this old chestnut, I was surprised to find the following on ask tom

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:51599874642303

Imagine my surprise to read that the illustrious Mr Kyte approves of manual scripted changes. Note that the ask tom page has a reference to something called "daversy", which looks like it might be of interest.

Keep me posted if you unearth anything useful.

Regards

Tharg

Grinding away at things Oracular

 

[sem]

I think that sql*plus scripts is a standard way for propagating changes as this is a standard tool shipped with database. You may develop using any tool but why should your customer pay for or learn anything else? You use toab and embarcadero, I use say pl/sql developer, my friend developed his own tool, better than any existed before and the only issue is money for promotion

Again: you may continue using your native language (sure, the best one!) but you mustn't expect everybody to know it.

As for your question, you may use Oracle Designer to keep your systems (structures, not data) in synch. It generates a delta between current schema in database and one stored in its repository. It's not ideal, so you may also look for other change management tools through the Net.

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

Marty, Dima,

I forgot one significant issue in my first post.

As Dima said, you should use SQL Plus because it's standard. I would go further than that. Since SQL Plus is the lowest common denominator, across all Oracle versions, and is the only sure-fire environment (notwithstanding the sheer excellence of TOAD) I only accept bug and fault reports, if accompanied by SQL Plus generated supporting evidence.

If it works in TOAD but not SQL Plus, tough, I don't want to know. I'll make it work in Plus, and the rest of the world can do what it likes. You'd be surprised at the number of 'faults' that disappear when you adopt this approach.

Regards

Tharg

Grinding away at things Oracular

 

[cappmgr]

Here's a thought, do not use VSS. We have a DEV QA and Prod.
I figured I could use a db_link and get a delta and also see the differences between objects.
Right now I am stuck on what views to use.
I have been trying different ones and have not had much success.

SQL> select text from all_source 
  2  where type = 'PACKAGE' 
  3  and name = 'PKG_ARS'
  4  and (text <> (select distinct(text) from all_source@ARS_QA_ISTCMGR 
  5      where type = 'PACKAGE' 
  6      and name = 'PKG_ARS'))
  7  /
and (text <> (select distinct(text) from all_source@ARS_QA_ISTCMGR
              *
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

Are there views that will give me a delta? Are there views I can use to show me the differences?
Any suggestions greatly appreciated.
Marty

 

[sem]

I don't think that comparing line-by-line (this way data is stored in all_source!) is a good way to get a difference. But if you want...

(SELECT line, text
  FROM all_source
 WHERE TYPE = 'PACKAGE'
   AND NAME = 'PKG_ARS'
MINUS
SELECT line, text
  FROM all_source@ARS_QA_ISTCMGR
 WHERE TYPE = 'PACKAGE'
   AND NAME = 'PKG_ARS')
UNION ALL
(SELECT line, text
  FROM all_source@ARS_QA_ISTCMGR
 WHERE TYPE = 'PACKAGE'
   AND NAME = 'PKG_ARS'
MINUS
SELECT line, text
  FROM all_source
 WHERE TYPE = 'PACKAGE'
   AND NAME = 'PKG_ARS')[code]

The above provides a difference, but I doubt it's useful for anything else than knowing that it exists.

Regards, Dima
[URL unfurl="true"]http://seminihin.narod.ru[/URL]

 

[cappmgr]

Sem,
Thank you very much. I have been gone a while and am back. I found a script at

http://www.quest-pipelines.com/pipelines/dba/tips02.htm

which uses checksum.

For each object in the database, the script computes a checksum using the hash function provided by Oracle with the DBMS_UTILITY package. ANY modification to the database structure will result in a different value for the checksum. This checksum allows easy comparison of the structure of a target database with a given baseline. Moreover, the computation of partial checksums facilitates the investigation of structural (DDL) changes among the various object types.

Is there a way to use this and a dblink to list the names of the objects that are different?
Marty