Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Lisa.

Asked: August 30, 2003 - 9:23 pm UTC

Last updated: September 27, 2019 - 3:00 pm UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Dear Tom,

I am a database developer/development DBA who ascribes to your development philosophy and approaches. I am also a Java certified programmer, although I do not claim Java as a primary skill. Recently, I have been tasked to serve as the lead architect for our system. Sounds great, however, I have a partner who is a die-hard Java J2EE type. Here lies the beginning of a power struggle.

Our system is a legacy, enterprise-wide OTLP web application, with some data warehousing and reporting components and multiple interfaces to external systems. Fully deployed, our database will size in the Terabytes. Unfortunately, every new mini-application added to date has used a different approach to architecture. Some applications have SQL embedded right in the Java business logic code (initially delivered without bind variables by my new partner), other approaches use SQL externalized in XML, and finally the approach that our database team lead relies 100% on stored procedures and a single package interface.

Tasked with defining the new Java/Oracle architecture, I am struggling with how to defend my position to keep all SQL within the database. I know that I do not have to present my arguments to you, but given the large database volume, the complete lack of SQL skills that I have seen come out of Java development to date, and the poor legacy database design which necessitates complex SQL, I feel that our system will absolutely not perform if I leave Java developers free to write SQL as they wish outside the database.

After preliminary talks with my new partner I know that he is loathe using any Stored Procedures. I have done research trying to understand why J2EE developers are so scared of making use of the database, and so far all that I can gather (other than just plain arrogance) is that they feel that somehow using Stored Procedures makes the system hard to maintain and not scalable. As far as I’m concerned, as long as you maintain and abide by generic interfaces, the implementation of data access is just that, implementation. Can you give specific arguments to help with this debate and advice on how to convince Java developers? I know that I am not alone in this battle; the war is waged in any development shop where J2EE is the word of the day.



and Tom said...

well -- the problem is this...

In order to scale java - you need tons of small machines spread out. that is what they see. lots and lots of machines = the way to scale. I've seen people time and time again take my stored procedure based approach (application already written -- works, runs, scales, in fact excess capacity on the server) and convert it to java. They go from a single machine hosting the http daemon and database to 4-8 front end processors for running java and a BIGGER database machine that never seems to keep up. What happened? they didn't "like" the stored procedures (they don't scale they say...). So, they reimplement in java and find they need N times the hardware to do the same thing slower. The database machine is bigger cause they are killing it. They can scale all righty - they HAVE to.

They have their hammer, everything is a nail. the way to scale for them is to add more hardware. if you cannot add an infinite amount of hardware you must not be able to scale right? (wrong).

You know what will make the database fall over in a spectacular fashion? Through some really bad sql at it. Make this sql serialize. Parse statements like there is no tomorrow. Best way to achieve that? You already answered that question -- "but given the large database volume, the
complete lack of SQL skills that I have seen come out of Java development to
date, and the poor legacy database design which necessitates complex SQL, I feel
that our system will absolutely not perform if I leave Java developers free to
write SQL as they wish outside the database"

Ask them for specific bullet points as to why the system would be harder to maintain when you can have that awesome separation of skills. Procedural coders (yes, Java is procedural coding -- OO whatever -- it is a procedural language) stink at SQL unless they get into the "set based mindset". I can do both equally well -- I was born and bred a coder long ago. But once I got it through my head that "sql is about sets", things got really easy.

the system would in fact be easier to maintain, tune and fix as the SQL would be coded by people who can do SQL -- which is not a java programmer. The sql people cannot in general code java either -- it is an even trade. they do what they are good at, we do what we are good at.

Ask them "so, if i identify your poorly performing sql -- how the heck are you going to fix it as it most likely involves ripping out half of your code since you must change your algorithms. rather then you worry your head about that, I'll take care of it for you"

Also, ask them "so, is J2EE the last thing?" -- and make them answer with a perfectly straight face please. The day after it goes production, it is the existing legacy system we'll be interfacing to in 5 years. If I dig out my "C programmers Journal's" from the late 80's/90's -- you would discover that today we shouldn't even be WRITING software! why not? why because the objects that we created in the 90's would be our components of the 21st century and we would just assemble any application from these off the shelf components. Programming would be dead, we'd be assemblers crafting new application from the old.

anyone out there doing that?

remember "AI"?
expert systems?

It is a religous war though, they cannot be won by reason or objectivity.


  (118 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Some other reasons why your Java SQL code does not perform

Eric Ma, August 31, 2003 - 8:23 pm UTC

In my opinion, a Java application built on top of an Oracle database fails to perform not because of less efficient SQL is written (although if you ask a Java developer what EXPLAIN PLAN and tkprof mean you probably will not get an instant answer), but because of the following:

1. Use of multiple database calls, instead of combining several SQL's into a single stored procedure to reduce network trips as well as database efficiency.

2. Ignoring the advantage of SQL in set operations by getting a complete resultset, turing it into a Collection of Java objects, and then using collection iterator to find the desired records.

3. Ignoring the difference between Read-Write and Read-Only situations, and allowing EJB container to unnecessarily maintain Tx context when the data is read-only.

4. Poor use of EJB entity beans without understanding the limitations.

Having said the above, it does pay to maintain business logic outside the database and in Java middle-tier components. The key is to know where to draw the line. I suggest all J2EE developers to read Tom's Expert One-on-One Oracle and Rod Johnson's Expert One-on-One J2EE Design and Development book in order to strike a balance between a Java centric and database centric approach.

Tom Kyte
August 31, 2003 - 8:42 pm UTC

1) well said

2) :)

3) and not really understanding the difference at all....

4) big time....

application logic -- i agree.

BUSINESS LOGIC -- that, well, no, i agree to disagree. unless your business is J2EE and that alone. business rules = data rules = close to data. a business rule could be "you can only be in EMP if there exists a record in DEPT". would you argue that should be enforced outside?

application logic = something outside of the database.


A reader, September 01, 2003 - 12:17 am UTC

Hi Tom

There are features like EJB QL that are being added in J2EE. But in this version there are lots of limitations and I feel it is not worth using. Do you think it will pick up? I feel it would be very difficult to tune/debug such features as you don't know until runtime what SQLs are being fired.


Tom Kyte
September 01, 2003 - 8:21 am UTC

i agree with you. i'll go a step further - it'll not really be POSSIBLE to tune since the people doing it will know nothing about the tool they are really using -- the database. applications live and die based on how they use it. I see lots of premature deaths out there. some things cannot be fixed by adding yet another tier.

J2EE apps should use database features intelligently

Eric Ma, September 01, 2003 - 10:45 am UTC

For the majority (>95%) of web-based applications today, which are HTML forms/views on top of database tables (I am afraid to say this site is one of them), I have no doubt that the stored procedure approach probably offers the best performance/cost ratio. As a matter a fact, this approach used to be the best practice promoted by Microsoft, in their DNA (distributed network application architecture) and early .NET days. If anyone paid any attention, last year .NET beat J2EE hands down WRT performance in the now infamous .NET PetShop vs. J2EE PetStore benchmarking exercise, because M$FT was using stored procs, and the Java camp was using BMP entity beans for God's sake!

However, with the remaining 5% of all apps, as well as some of the 95% of the apps that I mentioned above to which absolute performance is not the top priority, using middle-tier component-based architectures offer great benefits. Yes, this means more computing power is needed, but hardware and software are so cheap today (Dell servers and Linux anyone? :-). Now think about the benefits you gain:

1. For frequent web page look-and-feel changes, you don't need to touch your stored procedures, assuming the stored proc is actually generating the HTML.
2. For business rule related changes, hopefully it is a matter of changing XML configuration files, not your Java classes or stored procedures. Think about a workflow type application.
3. Think about the scenario where you have to integrate another data source, which happens to be M$FT SQL Server.

I can list more of these. My bottom line is, one has to adopt a pragmatic approach when building applications, just like in data modelling one has to strike the right balance between following perfect normal forms and denormalization for query performance. Stored procedures are great, if in the same operation I need to touch multiple tables, especially for retrieving data (REF CURSOR is my best friend). However, logic such as "add 1000 to sal if comm is >= 5000" is best kept outside the database, because tomorrow 1000 can be changed to 2000.

Just my 2 cents.

Tom Kyte
September 01, 2003 - 10:56 am UTC

dude, you are so preaching to the choir here.

I agree with you 110%

one has to adopt a pragmatic
approach when building applications

smartest thing anyone will say here today I am sure.

well, maybe 99.999%

However, logic such as "add 1000 to sal if comm
is >= 5000" is best kept outside the database, because tomorrow 1000 can be
changed to 2000.

is something I disagree with -- why keeping that fact outside the database (seems to me that FACT is in fact DATA itself that should be in a table perhaps for safekeeping, administrative purposes :)

My personal gripe is -- that 95% is huge. People take the 100% j2ee for everything approach. basically 95 times out of 100 or more -- it is not the right approach and it *kills* people.

our experience

Alberto Dell'era, September 01, 2003 - 5:21 pm UTC

We have had an experience quite similar to Lisa's one when we started to design the new version of the main site of a customer who ranks as the 1st/2nd (depends on who you ask ;-) ) company in his huge market sector, with millions of registered users.

We had a legacy Oracle database with a lot of stored procedures, and we had to replace a Microsoft-based web site with a new one based on J2EE. Our final (successfull) architecture has been based on Apache, a J2EE Web Server, a J2EE Application Server, and of course the lovely&lively Oracle database.

The web server calls the app server's Session EJBs which in turn call stored packages to do all the data-related work.

This Architecture has been *blessed* by our J2EE Architects, who have pointed out e.g. the limitations of Entity EJBs in sectors like concurrency & consistency (what if both nodes of a clustered app server try to update the same Entity bean - which wins ? Which version of the data they see ? This is not part of the J2EE standard ..) and their being "hard to use" in general, and so on.

Actually we use every possible J2EE technology out there (JSP, Servlets, java-based XSLT and XML, JMX ... name one, we have it!) but for the database access, we use the approach you suggest - JDBC and stored packages.
Guess what ? Even if the database has only 8 CPUs and 2Gigabytes of Ram, we haven't had a single serious issue in 6 months of production - neither consistency, nor performance. The dream of any manager.

And the Java developers are more than happy to leave the "boring" database stuff to the database developers - who find the same database stuff "cool and fascinating". There are a lot of cool features in J2EE to play with, and they don't miss at all the SQL - they see the packages as "big classes with only static methods", and they're happy with it [Well the smartest ones actually like sql too - but that's another story ;-)], especially the junior developers who know everything about Java but almost nothing of J2EE - they are able to use the database in a matter of hours.

Hope this helps winning your "battle" - at the end you will all win. Guaranteed.


The debate continues

Lisa Spory, September 02, 2003 - 8:34 am UTC


Thank you for response. You instructed me to "Ask them for specific bullet points as to why the system would be harder to maintain when you can have that awesome separation of skills...” Well, it just so happens that I had an interesting conversation Friday via IM with a friend that I consider to be a J2EE guru, and a pretty smart and reasonable guy despite the fact ;)

Here are some particular excerpts:

Java Guy - "The trend is to move toward persistence frameworks (e.g. TopLink) and actually let the frameworks write the SQL...less errors, fewer portability issues and better part of the performance sales pitch is that framework developers will know how to optimize the SQL for a particular database..."

Lisa - "So another case of Java developers thinking that they know all and can generically optimize to any database, any situation?"

Java Guy - " reason this trend may have arisen is that Java is a very open source technology...and Oracle costs a fortune..."

Java guy - "most apps can afford to sacrifice performance for ease of development and maintainability...many developers don't like stored's definitely a opinion why...they [9 out of 10 DBAs] don't normally control them...they almost always live outside of CM...they don't play well in the build...and the big one for Java types, they are not is more of a human nature thing than a compelling piece of logic..."

Lisa - "My experience with 9 Java developers...they are so enamored with what they can do that all 9 will come up with their own way of doing things. Reuse? Haven't seen it happen...however the Stored Procedure my colleague just wrote will be used by both Java and legacy code..."

We go on to argue these points; in the end he conceded that for my particular situation Stored Procedures might be acceptable (but keep out that business logic!!). But again, he only agreed for my situation (legacy code, large database, poor database design).

Bottom line - I asked for specific reasons to back up his argument and basically got nothing other than it's a matter of personal preference, that perhaps Java developers don't like Oracle because it's expensive, and a half-baked CM argument (ALL our database code lives in CM and we can and do support automated builds).

My plan of attack is set. I can and will come up with specific bullet points as to why we should use Stored Procedures and keep SQL in the right hands. Unlike a human nature based argument, I have proof (tkprof and 100's of bad SQL statements in Production today due to Java developers).



Tom Kyte
September 02, 2003 - 8:45 am UTC

if this:

"most apps can afford to sacrifice performance for ease of
development and maintainability"

were true, I would be retired as there wouldn't be any need for me.

ask him to give us a "business logic" example that needs to be kept out of the database. I'd be interested. I find most of the time, they need to actually be IN the database.

business logic = all about data
application logic = not all about data, all about making it look pretty.

Business Logic

Lisa Spory, September 02, 2003 - 10:12 am UTC


My Java Guru is on vacation, so I asked another Java colleague for an example of business logic that must be performed outside the database...

Java Gal - "You cannot schedule an appointment for more than 7 days in advance"

Lisa - "How about -'You cannot schedule an appointment for more than x days in advance, where x is a dynamic value stored in table t'

Java Gal - "...stored in table t or in an xml config file that can easily be modified...config file more easily allows different configurations of the BL to be applied to different users..."

Lisa - "Until your customer wants to be able to dynamically set x...."

Java Gal - "...which can be written back out to the config file.."

Lisa - "Why do all this when you have a database for the pure reason of storing data? Seems rather round-a-bout to me...."

Java Gal - "...why do you need a db?...some systems are simple enough that you can forego the whole expense of a db and just store info as flat files...not all dbs operate the same, but an xml file is an xml file and can port to any platform"

So there you go - why do we need a database at all when we have XML!!!!

Sounds like we can all retire ;-)



Tom Kyte
September 02, 2003 - 10:52 am UTC

if that "business rule" ain't in the database what happens when new UI "foo" comes along????

are they going to duplicate it???

I fail to see how a config file that has to be backed up, maintained, not lost, edited separately -- would be easier then a small admin screen that does CRUD on a config table personally.

there is no winning this argument -- unless you just edit their file and mess it up. they'll say "hey, you weren't supposed to do that", I'll say "you have nothing preventing me from doing so".

The person who says you don't need a db is not anyone you'll be able to convince, until they actually lose their data.

Code Generators

Lisa, September 04, 2003 - 3:06 pm UTC


What is your take on persistence frameworks (i.e. code generators)? My counterpart is saying "no way, won't happen" to having every single SQL statement wrapped in a stored procedure. He wants to use a persistence framework (Hybernate, TopLink) to remove SQL writing from the hands of developers all together.

How do you feel about using O/R mapping against views and not directly against tables? Although the SQL is dynamically generated, it will be generated against a single view. As database experts we are then free to tune the underlying views as needed. This is very similar to an approach my colleague and I took within a PL/SQL package designed to support "adhoc-like" queries, we dynamically wrote very simple SQL against pre-defined complex views. In the case of multi-table updates we utilized instead-of triggers.

I understand that this is a compromise, using pure Stored Procedures will not only let us tune single SQL statements but also consolidate database trips, etc. However, I highly doubt that I will be able to force 100% use of Stored Procedures on our program. He is not against the use of Stored Procedures entirely, just the exclusive use of Stored Procedures.

As long as the framework supports the use of Stored Procedures, and as long as generated SQL is executed against views and not tables (therefore no joins), isn't the goal of keeping the SQL in the database pretty much accomplished? Do you see hidden dangers in this approach?



Tom Kyte
September 05, 2003 - 2:02 pm UTC

can't you guess based on the above? :)

My 2 cents to Lisa's Most Recent Post

Eric Ma, September 05, 2003 - 3:36 pm UTC

Hey Lisa, sounds like you have struck the right balance between pure J2EE and PL/SQL approaches. It could be a win-win situation (seriously). Let us know how the solution works out. I have not heard anyone using O/R mapping against views which uses INSTEAD OF triggers for CRUD. I am very interested in your experience.

business logic outside the database

Stu Charlton, September 07, 2003 - 8:42 am UTC

The trend over keeping business logic out of the database is one that I don't think anyone is going to crack. This is something that has been hammered into developers heads over and over for the past 10+ years, and it's not going to go away.

I can understand where Tom is coming from on this. Keeping validation constraints in the database is obvious if you want to eliminate duplication of effort. But J2EE people are frightened by the database for many cultural reasons, primarily its proprietary nature, but also because they're first and foremost "developers" and don't think of things in terms of a "system". The object oriented paradigm is a very "closed world" model.

But there are several reasons for why business logic is kept outside of the database, most not applicable to all of these J2EE web applications.

a) a lot of data is "live". In financial services, it comes from market data feeds, order feeds, execution feeds, etc., all with a different format. Much of this is transient data, does not need to be transactional, and requires a place to store some fairly complex rules for mapping one data protocol into another and also business /regulatory rules for orders & executions. The database probably isn't the best place for this. Financial services are a big supporter of J2EE for this reason.

