Monday, September 19, 2005
PL/SQL Code Storage: Files vs In-DB Packages
I read this interesting exchange on Steven Feuerstein's Q&A:
http://htmldb.oracle.com/pls/otn/f?p=2853:4:1727923121986559057::NO::P4_QA_ID:246
Essentially the question is where to stored your PL/SQL stored procedures. H. Sheehan, Gary Myers, William Robertson, Pete Scott, Scott Swank, A. Nadrian and I discussed this on the Dizwell Forum.
http://www.phpbbserver.com/phpbb/viewtopic.php?t=458&mforum=dizwellforum
To sum up their positions:
Option 1: In organized, packaged files on your DB server
- Fewer security holes
- Handles failover situations better
- Easier to use version-control system
- Available for a greater number of nice PL/SQL editors
- Harder to inadvertantly overwrite source code, leads to greater confidence
Option 2: In-the-db packages
- Greater efficiency (pre-loaded)
- Greater code integrity (shows invalidation)
- Search code in USER_SOURCE table
- Use some PL/SQL tools easier
(Note: there are IDEs that integrate with source control and compile directly into the database)
It seems like the leading camp is Option 2. The advantages of having your packages pre-loaded into the database are just so significant, especially since you should be able to find an IDE that integrates directly with source control and can compile directly into the database. Scott Swank provided this suggestion:
http://www.oracle.com/technology/products/jdev/101/
howtos/extools/subversion.html
One general consensus, however, is this:
- Code should be contained in packages
- These packages should be wrapped.
http://htmldb.oracle.com/pls/otn/f?p=2853:4:1727923121986559057::NO::P4_QA_ID:246
Essentially the question is where to stored your PL/SQL stored procedures. H. Sheehan, Gary Myers, William Robertson, Pete Scott, Scott Swank, A. Nadrian and I discussed this on the Dizwell Forum.
http://www.phpbbserver.com/phpbb/viewtopic.php?t=458&mforum=dizwellforum
To sum up their positions:
Option 1: In organized, packaged files on your DB server
- Fewer security holes
- Handles failover situations better
- Easier to use version-control system
- Available for a greater number of nice PL/SQL editors
- Harder to inadvertantly overwrite source code, leads to greater confidence
Option 2: In-the-db packages
- Greater efficiency (pre-loaded)
- Greater code integrity (shows invalidation)
- Search code in USER_SOURCE table
- Use some PL/SQL tools easier
(Note: there are IDEs that integrate with source control and compile directly into the database)
It seems like the leading camp is Option 2. The advantages of having your packages pre-loaded into the database are just so significant, especially since you should be able to find an IDE that integrates directly with source control and can compile directly into the database. Scott Swank provided this suggestion:
http://www.oracle.com/technology/products/jdev/101/
howtos/extools/subversion.html
One general consensus, however, is this:
- Code should be contained in packages
- These packages should be wrapped.
Comments:
<< Home
It seems to me that the answer to this is one that comes down to mere personal preference. Both camps include the statement that no matter what you do, the trusted version of the source code is in version control and you always deploy from there.
I'd say that message is the important one.
I'd say that message is the important one.
I think the original answer missed the point of the question.
The alleged DBA seems to me to be arguing against using PL/SQL packages AT ALL, but the answer and many of the following comments seem to assume he/she could not have been that stupid and must have been referring to the best place to keep master copies of the source code.
>> My DBA friend argued that code stored in the database does not handle failover well and that storing code in the database creates security holes.
This is a non sequitur. It is SQL*Plus scripting that does not handle failover well and creates security holes, and pretty bizarre to argue the opposite. This goes for all the other points claimed as advantages too. I really don't see what is being argued.
Post a Comment
The alleged DBA seems to me to be arguing against using PL/SQL packages AT ALL, but the answer and many of the following comments seem to assume he/she could not have been that stupid and must have been referring to the best place to keep master copies of the source code.
>> My DBA friend argued that code stored in the database does not handle failover well and that storing code in the database creates security holes.
This is a non sequitur. It is SQL*Plus scripting that does not handle failover well and creates security holes, and pretty bizarre to argue the opposite. This goes for all the other points claimed as advantages too. I really don't see what is being argued.
<< Home
