4 ms·
I work with PL/SQL daily and I believe several points in the article are... debatable, if not misleading. 1. Yes, you can store your packages in SVN or whatnot
by msluyter 15y ago
I work with PL/SQL daily and I believe several points in the article are... debatable, if not misleading.
1. Yes, you can store your packages in SVN or whatnot, but two developers cannot work on the same package on the same database at the same time without stomping on each other's work.
2. Managing database changes: yes, you can analyze explicit dependencies for a given object, except that EXECUTE IMMEDIATE statements are not tracked. Nor, of course, are any queries done by your java webapps or whatnot.
3. "And PL/SQL has object oriented features too." We've moved away from most of the object oriented features because either we encountered bugs (in 10g) or because performance was abysmal.
4. EXECUTE IMMEDIATE -- I love the idea of execute immediate, but in practice, it's has problems. Performance tends to be poor (queries often need to be re-parsed) and as noted above, they break dependency tracking. If over-leveraged, they can also result in highly unreadable code.
5. Unit testing -- I've looked at several PL/SQL unit testing packages and they've always seemed to me to be fairly half baked. Unit testing procedures with side effects is also often inherently difficult because they rely on database state. I'm not against it, but it certainly isn't as easy as using jUnit. That said, if anyone has successfully used a PL/SQL unit testing package, I'd be curious to hear it.
5. Portability -- Irrelevant if you're using oracle specific features (which, you probably should if you're using Oracle).
None of this is to say that I'm against stored procedures. On the contrary, they provide some level of encapsulation, and I've generally found PL/SQL to be fairly transparent to understand. If I'm doing some complicated joins and/or several sequential DML statements with exception handling in a transaction, I'd much rather do it in PL/SQL than from an external language. I just think the article paints a picture that's a little to rosy.
- IgorPartola 15y agoRelating to #1: isn't that also true for say Rails/Django/PHP code? Two developers would stomp all over each other trying to modify the same piece of code on a production server. Relating to #5: doesn't your objection to unit-testing code that has side-effects also apply to say Rails/Django/PHP code that also modifies state in a data store? I suppose you could argue that if you are using Rails/Django/PHP, you could mock your entire database access layer (maybe through your ORM), and that this would be very difficult inside PL/SQL. However, mocking the entire data access layer is pretty expensive, in any server-side environment if you want to get some actual testing done (as in nuances of resolving foreign key constraint issues, or how locking of rows/tables must work for your code to run concurrently and deadlock-free). I am not arguing for or against PL/SQL. Just wondering why you think those points might have different obstacles in different environments.
- tomkarlo 15y ago"Two developers would stomp all over each other trying to modify the same piece of code on a production server." Yes, but it's trivial to create both developer instances and an integ instance of Rails/Django/PHP so you don't end up trying to both modify code on the production server. Doing that with the db, especially if you want to test against "production scale" data sets, is at the very least going to be expensive.
- IgorPartola 15y agoAre you suggesting having one production DB => multiple virtual machines with application code? If so, I suppose that might work for some read-only workloads. If you need to bring up a "production scale" server for a developer to go along with their virtual machine with application code, then you have to do it no matter what environment the application code runs in. I imagine modifying data using experimental code on a production system is not really desirable no matter what the code is written in.
- tomkarlo 15y agoNo, I'm not suggesting that. But having say 3-4 DBs for integ, testing and production is a long way from having one for each developer.
- piranha 15y agoFor #5 you usually have some database setup/teardown stage, where you create (or destroy) new testing database and setup all the stuff you need. This is pretty hard to do with pl/sql.
- IgorPartola 15y agoYes, that's what I usually end up doing: having a DB dump that I can load back in at the beginning of the test, then drop it at the end. I can see how a purely stored procedure test harness would have a hard time with this. However, if you just wrap your unit tests in a shell script, you should be able to do this.
- SoftwareMaven 15y agoThe problem with #1 is cultural. For whatever reason, DB developers don't seem to have caught on to the "run a local, development instance on my machine; run a deployment for production." I worked at a company that did a lot of Oracle PL/SQL, and pretty much all of their developers shared the same "development" server.
- greyfade 15y agoTo be fair, commercial DBMS are rather quite expensive. Setting aside one additional multiprocessor license for every single developer on your team is... prohibitive.
- VladRussian 15y agooracle is free for dev.
- gphil 15y agoThose types of licenses are generally free for developers. http://www.oracle.com/technetwork/indexes/downloads/index.html http://www.oracle.com/technetwork/indexes/downloads/index.ht...
- iamgoat 15y ago1. You can say the same about database schema changes. A lot of the bumping into one another would come from your sproc/udf trying to handle too many things at once. Which is typically a bad design decision.