b) a lot of data is NOT relational. Most large companies have significant investments in ISAM/VSAM and IMS that they still don't want to give up. Business rules need to run on top of this data given the most "productive" toolset (which, today is arguably J2EE). Lots of heavy legacy-invested industries like telecom and personal banking are therefore happy with J2EE for this reason.

c) a lot of rules are complex. People are frightened by the hideous complexity of the monster IF-statements of the past procedural COBOL programs that housed such "business logic". They equate stored procedures in Oracle with these massive COBOL programs, because usually these procs in legacy systems processed things one-at-a-time.

So the argument goes - well if a stored procedure is about processing things one at a time in a huge nested-IF statement, wouldn't it be more maintainable to factor those if statements out into objects instead? (The kicker is the proc never had to be written that badly in the first place, but tell that to 8+ years of PL/SQL programmers that did otherwise :-)

So the "complex rule" scenario is something that occurs mainly utilities (billing systems!), manufacturing, aviation scheduling, and some exotic financial systems (fixed income or derivatives trading, where traders can create new products on the fly, or quantitative analysis that need to tweak risk algorithms on the fly with a scripting engine).. All of this stuff probably should happen in an environment outside of the database.

In a more general sense, many complicated constraint validations that deal with sets of data have to be written in rather complicated triggers - especially time-oriented data. One might suggest it's worth the overhead to attempt to write this sort of validation in an object oriented fashion, and then to pick the "hotspots" and optimize them into triggers.

As for the O/R mapping trend, it definitely is a growing trend, and I actually see it as a potentially good thing now that the frameworks are getting quite mature. TopLink gives you tremendous flexibility with what kind of SQL is generated and allows you to tweak this or delegate into stored procedures if you really want to. The tradeoff is that now you have a fairly complex framework that you have to learn to use with your application. Most apps don't need this: a few stored procedures will do. Some apps, on the other hand, can benefit from this, especially ones that deal with primarily transient data that sometimes needs to be turned into a database transaction (I'm thinking financial systems here again: order systems that involve lots of non-transactional updates to a shared cache, and a few transactional updates).

A good summary of the various approaches to DB logic, from an object-oriented developer's perspective, was written by Martin Fowler on his website: </code> <code>

He's one of the few object-oriented experts that is pragmatic enough to recognize the advantage of using SQL and stored procedure, but he also is very familiar with the OO culture where so many devs run away screaming from SQL, so he tries to address both sides in this article.

re: business logic outside database

Menon:), September 30, 2003 - 8:18 pm UTC

From Martin Fowler's article that Stu posted above
( just looked at the conclusion ):
" would suggest that if you go the route of putting a lot of logic in SQL, don't expect to be portable - use all of your vendors extensions and cheerfully bind yourself to their technology. **If you want portability keep logic out of SQL.**"

I agree with the first half - don't think I agree with the last statement. Portability is usually associated with platforms and in that sense if your logic is in sql or pl/sql it is automatically ported to all platforms that the underlying database is ported to. If you don't want
to tie yourself to uyour database - then you have a
bigger problem - you lose out on the power of the database and also this can result in erroneous logic (Tom has
shown this again and again)

I was a fan of Martin Fowler - till I glanced through
one of his latest book on architecture
"Patterns of Enterprise Application Architecture"
It was good from OO point of view but lacked the
sophistication from the database point of view IMO.

Just my .5 cents:)

Tom your feedback on Stu Charlton's Review

Bala, October 06, 2003 - 8:37 am UTC

Tom I am a newbie, working mostly on the database and stored procedures for the past 2 years. We have these discussions about(the new guys..) chosing architectures, performance time and again....
I found this thread very useful in making some of our decisions...
Need your take on Stu Charlton's review above (point by point)
That'll help consolidate things. Please address the issues based on the nature of data(domains and the kind of data being handled.. e.g financials, derivatives data etc.. like what he has mentioned)

Use Facade and Factory Patterns In Java

Jon, October 20, 2003 - 8:41 pm UTC

Hi All,

As always with any debate, there are advantages and disadvantages on both sides.

Coming from the Java camp, the biggest advantage of putting the logic in Java is that we are not tied to any database vendor. However, from the database developers point of view, we are not utilizing the database features and fast performance (i.e. no network access latency, closer to data, etc).

Like solving most problems, I put another abstraction layer in the application. I create an wrapper class of the stored procedure. My application code will ONLY interact with the wrapper class.

If my company decides to use another database, I just have to plug-in the a new wrapper class that interact with the new database's stored procedure.


P.S. The JPublisher inside JDeveloper to generate Java wrapper class of the PLSQL package is a great tool. It is a bridge between Oracle and Java.

Tom Kyte
October 20, 2003 - 8:57 pm UTC

that is EXACTLY what I propose in my books :)

the best (and only) path to database independence is to put all things database into the databases stored procedure language.

you hide the different concurrency models
you hide the different features
you are ALLOWED to use (exploit) the different features


Your feedback

Bala, October 21, 2003 - 2:17 am UTC

Tom I posted this above, somehow never got your answer
"Tom I am a newbie, working mostly on the database and stored procedures for the
past 2 years. We have these discussions about(the new guys..) chosing
architectures, performance time and again....
I found this thread very useful in making some of our decisions...
Need your take on Stu Charlton's review above (point by point)
That'll help consolidate things. Please address the issues based on the nature
of data(domains and the kind of data being handled.. e.g financials, derivatives
data etc.. like what he has mentioned)"

Tom Kyte
October 21, 2003 - 7:19 am UTC

i didn't feel like taking stu's thing apart point by point, it is just information -- everything is valid. use the data here to form your own judgements.

i basically agree with what he said -- the problem isn't that the db shouldn't do it, but that people write bad code sometimes (and then blame the db)

there is nothing to really "say"

argument with no end

a reader, October 21, 2003 - 2:13 pm UTC

I have the same issue, being a DBA and loyal Tom follower, of trying to sell the idea ( "sell" being the keyword ) to developers of why they should implement the use of packages and procs in our J2EE application.
One of the problems of the issue for me is the perception of the DBA ( although we are the experts in understanding HOW the database works, and WHAT works BEST for each RDBMS - aka SQL Server is not Oracle is not Sybase is not DB2 ). Why the developer with black-box intuition/mentality is given the power to make architectural decisions without DBA consent is a mind blower to me. It is because they want the "independence".
Now they believe they have the independence without using the RDBMS features. They have written ( or bought ) email functionality + queuing functionality + blob processing functionality => the list goes on an on.
Now they want to change the main UI and that will require a rewrite of the schema - so they are not actually independent but dependent on a technology.
So now in 4 years when J2EE is replaced by someother new technology - another rewrite will be in order ??
Our application has gone out BETA and now they realize J2EE is as slow as the old technology. Not one SQL statement has come across my desk. The reactive approach is now to buy a 3rd party app to "tune" the J2EE app, and everyone waves arms and cheers ( i roll my eyes and bury my head in my hands ).
My point is there is a set of tools, and they should be used accordingly. The teams should TALK and DESIGN for performance right from the START. Design with the customer/end-user in mind. Understand performance goals and how the data is used before coding - then use the right hammer.

Wow... great explanation from great people

A reader, October 23, 2003 - 7:44 pm UTC

Tom, it's really very useful thread. i am keeping this page in my personal database.

independence from the dba

Stu Charlton, October 25, 2003 - 10:39 pm UTC

"Why the developer with black-box intuition/mentality is given the power to make architectural decisions without DBA consent is a mind blower to me. It is because they want the "independence"."

Two reasons:
a) A culture of cyncism within many DBA groups leading to distrust.

Management overburdens DBA groups with operational duties. They usually do not reward them for developer support and risk taking (using new features).

This leads to an acrimonious relationship with developer groups, and thus developers eventually develop the attitude that they should "avoid the DBA". They carry this attitude with them to other companies, and the attitude spreads, like a virus.

Of course, developers can develop the same attitude too, which leads management to not trust the developers and look towards outsourcing...

b) A widespread, but shallow, knowledge of "lock-in" economics.

This is the theory is that it costs you more in the long run due to "switching costs" if you are tied to a single vendor for any service. The reason it's shallow knowledge because it doesn't view this as a trade-off, it views it as a "rule of thumb".

And if you've hung out on Tom's site for any amount of time, you'll know that rules of thumb are often wrong, even if they have an element of truth to them under special circumstances.

Some thing have naturally high switching costs: switching office buildings, for example. Arguably, a database is similar, but "enterprise software" consultants pride themselves on soundbites for executives that are under pressure to keep costs down and don't have the attention span to truly think about the issue at hand.

My Two cents (A Java Developers point of view).

R&amp;R, October 26, 2003 - 12:00 am UTC

After being in the IT field for over 5 years, I've grown to love trying to keep various components of logic sperate in terms of technologies. Embedding SQL in Java code in my opinion is a very poor design and often leads to large amounts of code changing and testing to perform a simple change. I am all for the use of Procedures Vs Embedding the SQL within the Java code. For someone to make the statement that SQL Should be placed within the Java code, is pure ignorance or fear of giving up power to those that can better manage it within stored procedures.


Kimathi, October 29, 2003 - 5:37 pm UTC

Here is an interesting snippet of contradiction.

Chapter 7 - Data Access in J2EE Applications
Expert One-on-One J2EE Design and Development
by Rod Johnson

"Important Do not use stored procedures to implement business logic. This
should be done in Java business objects. ....."

Your thoughts Tom?

Here's my chance - now how to proceed?

Lisa, January 27, 2004 - 2:00 pm UTC


Hello, it is Lisa again. We have adopted a proprietary framework for building our J2EE applications; meaning we can customize the framework to suit our needs. This framework currently supports direct database access via declared SQL and through a set of strongly-typed Object Relational mapping classes.

This framework generates the classes based upon an XML description of the schema in a strongly typed manner. The implementation of all this is driven by a "database policy", for example, there is an Oracle database policy which describes Oracle-specific syntax.

I am proposing the development of a custom Oracle Stored Procedure policy under which direct table calls will be replaced with stored procedure calls. I am struggling with the best way to provide the interface, and I would like your opinion. Ultimately, I think the XML will describe the intended object model instead of the physical database structure. (Think Business or Value Objects that are passed around in a Data Access Object design pattern).

Option 1:

The procedures will accept a list of parameters that map to the object attributes. e.g. create_person (p_fname varchar2, p_lname...) The problem with this is that we have "objects" that are comprised of many attributes, close to 100 in some cases.

Option 2:

The procedures will accept object types as parameters. I have taken this approach before with weakly-typed objects (love object types as JDBC to Oracle bridges), but never in a strongly-typed way. In this approach each "object" declared in XML would get a matching Oracle type, and a stored proc to handle CRUD by passing the custom object type in/out.

Option 3:

Complete object-oriented implementation. The objects declared in XML will get matching Oracle objects, and these objects will have member functions/procs to provide CRUD operations. I have never used these object oriented features before, but they seem appropriate for providing an O/R mapping solution.

What do you feel is a good design? Do you have other suggestions? This is my chance to move SQL into the database, and I want to make a good decision.



Tom Kyte
January 28, 2004 - 7:57 am UTC

You are probably asking the wrong person as I would be intent upon moving all data oriented logic -- even much of that pesky "application logic" straight into the database -- so I can use and reuse it over and over as the languages and applications change over time.

I'll send you to, Cameron O'Rourke is more familar with what you want to do in j2ee to minimize your pain.

I would personally lean towards option #1 but I might use some SIMPLE object types (create or replace type) to hide the fact that there are 100's of attributes and use jpublisher to create the java mapping classes. I would not be using the object types in my create tables, just as complex datatypes in my stored procedures. I would be using regular old fashioned rows and columns in the database.

More about type bodies

Lisa, January 28, 2004 - 10:37 am UTC


Perhaps I wasn't exactly clear, my question, although related to Java, is actually a pure database question.

Instead of having the java developers declare in XML that there is a table called user_table with columns ssn, fname, etc,I want them to completely remove the database from their code. Currently, the framework looks at the schema and creates Java DAO's with methods that perform select by primary key, and CRUD.

