I would like to ask for advise to anybody doing team base PL/SQL development using SQL Developer. We've currently writing our stored procedures as standalone, and we're trying to move them inside a package( to make it modular ).
Our concern though since many developers are going modifying the stored procedures and since package body is laid out as a single view of all the codes there's a great chance that a stored procedure will get overwritten by the devs; This is not a problem with standalone storedproc.
Any advise how you're able to work around(work with) this?
SQL Developer v184.108.40.206 (Build Main-07.42)
Oracle 11g r2
Windows 7 64b
If you aren't using source control for your PL/SQL code, this is a good time to start. SQL Developer supports several source control tools, but the most popular one is Apache Subversion. You'll need a shared Subversion repository on one of your servers - the software for setting up a Subversion repository is free and open source at http://subversion.apache.org/.
We consider PL/SQL stored procedures in the database to be object code. The source code is in the CREATE OR REPLACE scripts in our Subversion repository. I would recommend making the PACKAGE a separate file from the PACKAGE BODY. The package specification gets changed much less frequently, and also provides a fixed interface.
When one of your developers wants to make changes to the package body, he/she will check out a working copy from the Subversion repository, or update an existing working copy. Then make the changes in SQL Developer. We like our developers to have their own copies of the database (doesn't have to be the whole database - just the schemas affected will usually do) to do unit testing without treading on other developer's work.
Commit your changes early and often - if you do this, you stand a better chance of avoiding conflicts with other developers. But sooner or later, you are going to try to commit a change and another developer will have committed a change in front of you. When this happens, subversion will offer you a chance to merge your code. You can show the differences between your version and the committed version, and choose which lines from each will be in your final version which will be committed. Do a web search for "apache subversion sql developer" and you will see some articles, tutorials, and even some videos on this.
Finally, I really like PL/SQL Packages and believe that you are doing the right thing in merging stand-alone procedures into packages. But group them logically, and don't try to put everything in a single package. You may find when you do this that the new packages will group into only three or fewer developers working on a single package