Monday, December 20, 2010

How to version control oracle sources?

Impatients go directly to: https://sourceforge.net/projects/orasvn/  :-)

I have Oracle 10gXE locally installed on my Notebook and a 11gR2 on my Desktop. When I started to develop things in Oracle I had to decide how to manage and deploy my code - especially between my Notebook and Desktop. Since I already knew subversion source control I wanted to use SVN. So I started do use SVN with my developer tools like SQL-Developer or TOAD. But I was unhappy with this solution because when I did some offline modifications on my Notebook and I wanted them to deploy to my Desktop I had to start a Developer Tool, check out the sources from SVN and install them. The next issue was that I had to deal with fresh installations and delta deployments. This made it necessary to store alter table and create table statements. And every time I had to start up some developer tool and go around 5 blocks to get what I wanted.


I thought it might be a good Idea to bind SVN directly to Oracle RDBMS. So I can mix development tools and databases. So I have started to build a PL/SQL API interacting via command shell client to SVN. Since I use Oracle XE too I do not want to use any Java. On the highest level a simple SVN.SPOOL_AND_COMMIT_SRC_ALL(, , ); and a SVN.UPDATE(, , ); should sync all the DDLs of my two Databases.


In the meantime I have added some features like versioning and deploying table data or c source code needed for extproc libraries. The whole ORASVN project itself is managed by this and uses the most features (including the deployment of c apis). This is still in pre alpha but you should be able to set up a project and version control your Oracle sources.



Quick Start:

1 Create a repository in your SVN

2 Add the repository to your Oracle project by issuing SVN.NEW_PROJECT NOTE! You cannot use subdirectories so pass as Url parameter your location of choice like http://xy/z/trunk/yourProject/src/rdbms/

3 Add the Oracle Source to your Project SVN.ADD_SRC. Optional you can use the all_objects_v view to migrate existing sources by calling SVN.ADD_SRC in a for-loop. You can also use the svn_sources_v view to see registered sources (Note: one source can be registered in several projects).

4 Commit changes in your Oracle objects by SVN.SPOOL_AND_COMMIT_SRC or do this for all your Sources registered with your project -> SVN.SPOOL_AND_COMMIT_SRC_ALL

5 Sync your databases by using SVN.UPDATE (tables will be altered if changed)

6 To deploy your project you can generate an install.sql file with SVN.GEN_INSATLL_SQL





Requirements:

Windows users have to do change the commands table data file and change the shell commands to dos equivalents. They also have to use the whole path to gcc and svn enclosed in “”.

You need to have gcc installed (windows users use mingw)

 
You need to create the following directories on your Oracle-DB-Host OS (mkdir) and in the Database itself (create directory as ;). You may need root rights and you need the Oracle SYS password for sure. Linux/Unix Useres do not forget to chown the directories to oracle:dba.

SVN_SPOOL /var/spool/orasvn/cmd

SVN_PROJECTS /var/spool/orasvn/projects

Locate the File libqsmashr.so or .dll on Windows and add the directory path to your database:
ORACLE_LIB /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/

Locate the File oci.h and add the directory path to your database:
OCI_H /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/rdbms/public/


Installation:
1 Change into the directory containing install.sql
2 Log in with sys as sysdba and issue the install.sql

 
Uninstall:
1 Drop the schema ORASVN
2 Remove the directories created previously