Instead, I want them to express the object and the methods in XML. The framework will then generate calls to Stored Procedures to implement CRUD, and potentially any other "methods" declared (there's the app logic).

My question relates then to the Stored Procedure implementation, and an alternate object type implementation.


Option 1:

Plain vanilla stored procedures.

procedure create_user (p_ssn in varchar, p_fname in...)

Option 2:

Use object types to pass the information to the Stored Procedure.

create or replace type user_type as object
(ssn varchar2(12),
first_name varchar2(15),
last_name varchar2(25));

And a procedure such as:

procedure create_user (p_user in user_type)
insert into user_table

Option 3:

Option 3 uses just types and member functions, no packages are involved. I am not sure if type bodies are even intended to be used in this way, but my thought is as follows:

create or replace type user_type as object
(ssn varchar2(12),
first_name varchar2(15),
last_name varchar2(25),
member procedure createUser,
member procedure updateUser,
member procedure deleteUser,
member procedure getUser(p_id in varchar2)

create or replace type body user_type
member procedure createUser
insert into user_table (ssn,fname,lname)
values (self.ssn,self.first_name,self.last_name);

I am not talking about using object types to store data, simply as data carriers (Option 2), or as data carriers and method implementations (Option 3).

Is is even possible to use type bodies in the manner I suggest in Option 3?

Thanks again for your time.



Tom Kyte
January 28, 2004 - 10:49 am UTC

option 1 with some of 2 sprinkled in when it makes sense is what my answer above was.....

yes, you can do 3 as you've shown (you'd be using some static member functions for "non-default constructors") but it is not an approach I've done (or am fond of)

So when are type bodies appropriate?

Lisa, January 28, 2004 - 11:24 am UTC


Thanks for responding again.

Just curious, if object types and type body methods are not appropriate as an O/R mapping solution, when are they appropriate? (and if you are willing to elaborate, why are you not fond of this approach?)

Again, I have only ever used object types to simplify my PL/SQL to Java interfaces a la Option 2. I am interested in when you would be fond of using type bodies.



Tom Kyte
January 28, 2004 - 2:32 pm UTC

I use them from time to time in PLSQL -- self contained in PLSQL.

From a 3gl to PLSQL -- i'd rather just call stored procedures rather then get "cute" and message an object. It just seems to complicate the code -- not make it easier -- using objects across languages like that.

I don't think that these J2EE-Fanatics could build big apps.

Olga, March 05, 2004 - 4:21 am UTC

Everytime you look in a computer magazine, you could find a new word for a new very cool programming method/tool/environment which would be a revolution in application development. And everything new is very cool and everything old is something for bad old guys.

Hey, look at these old big airline reservation systems and the old banking software where financial transactions in the whole world were managed! If those old guys who developed these big, reliable applications would have been started with these new "We need EJB, XSL, XML, JSF, JSP, n-Tier, J2EE, Apache here, Eclipse there, OSS"-Blablabla they would never bring it to a production-release!

They talk about how they would save money for the company and forget about the money consuming time they spent with sticking this and that together, deploy these things there and configure this XML-file in that directory and upgrading to release, and thinkling about why the hell everything has such a bad performance... Oh! And if a problem occurs nobody knows the reason.

I don't like these "New-World"-Fanatatics and the big trouble they make. It's like modern ugly art compared to an old renaissance painture. Nothing more than hot air for idiots.


Dave Rydzewski, May 24, 2004 - 4:36 pm UTC

Hello -

I read about some of the benefits of using Stored Procedures. Specifically that you get automatic caching of the parsed SQL which is only aged out based on MAX_CURSOR setting I think.

Does anyone know if you get similiar functionality using Hibernate or Toplink? Do these technologies keep the PreparedStatements open or do they re-open them with each access?

In general, I'm just curious if the automatic cursor caching you get with Stored Procs is a major benefit over the EJB O/R mapping technologies.

It might be nice if a tool like Hibernate could interface with a PL/SQL API, but it doesn't appear you can right now.


Tom Kyte
May 24, 2004 - 6:57 pm UTC

</code> <code>

if a tool cannot do something as basic as call a stored procedure, it is not very much of a tool.

HELP!! J2EE should kill PL/SQL

Rumburak, July 23, 2004 - 11:58 am UTC



My customer plans to introduce a J2EE architecture. He will replace all pl/sql-procedures (which were responsible for implementing the business logic) against EJB somewhere on an app server.

No logic inside DB! Oh, how I hate these J2EE-guys...ARGH!!!

I know that a lot of people made bad experiences with such an architecture, much more time was needed for development and solving big performance problems.

But I need facts not only philosophy! Could you give some?

Please HELP!!

And if not here you could write me:


A good example is the OTN-Forum build on JSP: It hangs often and now , 2 minutes ago when I was writing the same message, it crashed! Oh wonderful new world!

Tom Kyte
July 23, 2004 - 4:31 pm UTC

sorry, facts historically have never ever won out over religion.

Very good OTN-Article about J2EE with PL/SQL-logic

Rumburak, October 11, 2004 - 5:38 am UTC

Hi Tom,

while you were traveling in europe OTN published for a short time a very good article about "A DATABASE-CENTRIC APPROACH TO J2EE APPLICATION DEVELOPMENT":

The article was there on the OTN-Homepage for a short while, till somebody recognized "Hey this runs against our J2EE strategy" and banned it from there. You could find it only with the following link:

</code> <code>

(you wouldn't find it with the otn-search function either!)

And now they changed the PDF with the following instruction sentence: "The opinions expressed by the author of this document are entirely his own and do not
reflect the position of Oracle Corporation."

But it reflects the position of Tom Kyte, doesn't it?



Tom Kyte
October 11, 2004 - 8:03 am UTC

I have been known to use a slide that has

"the best way to achieve database independence is to outlaw the words "select, insert, update, delete and merge" in applications outside of the database and just use stored procedures to do all of your transactions"

I'm not sure I would read too much into "it isn't on the home page anymore" -- nor that the search did not find it (i find there are many things that the search for whatever reason doesn't find)

nice article

A reader, October 11, 2004 - 8:52 am UTC

some of the things may be extreme though:
"We chose SQL statements to be the standard API from UI code to BL code. The main reason for this is that model
frameworks (such as BC4J) use SQL statements as the de facto way to communicate with the data tier. Procedure calls
are supported too, but they are not the standard way to communicate with the data tier."
They do justify the above though we have seen on this
site the advantages of choosing PL/SQL based API from UI

"· Queries
- Are always based on a single view (FROM-clause). All additional logic, join, sub-query, etc., sits in the view
- Only hold bare view columns in the SELECT-clause. All additional logic, built-in or custom stored function
calls to format values, sit in the view definition.
- Never have a WHERE-clause. VPD adds this dynamically.
This gives us the following template for a query that is embedded in UI code:
SELECT <bare column>, <bare column>, …
FROM <view>;
· DML statements
- Are always based on a view
- Always manipulate one-row
This gives us the templates in Listing 1 for DMLstatements that are embedded in UI code:
UPDATE <view> SET <column>=<value>, <column>=<value>, …
WHERE <primary key>=<value>;
WHERE <primary key>=<value>;
INSERT INTO <view> VALUES(<value>,<value>,…);
Listing 1. Templates for DML Statements
· Instead-of triggers defined on these views are then able to interpret the single view-row DML statement and
translate this into (potentially complex) DML statements against underlying database tables."

Again they are probably going extreme - no where clause?
Everything using VPD? Not sure how you can have dmls always
manupilating one row.

Tom Kyte
October 11, 2004 - 9:35 am UTC

I don't necessarily agree with everything in the paper, no.

My approach would be to use stored procedures exclusively.

VPD would be used for security only. Not as a programming technique to just add where clauses.

I would not use instead of triggers arbitrarily like that.

So, I see it as another "paradigm", but not one that I would neccessarily do/implement myself....

for me, it would be all about "stored procedures performing well defined transactions, returning result sets when needed".

package modification/recompile in J2EE and oracle Environment

sreekanth gadhiraju, October 11, 2004 - 10:45 pm UTC


We are running oracle application 11.5.8. in the previous versions of apps self service applications like Internet expeses, internet time, iReceivables are based on modplsql. So every thing from data retrival to data presentation is done using packaged procedure. But the later versions moved to some kind of j2ee framework. Now these applications use jsps to present the data. before this change we used to change our procedure for bug fixes and compile them in the database without any problem. But now when we change or recompile any package we see errors in workflow/j2ee frame work saying the package status is changed even though the package is compile and created properly, because of this nobody can access the application. Even we bounce the apache still the problem exists. To get rid of this issue we have to restart the database. Our oracle internet time still based on modplsql. We can change any package or procedure for this application and every thing works fine without restarting database or apache.

Even when we change any configuration to our applicatons we need bounce apache to see the change, even simple things like adding a new expenditure type in internet expenses requires apache restart. We opend TARs for the abouve, oracle came back telling us to bounce apache first it that does not work then bounce database.

Can you please explain why this happens.

Tom Kyte
October 12, 2004 - 7:46 am UTC

bouncing oc4j is what you needed to do. apache is just an http listener. you needed to get the java code, running in oc4j, that has statements opened in the database to "shut down the statements and get them going again"

you just zap stuff into your production system? while it is up and running? during the workday? really?

A reader, October 12, 2004 - 11:36 am UTC

I don't think apps uses oc4J. here is the info from metalink

Can Oracle9iAS Containers for J2EE be used with 9iAS
This certification is still in progress and Oracle9iAS Containers for J2EE (OC4J) is not yet certified with Oracle E-Business Suite Release 11i. This is not expected to be delivered for Oracle E-Business Suite Release 11i until at least 9iAS 9.0.4 or later.

We are on oracle applications 11.5.8 with 9ias with db vesion

any suggestions.


Tom Kyte
October 12, 2004 - 11:47 am UTC

can only say to contact support (i don't run apps myself) - if bouncing apache did not "clean it up", then you didn't bounce the apps -- they are running somewhere else, perhaps under a different apache (you need to get the jserv component 'bounced' here i see now, not oc4j).

because shutting down the client would close the connection, closing the connection makes that "go away".

so, the only thing I'm sure of is that you did not bounce the right component(s), cause killing the connection 100% 'corrects' that.

You do not need a db bounce, you need to stop and restart the clients that still have that package "open", else they will get that warning the next time they touch that package (and are probably totally BROKEN at that point since you gave them a partial lobotomy by wiping out part of their brain)

It's time for sombody who writes "THE J2EE LIE"

Rumburak, October 12, 2004 - 11:53 am UTC

Take a look at these arrogant J2EE-folks:

</code> <code>

Tom Kyte
October 12, 2004 - 1:10 pm UTC

hindsight will be 20/20 for folks, of that I'm very sure.

but whatever -- "its all about the data" if you ask me. Applications -- guess what, they come, they go, they crash, they change, they are fleeting.

The data, well, my HR data has been in a database for over 11 years now -- the applications that access (should say "have accessed in the past for they no longer exist") it come and they go and there will be many many many more in the future.

Wonderful as usual

A reader, November 01, 2004 - 9:21 pm UTC

Tom, Your site is wonderful as usual. I could not agree with you more. There are people out there that think Java and J2EE is the golden hammer going to fix all problems.

I have picked up an read a lot programming books written by these java nuts. Most of them preach design, then security/performance. I could not image going to a customer and saying "I have 20 levels of abstraction and a great design but the application is going to need 200 more application servers to perform".

I blame a lot of this hype on the college system. I have a degree in Computer Science and I did not have to take 1 course on database design. It was only after I got out of school and worked with DBAs that were willing to show me the differrence in performance did I truely understand.

I think that Java has its place and does it well. I just worked on a program that performed extremely well with a high load on a small application server. How did I accomplish this? I had a pl/sql procedure that ran at night and performed all the calculations that I needed. Then I created and tuned database views on top of those tables. Finally I hard coded my sql to read the views. Tunning all the way, the application performed with over twice the expected users with only a 5% hit to the application server.

I feel the same fustration as most of you. There is a group of people where I work that want to use Java for everything, thick clients, data access, etc. These same people feel like SQL is "old school" and going away and think that persistance frameworks are the future. You know how hard it is to tune sql generated by one of these tools, it is nearly impossible.

I just wish that people would try and understand their customers and not try and build their resumes.

an interview experience

A reader, December 08, 2004 - 3:19 pm UTC

I interviewed for a job recently; mid-level database and web development. The first technical question I was asked was about EJBs. I laughed. I said I don't know much about EJBs. Then went on to explain why, citing much of this article and related ones. His response was that it wasn't scalable (that's all I remember - I was raging inside). I realized that he was one that will probably never be persuaded otherwise (and that an interview wasn't the place to do it).

Just thought I would share.

scheduling stored procedures

Arundhuti, February 10, 2005 - 12:54 am UTC

Hi Tom,
I have created a stored procedure which is running properly but i want it to be executed automatically,after a certain period of time and repeat this act of execution after that certain period all by itself ,without my interference,Just like the materialized views which refresh automatically after certain period of time.Is there any possible way to do that?It'll be kind enough if you supply me with a suitable code.

Tom Kyte
February 10, 2005 - 1:14 am UTC


search for it on this site.
read about it in the supplied packages guide.

it is what the mv refresh uses, you can use it too.

scheduling stored procedure

Arundhuti, February 10, 2005 - 2:16 am UTC

Hey Tom,
thanks for the suggestion,it worked.
But i am stuck in another problem.Actually i want to run the stored procedure now (say at 10:00 am) ,want it to be automatically reexecuted after 14hours (i.e at 12 midnight) ,which i am able to do successfully with dbms_job package.but from the third execution i want the stored procedure to run after24 hours i.e at 12 midnight everyday and not at a 14 hours interval.
please suggest.

Tom Kyte
February 10, 2005 - 2:29 am UTC

so, you want to run it with an interval that says "next midnight"

the submit would be

( job => :n,
what => 'theproc;',
next_date => sysdate, --- right now
interval => 'trunc(sysdate)+1' --- when you evaluate that, it'll always be the
--- next midnight

Arundhuti, February 10, 2005 - 3:05 am UTC

Thank You very much tom it worked


sharda patel, February 18, 2005 - 12:02 am UTC


Typical Ask Tom

Christoph Henrici, February 26, 2005 - 12:25 pm UTC

His answer boils down to: it's a religious war .....
And he really go's to war ... bashing Java, specially bashing his faviourte bashing subject J2EE, bashing OO, bashing everything that's not running within the database.... for what is he payed for? For being helpful or for beating guys out of the line back to the "right" religion?
And like all "Religion Wars", rationality and objectivity are lost.
One reader pointed out: one has to take a pragmatic approach - i coould'nt agree more: only that a pragmatic approach cannot develop within a "emotionalized battle field": the OO Folks against the DB Folks. I have lived "both" worlds and i have seen "tremendous" failures in both worlds and have seen a couple of "success stories" in both worlds.
The reason for "success" was mainly for the people involved: pragmatic and intelligent and not for taking one side - but being open, flexible and willing to learn.
Since i am writing a DB folks forum, i just would like to point out some problems in the pl/sql area ;-)

I just am coming off a site, which has > 10000 of PL/SQL code, which after a two years life cycle is in some areas already "dead": programmers do not "dare" to change it anymore. This is certainly not PL/SQL specific problem, but some of the additional issues below, make the maintainability issue certainly critical.

There is no large market of PL/SQL Tools. It boils down to maybe two choices. The IDE's do not support refactoring in a any decent way, which makes maintanance of large PL/SQL a highly risky undertaking.

Unit Testing:
Support for Unittesting boils down one or two individual initiatives. But reality is that anybody, who has maintained any significant amount of code, knows that Unit and Regression testing is absolutely crucial for the sanity a system.

The knowledge is monopolistic. It boils down to a couple few "gurus", like Tom and Steve F.. who lead the pack. I would really appreciate more alternatives and a more decentralized and democratic knowledge pool.
And in spite of Tom and Steve, there is also not a wide spread and lived consensus of "good practices" of pl/sql coding.

Libraries and Reuse:
There is no open "market" for pl/sql libraries. About in every pl/sql site, i have worked in, they have developed a "own" layer atop of dbms_job and other oracle supplied packages. Why does every site, has to write it's own logging layer? Well Tom even advocates this in his books. But why, don't you go out write this packages and maintain them?

Nobody seriously questions the superiority of sql with regard to database operations in terms of efficiency. But unfortunately you are not likely to be able to write a application with sql solely ... so here comes pl/sql. Well pl/sql is very likely to be more efficient then other alternatives - upfront.....
But taking the above + 10000 lines of PL/SQL code, which is slowly degrading performance and efficiency is becoming a big issue. People - dba supported - are even looking at taking stuff out of the database - because of efficiency considerations.

Tom will certainly be able to tell you why .... (no, he will not be able to bash Java programers, there are'nt any around...), but he will not be able to tell you a way out..... He will also not likely to take responsiblity,
Well it's always easy to say things, without taking responsibility.....

Yes, being pragmatic is a key, but being pragmatic is not as easy and certainly not as simple, as Tom tends to see the world: from his pl/sql sandbox.

But as a recommendation to the initial question of Lisa:
Take a look at it's a j2ee based open source framework, which supports a light-weight container and does'nt dicate the use of a "heavy weight ejb based application server". It let's you nicely integrate with stored procedures in the database. j2ee != ejb or o/r mapping. I would also recommend your j2ee "friend" to read the book "j2ee without ejb" , which' s gives many insightful approaches, to a effective coexistance of db and j2ee code.

Best regards

Tom Kyte
February 26, 2005 - 2:22 pm UTC

No, I believe there are tools (many thereof) and projects (many as well)

And using the right tool for the job is paramount.

And many jobs need not the size/scale/complexity of a big J2EE environment.

...This is certainly not PL/SQL specific problem,
</quote regarding maintainability>

I totally agree, you can do things right, you can do them wrong. To me, code is code and CREATE or REPLACE PROCEDURE is code just like C, Java, VB and needs the same level of "care and feeding". Done right -- any code is in fact maintainable. Done wrong (no documention, not a comment in place, no design, no thought, no configuration management) and you have a nightmare. I think we agree on that point.

All I asked here was "please, take a look at the SQL, think "database" when building a "database" application, think sets everynow and again, thing SQL -- that is crucial to your success."

Remember, applications come, applications go, the data however -- that'll be here long after both of us are dead.

(and why cannot you unit test plsql code? there are tools for that, there are database features for doing so? stuff exists -- the only reason you wouldn't is because you don't do that in any case -- plsql isn't any different here? )

And Please, I wasn't even talking PLSQL here -- The original poster is the one that says:

"but given the large database volume, the
complete lack of SQL skills that I have seen come out of Java development to
date, and the poor legacy database design which necessitates complex SQL, I feel
that our system will absolutely not perform if I leave Java developers free to
write SQL as they wish outside the database"

and I heartily agree..

why would I take responsibility for a bad implementation? I'm very confused as to why I should?

You can do really nasty things with java, do you take responsibility for the failed implementations? Or who is supposed to?

I've actually written lots of C, even (surprise) a Java based thing or two (or more), and PLSQL.

And your last paragraph seems to certify what I was proposing -- do something light weight, use stored procedures to process transactions (it let's you nicely integrate with stored procedures in the database). But did you read the original posters question?

After preliminary talks with my new partner I know that he is loathe using any
Stored Procedures.

I've been saying the answer lies in between -- don't move it all out of the database, that would not be a good thing -- that is what was being proposed here.

I am a fan of people knowing the tools they are using -- from the database on up. It is somewhat relevant. I have issues with people writing database code that have not any idea the impact of what they are doing (or how *wrong* they are doing it -- especially with regards to being able to scale and particularly with regards to being able to maintain data integrity -- any *client* program that says "I'll enforce integrity across rows in a table OR I'll enforce integrity across objects in a database (like RI) in the application -- that doesn't have a LOCK TABLE or big select for update in it, has done it wrong (not maybe, but definitely) And I just don't see that level of understanding about how to use the database coming from someone who has be trained only in Java.

I would have said this of any language. Any architecture that says "treat the database as a black box"

Pragramatic Approaches

Jon, February 26, 2005 - 10:29 pm UTC

+1 for Christoph Henrici.

I have been on both J2EE and Database camps. I agree with Christoph that people need to be prgamatic. I have defended the database folks when the Java guys bashing SQL and stored procedures. I have also pointed out problems with ONLY database centeric approach when the database guys criticize J2EE.

In my experence, I think people who do the bashing don't have or want to know what the other sides are doing. These people are ignorance and dangerous to have in the team.

I agree with Christoph's points:
1)Maintainability - (Having over many thousand lines of PLSQL is very difficult to maintain. I say much more difficult than Java.)
2)Tools - (limited powerful and affordable database IDEs. The tools that are available (e.g. Toad, SQLNavigator, PLSQL Developer, JDeveloper) lack lots of features, such as CVS integration, refactoring, etc.
3)Unit Testing - no robost and popular unit testing framework.
4)Community - yes, only a few so called "gurus" have most of the knowledge. The so called "Oracle community" is not even close to the Java community, which have lots of resources, discussions, open-source projects. Can anyone name me 5 open-source projects for Oracle database? I can easily name 10-15 robust open-source Java projects.
5)Libraries and Reuse - the lack of published libraries and utilities are so true with Oracle database. It is a real waste of time and $ for each shops to re-invent the wheels, no matter how small they are.

No, I believe there are tools (many thereof) and projects (many as well)

Tom, can you provide us the name of these tools? What projects are you referring to? Are these projects open-source?



Tom Kyte
February 27, 2005 - 8:28 am UTC

1) why? Tell me technially HOW or WHY one "language" (which is just bytes in source code files) be more "manageable" than another?

2) they have massive CVS integration? At least JDev does -- anything you would into JDev is cvs'able -- from ddl to xml to whatever.

I use htmldb personally -- for every N projects out there, there are M that are 'small' or 'medium' (eg: won't have 10's of thousands of users with billions of transactions per second -- that online employee review system, that project tracker, that people lookup system, that asktom portal like system, that case processing system.......) whereby given the large professional staff onsite that has been doing database programming for years or decades -- SQL and PLSQL make sense.

Again Typical Ask Tom

Christoph Henrici, February 27, 2005 - 12:08 pm UTC

Typical Ask Tom:
No real answer, just the tough stance:

There is no difference in managebalilty between programming languages per se? Starting with the first, second generation languages, moving on to the third and fourth generation, covering functional lanuguages, OO languages, Procedural Languages, Scripting languages, etc. That seems like quite a big sentence, i don't really want to get into that discussion and would'nt even trust me to be able too....

But the point is not per se, but de facto, just as Jon pointed out:
The point is that pl/sql is not as maintainable as other languages, not because of the language it self, but because of the existing tools.
I do not know of a pl/sql editor, which points you to dead code, which tells if a variable is unused, points out self assignments, lets you move a procedure from one package to another, which is context sensitive on a below package level (local variables etc), which consistently tells you were public procedure or functions are used , which lets you rename a package or a procedure gracefully, etc, etc. All things which a programmer might want to do, to keep code alive. You can buy additional costly tools (Cast, XPert), but which are of douthful use for daily programming.
Now, you can take the stance that "real" programmers don't need all that fancy stuff, well heck "real" programmers don't even need compilers - at least not for syntax checking - they do everything right to beginn with... well, on background of + 10000 lines of code, even those will have some problems --- at least having to maintain the code. And that's the reality of quite a many of pl/programmers.

On this background, it does make pl/sql a significant risk for large enterprice shops. Having said all this, i don't want this to be misunderstood as veto against pl/sql per se, i actually like pl/sql and believe me: in many situations , i would really prefere to do something in pl/sql, but i tend to give it a second thought, and maybe even decide to do seemly "stupid" things, instead of using pl/sql .. blame it on me...

But maybe one more point on this area:
What i am also wary about with pl/sql is the proliferation of different conquering concepts and the fact that this leads to a "general" uncertainly about what good engineering pratices are.

Taking as example the different "type" constructs in oracle: object types, plain data types, pl-sql records. Choice is generally something great, if there is a general agreement on the criterias for choice. There is for example no general consense, when to use, for what or even if to use Object Types in pl/sql: Some would love to ban them altogether, others say there great for parameter passing, other say they really for interfacing with java, others want them as subsituation for views, for a few others it's the "real" thing.

Tom himself down plays them - and is probably right - and gives a neat example how to use them in context of logging as "little helpers" and another one, which he himself says, has been done better in Java.

The problem with this that it conveys a uncertainty about what sound engineering practices are in pl/sql and leaves it up the individual programmers, his skills and preferences, which in a + 10000 lines of code site can have truely disastraous consequences.

And again Tom will say, this is not per se a problem of pl/sql. Well i am not so sure, there seems to be quite many different forces driving the evolution of pl/sql and not all sound engineering reasoning: it is also for the average programmer not always clear, where pl/sql stops and where sql begins, which should be crystel clear, because actually pl/sql should only be a enabler of sql.

It's really not a matter of religious wars, but a matter of being a "pragramatic programmer" ;-)

Best Regards

Tom Kyte
February 27, 2005 - 12:38 pm UTC

I've just one question:

should all applications be written using the same techniques? The same language, the same architecture. Or are there at least two classes of applications and room for more than one approach?

I have many times said "there is a time and place for <X>".
I have also said "however, there is a time and a place for <Y>".
Do not see <X> as being a holy grail, as the only approach.

Have I seen incredibly successful, implemented, running, managable systems using "old fashioned techniques" and "refactored into j2ee". Oh yeah -- even though most of the staff didn't have the experience required (but hey, j2ee has "E" in it and we are an enterprise...).

In fact, I've seen successful VB, Perl, whatever language.

But time and time again, I see a programmer writing *a database application* and having not a clue about transactions, SQL, physical schema design. Recipe for disaster -- totally. I don't care what language you are talking about using.

Sorry if you take this as a non-answer, but the answer of "remove all sql into the application and outlaw plsql" is bad. And that was the crux of the original question. I was not saying "outlaw java", I was (am) saying "make sure you can keep control of your database or your 'scaling' approach with j2ee will just mean your database is nibbled to death by billions of guppies".

I do consider myself a pragmatic programmer -- when I see "all java for everything", I just know that is wrong. Just like I would not use PLSQL to write a fourier transformation.

I've seen as much bad java as bad plsql as bad VB as bad C as bad whatever. For many small to medium applications (of which there are thousands) using an environment like htmldb works very well, and allows for application development and deployment the speed of which I've never seen.

But I will still put forth that most application developers do not have the foundation in the database needed -- and would be well served by "database programmers" supplying them transactions (the data logic) in a stored procedure. The stored procedures don't have to be 10,000 lines -- just encapsulate the SQL (the correct sql, the performant sql, the sql we can change as we change the physical schema). You'd be surprised how small my plsql is behind this web site (htmldb does the heavy lifting, I just have a couple of transactional API's at the end that do "stuff" and could be in fact called by anyone)

Jon, February 28, 2005 - 10:40 pm UTC

1) why? Tell me technially HOW or WHY one "language" (which is just bytes in
source code files) be more "manageable" than another?

Tom, I am surprise with your reply. A "language" is just in "bytes" in source code files.

Let's me understand. So, you think every language have the same "manageability"? Ada, PLSQL, T-SQL, COBOL, VB, Perl, C, C++, Java, etc. have the level of "manageability"?

All the people (Java or database folks) that I have encountered know that every language is NOT created equal. Not every language has the same level of "manageability".

PLSQL and T-SQL have good "manageability" dealing with data-related (e.g. table, columns, SQL types, etc.). However, for the application non-data logic processing, PLSQL/T-SQL are hard to compare on ease of maintenance with Java.

For the non-believer, try to refractor >1000 lines of logics PLSQL and Java. Then, you will understand why people like OO.

2) they have massive CVS integration? At least JDev does -- anything you would
into JDev is cvs'able -- from ddl to xml to whatever.

