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 :


select 
  env,name,type,line,text 
from (
  select 
    env,name,type,line,text,
    count(distinct text) over(partition by name,type,line) c
  from (
    select 
      'TEST' env,name,type,line,text 
    from 
      user_source 
    union all
    select 
      'PROD' env,name,type,line,text 
    from 
      user_source@prod
  )
)
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.

Published by

Laurent Schneider

Oracle Certified Master

5 thoughts on “package version control”

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

  2. 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.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>