Home > dba, sql > package version control

package version control

March 24th, 2009 Leave a comment Go to comments

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.

Tags:
  1. MironM
    March 24th, 2009 at 15:43 | #1

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

  2. March 24th, 2009 at 18:52 | #2

    I guess having a dblink to test database from PROD will be as bad, if not worse thing than having a dblink from test to PROD.

  3. Rich Langer
    April 21st, 2009 at 21:01 | #3

    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.

  4. April 21st, 2009 at 21:13 | #4

    Rich, this sounds like you have build your own cvs for PL/SQL!

    Kudos

  1. April 20th, 2012 at 22:59 | #1
*