As of JDev 9.0.5, it does NOT have CVS integration with PLSQL editor. It does have CVS integration with Java editor, which any modern IDE has have.

If you meant to say that JDev can CVS enabled files (SQL, DDL, etc.) that are part of the users' local file systems, then of course JDev can have CVS integration. In fact, there any modern IDE can do that.

What we need is a PLSQL editor that is integrated with CVS. I find it very common for proprietory tools (e.g. PLSQL or PeopleCode/Tool) to NOT have any built-in support for CVS or any version control system.

Based on my experience, the closest PLSQL editor that can have CVS integration is SQL Navigator (i.e. must be enabled with team coding along with CVS plug-in for VSS).

You have mentioned that you know of many tools and projects in your previous posts. Can you let us know which tools and projects so we can use Oracle more effectively?



Tom Kyte
March 01, 2005 - 8:14 am UTC

Let's me understand. So, you think every language have the same
"manageability"? Ada, PLSQL, T-SQL, COBOL, VB, Perl, C, C++, Java, etc. have
the level of "manageability"?

absolutely, it is all about the environment. I've written in Ada, PLSQL, Cobol, C, C++ (ugh), Java. They are all *just languages* -- truly, they are. And given the proper environment with the proper rigor (check in, check out, some degree of coding standards, documentation, comments, *managment*) they are all manageable.

PLSQL and T-SQL have good "manageability" dealing with data-related (e.g. table,
columns, SQL types, etc.). However, for the application non-data logic
processing, PLSQL/T-SQL are hard to compare on ease of maintenance with Java.

I utterly agree, I think I've said that -- however, the converse is true. PLSQL has good manageability dealing with that data related stuff -- Java does not. Don't write a fourier transform in PLSQL.

(or try writing the code using rigor in the first place and obviate much of the need to refactor? Isn't that a choice? Or should we constantly be taking the output of bad programmers and refactoring it? Or are you saying bad code is like death and taxes and cannot be prevented?

So, you are telling me I cannot check things in and out with jdev?

And for all of my plsql projects in the last couple of years -- it is all about htmldb

</code> <code>

Which is itself written entirely in plsql (and the programmers working on it, a databased application, have found that java was the wrong language for them in their environment)

Same select from J2EE and SQLplus

Branka, March 03, 2005 - 12:51 pm UTC

I have same select that I run from J2EE and SQLplus.
This is result from Statspack report

First one is J2EE

Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
81,853 2 40,926.5 98.4 64.42 122.19 241639263
select distinct b.bkng_id, b.fngrprnt_dt, p.pers_id, p.fbi_no, p

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
64,974 14 4,641.0 37.2 0.42 0.41 2122689601
Module: SQL*Plus
SELECT distinct b.bkng_id, b.fngrprnt_dt, p.pers_id, p.fbi_n

Selects are exect same. What can make this big difference?

I thought that Statspack report give you result what is going on in database. So it is not important it JAVA code is pure, for this particula case.

Please help me to understand diff.

Tom Kyte
March 03, 2005 - 1:01 pm UTC

what is the entire query? were there inputs to it I cannot see (bind variables)?

was this done on the same system, with the same load? (given %total time...)

same query

branka, March 03, 2005 - 1:12 pm UTC

This is query.
Bind wariables are used in bought cases.
Same sistem.
Everything same.

SELECT distinct b.bkng_id, b.fngrprnt_dt, p.pers_id, p.fbi_no, p.usms_no,
p.brth_state_cd, p.brth_cntry_cd, p.dob,
p.gender_cd, p.race_cd, p.hair_clr_cd, p.eye_clr_cd, p.height, p.weight,
ar.arrest_dt, t.trans_id, t.status_cd, b.agnt_id "book_agent_id",
b.agncy_loc_id "book_loc_id", ar.agnt_id "arrest_agent_id",
ar.agncy_loc_id "arrest_loc_id"
from bookings b, arrests ar, transactions t, persons p, names n,
names n2
where n.name_typ_cd='B' and t.trans_typ in (1,2,4,5,8,9)
and t.active_trans_fl = 'Y' and p.pers_id = n.pers_id
and p.pers_id = t.pers_id and p.pers_id = b.pers_id and b.arr_id = ar.arr_id
and p.pers_id = n2.pers_id
and upper(n2.first) like :first
and upper(n2.last) like :last
and t.curr_trans_fl = :fl
order by n.nist_name, ar.arrest_dt desc, p.dob asc

Tom Kyte
March 03, 2005 - 5:27 pm UTC

were the bind variables *the same*

same system? why the difference in %total time?


Martin Guillen, March 09, 2005 - 1:47 pm UTC

After reading this (long) thread I have get a few interesting words from the response...
"set based mindset" and "sql is about sets"
Could you elaborate on this (maybe just a couple of lines) or point to where can I read about this "way of thinking"?
Thank You,

Tom Kyte
March 09, 2005 - 3:12 pm UTC

simple example:

for x in ( select * from t )
insert into t2 values (x.c1, x.c2, .... );
end loop;


insert into t select * from t2;


for x in ( select empno from emp )
update emp set sal = sal * 1.1 + 0.10 * nvl(comm,0) where empno = x.empno;
end loop;


udpate emp set empno = al * 1.1 + 0.10 * nvl(comm,0);


for x in ( select * from stage )
update t set .... where ...
if ( sql%rowcount = 0 )
insert into t .....
end if;
end loop;


merge into t using stage on ( join... ) when matched then update ... when not matched then insert ....

Using analytics, using joins, using SQL instead of writing code.

publishing packages as J2EE session beans

Alberto Dell'Era, March 21, 2005 - 4:10 pm UTC

Are you aware of any tool to publish a package as a J2EE session bean ?

I've looked at Jpublisher (and googled and searched otn) but no joy ... but it seems to me a very natural&intuitive way to interact with pl/sql from J2EE !

TIA very much (even if you don't know)

Tom Kyte
March 22, 2005 - 11:01 am UTC

no, not even remotely.

not that I've ever looked very hard.

EJB QL and optimisation

Adrian, March 22, 2005 - 8:20 am UTC

I'm glad Alberto is back on this thread, because based on his earlier postings, he seems to have good knowledge of J2EE/Oracle environments. I have some open questions to Tom, Alberto, anyone who can answer them about EJB QL and Oracle...

Today somebody was showing me their prototype for using Container Managed Persistence (CMP) in a J2EE app, which included EJB QL as the mechanism for joining two EJBs. I have major concerns about this technology for three reasons, which should be obvious by my questions below...

1) When an EJB QL query joins two or more beans, which technology performs this join? Is it Oracle, with its many years of SQL optimisation techniques, or is the very new container hashing two memory-beans together? I'd like to think that the EJB QL is generated into a single SQL statement that is executed against the database, to make use of the CBO and statistics. But my fear is that if the Beans are already cached, then these will be joined in what can only be limited optimisation techniques.

2) If CMP is being used to "persist" a table in the J2EE layer and the underlying database table changes, does the EJB know about this ? Does it get instantly updated or does it become out of synch. with the database table ? My fear here is that we are going to shoot data-consistency and integrity to pieces. And if application logic depends on the answers provided by the data, then we might have found a gaping bug as the data represented in the J2EE layer might allow users to perform actions that the data in the database would otherwise prohibit.

3) If you have a database table in an EJB, how do you perform lookups against that Bean ? Is it via (hash) key or via a loop/binary search ?

If anyone can assist, or point me to some answers to these questions, I'd be grateful...


Tom Kyte
March 22, 2005 - 11:13 am UTC

1) generally the latter (your fears). even when they generate the sql, they seem to generate their "own join" -- a select run for every row fetched from a larger select.

2) depends on the vendor... when cached in middle tier it is usually fatal to touch the data any other way.

To Alberto

Menon, March 22, 2005 - 11:19 am UTC

Alberto, JPublisher does allow you to convert
packages into Java representation as far as I know.
Can you be a bit more specific in your question?

to Menon (and anyone interested)

Alberto Dell'Era, March 22, 2005 - 5:04 pm UTC

Menon wrote:

>Alberto, JPublisher does allow you to convert
>packages into Java representation as far as I know.
>Can you be a bit more specific in your question?

Absolutely, and thanks in advance for your interest!

The J2EE Session Bean is architecturally the same as a PL/SQL package (they are both incarnation of the Remote Procedure Call "paradigm") - it just exposes methods that can be called by clients (over the network or in the same server aka container); its Stateless variant corresponds to a Stateless Package, and its Stateful variant corresponds to to a Stateful Package (one that has at least a global variable, so that has a state).

I would like to be able to automatically generate a Session Bean that simply calls the package it was generated from; that way i could put all the Data-oriented Business Logic into the database, and use that Logic seamlessly from the J2EE environment.

Think to it as "bridge" between the J2EE front-line Developers (JSP, Servlet, fat clients) and the Database Data Developers [definition (c) 2003 by Thomas Kyte], when the formers can't call the package directly due to the mystical law "Thou shalt not access the db directly, thou shalt access only the Session Bean layer"; you could think to it also as the incarnation of the law "All Business Logic belongs to the Session+Entity beans layer", but with the Business Logic implemented in PL/SQL inside the database.

Jpublisher automatically generates a Java class that has the same methods of the package, but that class is not a Session Bean (which is composed by a Remote Interface class and a strict-sense Bean class, both implementing additional methods - eg service callbacks to interface with the J2EE container).

So it gets "close", but no "close enough"; we have the Java <--> PL/SQL mapping, but not the J2EE <--> PL/SQL one.

To Alberto

Menon, March 22, 2005 - 6:28 pm UTC

Hi Alberto
Thanx for a detailed explanation of your query.
I am afraid I am not aware of a JPublisher feature (or any other tool) that does what you are saying :( Perhaps
someone else may chime in.

PS: Btw, I enjoy your postings (both questions and answers)
here - my favorite question of yours is
your question on "write concurrency" which, I believe,
became a popular presentation by Tom :) And I think a good
question/answer was the one where you tried to graphically
sketch the PL/SQL cache and session cache :) Keep posting!!

Alberto Dell'Era, March 23, 2005 - 12:03 pm UTC

> Thanx for a detailed explanation of your query.
> I am afraid I am not aware of a JPublisher feature ..

Well, thank you for reading, detailing and clarifying the q is the duty of any question-asker (if S/He cares about being answered ;)

I will continue my quest, perhaps i will log an Enhancement Request or resort to build my own code generator, time permitting; it's both technically interesting and relevant, wish the day had more hours :)


Menon, March 23, 2005 - 12:20 pm UTC

"I will continue my quest, perhaps i will log an Enhancement Request or resort to
build my own code generator, time permitting;"

I am sure you have also considered using
the Jpublisher to geenrate classes mapping the
package to a Java class and then extending that
class to add your functionality. That could
perhaps be an intermediate solution till such
time you share your code generator at asktom!;)

Alberto Dell'Era, March 23, 2005 - 3:49 pm UTC

> I am sure you have also considered using
> the Jpublisher to geenrate classes mapping the
> package to a Java class and then extending that
> class to add your functionality.

Yes, or perhaps using Reflection to peek into the methods and then generate the required Session Bean classes.
Perhaps xdoclet may be a viable tool, too, as my java colleagues told me "at the coffee machine".
I have to examine the classes generated by Jpublisher, in order to choose ... and it's not a simple task, so it will take a bit of time :) especially since i'm not a Java guy really, i've been programming exclusively in pl/sql during the last 2 years (but last thing i wrote was actually a code generator) !

But the more i think about this, the more i love the idea.
Write in PL/SQL and then forget about the Java layer. Heaven!

effective j2ee design with oracle

Ryan Gaffuri, March 27, 2005 - 2:23 pm UTC

have you ever successfully implemented a j2ee and oracle project using EJBs? I'm not sold on Entity beans, but an approach with session beans calling database packages that contain all the sql seems like a valid approach.

what are your experiences? Alot of people don't like EJBs because they are rather complex. If you don't want to use EJBs what would you use to call the database packages that contain your sql?

Tom Kyte
March 27, 2005 - 3:20 pm UTC

