tag:blogger.com,1999:blog-5215551487816981140.post8366732697769499889..comments2024-01-23T06:10:31.184-08:00Comments on ORA-00001: Unique constraint violated: Mythbusters: Stored Procedures EditionMorten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.comBlogger54125tag:blogger.com,1999:blog-5215551487816981140.post-89600397293305723522015-09-07T19:03:37.442-07:002015-09-07T19:03:37.442-07:00I'm glad this thread was revived recently on t...I'm glad this thread was revived recently on twitter, I think myth #10 regarding logic in the database is quite relevant regarding APEX. I've recently heard a DBA still think it's strange/bad to have application source data stored in the DB, though I'm yet to hear reasoning.Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-1629258899598337892013-09-24T04:53:46.637-07:002013-09-24T04:53:46.637-07:00Well said. Myth #10 covers the most serious defici...Well said. Myth #10 covers the most serious deficiencies in the latest-hyped approaches to application development. This is why crap technologies like Object-Relational Mapping (e.g. Hibernate, Entity Framework) proliferate; and why systems that are built upon such technologies become increasingly cumbersome and badly-performing whenever they are pushed to do anything more trivial than a simple blogging engine.<br /><br />I cannot fathom how some (most, actually) developers come to the conclusion that "business logic" or "rules" should be stored outside the database.<br /><br />These rules *are* data! Thus they belong in the database such that the DBMS can enforce their correctness!<br /><br />As an aside, do these developers even learn the notion that "code *is* data"? (See http://c2.com/cgi/wiki?DataAndCodeAreTheSameThing for more)<br /><br />There are reasons why idiotic OR/M technologies proliferate:<br /><br /> - Too many developers have sub-standard skill in SQL and view the OR/M as a magic bullet that both designs and queries the database for them. The results are always laughably inefficient and frequently mandate bad (or at best questionable) design principles (e.g. mandatory artificial identity keys).<br /><br /> - Many businesses aren't aware of the rules they have in place and thus aren't explicitly specified. For example, a price may not be negative or we'll end up paying for customer purchases! This sort of rule is trivially easy to express as a constraint on an attribute. Bearing in mind that all unit testing frameworks feature "ExpectedException" checks, such a rule is also trivially easy to unit test.<br /><br /> - To be fair, SQL (even the ANSI standard) is quite a poor language. A language that was fully compliant with relational algebra (I'm thinking of Date & Darwen's Tutorial D) would offer better expressivity for awkward business logic. It would also mitigate the vendor lock-in myth. Unfortunately, entrenched standards are hard to shift; this won't happen until enough database practitioners become knowledgeable enough to demand such a language. Perhaps the open-source community could give the established database vendors a fright.<br /><br />Keep fighting the good fight.Mattsternoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-13376147681098642022013-04-26T04:07:09.446-07:002013-04-26T04:07:09.446-07:00I know, it is an old post, however...
When I was ...I know, it is an old post, however...<br /><br />When I was reading it I was very much tempered by the 'what for' hammering my mind (even if I found your arguments consistent in itself). As key result I got from your post that you can put any logic into the database and you can't. Let's focus on applications that use browsers of presenting the UI, which I usually deal with. My experience is, that on the server you need any kind of code that:<br />1. transforms table data into html,css,javascript,jpeg,etc.<br />2. can communicate via http<br />3. can cope with browser communication ($_POST,$_GET) and eventually authentication mechanisms<br /><br />I trust your blogspot pl/sql to be able to cope with 1. I am too little in the topic to judge 2 and 3. My best guess would be that at least you require some adapters, which in worst case are self coded. As long as there are out of the box adapters, full stop - all fine. If you have to code something, which I would assume, you need to put any kind of logic onto your middle tier. Basically I am not interested in these fights about, what is business logic and what not. I am just worried to put lines of code on different places, some in the middle tier and some into the database. Jumping between buckets has a potential to become a monster debug challenge. So how would argue on that one? <br /><br />Also I am doing lots of things in php, because of its extensive support on associative arrays, implicit type conversions, rich inbuilt libraries, large user base with tons of ressources for each and every problem you can imagine and even on things you could not imagine, that they existed. Because I do not want to maintain code at two places, a technology change (which might be beneficial) would be an all or nothing. So would I find all this for pl/sql as well? At this point I have to admit that I so much frightenend to displace code from each other that I usually avoid any places with poeple who promote mvc object models and frameworks. I find encapsulation eg. in libraries useful, but this in my eyes has more of sort&order than just shooting everything into an mvc model. I even try to avoid to overuse objects as I do not find them appropriate to serve web related coding challenges. However different topic - I just wanted to point out, that my personal fear to push down code into different alleys just for the sake of it is great enough to avoid experiments on that edge.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-54953644745750061662012-03-01T11:38:17.620-08:002012-03-01T11:38:17.620-08:00Morten,
Brilliantly written.
To begin with I am...Morten,<br /><br />Brilliantly written. <br /><br />To begin with I am a programmer who works primarily in the middle layer and am a strong advocate of business logic in the middle layer.<br /><br />You make a lot of good points. The advocates of logic in the middle I think often work on very simple applications where there is one application per database.<br /><br />When you start hiring a consulting team or purchasing software that wants to integrate with your database it is much better to have business logic in stored procedures to prevent catastrophic data integrity issues.<br /><br />The only way that logic in the middle layer works every time is in a SOA system where ALL applications are FORCED to use the web service bus. In other words, put all logic in the database or use a SOA with a well defined and documented service BUS.<br /><br />It seems to be developers who work on stand-alone applications and don't do enterprise level development who say things like "XML is horrible, use JSON" and "data belongs only in the middle layer." Really? Where does your portability come from? What if your third party app provider doesn't know what JSON is and equates web services with "SOAP"? How does one dynamically generate consuming web services based on a JSON endpoint that merely dumps data in javascript notation?<br /><br />In my experience, both work very well if done properly. To say one or the other is necessarily bad is hubris.<br /><br />Well done.Seth Griffinhttp://none.comnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-34684284032167015592011-10-26T05:11:56.733-07:002011-10-26T05:11:56.733-07:00@Pete B
"Databases are for storing data!&quo...@Pete B<br /><br />"Databases are for storing data!"<br /><br />This is another myth that needs to be busted. <br /><br />Disk drives are for storing data. An RDBMS represents (not stores) the data in a manner such that it is possible to perform logical inference on the data to derive new facts from existing ones, using the relational operators. <br /><br />SQL supports standard logical operators like quantification natively. Popular middleware languages like Java, C# and Python force you down to using machine level constructs like loops.<br /><br />SQL is the nearest you can get with a widely used commercial product to programming directly in logic. <br /><br />Thus doing all the logic in the database delivers not only higher productivity, greater reliability and faster performance; it is also fundamentally more logical.Will Sissonnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-58374703668963763502011-10-25T06:41:57.682-07:002011-10-25T06:41:57.682-07:00@Pete B
The telephone number type is defined in t...@Pete B<br /><br />The telephone number type is defined in the database. <br /><br />It is easy to derive the UI validation out of the database metadata. <br /><br />By leaving out the middleware completely you avoid the danger of your middleware definition being inconsistent with your database. <br /><br />One level less is always good.Will Sissonnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-25597094492742545462011-10-23T18:23:17.004-07:002011-10-23T18:23:17.004-07:00Thanks Morten.
Those examples may work but they a...Thanks Morten.<br /><br />Those examples may work but they are cumbersome, reliant upon the middle tier (i.e. UTL_HTTP) or are unsuitable for real-time interaction with complex hardware devices (i.e. telecommunications equipment).<br /><br />I must comfess it was a leading question given that my company, <a href="http://www.dbPlugins.com" rel="nofollow">dbPlugins</a>, has developed a 'User Extensible Database Plugin Architecture'. <br /><br />This architecture allows one to easily integrate to any 3rd party technology API and express that API as a PL/SQL package. The package then becomes a universal database layer API that can be called by any modern programming language.<br /><br />A database plugin gives the data-tier the same level of programable extensibility that is available at the middle tier.<br /><br />My company is looking for beta testers to validate and comment upon this emerging technology. <br /><br />We have working examples of plugins that distribute in source-code format which integrate to a GeoIP API and to tarlib. <br /><br />The plugin server effectively masks the complexity of database integration allowing the author to focus on the task of integrating to the 3rd party API. There are two components that an author writes, the PL/SQL package that faces the user application and the 'C' based plugin module that interfaces to the 3rd party API.<br /><br />SQL*VOX - a more complex plugin - interfaces to the telecommunications API from Dialogic allowing for IVR, VOIP, Text-To-Speech and ACD capabilities from a database centric point of view.<br /><br />This is very innovative technology that you will not find anywhere else. I welcome any interest you may have in evaluating this technology.<br /><br />Steve Guilford<br />Founder, CEO<br />dbPlugins<br /><a href="http://www.dbPlugins.com" rel="nofollow">dbPlugins</a>Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-82625848153106956752011-10-23T10:26:28.658-07:002011-10-23T10:26:28.658-07:00@Unknown: "How would you control an industria...@Unknown: "How would you control an industrial or manufacturing device for instance? Could you write functions/procedures at the database level that directly control these foreign devices without going through the middle tier?"<br /><br />Depends on the device, but yes, assuming the device has some kind of control API and can be reached over a network, then you could use UTL_HTTP and/or UTL_TCP to communicate with it directly from the database.<br /><br />Depending on the database version and edition, there's also the ability to execute OS commands via DBMS_SCHEDULER, external C callouts, and Java stored procedures, all of which run on the database server and not the middle tier (ie not on another server).<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-34598852108012892442011-10-22T11:00:06.094-07:002011-10-22T11:00:06.094-07:00How do you incorporate user extensibility into the...How do you incorporate user extensibility into the logical processing capabilities of the Oracle Database (i.e. PL/SQL extensions) ?<br /><br />At the middle tier we can integrate disparate sources of data (i.e. foreign hardware) to extend the capabilities of the business logic as expressed in Java, PHP, C etc. etc. How would you control an industrial or manufacturing device for instance? Could you write functions/procedures at the database level that directly control these foreign devices without going through the middle tier?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-41256967141180655232011-10-17T03:05:19.362-07:002011-10-17T03:05:19.362-07:00I'm really on your side. I happen to think tha...I'm really on your side. I happen to think that SQL is a high-level language specially suited for data management (specially validation) and so it's no-sense not to use it in favor of a more general purpose language where you need to re-invent several wheels. The case with procedural languages is exactly the same.<br /><br />I also agree with you in your myth busts. Although in the PostgreSQL world, mostly everything you say applies similarly (BTW, @rane, don't put PostgreSQL and MySQL in the same bag and call them "poor"; PostgreSQL is extremely featured and able to do anything that Oracle does shown in this post).<br /><br />However, there's one valid point IMHO in not putting the business logic in the database: the database is a precious, scarce resource. Write scaling a database is hard, and so most uses cases really want to have a single central server (however with read replicas for read scaling). What is definitely cheaper is to have several application servers running on commodity hardware rather than buying an Exalogic.<br /><br />So this is what I do: I clearly separate what I also call "data logic" from "business logic" and have the former reside in the database and the latter in the application servers. Also data logic may be replicated in the application servers, so validation does only happen in the database "as a last resort", but always keeping your data model consistent. Yeah, its a trade-off, as the "business logic" is spread in two tiers and also some data logic may be needed to keep in sync. But IMHO it's worth: you still have a db which ensure all your invariants, while having a cheap way of removing processing from the scarciest resource (the database) to cheaper application servers.ahtnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-12206361341070554022011-10-13T06:50:01.752-07:002011-10-13T06:50:01.752-07:00Hmmm, there is one very BIG disadvantage to having...Hmmm, there is one very BIG disadvantage to having all your business logic in the database!<br /><br />If I want to validate something as simple as a phone number on the UI and all the logic is in the database then I would have to write some nifty logic in the database and make the front-end call through to the middle-tier that then calls the database (which doesn't even touch data in the database only the value I've passed in) run it's validation and pass back (via the various tiers) back to the front-end to say INVALID PHONE NUMBER?! <br /><br />That's daft you say! Simply add the same logic in the UI so you don't have to call the database... Right, now the phone number format changes and we need to store the country code. I change the UI but not the database... Whoops, now they don't perform the same validation?!<br /><br />Alternatively you could write it in shared code that both the UI and back-end can access? That way the UI can validate a phone number instantly without having to go all the way to the database?!<br /><br />Databases are for storing data! Sure, have your data integrity and things that databases are good for but don't just dump all your logic in there because you can't be bothered to architect the system properly!Pete Bnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-11611071823575357642011-10-07T09:38:32.121-07:002011-10-07T09:38:32.121-07:00Same thing I have asked in Postgresql mailing list...Same thing I have asked in Postgresql mailing list whether postgresql can be used as Application Server. Many replies have said they are using the same for years. Now I can confidently say yes it is, not just postgresql but also oracle.<br /><br />Thanks.Chaitany Kulkarninoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-38812761915836529872011-09-22T03:31:38.488-07:002011-09-22T03:31:38.488-07:00I am a performance/tuning expert. And really nothi...I am a performance/tuning expert. And really nothing matches the performance of PL/SQL. <br />You just need INTELLIGENT programmers instead of..Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-58113979224387308482011-09-04T04:48:23.320-07:002011-09-04T04:48:23.320-07:00Interesting article. I wholehearted agree with pla...Interesting article. I wholehearted agree with placing logic in the database. <br /><br />I think it is worth pointing out that these techniques are just as applicable to (almost) all SQL DBMS rather than just Oracle. Database centric practices are just as viable in SQL Server or Postgres for example.<br /><br />Given that the RDBMS is directly based on logic, as soon as you have created a table you have started putting logic in the database, so why not be consistent and put all the logic there?Will Sissonnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-71723770751685147052011-08-30T19:57:13.018-07:002011-08-30T19:57:13.018-07:00I've got to agree about the ignorance of many ...I've got to agree about the ignorance of many developers when coding against (and I do mean "against") Oracle. The other factor that comes into play is that it takes at least 10 people to get any significant work done in Java. Management packs so many Java drones into the organization that they wind up having numbers on their side and the next thing you know ... no more PL/SQL (nobody can do anything without those elegant curly braces).Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-58285883139219560502011-08-16T04:09:24.510-07:002011-08-16T04:09:24.510-07:00This is brilliant. I am saving this link to use i...This is brilliant. I am saving this link to use in response to myth-peddlars in future!Tony Andrewshttps://www.blogger.com/profile/16750945985361011515noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-26703631608166660272011-08-11T08:52:53.426-07:002011-08-11T08:52:53.426-07:00pl/sql is the best. it iseasy to code,easy to mai...pl/sql is the best. it iseasy to code,easy to maintain and no other thin can match the performance.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-88332681963639043132011-08-11T04:02:20.988-07:002011-08-11T04:02:20.988-07:00I am working in a large software engineering firm ...I am working in a large software engineering firm with 17k employess and here stored procedures are a "best practice". Many people just dont know the power of SP. Awesome article :-)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-81856845501594944092011-08-06T16:24:23.147-07:002011-08-06T16:24:23.147-07:00Wow, now that people are quoting me (#11, last quo...Wow, now that people are quoting me (#11, last quote) I really should be more careful about what I say ;) I believe the Facebook quote was in reference to Exalogic and specifically http requests, but that is definitely not from me.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-5176536585966386272011-08-05T09:45:33.042-07:002011-08-05T09:45:33.042-07:00awesome post and nice format , ie myth busting and...awesome post and nice format , ie myth busting and the graphics. When I run into a bunch of Java developers hard core laying down the law on no stored procedures or other similar tactics I'm always amazed out how smart people argue for bad ideas. One of the better coders I ever worked with wanted to ditch Oracle and write a transactional data storage layer himself. He was a smart guy but smart people often blindly argue against anything they've never used because if it was good, of course they would be using it.Kyle Haileyhttps://www.blogger.com/profile/13586511268045480856noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-4273146247030395342011-08-04T07:49:22.062-07:002011-08-04T07:49:22.062-07:00ALL of the problems that developers and architects...ALL of the problems that developers and architects have with relational databases are due to a lack of knowledge and skill. I am so tired of people telling me that something is wrong with a technology that they do not even remotely understand. Most projects I've seen have done such a poor job of discovering the business process that it is no wonder they can't figure out how to use a database. Most databases I've seen have are so poorly designed and so filled with redundancy and trash that they are almost completely unusable for business purposes.<br /><br />This whole debate reminds me of a spectator telling Steinitz (chess champion of the world) that he couldn't understand his game. Steinitz replied, "Of course not! Have you ever seen a monkey playing with a watch?" Unfortunately, most developers don't even rise to the monkey's level.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-45839356867537502502011-08-03T03:14:50.875-07:002011-08-03T03:14:50.875-07:00This is a very interesting post.
As you can read i...This is a very interesting post.<br />As you can read in the comments people have different opinions.<br /><br />Strictly spoken if the logic layer is not implemented in the database, it would mean that tables would not contain any primary -, foreign -, unique and check keys. No not null restrictions. Just data.<br />This is something I strongly would not advice. Use the power of the Oracle database. So there always will be some kind of logic in the database anyway.<br /><br />What about the logic that can't be implemented in tables keys? Before deciding where you are going to put it, think about it:<br />Are you going to perform only single DML statements or will you have to perform also bulk DML statements? What type of developers do you have available? Is it a closed environment or is anybody allowed to write an application that manipulates the data? Which layer is probably the first to be changed (Most of the times is not the database. In cases the datamodel has changed your logic has to change as well)?<br />It all depends...<br /><br />I would personally use PL/SQL for the logic layer in case of the Oracle database. Pl/SQL has special been developed for it. If you want to pass the logic layer and manipulate the table directly, with PL/SQL this would be the hardest way (especially when there triggers on the table). PL/SQL is maybe not as beautiful as Ruby. Still with a good developer tool it is very easy to write and read.<br />But this is personally. <br /><br />One thing I also believe is: keep it simple. Use not to many layers. With many layers it is getting more difficult when there is a performance issue or if you want to debug it. This is what I like about APEX. It has a simple but strong layer structure.Mathieuhttps://www.blogger.com/profile/18076336383940597681noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-74580981378266890402011-08-02T13:31:00.228-07:002011-08-02T13:31:00.228-07:00This article is extremely true !
Thanks !This article is extremely true !<br /><br />Thanks !Ola Roosnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-28445133162463997152011-08-02T13:23:45.811-07:002011-08-02T13:23:45.811-07:00Oracle much???
The idea of mythbusters-style post ...Oracle much???<br />The idea of mythbusters-style post is awesome and I have to admit the content is well written (although vendor specific) but:...<br /><br />#1: vendor specific, what about a solution for Oracle's main rival SQL Server ??<br /><br />#2: The information is indeed there but: try tracking it down in a moderately complex system and tell me how long will take you. By the way you better pray that documentation is up to date because making changes in your db WILL result in things breaking<br /><br />#3: Granted that one is busted BUT frankly it was about bloody time. Surely we don't want to go down the road of comparing them with developer IDE's (see VS or Eclipse)?<br /><br />#4: That one is an axiom in software development <br /><br />#5: see #1 plus although in SQL Server for example it is possible to import and use C# libraries from within the DB server I personally think it a horrible idea<br /><br />#6: see #5<br /><br />#7: it is certainly doable but not as productive or as pain free as I am sure we all wished it would be (although we are getting there)<br /><br />#8: see #1<br /><br />#9: see #1<br /><br />#10: The architecture argument is valid and millions of keystrokes have been wasted on it. It comes down to the architect, the project requirements and constraints and so on and so forth...<br /><br />#11: With money and patience everything scales<br /><br />I thoroughly enjoy your blog, keep up the good work<br />CheersAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-10172304247264430162011-08-02T10:29:19.925-07:002011-08-02T10:29:19.925-07:00Lets have an Oracle DBA party to celebrate!Lets have an Oracle DBA party to celebrate!Anonymousnoreply@blogger.com