dba sql

package version control

Oracle does not offer any kind of version control like CVS or subversion in the database. CVS and subversion could be used in the filesystem, then the code could be deployed with sqlplus.

To quickly compare packages in Test and Production I used :

from (
count(distinct text) over(partition by name,type,line) c
from (
'TEST' env,name,type,line,text
union all
'PROD' env,name,type,line,text
where c>1 ;

where “PROD” is a database link to production database. My security friends will scream to hear I have a database link to production in my test schema, but you are free to do it the other way round :mrgreen:

I also explained TOAD method in how to compare schema a few months ago.

By Laurent Schneider

Oracle Certified Master

5 replies on “package version control”

Pl/Sql Developer -> tool -> Compare User Objects
Very comfortable + no links
I’m sure that Oracle Developer has the same

I actually have a plsql package that extracts all of the newly changed ddl to the file system (via DBMS_METADATA.get_ddl)
I then automatically check these into a cvs repository.

This method does not create useful comments in the cvs repository, but the other developers can use a viewcvs webserver to see historical changes.
And we do not need to worry about what sql program was used to create the object (sql navigator, sqlplus, ps app designer, etc.)

I also generate a web page with list of the diffs between prod and test.
It also has links to diff reports between the two ddl objects.
This allows us to quickly see what will be lost when we overlay test with prod.

It is quick and dirty, but it gives us both the diff report and a basic history of the object.

Leave a Reply

Your email address will not be published.