I'll let others comment... not enough practical experience from this end (the java/j2ee part)

To Ryan

Menon, March 27, 2005 - 3:34 pm UTC

I have not formed any opinion on EJBs yet.
The following article/interview with Rod Johnson seems
to give a well-thought out perspective.

</code> <code>

I have heard good things about Rod's J2EE books as well
though I have not read them myself so can't comment
on their quality.

and contining the previous post..

Menon, March 27, 2005 - 3:37 pm UTC

"If you don't want to use EJBs what would you use to call the
database packages that contain your sql? "

How about the good old JDBC?

Alberto Dell'Era, March 27, 2005 - 5:30 pm UTC

>have you ever successfully implemented a j2ee and oracle project using EJBs?
>I'm not sold on Entity beans, but an approach with session beans calling
>database packages that contain all the sql seems like a valid approach.

We have implemented (and designed of course) exactly that kind of architecture for one of the customers i'm currently serving; it's one of the most visited web portals in Italy (6,000,000 registered users - about 2,000,000 have visited the portal at least once in the last year).
1,500,000 page hits/hour peak time.

The complete Architecture is quite complex, involving third-party products and access to legacy systems, but the bulk is designed the way you described.

Basically we have JSP in the front layer, (Stateless) Session Beans in the middle layer, and of course Oracle as the database.
The Session Beans call the Stored Packages by using plain-simple CallableStatements, returning ref cursors when needed. (Almost) no SQL is stored in Java, but stored in Packages instead - we have some SPs that simply OPEN a single SQL statement as a ref cursor.

We haven't used a single Entity Bean, and you may be interested in knowing that while attending a Java Conference, the Sun Education presenters told us that "it's not mandatory to use them - the fact that they exist doesn't mean that they HAVE to be used by a J2EE application" - very savvy and that's what we normally repeat to customers ;)

The main advantage of the architecture, besided performance of course, has been "division of labour" - Oracle People work on the packages, Java people call them. Both happy to do what they like the most.

Quick fix and refactoring of data-oriented code is another sound advantage. An interesting example of a common occurrence - recently i was called to fix an application that another Company (not very skilled in Oracle) had made, luckily using the same architecture we use - i was able to refactor&rewrite the SQL+PL/SQL code without touching their Java code, intelligently adding indexes after examining the SQL contained (that was the totality of the SQL generated by the application of course), etc - at light speed. That turned a doomed failure into a success.

Really, the PL/SQL+SQL layer is the only layer that gives us very few problems, and when a problem is found, it is normally solved in a matter of hours from defect detection to production deployment (last time - 1 hour).

In fact, one of the most succesfull modules, that fetches infos from the DWH and puts them into the OLTP subsystem, doesn't use Java at all - it is a quite complex PL/SQL module I've written, which works in minutes instead of the hours they foresaw given the data volumes ... since then, any critical, performance-wise, data-processing has been implemented that way, with as few Java as possible. Luckily that customer of hours is very pragmatic, not "religious".

Go for that Architecture - you will not be disappointed.

PL/SQL Forever?

Michael Friedman, March 27, 2005 - 8:38 pm UTC


Biggest issue on picking PL/SQL for new development is the widespread perception that Oracle is demphasizing PL/SQL and that we should all expect that sometime around Oracle12 or Oracle13 it will no longer be supported.

The factors that lead to this are:
a. The strong marketing push on J2EE and the relative silence on PL/SQL

b. The luke warm support for Oracle Forms and Reports

c. The semi-termination of Oracle Designer - Oracle is really stepping away from DB oriented CASE

d. The fact that many newer Oracle technologies (ie. Oracle Files, CMSDK, etc.) only have Java APIs

Frankly, I agree with you on the best approach, but I can't afford to lock into a technology that may be dead in 5 years - already been there, done that, bought the T-shirt, got screwed.

All the pretty talk in the world won't solve this problem. If Oracle wants people to believe they need to step up to the plate and deliver:

1. More marketing dollars to PL/SQL

2. Ongoing development of tools that use PL/SQL

3. Better integration of Oracle Forms and Reports with Application Server (for example, check out the Single Sign On implementation - it's ludicrous - Oracle DB passwords are stored in LDAP in plain text, RADs cannot be shared so each account needs its own RAD even if they use the same Oracle user account, etc. Why not use the proxy authentication you always go on about?)

4. Provide PL/SQL APIs for all Oracle products that have Java APIs

Tom Kyte
March 28, 2005 - 7:17 am UTC

where is that perception coming from? If the plsql guide ceased having a "whats new", I'd say maybe. But, with the advent of java -- plsql has been seeing an explosion of new features...

PLSQL, Java, O/R Mapping Tools

Jon, March 27, 2005 - 11:28 pm UTC

Hi All,

I agree with Michael that Java continues to gain greater popularity within Oracle and its products.

Compare to Java, PLSQL has many weaknesses. (See the above posts on "Pragramtic Appoaches")

Nevetheless, I think that PLSQL will not be gone within 5-10yrs (not a long shot). PLSQL has its value when working with database data, such as cursor caching, tightly coupled with SQL (e.g. anchor syntax like %type), etc.

I see more people use PLSQL less when working with other things, such as large domain logic (i.e. non-database data related processing, like certain calculation logic), presentation logic, etc.

Has anyone used O/R mapping tools, like TopLink, Hibernate, JDO, etc? These tools are very efficient and productive. However, it seems like these O/R mapping tools don't emphasize using stored procedures, which can cause scaleability issues down the road.

Like Alberto, I have used mostly stored procedures with Java. I use DAO design pattern. I create the stored procedures DAO implementation to execute the SPs. Only this SP DAO Java class has Oracle/SQL call. This architecture works out fairly well.

The drawback of this architecture is that I have to manually create the SP DAO implementation class. I would like to use O/R mapping with SP. I use SP as my database application APIs, which help to avoid many users and applications have to use SQL.

I would like to ask if anyone has used Oracle TopLink to call Oracle Stored Procedures. How will SP work with Oracle TopLink's caching?



Alberto Dell'Era, March 28, 2005 - 6:00 am UTC

> I create the stored procedures DAO implementation to execute
> the SPs. [cut] The drawback of this architecture is that I
> have to manually create the SP DAO implementation class.

If your DAO Pattern incarnation is a class, look no further than JPublisher to automatically create it ... if it's a Session Bean, we may log a "collective" Enhancement Request in the future after having researched a bit more (TopLink, looks promising, but perhaps most of what we need is already in Jpublisher 10g, i need to find the time to study it).

PL/SQL is not going to die. In 10g, as Tom has already said, they have turned the compiler into an optimizing compiler, which is a radical change - you need to invest a lot of money to make it working, something you don't do if you are going to desupport it "in 5 years".
</code> <code>

j2ee and oracle

Ryan Gaffuri, March 28, 2005 - 10:27 am UTC

Rod Johnson wrote the expert one on one book for java. It's excellent and he says you should use the features in your database unless you absolutely have to make your system database independent. He also has another one out called 'J2EE without EJBs'. Anyone read it?


Menon, March 28, 2005 - 10:41 am UTC

"you should use the features in your database unless you absolutely have to make your system database independent."
Don't know if Rod says that in so many words but the
above statement is not strictly true, in my opinion. I
would say that even if you want to make the application database-independent
(e.g. because it is known in advance that the app
is required to run against multiple databases), you
should still strive to use the database features.
Otherwise, you run the risk of making your applicaiton slower,
less scalable, less maintainable (more reinventing
the wheel of database features), and bug-ridden
(different databases are inherently different.)
Tom makes these argument very nicely in his
older expert one-on-one and its newer edition
(see sample chapters by clicking on the main page
link to apress for the newer edition.)

To do this, you can isolate your application from the database by creating a layer (or API) of software
(such as a PL/SQL layer for Oracle or a T-SQL layer
for SQL Server) and then making sure that the layers
on top of it are written to work largely independent
of the underlying database layer. But the database layer
itself should be able to implement the API fully
exploiting all the database features. Thus you
get the best of both worlds - database independence
plus all other benefits that come from exploiting
the database features.

J2EE - PL/SQL Integration

Rumburak, April 18, 2005 - 4:32 am UTC

I think, we need more published examples about integrating PL/SQL into a J2EE-environment like this one:

</code> <code>

There are a lot of people in the world telling us, that EJB- and "AppServer-Business-Logic" technology is the way we should go ("forget the database"). If I tell them, that they should use PL/SQL they can only smile and laugh. But a few days after they have gone into production they come and tell me that the database is so slow and if I could take a look on their SQL calls.

Alberto, have you published your approach somewhere? At the last German Oracle User Group (DOAG) was a presentation of a PL/SQL - J2EE-integration with PL/SQL-Object Types. Very interesting (the Powerpoint is somewhere under

A few days ago I saw something about EJB QL - Cool, they use old SQL-syntax in the simplest form to query EJBs and sell this stuff as wonderful new technology. Fascinating, I remind me that they told me years ago "Hey Rumbu, we don't need that boring SQL anymore, we have EJB pure Java getter-setter...blablabla"

Primary Key

Vamsi Krishna, June 17, 2005 - 6:10 am UTC

Hi Tom,

The Question is We all Know that the table should contain only one Primary Key but when i faced the Interview,the Interviewer argued me that there can be more Primary keys for a Table.I know that the second key is termed as Candidate Key.Instead we can say that it is also a Primary Key..... stating that there can be any number of Primary Keys(Candidates Keys)
Then what according to you is correct

With regards
Vamsi KKrishna.

Tom Kyte
June 17, 2005 - 2:59 pm UTC

A table may have one primary key.

There may be other keys as well, but you cannot have more than one primary key. (that is built into the defintion of PRIMARY key)

You can have other keys. They may be unique as well. But they are not the primary key.

It is arguing semantics at that point.

Key Constraint

Vamsi Krishna, June 17, 2005 - 6:12 am UTC

Hi Tom,

The Question is We all Know that the table should contain only one Primary Key but when i faced the Interview,the Interviewer argued me that there can be more Primary keys for a Table.I know that the second key is termed as Candidate Key.Instead we can say that it is also a Primary Key..... stating that there can be any number of Primary Keys(Candidates Keys)
Then what according to you is correct

With regards
Vamsi KKrishna.

Concerns with J2EE Applications

dav, August 08, 2005 - 4:54 pm UTC

Recently I'm involving in supporting of some J2EE applications. I've concerns that Updating records into a table was implmented as delete and then insert. Does this not impact the performance ,the table storage characterstic and index storage characterstic? Any thoughts? Please let me know

Tom Kyte
August 08, 2005 - 8:36 pm UTC

wow, that is really bad - if it is a single row update.

many things pop to mind, but first, explain more - is this every update, or just a "special one"


dav, August 12, 2005 - 2:21 pm UTC

Yes., some of the single row updates are done that way and it's done that way for all multi-record detail records. The number of detail records for a parent are not that many.

The beauty is that tables are shared across many applications and I'm wondering whether other applications will have performance impact due to this. (not sure other J2EE applications using these tables are also done the same way..)

Please let me know.

Tom Kyte
August 13, 2005 - 9:44 am UTC

The beauty is that tables ....

How does this facilitate that????? This is what databases do, it is what they do.

Doing a delete+insert when you meant to do update is *wrong* -- for a single row, it is always wrong.

For the child records, it can *sometimes* be right. If you fetch out all of the N records of a master/detail relation (locking the master record for the duration), and work on them in the client -- sometimes checking that the master wasn't modified during the timeframe you had the rows out in the client, and then deleting+inserting new is OK (easier). Depends on the business logic.

But to do it every time, in every case -- no way. No way.

let me explain

dav, August 16, 2005 - 9:54 am UTC

Yes., delete and insert is done as Update ., The beauty is that paragraph was meant to be a question to know the impact of other applications due to this delete/insert instead of update.

Tom Kyte
August 17, 2005 - 10:42 am UTC

The beauty is that paragraph was

didn't get that comment.

updates should be updates in general, doing them as delete+insert in all cases is wrong.


A reader, August 19, 2005 - 10:14 am UTC

Hi Tom,

I have a PL/SQL procedure with 3 arguments.

Two are of String types as IN parameters and the other is an
OUT parameter of PL/SQL table of records.
From the application side, I am planning to use callableStatement to process
the PL/SQL table of records in Java.
To which java.sql Object can the PL/SQL records can be assigned??

I don't know how to proceed further.
Could you please help with some suggestions???


Tom Kyte
August 20, 2005 - 4:11 pm UTC

You cannot retrieve a table of RECORDS by any language other than PLSQL.

You can use a collection of object types, but not records, records (like the other unique plsql type -- boolean) are only in PLSQL.


Raj, August 24, 2005 - 12:49 am UTC

Hi Tom,
I have a procedure which returns a refcursor based on a
temporary table into Java environment.But I am not able to
get the resultset in Java Environment.

When we return a resultset from Oracle session to Java,
Does it open a different session and different transaction??
Please clarify on this.

Tom Kyte
August 24, 2005 - 9:07 am UTC

Ha, I'll be the java program is using "the default autocommit mode" and the temporary table is a transaction based one.

That is, every stupid call from java includes a COMMIT. Turn it OFF (set the autocommit attribute off for the connection)

Can't blame the programmers

Andrew, September 28, 2005 - 1:17 am UTC

Would like to add my 5 cents worth of bragging, although the thread seems to have died out…

Antibiotics. When they were invented they were a true boon to the human kind, curing all kind of nasty bugs and providing people with much relief and happiness. However, people, and not just people but many doctors too, thought they were just great and used them as a silver bullet – bacteria, viri, heart problems, communism - everything could be magically eradicated with the mighty antibiotics. Even nowadays you get a few chemists who hand them out like lollies.

When Java came out it was really a big thing in the programming community. Almost as big as Star Trek. It wasn’t revolutionaty just because it was OO (Smalltalk was OO as well) but because it carried a promise to be the (almost) complete programming platform – front to end! I myself started off real programming on Applets (sorry TurboPascal, nobody wants you know) and really liked the idea of everything being coherent. Single paradigm, clean, resuable. After a few years of applets and just a few months of servlet programming I was, one might say luckily, pulled to the DB side of things and sort of lost track a bit with what’s happening. Otherwise I might have jumped on the bandwagon, especially since I would have really liked the idea of purism combined with portability. Only now I’m trying to look a bit deeper into EJB and I thank the Java guys I’m working with that they didn’t buy into it. Again, I can’t really blame pure Java programmers for believing that databases can be (and should be!) portable and that a single unified programming paradigm is there to rule the world, while the others should submit to. I think it’s in our human nature to be idealists and to tend to like cleanliness. Now, I do have some real blame and that’s on the people who designed the concept of EJB and handed it out to the Java community like lollies. Were they thinking that data concurrency, integrity, transaction logging, cost-bazed optimizers and set operations which were developed over the previous two decades (an era in terms of IT timelines) can be easily overcome and pushed aside with EJB persistence and EJB QL? Sure, it works if you’re working on a system when you don’t get that many users accessing the system at once. But the whole model is very very complex and Java programmers have enough on their plate, they don’t have time to worry about database related issues. And they’re written in some ugly 3GL database language too, yuck… In other words – the EJB architects, I would say pretty good theorists, didn’t quite convey that this framework should be used with a lot of caution, if at all.

Had enough in this rant session, but I may be back again with some practical examples of how set related logic (that’s SQL) can improve performance not compared to Java but compared to PL SQL!

well discussion

Tahir Akram, November 26, 2005 - 6:15 am UTC

Hi all

well this is good thread contributed by many

And as well the scalability of EJB architechture is concern that scales, b/c it consits on layered archtechture at any level of abstraction it is easy to maintain/enhance. also J2EE architechture defines the role of works so there is no any need to get war between java devlopers and dba guys. both has there own work role and must be synch 2 devlope any enterprise system.

cheers JGuys + dbGuys

Tom Kyte
November 26, 2005 - 12:31 pm UTC

... on layered archtechture at any level of abstraction it is easy to
maintain/enhance. ...

laughing out loud at that one. It is just a programming method that says "stuff it all in the middle tier please. I would hardly say "easy to maintain or enhance".

The J2EE architecture I see implemented many times defines these roles:

a) everything including the kitchen sink into the middle tier.
b) bits in the database, nothing but bits - they are bit buckets.

Java connection not closed in DB

mAg, November 29, 2005 - 12:38 pm UTC

Hi Tom

We are using Java to execute stored procedure, after executing the procedure connection has been closed from Java using close method, I am seeing the connection remain inactive in v$session. This is frusturating to the developers, some times we are getting login error ORA-00020 because of this. This happens only when developers work with stored procedure alone?

Can you throw some lights on this.


Tom Kyte
November 30, 2005 - 10:50 am UTC

give me an example, I've not seen this myself. You would want to supply versions of software and all (of course ;)

Java not disconnected

mAg, December 01, 2005 - 12:39 pm UTC

Hi Tom

Here is the information for further investigation,the connection has been establised one hour back and the procedure got executed fine, but the connection still remain. Please let me know if you need more information.

<quote> Oracle Version and Query on DB </quote>

system@ORLADMDP> select * from v$version;

Oracle9i Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for HPUX: Version - Production
NLSRTL Version - Production

Elapsed: 00:00:00.00
system@ORLADMDP> select username, machine, program,sid, serial# from v$session where username ='APP_RPM';

----------- ---------------------------------------------------------------- ------------------------------------------------ ---------- ----------
APP_RPM WORKGROUP\LAAS1 wlserver.exe 21 4055
APP_RPM WORKGROUP\LAAS1 wlserver.exe 27 6594
APP_RPM WORKGROUP\LAAS1 wlserver.exe 35 7812
APP_RPM WORKGROUP\LAAS1 wlserver.exe 49 438
APP_RPM WORKGROUP\LAAS1 wlserver.exe 60 2950
APP_RPM WORKGROUP\LAAS1 wlserver.exe 66 1816
APP_RPM WORKGROUP\LAAS1 wlserver.exe 72 2098
APP_RPM WORKGROUP\LAAS1 wlserver.exe 85 6704

<quote> Java Code for DB Connectivity: </quote>

* Created on Sep 2, 2005
* To change the template for this generated file go to
* Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments
package com.loyan.LoyanDataAccessObject;

import com.loyan.util.Constants;

* @author kmukherj
* To change the template for this generated type comment go to
* Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments

import java.sql.*;
import java.util.Locale;
import java.util.ResourceBundle;

import javax.sql.*;
import javax.naming.*;

public class DataManager {
private static ResourceBundle labels;

public static Connection getConnection() {
Connection con = null;
DataSource dsName = null;

try {

labels = ResourceBundle.getBundle("loyan_operationsConsole_web",new Locale("", ""));
//System.out.println("before in the data manager after jndictxt");
InitialContext jndiCntx = new InitialContext();
//Context ctx = (Context) jndiCntx.lookup("java:comp/env");
Context ctx = (Context) jndiCntx.lookup(labels.getString(Constants.DATABASE_CONTEXT));
//dsName = (DataSource) ctx.lookup("operationsConsole");
dsName = (DataSource) ctx.lookup(labels.getString(Constants.DATA_SOURCE_NAME));
con = dsName.getConnection();
//System.out.println("got connection");
} catch (Exception ex) {
"Exception in DataManager getConnection" + ex.toString());
return (con);
} //end of method

public DatabaseMetaData getMetaData() {
DatabaseMetaData objDBMetaDeta = null;
try {
Connection objConnection = DataManager.getConnection();
objDBMetaDeta = objConnection.getMetaData();
} catch (SQLException sqe) {
return objDBMetaDeta;


<quote> After executing the statement the connection is closed, here is the closing of DB part...</quote>

strUpdateSQl = strUpdateBuf.toString();
System.out.println("strUpdateSQL=" + strUpdateSQl);
bstatIsUpdate = objStatement.execute(strUpdateSQl);
} catch (Exception e) {
System.out.println("***********************************exception in update message="+ e.getMessage());
bstatIsUpdate = false; } finally { objConnection.close();}
return bstatIsUpdate;

Sorry for filling the page.

Consulting anothers databases

Gerson, December 02, 2005 - 2:48 pm UTC

Hi Tom, I need to read data from another databases (postgress, SqlServer) for update tables in my oracle database,i want to make this directly from a stored procedure in oracle, how i can do it???????

Thanks for your help

Tom Kyte
December 03, 2005 - 10:13 am UTC

Java not disconnected

mAg, December 05, 2005 - 4:53 am UTC

Hi Tom
This is follow up of my Question above, do you need more information on this? Please let me know what you want?


Tom Kyte
December 06, 2005 - 4:12 am UTC

I was waiting for a java guy to take a peek at this for me. He said:

It looks like it does something but I would like to know what the underlying repository is for this context stuff -- LDAP, filesystem, etc. I guess I won't dispute that it works if they say it does but having the full details (or source) would be good. Also, that DataSource they get could be any type since it's generic. It could be that this connection is not really closed because the type of DataSource uses a virtual close but still remains connected. Pooling and all that.... I'd like to know how they defined their DataSource and what naming repository they use.

so how about a self contained, 100% complete sort of example with as little "extra" as possible.

A bit of feedback from a Java developer

rakesh, December 31, 2005 - 8:24 am UTC

...and an ex-Oracle programmer looking to get back into it again!!!

The whole EJB thing has not been a resounding success. Even the Java people know this. The new EJB 3.0 spec is a great simplification confirming how difficult it was. In the ealry days you pretty much were told to use EJBs. Now its very different.

I have read all 3 of Rod Johnsons books on J2EE and he has highlighted many reasons not to use EJB.

There are, i think, 2 valid reasons to use EJB in todays apps:

1. You need distributed objects - ie more than one app server with the ability to have your objects move between them semi-transparently.
2. You need to coordinate transactions across multiple resources (ie you need 2 phase commit).

Hope that helps,


PS The post a couple of posts up looks like their is a connection pool in use. That would explain lots of identical connections. Closing the connection (when using connection pooling) only returns the connection to the pool NOT closes it.

Java and the Empowered Database - Nice Article

VK, March 16, 2006 - 1:12 pm UTC

Visit the URL below for a very nice article related to the topic of RDBMS and Java.

</code> <code>

Just came out of EJB trainning

Ramki, May 25, 2006 - 10:15 am UTC

i just came out of EBB training.

I like to know is there any successfully project done using Entity Beans & EJB QL.
I think J2EE programmers should not be blamed, people who create such J2EE spec should be blamed.
1. inventing a new query language ( re-inventing wheel )
2. Caching data in middle layer.
3. Loads every rows & every column in to memory, is called as middle layer.
4. Using set method for each column update !!! (to update 4 column, 4 sql will be generated & fired)
5. putting table name & column name in XML file !!!.
In my opinion - trying to achieve "Database independence" is equivalent to achieve "Language independence".

*Kindly read links given in above thread*

A reader, January 12, 2007 - 2:23 pm UTC


How can I declare a procedure argument of PL/SQL table type with 'DEFAULT NULL' clause?

Thanks a lot!

A reader

A reader, March 16, 2007 - 4:56 am UTC

In our New project we are using persistence framework(hybernate) along with oracle APP server.Now we are thinking whether this combination will work on ORACLE RAC setup or not.Currently we are testing on single instance.
One camp says "it will not work" another says "it will work".We don't have hardware as of now to setup this(RAC) environment to test.
But your advice/past experience in this regard will be extremly helpful.


Tom Kyte
March 17, 2007 - 2:36 pm UTC

it'll work as well as hybernate works anywhere.

Is one camp perhaps saying "hybernate won't work" - regardless of RAC.

Of course it will work.

The question is - does a hybernate 'persistence framework' scale in real life...

A reader

A reader, March 18, 2007 - 5:35 am UTC

What I am trying to get from you is whether "hybernate & oracle app server" together would work with oracle RAC db or not?? It is working perfectly on single db instance. Same database if we port on RAC instance, Is there any problem in that??

Is there any documentation/white paper available for this?

Your help is appreciated.


Tom Kyte
March 18, 2007 - 7:46 pm UTC

They will absolutely work together, I said that.

... it'll work as well as hybernate works anywhere. ......


A reader, May 02, 2007 - 6:03 pm UTC


Is this true for the benefits of migrating pl/sql to J2EE application. How would you answer each point for a manager/salesperson telling you this.

1. Legacy Constraints: Maintaining the web app in PL/SQL keeps customer tied to license fees and upgrade cycles of Oracle. Over the life cycle of the application this adds up, especially when the customer wants or needs to upgrade and finds functionality deprecated in new versions of Oracle release requiring rewrite to work around.

2. Maintenance: Moving the business logic out of PL/SQL to the mid tier allows the development team to do more pinpoint upgrades without need to disturb database to change business logic in the stored procedures

3. Performance and Scalability: As the user base and data grow, separation of BL out of database will improve it¿s performance , especially when the database is shared across several applications.

Tom Kyte
May 02, 2007 - 6:23 pm UTC

1) ask them for hard numbers on how many times they've actually switched databases. That my friend is hard work that costs $$$$

2) please do not worry about disturbing the database, the database is non-disturbable. We'll actually be able to maintain, manage and tune the code (since the java developers do not really know how to program a database anyway...) but if you move it out, we will not be able to do that!

3) hmmm, nope, don't buy it except in cases so extreme that j2ee isn't even considered for them either (they are down to the wire and very customized).

Re: J2EE

Stew Ashton, May 03, 2007 - 7:02 am UTC

Concerning point 3), I just helped a project move business logic from the middle tier back into the database: the code now runs 50 times faster. Granted, it was "bad" code that made lots of little queries instead of one big one, and it is less "bad" now. Still, it is highly likely that your J2EE developers will write this kind of procedural code, in which case the application will slow down terribly as data increases.

The overhead of any call from J2EE to the database is important: you have to go through the network (including probably a firewall), maybe parse and execute. I have seen cases in production where one user request generated hundreds of SQL queries, meaning slow response time, every time, all the time.

The worst thing is, the DBAs don't seem to get a handle on the problem because each little query taken alone may be OK. The problem is their sheer number, but you don't see that unless you use the right tools (TKPROF).

To put the data logic (yes, it is data logic and not just business logic) in the middle tier and get performance and scalability, you must:
a) use bind variables;
b) properly configure connection pooling and prepared statement caches;
c) fulfill each requirement with ONE SQL query;
d) when unable to fulfill the requirement with one query, write PL/SQL anyway.

If you do all this, you will at least avoid most performance and scalability issues, even though you won't have the other advantages of the database-centric approach such as centralised security.

Now of course I learned all this from Tom, but I have also seen it for myself in real life n-tier projects.

J2EE vs SP

Duke Ganote, May 03, 2007 - 11:16 am UTC

IMHO, this issue is fundamentally the same philosophical issue for any one who says: "I wanna write once, run anywhere (WORA)". Pilgrams to the "promised land" of Java WORA don't like the real answer, as I noted here:
The real answer requires exactly the same approach as Oracle uses to run on multiple operating systems (I'm pretty sure I remember reading this in one of Tom's books): there's a platform specific layer that decouples most of Oracle's code from the OS. Oracle has a team that tailors this OS-specific layer to the strengths of each OS.

Similarly, there should be a DBMS-specific layer on any j2ee application. This specific layer should be stored procedures stored in the database.

The related issue is "just how much code (business logic) is 'below the waterline'" (that is, in the database). Java WORAs want as much as possible 'above the waterline', while anything data-related (which is the majority of 'business logic') really should be below the waterline:


A reader, May 03, 2007 - 5:16 pm UTC


Thanks for you common sense answer for the J2EE issues. I think people are getting hung up on the buzzword than understanding the complexity of the architecture.

I would like to take your opinion on struts and MVC/Model2 architecture where they split HTML from navigation from business logic. This seems to be complex compared to one pl/sql procedure that does everything.

1. Do you agree with the folowing drawbacks of one page approach

Consequences of the single-page approach

Heavy HTML and Java coupling
The coder of the JSP file must be both a page designer and a Java developer. The result is often either terrible Java code or an ugly page, or sometimes both.

Java and JavaScript blur
As the pages become larger, there can be a tendency to implement some JavaScript. When the JavaScript appears in a page, the script can get confused with the Java code. An example of a possible point of confusion is using client-side JavaScript to validate the email field.

Embedded flow logic
To understand the entire flow of the application, you have to navigate all of the pages. Imagine the spaghetti logic on a 100-page Web site.

Debugging difficulties
In addition to being ugly to look at, HTML tags, Java code, and JavaScript code all in one page makes it difficult to debug problems.

Tight coupling
Changes to business logic or data means possibly touching every page involved.

Visually, in large pages, this type of coding looks messy. When I was doing Microsoft ASP development, I would commonly see 1000-line pages. Even with syntax coloring, it was still difficult to read and understand.

2. What architecture do you recommend in doing using Pl/SQL. WOuld you use something like PSP or stored procedures but you break them in to several pieces. For example, let us say you have one html page that gets poulated dynamically by database. How many procedures you create?

Tom Kyte
May 04, 2007 - 12:49 pm UTC


A reader, April 29, 2008 - 10:54 pm UTC


1. what is the differene between "application logic" and "business logic". can you show an example and how can you tell if the logic is application or business?

2. would Oracle application framework or java server faces used to create a j2ee application. it seems those are drag and drop technology for UI and not diffcult to use. any comments.
Tom Kyte
April 30, 2008 - 10:04 am UTC

1) depends on who you ask (and I'd say application logic is a subset of business logic, data logic being the other half of business logic). To me, it is pretty simple:

application logic example: when the total sales is less then total expenditures, please print this message in uppercase red text "SELL MORE STUFF NOW"

data logic: total_sales is a number(20,2). total_sales must be not null. total_sales must be greater than or equal to zero.

2) drag and drop to create a pretty screen is easy. Making a functional, useful application is difficult. Making a functional, useful, CORRECT application takes skill and practice. Regardless of the environment you program in. You may use any 'drag and drop' you want - whichever one you like best


A reader, May 05, 2008 - 7:10 pm UTC


So you are saying that the statment should be coded outside the database "if total expenditures > total sales sell more stuff now".

application logic = something outside of the database.

Trying to see how you determine that this is "application" and this is "business" logic.

2. also why do you think application logic should be outside the database? we use pl/sql and stored procedures. what is the problem with that.

Tom Kyte
May 06, 2008 - 1:10 am UTC

No, that isn't what I said - I said the 'message' on the screen should be printed in red when that condition exists. formatting, display - that is outside of the database.

things that don't make sense in the database - the color 'red' - doesn't exist in the database, it is a user interface thing.

2) you can write an application in plsql - while that 'runs in the database', it is quite easy to see that is the client itself - it isn't really "in the database", it is just code that happens to be in the database.

think conceptually here - what is the CLIENT code, versus what is the "code in the database that regardless of what client accesses the data - will be executed"

Distributed Systems

Emad Kehail, September 02, 2008 - 8:45 am UTC

Hello Tom,

Our systems currently running using Oracle Forms and Reports 6i against Oracle Database. We have a range of 500 concurrent sessions in normal working days and around 2000 concurrent sessions in peak working days, and we are planning to move to n-tier architecture and have all the applications to be in web environment.

My manager is totally convinced to implement the new systems as "Distributed Systems", where the middle tier will do the business logic. For example, instead of calculating taxes on the database tier, the records will be fetched to the middle tier, calculation done there, then sent back to the database.
He claims this will off load the database and help to scale more.

I am totally against this. I believe in
1) The data processing MUST be done at the database tier using Packages and Stored Procedures, the middle tier is for the application logic... not data.

2) If there is load on the database and I need to create a load balancing for Oracle, then I can go for RAC.

What do you think regarding this??

Tom Kyte
September 02, 2008 - 12:00 pm UTC

this might surprise you - but....

given the example of calculating taxes - if it involves a ton of logic, it can make sense to do it in a program. You would invoke a procedure to get a result set, you would manipulate the data, and invoke a stored procedure to process the transaction against the data in the database.

What I'm saying is "it depends". Data integrity logic - Security logic - that should 100% be in the database. True, really honestly true, business logic (which is a smallish subset of all processing) can be done in the application itself.


A reader, September 02, 2008 - 3:05 pm UTC

What is "ton" of logic? What is the problem of doing it directly in the database? What can be the advantage of doing it in the middle tier? To be honest - this is the most amaizing answer from you for the last 7 years taht I have been reding your answers...
Tom Kyte
September 02, 2008 - 4:02 pm UTC

good question, what is a "ton"

the only answer is "it depends". If the tax calculation could be done simply:

select c.customer_address, 
       c.order#, c.sales_price, 
       <b>c.sales_price * (1+t.tax_rate) total</b>
  from customer c, tax_rates t
 where c.customer = :x
   and c.state_cd = t.state_cd;

then quite obviously it would be "not smart" to do it in a client, just use sql. If the tax_rate is a complex formula requiring for whatever reason procedural logic - AND the only place this tax rate is really needed is in this module - then doing it in the application would be fine.

follow my mantra:

* You should do it in a single SQL statement if at all possible.
* If you cannot do it in a single SQL statement, then do it in PL/SQL (but as little PL/SQL as possible!).
* If you cannot do it in PL/SQL (due to some missing feature like listing the files in a directory), try Java
* If you cannot do it in Java (not low level enough), do it in C


A reader, September 02, 2008 - 5:54 pm UTC

But I thought the questioner was asking about scalability, not complexity.

I'm dealing with a legacy application that right now that does a lot of processing outside the database by pulling data out the middle tear, modifying the rows, then putting the rows right back. In some cases the modifications were as simple as the your example above. The developers thought they were getting a more scalable solution that way -- They could always add more servers in the middle tear. I'm even seeing joins done that way in this code; It gets a set of rows back (maybe 2000 rows), and then in a loop fetches related rows from another table.

The application is hideously slow because of this architecture.

I've read enough of your site (and books) to know you can't be suggesting that building an application this way scales better. Are you suggesting that the tax rate calculation could be to complex for PL/SQL, so it maybe it belongs in Java?

More explaination

Emad Kehail, September 04, 2008 - 3:51 pm UTC

The last feedback in this thread is almost what I am talking about.
I am talking about re developing a complete set of university systems; Registration, HR and Payroll, Order Entry, Financial System...etc

Imagine the Registration System pulling a classes data "How many open seats for such a class; and at which days and time it is available...etc" Imagine all of this is pulled to the middle tier.. processed through procedural logic "for loop and so on"..

Would not it be much more better to have all of this done on the database.. stored procedure with implicit or explicit cursors..and the middle tier invoke this procedure..

1) Do not I lose a lot of features when I process data on the middle tier .. features such as query optimization and how Oracle CBO build the execution plan with the help of statistics

2) Would not it be much more easy to scale the load of the database using RAC instead of adding more machines to the middle tier "or even tons of it as you have said in your first comment in this thread"

3) Now we are using Oracle Forms and Reports 6i...we might go now for J2EE or .Net... and after 10 years from now.. God only knows what we will be using as front end... would not it be much better to have the code; code that process data; at the database side.. then whatever front end I am using will just call this code.. "stored procedures and packages"..

Kindly asking for your contribution Tom.. as well as the rest of the readers of this site..

Tom Kyte
September 04, 2008 - 4:37 pm UTC

... Would not it be much more better to have all of this done on the database.. ..

it is as I said "it depends". Can you easily compute "how many open seats" there are - or does it take 5,000 lines of procedural code for whatever reason.

The only answer I can give is "use common sense, do not rule out anything, use stored procedures when it makes sense (quite often) and use procedural code outside of the database when you need to (less often then you think)

See my mantra above, that is about all I can say - there is no single black and white answer.

Generic SQL vs Oracle-Specific SQL

A reader, November 20, 2008 - 7:59 pm UTC

What would be some good examples to demonstrate that an Oracle-specific SQL can outperform a generic SQL? Do you have any specific examples? Thank you.
Tom Kyte
November 24, 2008 - 1:32 pm UTC

think analytics
think connect by
think full outer joins
think partitioned outer joins
think function based indexes
think partitioning
think index organized tables
think bitmap indexes

Here is an interesting example - compute a median value by some group. Query 1, analytics. Query 2, almost ansi sql (we'd have to change decode into CASE)

ops$tkyte%ORA10GR2> create table emp
  2  as
  3  select mod(level,4)deptno, dbms_random.value( 100, 1000 )sal
  4    from dual
  5  connect by level <= 3500
  6  /

Table created.

Elapsed: 00:00:00.21
ops$tkyte%ORA10GR2> select deptno,
  2         count(*),
  3         percentile_cont(0.5) within group (order by sal) med
  4    from emp
  5   group by deptno
  6  /

    DEPTNO   COUNT(*)        MED
---------- ---------- ----------
         0        875 553.277886
         1        875 555.319889
         2        875 554.016651
         3        875 551.510361

Elapsed: 00:00:00.02
ops$tkyte%ORA10GR2> SELECT deptno, AVG(DISTINCT sal)
  2    FROM  (SELECT cp1.deptno, CP1.sal
  3             FROM emp CP1, emp CP2
  4            where cp1.deptno = cp2.deptno
  5            GROUP BY cp1.deptno, CP1.sal
  6           HAVING SUM(DECODE(CP1.sal, CP2.sal, 1, 0)) >=
  7                          ABS(SUM(SIGN(CP1.sal - CP2.sal))))
  8   group by deptno
  9  /

---------- ----------------
         0       553.277886
         1       555.319889
         2       554.016651
         3       551.510361

Elapsed: 00:00:04.19

or another I used in expert one on one Oracle years ago:

So, as you can see ¿ we were able to compute a RUNNING_TOTAL for the entire query. We did this by using the entire ordered result set via ¿SUM(SAL) OVER (ORDER BY DEPTNO, ENAME)¿. We were also able to compute a running total within each department ¿ a total that would be reset at the beginning of the next department. The PARTITION BY DEPTNO in that SUM(SAL) caused that to happen, we specified a partitioning clause in the query to break the data up into groups. Just to see the data with a sequence number within each partition ¿ we also added in a SEQ column. It shows the position of the row within a partition ¿ the same partition we used to compute the department total. That lets me see that SCOTT was the "4th" row in department 20 when ordered by ENAME. This ROW_NUMBER() feature will have many uses elsewhere as we will see later ¿ not just to display the fact that SCOTT is the 4th row ¿ but to transpose or pivot result sets.

As you can see ¿ if you are a pretty good SQL query developer now ¿ this new set of functionality holds some pretty exciting possibilities. It opens up a whole new way of looking at the data. It will remove tons of procedural code and complex/inefficient queries you would have had to develop yourself to achieve the same result. Just to give you a flavor of how efficient this can be over the old ¿pure relational ways¿ we can see how the above query might work on 1,000 rows instead of just 14 rows performance wise. We¿ll test using both the new analytical functions and the ¿old¿ relational methods. The table I used is an EMP look alike:

ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2> drop table t;
ops$tkyte%ORA10GR2> create table t
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2> select object_name ename,
ops$tkyte%ORA10GR2>        mod(object_id,50) deptno,
ops$tkyte%ORA10GR2>        object_id sal
ops$tkyte%ORA10GR2>   from all_objects
ops$tkyte%ORA10GR2>  where rownum <= 10000
ops$tkyte%ORA10GR2> /
ops$tkyte%ORA10GR2> create index t_idx on t(deptno,ename);
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2> set timing on
ops$tkyte%ORA10GR2> set autotrace traceonly statistics
ops$tkyte%ORA10GR2> SELECT ename, deptno, sal,
  2         SUM(sal) OVER
  3                (ORDER BY deptno, ename) running_total,
  4         SUM(sal) OVER
  5                (PARTITION BY deptno
  6                 ORDER BY ename) department_total,
  7         row_number() OVER
  8                (PARTITION BY deptno
  9                 ORDER BY ename  ) seq
 10  FROM t emp
 11  order by deptno, ename
 12  /

10000 rows selected.

Elapsed: 00:00:00.18

          4  recursive calls
          0  db block gets
         93  consistent gets
          0  physical reads
          0  redo size
     510290  bytes sent via SQL*Net to client
       7725  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

ops$tkyte%ORA10GR2> select ename, deptno, sal,
  2        (select sum(sal)
  3           from t e2
  4          where e2.deptno < emp.deptno
  5             or (e2.deptno = emp.deptno and e2.ename <= emp.ename )
  6        ) running_total,
  7        ( select sum(sal)
  8            from t e3
  9           where e3.deptno = emp.deptno
 10             and e3.ename <= emp.ename
 11        ) department_total,
 12        ( select count(ename)
 13            from t e3
 14           where e3.deptno = emp.deptno
 15             and e3.ename <= emp.ename
 16        ) seq
 17    from t emp
 18  order by deptno, ename
 19  /

10000 rows selected.

Elapsed: 00:00:21.89

         13  recursive calls
          0  db block gets
    1318913  consistent gets
          0  physical reads
          0  redo size
     510242  bytes sent via SQL*Net to client
       7725  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

ops$tkyte%ORA10GR2> set autotrace off

A reader, November 24, 2008 - 2:11 pm UTC

but...isn't analytics part of ANSI SQL now?
Tom Kyte
November 24, 2008 - 7:38 pm UTC

part of the draft I believe - not sure if it was 'ratified'.

But, show me another database that has the breadth of analytics....

Better yet, show me a database 'independent' tool that uses them.

Generic SQL vs Oracle-Specific SQL

A reader, November 24, 2008 - 6:30 pm UTC

think analytics
think connect by
think full outer joins
think partitioned outer joins
think function based indexes
think partitioning
think index organized tables
think bitmap indexes

The last 4 items have nothing to do with SQL though. Can't I still write ANSI-compliant SQLs while at the same time delivering a physical schema that utilizes Oracle-specific features?

Tom Kyte
November 24, 2008 - 7:56 pm UTC

sure they do - they are Oracle specific features following the keyword 'create', features that people say "oh, we cannot use those, they are not database independent"

You could write 'ansi compliant sql' while delivering a physical schema that utilizes oracle-specific features but...

frankly, the people in the mindset of 'database independence' never will even consider opening the documentation, let alone think about performance, lot alone consider using a vendor specific feature.

Stored Procedures

Branka Bowman, January 05, 2009 - 1:25 pm UTC


I got this answer from Java Developer, as why we will not use Oracle Stored Procedure. Can you please give me your opinion?


"I have run an inspection on your stored procedure via JDBC metadata examine process and looks like Oracle is confused to deliver complete procedure Metadata via JDBC.
This is major problem for coding.
Little bit of changes in the Procedure signature, development tools (e.g. eclipse) cannot do auto refactoring. For refactoring Metadata palys important role. If it is about one or two programs, then you can do it manually. But it¿s not possible at all when the project is big.

Procedure parameters are position dependent and this against the object oriented coding approach. To simulate object oriented style in your stored procedure, it has to be written different way. Object oriented approach is more about maintainability than runtime efficiency. When the runtime performance loss is not noticeable by user, maintainability wins always. Loss of runtime performance is covered up by increasing machine horse power. From Java, one can use OCI calls to make faster SQL calls. But why are we not supposed to use OCI via JNI, because of maintainability issue. SOA introduces many translation layers and reduces the performance little bit. But, Seamless data binding, Seamless integration and interoperability are very important factors not only for external service consumers and also important for the program module within the project.

Out of sync info and finding the bug if something changes are the main consideration in the project like us and Metadata plays a major role in that. If metadata is not exposed properly by the Vendor product, all development tools are meaningless (e.g eclipse) and they will become as good as writing code using note pad.

Tom Kyte
January 05, 2009 - 1:50 pm UTC

... I have run an inspection on your stored procedure via JDBC metadata examine
process and looks like Oracle is confused to deliver complete procedure
Metadata via JDBC....

not sure what they are trying to say there. The entire set of metadata about stored procedures is completely and totally exposed.

... Procedure parameters are position dependent and this against the object
oriented coding approach....

100% false, tell java programmer to read documentation.

... Object oriented approach is more
about maintainability than runtime efficiency. ...

again, not really true - but actually makes a point backwards, against O-O if it were.

... From Java, one can
use OCI calls to make faster SQL calls. ...

faster than WHAT? come on - SQL is what the database does - OCI is not going to make your select or insert work faster. And guess what - what do you think is "closer to the metal, with less layers" - Java+JDBC or - plsql which is sort of burned into the engine itself?

I like how in the same paragraph, they state "performance is secondary" and then "and we perform faster because we have OCI". PLSQL has OCI on steroids, it has better OCI, it is inside OCI, it is all of the way down there.

This is a good reason to not let java programmers have select/insert/update/delete privileges. On any tables.

Faster OCI in Java?

Martin Vajsar, January 05, 2009 - 2:30 pm UTC

Regarding the last review, I think that the sentence

"From Java, one can use OCI calls to make faster SQL calls."

might have been meant to say

"From Java, one can use OCI calls to make calls that are faster than JDBC thin driver calls. (So that Java doesn't perform all that bad calling the database.)"

I'm just guessing, however. Maybe I'm giving them too much benefit of the doubt.
Tom Kyte
January 05, 2009 - 2:47 pm UTC

... "From Java, one can use OCI calls to make calls that are faster than JDBC thin driver calls. (So that Java doesn't perform all that bad calling the database.)" ...

and since about 10g (years ago) the thin versus thick drivers are pretty equivalent speed wise and going JNI to OCI directly would do nothing - eg: it is still a "not relevant point"

you would use the thick drivers to achieve 100% functionality (not everything is available via the thin drivers) and I cannot imagine using OCI directly from Java - the jdbc mapping is going to perform about the same.

Stored Procedures from JAVA

Branka Bowman, February 09, 2009 - 5:03 pm UTC

Stored procedure approach is approved, but now we run into problem with stored procedure parameters. Java expert told me that parameters cannot be called by name, and number and position of parameters cannot change, because JAVA need ¿signature¿, and if something change in that ¿signature¿, than they have to make a lot of changes. Solution that they suggested is to use Oracle_type and to put all input parameters into type. We are in development phase, and some new parameters will probably need to be added.
Is it only approach?

Tom Kyte
February 09, 2009 - 7:45 pm UTC

java EXPERT might be expert in java, but knows not much about calling stored procedures.

You can certainly prepare the statement:

"begin p( parameter_x => ? ); end;"

no clue what he is talking about.

re: Stored Procedures from JAVA

Stew Ashton, February 10, 2009 - 4:10 am UTC

I think "Java expert" wants to let the stored procedure change its signature without having to change the calling code in Java. I suppose the reason behind this is to allow more independence between the development teams.

Well, suppose the stored procedure adds an input parameter that it really needs to do its job. Won't the calling code in Java have to provide a value for that new parameter? "Java expert" has only solved half the problem.

If the purpose is to let development teams advance at different rates, here are a few ideas to be used separately or together:

1) Have a data access layer in Java so the stored procedure is called from one place (or a few places at most). Then Java programmers will not have to make "a lot" of changes.

2) Use packages, which allow multiple signatures.
create or replace package testsig as
procedure mysig(parm1 varchar2);
procedure mysig(parm1 varchar2, parm2 varchar2);
package testsig Compiled.
create or replace package body testsig as
procedure mysig(parm1 varchar2) is
dbms_output.put_line('Called mysig with one parameter');
procedure mysig(parm1 varchar2, parm2 varchar2) is
dbms_output.put_line('Called mysig with two parameters');
package body testsig Compiled.
set serveroutput on

call testsig.mysig('1')

call testsig.mysig('1') succeeded.
Called mysig with one parameter

call testsig.mysig('1','2')

call testsig.mysig('1','2') succeeded.
Called mysig with two parameters
Now the "old" signature can contain the "old" code, or might even call the "new" signature if it knew how to pass the right values.

Whatever you do, if you change a public API, you have to coordinate with the developers who call that API.
Tom Kyte
February 10, 2009 - 7:09 am UTC

... Won't the calling code in Java have to provide a value for that new parameter? "Java expert" has only solved half the problem. ...

No, not if they either

a) add it at the end of the parameter list and use positional calling notation in their code

b) add it anywhere they want if they are using named notation in their code


c) supply a default value for it.

Good point !

Stew Ashton, February 10, 2009 - 8:34 am UTC

Indeed, yet another way that PL/SQL procedures limit the pain of changing APIs.

This subject should go into the write-up I'm supposed to do for my company on how to fit stored procedures into our architecture (as they say, in the land of the blind the one-eyed man is king).

Stored Procedures from JAVA

Branka Bowman, February 10, 2009 - 2:04 pm UTC

When you use JPUB, you mast have "signature". That is what I am told.
Tom Kyte
February 11, 2009 - 10:12 am UTC

but you know what, if you do in fact change the signature, don't you think that perhaps the invoking code *must be inspected* - can the invoking code utilize this procedure still - might it actually have to change.

Think about this - if the inputs/outputs actually change, isn't that a rather *dramatic* change to the interfacing code.

And besides, jpub spits out the code for you - so I'd ask "so what", redeploy.

and if you feel a tool is getting in the way of doing things right - what should you do?

a) abandon the right way
b) change tools, change the way you use the tool, live within the confines of the tool



A reader, May 27, 2009 - 1:39 pm UTC

It appears that this Java vs. PL/SQL debate has been going on since 2003. The folow up that I add is this:

If PL/SQL is a viable technology for the inplementation of business logic, rendering multi-tier Java application servers unnecessary, then why did Oracle buy BEA and Sun?

I believe Oracle's recent acquisitions have in effect resolved this debate squarely in favor of the Java camp. Oracle's purchase of the WebLogic application server and Java itself as well as their on-going investment in "middleware" is an admission that database centric solutions are simply not sufficient for the kind of large enterprise customers that Oracle courts.

Unfortunately there exists a camp of PL/SQL programmers and DBAs who deperately want to believe that the solutions they created for small to medium size businesses can be scaled up to national and international enterprises. They can't.

PL/SQL is a proprietary Oracle solution. I can only suggest to the PL/SQL programmers out there that when the proprietor of your means to a livelihood is looking further afield, maybe it is time for you to do the same.

Tom Kyte
May 27, 2009 - 3:52 pm UTC

If PL/SQL is a viable technology for the inplementation of business logic, rendering multi-tier Java application servers unnecessary, then why did Oracle buy BEA and Sun?

Do you think there is one way to do everything? Really?

Is there a single 'best' way?

Does the fact that multiple ways to do something exist render all but one approaches "stupid"? (no, obviously)

I believe Oracle's recent acquisitions have in effect resolved this debate squarely in favor of the Java camp.

what are you smoking there? Let's see, we have this sqldeveloper thing - it is written in java, but you know what it enabled the development of? (hint: it ain't java). What about APEX?

Unfortunately there exists a camp of PL/SQL programmers and DBAs who deperately want to believe that the solutions they created for small to medium size businesses can be scaled up to national and international enterprises. They can't.

Ok, now I know you are being silly. Trolling is what it is typically called. Have fun with that.

When you present some technical evidence - proof points - something concrete - come on back.

Why do we still continue to enhance plsql - if it is in fact "dead" as you say.

Why did we in 10g Release 1 strip the functionality of Enterprise Manager (this tool written in pure java) entirely out of Enterprise Manager and place it squarely in...... well - the database. Enterprise Manager tuning and diagnostic features - those are all big old calls to..... stored procedures. None of the "smarts" about the database are actually in Enterprise Manager. Enterprise Manager draws pretty pictures - stored procedures and database code does the real heavy lifting there.

The discussion about "plsql vs java" - which can be easily extrapolated to "language A versus language B" dates back to the point in time at which two programming languages simultaneously existed for the first time (eg: long ago).

And I've no doubt that it is not every going to end.

some people are calling java a dead, legacy language - do you believe them? Maybe you ought to start learning php or ruby (two other languages we offer special features and support for) - or (something else we support completely - if you get into visual studio, it is so tight in there you cannot even tell you are developing against and oracle database)

When there is a single right answer for every question - then maybe your train of thought holds a drop or two of water. Until then - the acquisition of BEA (an application server, which lots of people buy and hence makes money...) for example doesn't mean "abandon plsql"


A reader, May 28, 2009 - 10:59 am UTC

"Unfortunately there exists a camp of PL/SQL programmers and DBAs who deperately want to believe that the solutions they created for small to medium size businesses can be scaled up to national and international enterprises. They can't."

This implies there are no database centric applications for national and international enterprises which I believe is false.
Tom Kyte
May 28, 2009 - 2:02 pm UTC

c/which I believe is false. /which I know is false. /

I spend far too much time in sqlplus :)

Data Logic needs to be coined

Galen Boyer, May 28, 2009 - 8:08 pm UTC

There is that term "business logic" which, in all conversations I've had in my 15 years in the industry, has no clear definition. The java architect says, I want business logic in the middle-tier. I say, what do you label business logic? He does not have an answer but he is sure that the term has a definition. So, then I come back with let us have 2 terms, Business Logic and Data Logic. Then, one can say, anything having to do with data should reside in the database and anything having to do with non-data related logic can be put somewhere else. Yeah, I know, it is still difficult to truly define, but I'm convinced the database camp needs their own term which can be on the same plane as Business Logic. This arrogant java crowd somehow coins a term (I just assume the J2EE crowd coined it, but at a minimum, they adopted it with zeal) that then lets them go off and do such horrible deeds as architect EJB's and joins in java and row by row by cell by cell, sql hidden in tons of unknown .xml files, sql generated by some OR layer's runtime creation of, completely ignoring the dependency tree from Oracle, not understanding the power of the compiler, creating schemas from object models, removing RI because the business logic tier is taking care of it, ... That one term, Business Logic, put the tier power into the hands of hundreds of architects and very few of them wielded it with the kind of precise care it demanded. That term is one of the most dangerous terms I've experience yet.

The term I'm trying to coin to turn the tide back in the favor of sanity, in every conversation I have, is Data Logic. Anybody want to help me get the word out?
Tom Kyte
May 29, 2009 - 8:29 am UTC

I have been calling it "data logic" for many years.

It wasn't J2EE, before J2EE there was Corba, before Corba there was Green Screen and so on...

In many cases, in most cases, in my experience -

business logic = visualization, end user interaction.

data logic = everything else.

Galen Boyer

Sokrates, May 29, 2009 - 11:56 am UTC

The real issue behind "database independence"

Victim, December 09, 2009 - 3:00 am UTC

Why do java guys promote "database independence"?

Here's my answer (based on several projects):

In almost all cases the issue is not really about database independence. The java guys are absolutely not interested in database independence by itself.

The real issue is about database ignorance.

For many, many java guys it seems so that the holy grail of database independence is the perfect justification for their database ignorance.

Their reasoning is as follows:
"To achieve database independence we have to put everything into java, the database is just a bit bucket.
And since we have everything in java (and nothing in the db), we only have to know java. Mission accomplished!"

I've seen a funny case where some long-time java advocates of database independence changed there minds within seconds, when they realized that promoting database dependence (in this case mySQL) helped them to push through the architecture of "put everything into java", so that "we don't have to know about the database".

Btw, the same applies to the .NET dogmatics as well.

Tom, do you agree with with diagnosis?
Tom Kyte
December 10, 2009 - 1:26 pm UTC

in a word


I'm working on the 2nd edition of Expert Oracle Database Architecture, I just wrote pretty much the same thing yesterday in chapter 1.

Another thought

Victim, December 11, 2009 - 6:05 am UTC

It's nice to know that i'm not alone ;-)

In this debates with the OO guys (database independance/hibernate etc.) the human or psychological aspects are at least as important as the technical issues and it's surely advisable to know about the non-technical but crucial motives of your opponent --- well, we should be partners rather than opponents, but unfortunately that's the state of affairs ...

In my experience, the (conscious or not) feelings of the OO guys seem to be:

"Java/.NET is the (only) technology i know (and want to know about) and it's cool and trendy. Now, if that technology is the only one that gets seriously used then i'm important, my opinion will be respected by the superiors etc. etc. If the database plays an important role as well then i'm not as important as i could be, i won't be the master of the universe etc."

Of course, database guys are humans too and will perhaps have similiar thoughts, but for projects where the data is central (quite a lot of projects), it seems to me that the technical arguments of the db guys (irrespective of their subjective motives) are generally much more in line with the objective necessities of the project than those of the java (or GUI) guys.
Tom Kyte
December 11, 2009 - 7:48 am UTC

... , but for projects where the data is central (quite a lot of projects),...

tell me about a project whereby that isn't true.

.net is becoming almost passe in the year 2009
java - so huge, most people cannot fathom it
ruby, php, python, apex - really big and growing.

I've seen so many "last language ever", "best way of programming ever", "if you do this you'll never have to write code again"...

I remember reading near the end of the 1980's that by the year 2000 there would be no more 'coders', all useful code would be written and we'd have libraries of components from which all new software would derive. Hah.

When I began programming - the environment was:

database (ims, vsam, sql/ds was the new kid on the block)
CICS (transaction manager)
ISPF green screen panels on a 3278 terminal.

Today the environment is

application server (transaction manager)
browser (the equivalent of a 3278 terminal in many respects, before you say it has WIMP (windows-icons-mouse-pointers) and other stuff - let me tell you - so did my 3278 terminal believe it or not. Heck, I even had color)

My my my, how simply far we have come in the last 20 years.

mfz, December 12, 2009 - 7:26 am UTC

Here is my experience .. When I started my career in 1993, it was PowerBuilder / Visual Basic Era ( Client Server Applications).
We wrote lot of business logic in the application code .

Guess what ,as PowerBuilder is not in the radar now , almost all the applications are ( in the process ) migrated to J2EE / .NET apps .
If we would had the business logic in the database , then it would have been easy migration ( just a facelift) .

Now . again the business rules are written in middle tier ( the developers / management did not learn for the mistakes) .

From my perspective , UIs have gone thru cyclical life cycle

MainFrame --> Client Server --> J2EE / Browser ( Browser is another form of dumb terminals ) -> Cloud ( MainFrame in disguise)

But the database is database .

Should have read this one first

Ted, January 06, 2010 - 9:48 pm UTC

I wish I had found this question and answer(s) first when I was dealing with my own "who owns the application logic" issues. Great real-world info.

The rest are my own opinions on this topic...

One thing young software developers likely do not have experience with is maintaining their own code. This seems esspecially true for consultants who parachute into a development project, write some code and move on to the next contract. They are often unaware of the production issues they may have left behind.

Most of us who have had to either maintain our own code or someone elses have learned, the hard way, that digging business rules/logic out of legacy code - a.k.a. reverse-engineering - is painful and time consuming. When your trying to fix a problem, you do not have days or weeks but often minutes or hours.

On the other hand, many of us have been able to port our databases not only between different operating systems but between different database vendor products, when needed.

Time will show if Java will become the next Cobol - a language that worked very with the OS processes and files but required a hierarchical database design embedded within hundreds of seperate programs (ever look at how many directories and files a Java "Project" requires?) Instead of a hierarchical database I guess it would have an Object Oriented database embedded. I suspect this will be as rigid and difficult to maintain as the Cobol ones turned out to be. "Those you do not learn from History..."

Personally, I can do alot more faster with something like APEX. My boss - who is pro-PHP/3GL and pro-Java - came to my office today telling me how much the users like my latest APEX-based Survey Builder app. My clients are happy, my Boss is happy and the data is being caputured with about 5-10 "web pages" (instead of dozens of disk files) that I really did not need to code - I focused on the SQL; the important stuff.

I have done some coding with Java - I wrote an application that can read emails from an MS Exchange mail account - including attachments - and save them into an Oracle Database (which is kinda tricky if you've had to work with MS Exchange) - so I think I'm qualified to talk about this. Java is definitely better than the alternative 3GLs but it is still a 3GL and not the complete solution.


Samo, January 03, 2011 - 3:45 am UTC


This is one of my favourite threads on asktom! I found this thread in 2006 and I'm comming back every now and then to check whether something new is written.

A few days ago one of my friends pointed out that getters and setters are defacto standard how attribute values are retrieved from a java object. Things get interesting from here on...

If I remember correctly from my university days, there are basically three axioms which
java programming relies upon. Those are:
1.) Encapsulation
2.) Polimorphism
3.) Inheritance

Now let's start a mathematical induction. If we assume that following statements are true:
1.) the encapsulation axiom
2.) getters and setters as the defacto standard
3.) everything in java is object,

we can deduct that every object in java is comprised of:
1.) attributes
2.) methods (a constructor, getters and setters, other methods)

Because of all that "the purest of the pure" java code uses methods to access data from objects. Based upon postulate 3. (everything in java is object), we can replace the term
"object" with a term "DataSource". DataSource is object which defines methods of its own and is an abstraction of another object - the "Database" object. The Database should (or better, MUST) use methods of its own - at least getters and setters. Ergo: stored procedure with refcursor out parameter can be regarded as a getter and a stored procedure with insert/update/delete statement is a setter.

Now we can conclude, that a statement "we want to access tables directly with Hibernate" directly violates above proof.

So why is it again that java guys don't want to use stored procedures and want to access data directly but absolutely refuse to do so with their own java objects? Maybe there is a more subtle, psychological reason? Java guys probably have a gut feeling that a database is a heart of any system. And because of that, a person who is closest to a database has to be the most important person for the whole system. Someone from other than java church just doesn't add up, does it?

But I know... Mathematics historicaly didn't win over the religion either :).

A reader, February 25, 2011 - 3:59 pm UTC

java is not that great!

database independence revisited

Mark, March 15, 2011 - 9:53 am UTC


I've been thinking about the ol' database independence arguments lately. The argument about different concurrency models was always the most striking argument I'd use against pro-database-independence folks ("Umm, they don't even work the same. Watch this!") I'm wondering if it's less relevant these days.

What I mean by that is multi-version concurrency is now supported by Oracle, SQL Server, PostgreSQL and MySQL with InnoDB. (Basically most of the major players - not sure about DB2.) Do you think the "different concurrency models" argument is becoming "less true"? I MIGHT be wrong, and please correct me if so, but I believe logically the aforementioned databases' concurrency models all LOGICALLY function the same, even if they don't always work the same under the hood (e.g. Oracle/MySQL use multi-version read consistency while PostgreSQL uses multi-version timestamp ordering).

I understand there's still the "you should actually use the database's features" argument, and the "applications come and go" argument, but I'm wondering about the "different concurrency" argument. Is it becoming less and less relevant as database concurrency models converge?

Your opinion please? Thank you...
Tom Kyte
March 15, 2011 - 10:44 am UTC

... What I mean by that is multi-version concurrency ...

sql servers implementation has serious limitations - first and foremost, not many people are using it. Most sql server databases run in the default isolation level like they always have been. And multi-versioning does NOT imply read consistency. Oracle was multi-versioning in version 3. Oracle was read consistent in version 4.

And there are still many nuances out there. For example, what would happen in sql server with the following code:

create table p ( x int primary key );
create table c ( x references p );

insert into p values ( 1 );
insert into p values ( 2 );


insert into c values ( 1 );

IN ANOTHER SESSION --> delete from p where x = 2;

I know what happens lockwise in Oracle - how about sql server? Innodb, postgressql?

If you cannot answer immediately, you cannot write database independent code. And if the answers are different - you cannot write truly database independent code.

What happens if you issue insert /*+ append */ into T select * from t2. In Oracle a very specific set of operations take place - and certain locks are involved.

What happens in sql server when you get "too many" row locks? We are back to page level locking...

And so on

many differences.

You'll get an error.

Shannon Severance, March 15, 2011 - 11:27 am UTC

For example, what would happen in sql server with the following code:

create table p ( x int primary key );
create table c ( x references p );

insert into p values ( 1 );
insert into p values ( 2 );


insert into c values ( 1 );

I don't have a copy of SQL Server handy to check, but I believe you'll get an error at the COMMIT because you are missing the BEGIN TRANSACTION statement.
Tom Kyte
March 15, 2011 - 1:12 pm UTC

well, no you wouldn't. committing when you don't have a transaction isn't an error.

but - see what I mean :)

and you don't necessarily need a begin trans anymore if I'm not mistaken.

Alexander, March 15, 2011 - 1:29 pm UTC

Running those insert in SQLServer commits magically for you. I just took a class last week, the instructor was pointing out how nice it is you don't have to type 'commit' all the time like Oracle.

Then a little later he made a mistake and updated the wrong data in his example. Whoops, can't roll back. If that had been a real database with real data....He didn't see the irony either but I kept my mouth shut.
Tom Kyte
March 15, 2011 - 2:07 pm UTC

ops$tkyte%ORA11GR2> show autocommit
autocommit OFF
ops$tkyte%ORA11GR2> set autocommit
SP2-0281: autocommit missing set option

and if you wanted - you can do it in SQLPlus if you like - it would just be a dangerously stupid thing to want to do.

and SQL Server does have a setting in the database (not just in their sqlplus like tools) to correctly behave transactionally - it just isn't enabled by default

I would have asked the instructor how they were liking that autocommit now... Not because I'm an Oraacle guy - but just because it would be fun :)

Alexander, March 15, 2011 - 2:15 pm UTC

Yeah I know we can do that if we want, but like you said why would you want to. I knew they could turn that off too, but the fact that it's default...

I think I was already building the reputation as an Oracle snob (self proclaimed too) when they were covering this great new feature called 'merge'. Then a developer in the front row asked if Oracle had it, and the instructor paused and hemmed and hawed a little until finally I spoke up and pointed out they had it for "a long time, like 2002". I just searched your site real quick and took the first thread date for merge.


Mark, March 16, 2011 - 9:05 am UTC

Thanks for your insight Tom. I think I will have to delve into this subject deeper...

why java

A reader, November 23, 2011 - 1:00 pm UTC

Great thread. Using this thread to express my frustration with java. I have been using Java about 4 years now. I hate Java/J2EE.
Why ?
1. Java language itself is not that bad, but frameworks around that are very intimidating and frustrating.
2. Majority of java shops tend to implement overly complex solution for the simple problem.
3. Frameworks supposed to improve productivity but I find that most of the time we spend debugging xml, run time exceptions etc instead of working on actual business problem -Just try to use hibernate for couple of simple joined tables, you will find whether it improves productivity or creates headache.
4. In the name of patterns/OO design, java people tend to create tons of files, interfaces, layers, xmls which not only confuses developers but also creates build nightmare.
5. Java does not have any new features that other languages lack. In fact, most java code is not even object oriented.
list goes on...I think this whole java framework thing is a fantasy and no real benefits.
More frustrating thing is that, there are more job postings are for Java/J2EE than other technologies. Now,Oracle may push java even further. Trend seems to be towards java/j2ee than stored procedures.

It's 2015 and we are still there...

Yelena, May 05, 2015 - 8:52 pm UTC

I check this thread periodically after dealing with java guys to release steam. One of feature I "enjoy" when I help with application code - multiple duplicate SQL statements. If you look what's executed in the database you'll see repeating SQL statements all with the same parameters over and over again. In one of my assignments I put logging of all database calls (fortunately application architecture was correct - all access to database was by calling PL/SQL packaged functions). After several days of running regular work I grabbed log data and showed them to app developers. They were stunned to find how many times they executed the same database calls with the same parameters. These repeated calls were in thousands in a small application with few users. I was lucky: app team was really good - they acknowledged the problem, rolled their sleeves and started fixing their code using my logs to check their progress. Bottom line - java code can create problems for itself and for database but not all java developers are bad guys. Sometimes they need a bit of help provided they understand they need help. I guess I was very lucky.

Joju, September 04, 2019 - 1:19 pm UTC

Hi Tom,

I have an application that is written mostly in plsql. Only the presentation logic is written in java. Now we have a requirement to change our application so that it supports multiple databases - oracle,mysql,postgre. Which of the below approach do you think is the right way

1) Convert the already written pl/sql procedures to java and use ANSI sql so that it will work in all databases

2) Try to write similar logic in the procedural languages of other Databases ( don't know if such language exists for other DB)
Chris Saxon
September 05, 2019 - 8:32 am UTC

I go for option:

3) Don't try and make your application database-agnostic! :)

But assuming that's a battle you've already lost...

I would tend to option 2. But it depends on exactly which databases you intend to use, their support for stored procedures generally and the features you currently use specifically.

It may be worth exploring using ORDS to expose your PL/SQL as REST endpoints. And seeing how you can do this in other databases you plan to support.

Joju, September 27, 2019 - 10:34 am UTC

Hi Chris,

If you follow option 1, you will create an application that will have performance issues even though it will be generic and work in all three db. Is this the reason why you don't favour option 1.
Chris Saxon
September 27, 2019 - 3:00 pm UTC

Performance is one consideration. But a key thing to remember is you already have a working application using PL/SQL.

For any non-trivial app, throwing all this away to rewrite it in Java is a big undertaking. In my experience, major rewrites like this:

- Take forever
- Never fully replace the original code

This isn't a PL/SQL vs. Java thing. It's taking any application and trying to convert to a different language.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library