Skip to Main Content
  • Questions
  • Business Logic - PL/SQL Vs Java - Reg

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 29, 2001 - 10:17 am UTC

Last updated: February 16, 2012 - 7:22 am UTC

Version: 8.1.7.0

Viewed 10K+ times! This question is

You Asked

Hi Tom

With oracle 9i where do u suggest to write the business logic
PL/SQL or Java.

Which is faster/flexible/portable.
Is oracle going to support PL/SQL in future.


Regards












and Tom said...

PLSQL isn't going away -- PLSQL was greatly added to in 9i.

For example -- native compilation was added -- turned plsql into object code that runs natively on the OS.


I prefer to put all logic that deals with data in PLSQL. There is no more natural language to interact with SQL data then PLSQL. None.

Rating

  (92 ratings)

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

Comments

A. Vijayan, July 02, 2001 - 6:08 am UTC

We have done this in our project and this will help us inform our clinet that we have given one of the best methods.

PL/SQL vs. Java

John, February 12, 2003 - 8:20 pm UTC

Tom,

You said you prefer to put business logical that deal with data in PL/SQL.

Could you be more specific in term of what kind of data and how much in term of quantity, what kind of transactions, etc. It will be nice you can give some examples.


Tom Kyte
February 12, 2003 - 9:54 pm UTC

if it touches data -- plsql.

If it is computing a fourier transformation -- java.

If it is processing data -- plsql.

If it is generating a graph -- java.

If it is doing a transaction of any size, shape or form against data -- plsql.



what about EJB's?

Dev, February 13, 2003 - 9:16 am UTC

Hey Tom,
I thought EJB's were created for the very purpose of storing business logic that interacts with data.
Any pointers on when to use EJB's vs PL/SQL ? I know you prefer to have everything in the database as far as possible but any occasion in which you would recommend using EJB's ?

Thanks for the great work keep it up.

Regards Dev

Tom Kyte
February 13, 2003 - 9:42 am UTC

Me, recommend a bean, for doing things to data... hmmm

As long as the bean does not cache the data outside of the database (else data integrity is moot)

As long as the bean does not assume "only my bean logic does things to this data (else why bother with a database)

but many times both points are violated - the java encapsulation logic caches and assumes it owns the data -- making the data really not useful to any other programming paradigm, of which there are still others out there.

In the end it is up to you -- but I will point you to this "interesting" article:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6692296628899 <code>

I only published that one as a warning to others (since I never really answered their un-answerable question)

9i pl/sql

mo, February 13, 2003 - 10:21 am UTC

Tom:

<For example -- native compilation was added -- turned plsql into object code
that runs natively on the OS>

You mean I can run pl/sql like a unix shell script. Does not it need pl/sql engine? do you run sql*plus and then the code like I used to do before from unix or not?

2. Is there a good summay of the new 9i plsql features anywhere?

Thank you


Tom Kyte
February 13, 2003 - 10:40 am UTC

No, that is not what it means at all. It means that when Oracle runs plsql it can either

o interpret the pcode as it did forever before
o let the os run object code

It needs the database, the plsql engine, etc. It is not a compiler to build plsql programs outside of the database.


2) goto my homepage. click on tidbits and howtos -- goto the bottom of that, i have various presentations

Or -- we have this neat document entitled "Oracle9i New Features" -- it is on otn under documention for the database ;)



native

mo, February 13, 2003 - 10:53 am UTC

Oh is see like compiling java into bytecode that any o/s can interperet?

you compile pl/sql into o/s object code and then o/s will understand it?

Tom Kyte
February 13, 2003 - 4:49 pm UTC

we translate PLSQL into C, the C compiler on the system compiles it into a shared library (dll on windoze) and we call that compiled code.

SQL in Java

Sean, February 13, 2003 - 4:05 pm UTC

Hi Tom,

This topic is very helpful to us. Are you saying that ideally there should be no select and DML statement in Java or VB?

Sean


Tom Kyte
February 13, 2003 - 5:33 pm UTC

In *my* view of a perfect world -- yes ;)

It is an opinion, grounded in experience, but an opinion none the less. There are quite a few people with different opinions on that topic out there.

adding Object-oriented flavor into PLSQL

Jianhui(Frank), February 14, 2003 - 11:45 am UTC

Tom,
I'm personally a strong dictator of puting business logics into the plsql code for the same reasons you mentioned here.
I strongly oppose the object-oriented database or object-relational database model. The reason is there is really no such OODB or ORDB models. The relational model is very good for the OLTP system to manipulate the data that need to be stored. The OO model is wonderful for applications which are the objects in the memory and talking to each others, but not good at how to store&share the data into the persistent database. There are just two different things and have their own applications. Because the data should be shared and seen by the users that's what database is all about. Although some DB vendors including Oracle add some object flavor into the relational db, but those really aren't the essense of the OO. For example, you cant make SQL object-oriented and take advantage of the encapsulatioin, polymorsims, inheritance. So i would say the OO is good for application analysis, design and programming (OOA/OOD/OOP). But relational model is good to provide the views of data to multiple users by perciving data as tables(relations), and relational operations(algebra) against these tables(relations).
OO is good but is not almighty, it has it's onwn applications. I havnt seen any OO database models have the beauty of the relational model(simplicity, solid methmatic basis, easy to use). We still need to do the research of the OO model for database, and SQL is not a OO at all.
As for OLAP applications, the relational model isnt good enough in my oppionion. Since the fact tables have huge redundancy on the demention columns. For example, a simple sales table (month, region, sales). for 12 months period, 100 regions, we have to stored 12*100 redundant data of the months and regions. Imagine we have more demensions, d1*d2*d3*...*dn redundant data for only ONE column real sales data we care. The overhead is singnifacant!!! we may use 90% of the table just for these redundant data. Because it's relational model!!! If we use multi-dementional model, we just need to store d1+d2+d3+...+dn , drastically reduce the redundancy and increase the performance by reducing the amount of data we need to scan.
All above i said just for a point which is everything has it's own limitation and application.
BTW, I have a suggestion for Oracle, which is adding some OO flavor to PLSQL and let's call it PLSQL++. So i would create objects in the PLSQL just like java or c++, inside of the methods of the objects, it's still the tradional PL + SQL which are powerful to manipulate the data. But you overall plsql application is OO and will take the advantage of OO. I would like to see my PLSQL++ code look like this:

public class OrderProcess {
...
public void addOrder (int OrderId, int customerId, ...){
INSERT INTO order (orderId, customerId)
VALUES (....)

}
So we can use it as following code:
OrderProcess op = new OrderProcess();
op.addOrder(OrderId, customerId, ...)

I hope Oracle10i would have PLSQL++ implemented!!! But the DB itself is still based on the relational model!!! Until the computer scientists build a solid OO-DB model, we still can see the long life of the relational model in the market.



Tom Kyte
February 14, 2003 - 8:29 pm UTC

Guess what -- your wish is our command -- we do that.  It is called "Object Types" in oracle.

ops$tkyte@ORA920> create or replace type OrderProcess
  2  as object
  3  (       orderId int,
  4          customerId int,
  5
  6          member procedure addOrder
  7  )
  8  /

Type created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace type body orderProcess
  2  as
  3
  4  member procedure addOrder
  5  is
  6  begin
  7          dbms_output.put_line( 'insert ' || self.orderId || ' here..' );
  8  end;
  9
 10  end;
 11  /

Type body created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2          x orderProcess;
  3  begin
  4          x := orderProcess( 11, 532 );
  5          x.addOrder;
  6  end;
  7  /
insert 11 here..

PL/SQL procedure successfully completed.




it already exists.  polymorphism, inheritance, encapsulation -- it is all there.  No curly braces, but hey, you cannot have it all.

This is where I find the OR stuff useful actually - in making PLSQL easier to code in some cases. 

adding Object-oriented flavor into PLSQL

steve, March 28, 2003 - 10:24 am UTC

Hi Tom,

You said " it already exists. polymorphism, inheritance, encapsulation -- it is all there.
No curly braces, but hey, you cannot have it all."

Could you please give me some example for polymorphism (not static overload), inheritance of Oracle Object?

Thanks!

Steve



Tom Kyte
March 29, 2003 - 10:25 am UTC

We'll start off with the spec of the PARENT type.  We'll have
a function that is inherited, one we will overload and another
that we will override


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace type parent_type
  2  as
  3  object
  4  (       x int,
  5
  6      member function demo_inherited return varchar2,
  7          member function demo_overload( x int ) return varchar2,
  8      member function demo_override return varchar2
  9  )
 10  NOT FINAL
 11  /

Type created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue

=================================================================

The body is trivial, just shows what function is actually being
called at runtime.  When we invoke a method, it will be clear
what method is really getting run


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace type body parent_type
  2  as
  3
  4  member function demo_inherited return varchar2
  5  is
  6  begin
  7          return 'I am defined in parent_type';
  8  end;
  9
 10  member function demo_overload( x int ) return varchar2
 11  is
 12  begin
 13          return 'I am parent_type with a number ' || x;
 14  end;
 15
 16  member function demo_override return varchar2
 17  is
 18  begin
 19          return 'I am parent_type';
 20  end;
 21
 22  end;
 23  /

Type body created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue

=================================================================

Now, lets create the subclass type.  It will inherit, override
and overload from its parent


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace type subclass_type
  2  under parent_type
  3  ( member function demo_overload( x date ) return varchar2,
  4    OVERRIDING member function demo_override return varchar2
  5  )
  6  /

Type created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue

=================================================================

And its type body now...


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace type body subclass_type
  2  as
  3
  4  member function demo_overload( x date) return varchar2
  5  is
  6  begin
  7          return 'I am subclass_type with a date ' || x;
  8  end;
  9
 10  OVERRIDING member function demo_override return varchar2
 11  is
 12  begin
 13          return 'I am subclass_type';
 14  end;
 15
 16  end;
 17  /

Type body created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue

=================================================================

We'll use this procedure to see polymorphism at work in PLSQL


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace procedure demo_proc( p_object in parent_type )
  2  as
  3  begin
  4          dbms_output.put_line( p_object.demo_inherited() );
  5          dbms_output.put_line( p_object.demo_overload(55) );
  6          dbms_output.put_line( p_object.demo_override() );
  7  end;
  8  /

Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue

=================================================================

Note the procedures that get invoked via a DIRECT dispatch on the
instance variable as well as a dispatch via the procedure above (which
thinks its working on a parent_type object all of the time)


ops$tkyte@ORA920.US.ORACLE.COM> declare
  2          l_parent_type parent_type := parent_type(0);
  3          l_subclass_type subclass_type := subclass_type(0);
  4  begin
  5          dbms_output.put_line( 'parent_type Direct Call...' );
  6          dbms_output.put_line( l_parent_type.demo_inherited() );
  7          dbms_output.put_line( l_parent_type.demo_overload(55) );
  8          dbms_output.put_line( l_parent_type.demo_override() );
  9          dbms_output.put_line( 'Via procedure...' );
 10          demo_proc(l_parent_type);
 11
 12
 13          dbms_output.put_line( 'subclass_type Direct Call...' );
 14          dbms_output.put_line( l_subclass_type.demo_inherited() );
 15          dbms_output.put_line( l_subclass_type.demo_overload(55) );
 16          dbms_output.put_line( l_subclass_type.demo_overload(sysdate) );
 17          dbms_output.put_line( l_subclass_type.demo_override() );
 18          dbms_output.put_line( 'Via procedure...' );
 19          demo_proc(l_subclass_type);
 20  end;
 21  /
parent_type Direct Call...
I am defined in parent_type
I am parent_type with a number 55
I am parent_type
Via procedure...
I am defined in parent_type
I am parent_type with a number 55
I am parent_type
subclass_type Direct Call...
I am defined in parent_type
I am parent_type with a number 55
I am subclass_type with a date 29-MAR-03
I am subclass_type
Via procedure...
I am defined in parent_type
I am parent_type with a number 55
I am subclass_type

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM>
 

BC4J and E-Business Suite

Rasin, March 30, 2003 - 12:43 am UTC

Dear Mr. Tom,
In my 4 years of IT experience
I've seen many applications which wrote all their business logic in PL/SQL and are running successfully till now.

I was with Oracle Financials for 1 year, and saw each bit of business logic stored in DB (PL/SQL) and called from front end, although the front end was Oracle Forms.

According to Oracle's new statement
</code> http://otn.oracle.com/products/jdev/htdocs/bc4j/bc4jsod.html <code>

I think they are doing everything on middle tier with BC4J and version 13 of E-Business suite will be fully J2EE based with 9iAS.

Can you put some light on the above.

Business logic which is nothing but business rules for the organisation are ultimately the validations which we write.
Here is what I do
do validations with triggers or in stored procedures.
repeat the validation on the middle tier by calling the stored procedures or directly executing sql and also on the client side (values entered are in proper format or not)with javascript if using browser or in applications either VB or Forms.

Why do I repeat validations on the middle tier or the application client ?
why because you need to tell the user of the application what he did wrong, not just displaying annoying messages like "Something went wrong with your data entry please chek it"

I want to know whether I am right, repeating validation logic on each tier ? Or just validate it at the database level.

Also what is your opinion about implementing business rules. How does you go about writing it.

Thanks
Rasin


Tom Kyte
March 30, 2003 - 8:24 am UTC

do you think that middle tier programming is the end game? The last of the last? The final programming paradigm?

If not, you need to put security and data integrity logic right next to the data in the database. Else when next great programming paradigm comes along, you'll be ripping it out yet again from the applications and redoing it all over again.

If you do believe it is the final programming paradigm, I've got a bridge in Brooklyn....

I did'nt mean it is the final programming paradigm

Rasin, March 30, 2003 - 10:50 am UTC

I am sorry Mr. Tom you've taken my views the other way.

>I want to know whether I am right, repeating validation logic on each tier ? Or
just validate it at the database level.

I know that i am wrong but not this way

>If you do believe it is the final programming paradigm, I've got a bridge in Brooklyn....

I beleive that security logic after implementing at the DB level is also repeated at the application level, FGAC an exception to this.
I create screens and menus (either web pages or application forms) based on users permissions and he/she should not be allowed to see the screens which he/she does'nt have permission.

BTW
Oracle applications is a huge product, do you think that oracle choose BC4J just to promote its J2EE products ?



Tom Kyte
March 30, 2003 - 10:57 am UTC

Don't get me wrong -- I'm agreeing with you.

You must do it at the database level, whether you want to do it for UI convienence at the middle tier is up to you -- there are pros and cons for doing it. My point is -- you have no choice but to do it at the database level, in the middle tier, go for it if it makes your application more usable.

BC4J is a framework -- J2EE is a standard. We have products that conform to the J2EE standard -- BC4J is just a framework on top of that to speed development.

Oracle invented BC4J (a framework).
We implemented it on top of J2EE (a standard).
so we can rapidly and reliable develop data based applications (apps)

now we have PL/SQL++

jianhui, March 31, 2003 - 8:45 pm UTC

Thanks Tom for pointing it out, now i'm gonna implement lots of my PL/SQL code by adding the OO flavor just like i have the new language PL/SQL++.

OO is good for analysis, design and programming. But i still dont see it's good for data storage where the relational model is so graceful to map the logic view of the data into table and connect the relationship by values instead of by pointers.

If you mean OR is object oriented programing(plsql+type) plus relational model, i think it's good methodology. I have no idea and dont see the necessaty of using OO model to store the data, actually we need a new language other than SQL to do this. SQL was designed for the relational algebra like projection, selection, join, etc. Hard to imagine usging sql to run any type of OO database.

Tom Kyte
March 31, 2003 - 9:11 pm UTC

if you have my book "expert one on one Oracle" -- read my chapter on OR features. we are in agreement here.

A reader, April 02, 2003 - 12:37 pm UTC

I need polymorphism with out inheritance
is it possible ?

Tom Kyte
April 02, 2003 - 12:59 pm UTC

and how would that work? what do you mean? polymorphism without inheritance does not compute to me.

I wonder how to store OOP data

iamweng, April 08, 2003 - 5:47 am UTC

I have saw the OOP character in Oracle 9i before, But I have not understood how they are stored in ORACLE, and how to tune them. For an Instance, I can create an abstract OOP type and an object table, then save all his child objects and sub-child objects in this table. That is to say , I can store all the data in one table from OOP view. However, Is it feasible?

Tom Kyte
April 08, 2003 - 7:52 am UTC

if you have my book "Expert One On One Oracle" (i know it is in china -- i have a chinese copy, not that I can read it or anything...) I do a chapter on HOW these things are stored.

In short -- not much different then if you did it yourself. We are a relational database -- we have object RELATIONAL extensions. They are stored in relational tables. For example, A nested table is just a parent child table in disguise.

more questons on what to put on middle tier versus database

A reader, June 20, 2003 - 1:23 pm UTC

Tom
Please keep up the great work!!

Q1. "As long as the bean does not cache the data outside of the database (else data integrity is moot)"

a. Could you elaborate how data integrity may be compromised
by a middle tier cache?
b. Does this mean that one should not ever cache
anything in the middle tier?
c. What are the precautions one should take
and when would you think a cache of database data is
justified/recommended?

Q2. An argument is that since middle tier runs on
cheaper hardware, for smaller business to scale in
a cost effective manner) we can move some of the
logic to middle tier. These could scale based on
appropriate load balancers.

a. What are your thoughts?
b. According to you what business logic(if any at all)
should be retained in middle tier ?

Thank you!

Tom Kyte
June 20, 2003 - 5:41 pm UTC

q1a) go into sqlplus and update the data after the bean retrieved it. now what?

using that bean means "forget the fact you have an open database -- you cannot use it, you can only use these beans over here for if you were to update the data via some other method (like SQL), we will overwrite it later -- poof, by by data"

b) if you ask me...... besides static reference data (lookups), no I do not believe you should.

c) static read only reference data....


q2a) why is it cheaper? I sort of thought a cpu cycle was a cpu cycle?

I put application logic in an application. Data rules and Data processing in the database. Unless an application developer can tell me what ACID means, why it is important, and how the database does it -- I'm not sure I want them owning my data in that fashion, much much rather have it protected and managed in the database where I know it is being used and maintained correctly.



re: more questons on what to put on middle tier versus database

A reader, June 23, 2003 - 12:23 pm UTC

Thank you for your help as always!!

"go into sqlplus and update the data after the bean retrieved it. now what? "

I guess most developers would scream - hey you cant do
that - we dont guarantee that if you start updating
tables from sqlplus it would not screw things up!!
Also, what if you bypass the plsql procedures (That do
updates etc) and run a simple update (which for
example does not do some of the checks that the plsql procedure did?) I have a feeling you would claim that
a user would not have the privilege to do this in
the first place? What I am saying is that if you start
mucking around in sqlplus you are toast anyways regardless
of whether your logic is in plsql or not, is nt it?

"why is it cheaper? I sort of thought a cpu cycle was a cpu cycle?"

well, I guess we are talking about web farms with
appropriate load balancers - versus RAC. I am not
sure about the cost involved but I am told that the
former is much cheaper (in terms of expertise involved,
ease of attaching/detaching from the system/
maintenance etc). The above solution is being
proposed for smaller deployments where the customer
wants a cheaper solution.

Thank you once again for the great service that you
are doing to the Oracle community!!


Tom Kyte
June 23, 2003 - 1:42 pm UTC

No, the developers are going to use some new programming paradigm -- they own the data, why can't they update it? If you want, use a PLSQL routine to update the data -- does the same "bad cache thing".

My point is -- you start "beaning the data" with caching and all of a sudden you no longer have an open system with a SQL RDBMS -- you have bean access -- period, zippo, thats all she wrote.

So, will this bean thing be "the last programming paradigm"? (don't even go there...)

Lets see, one professionally managed database machine, or a "farm" -- which would I rather have?

If you have the need for a farm, you need farmers. You've just moved the goalposts.

Did you ask this question of a database guy or an app server developer? Different perspectives might bring about different answers. Not saying I have the right answer for you -- just that my answer comes based on my background -- where does theirs come from?

Most importantly, hugely important....
Why would a small deployment need anything above and beyond a SINGLE server? Asktom is bigger then most small deployments here. I do about 30-50,000 transactions a day, servicing about 4-6,000 users a day, 15-20,000 a week. About 1,000,000 transactions a month.

I've a rather small (old) machine running a database, mod_plsql and apache. Spare Cycles (and I am definitely not the only application on here -- promedmail.org, oai.oracle.com and others are all hosted on this same machine, same database, same apache).

You can squeeze quite a bit of juice out of this fruit, if you do it right. App server farms? I see them being used for systems that need nothing bigger then my desktop PC -- managed by an army of farmers. cheaper?



A reader, June 23, 2003 - 12:26 pm UTC

"I put application logic in an application. Data rules and Data processing in the database."

What is the boundary of application logic and data rules?
What is an "application logic" according to you?
If I am selecting rows from the database, doing some transformation with it and displaying it, which portions
of the logic are "application logic" out of these steps?

Is application logic only about the "ui" portion - meaning
serving up jsp etc in case of a web application?

thank you!

Tom Kyte
June 23, 2003 - 1:45 pm UTC

Application Logic = print numbers below 50 in red, between 51 and 100 in green, above 100 in black.

Data Logic = numbers must be between 0 and 1000

for example.


selecting rows and transforming it for display -- depends on the transformation. I find SQL to be alot more efficient then most lovingly hand crafted, developed code so I tend to do it there -- but that is somewhere between the data and the application (eg: the query could be in the application, could be in the database)


Specific application only rules -- rules that do not apply universally -- they need be in the application (maybe this application can only work with "green and black numbers" -- no red)...



generally agree, but times are changing

Stu Charlton, July 11, 2003 - 5:21 pm UTC

"My point is -- you start "beaning the data" with caching and all of a sudden you no longer have an open system with a SQL RDBMS -- you have bean access -- period, zippo, thats all she wrote."

At an Oracle presentation at JavaOne, the presenter suggested that you guys are working on ways to keep the database and EJB cache in synch, thus eliminating this problem. So I'm not sure this point will be relevent forever.

Caching data in a middle tier is a trade-off that's almost no different from the one implict in a materialized view refresh policy. If the data can diverge (i.e. it's "read mostly"), then perhaps a middle tier cache works. If it's subject to highly concurrent reads & writes, then you're probably best to forego the caching.

Here's the fundamental problem: most of the problems of middle-tier vs. database split have nothing to do wtih technology and everything to do with developer culture and knowledge. The general trend is to develop systems with object oriented techniques for greater flexibility and maintainability. Whether you agree with the effectiveness of that or not, it is the trend. The problem is that Oracle only very recently has included basic features for enabling this kind of processing *inside* the database. And even then, the language for doing so is PL/SQL, not exactly a language that most OO developers are familiar with.

Tom is correct that most systems do not need these fancy OO frameworks or application servers. They're overkill in a lot of ways. But I'm not interested in simple systems. I'm interested with complex systems: really beefy, thorny, hairy systems with terabytes of data, mixed workloads (OLTP and OLAP), and enough divergent and varied business rules to make your head spin.

In my experience many cases of complex business systems that are difficult to maintain precicely because the level of expression in the language is not as appropriate as one at a higher level. I used to be an object database programmer (GemStone). For a developer, it was the best thing since sliced bread - you could create extremely sophisticated systems with it. I know of systems that even developed frames & slots based knowledge representation frameworks -- and it performed extremely well.

It's clear today that object databases are not workable for a variety of reasons. But we're still stuck in a bit of a straightjacket here -- if we want to create highly sophisticated systems, the only workable solution is to use the higher level languages and framewokrs -- which are only available in these massive middle-tier application server monstrocities that take over many of the functions of a database (poorly), and usually have horrible network overhead to pull the data across from the database.

So I see a need to be filled by the dearth of rich toolsets to develop complex, sophisticated information applications. These are the kinds of applications that the mainstream will be developing in 10 years. And Oracle seems to be the best company to get there, to me, but it's still not there yet, it still doesn't "quite" have the flexibility of development we had with Gemstone 10 years ago. OO is fundamentally about combining data and function together, and dividing modules along lines of logical responsibility -- hence business logic probably is best residing along side the data. The problem is, we don't have the realistic capability of doing that today for complex object-oriented systems.

In short, we really need a better integration between a host language and the relational model. Tom keeps on suggesting that J2EE will not be the last paradigm -- I agree. What should be next? I think we need to bridge the cultural gap between set-orientation, record-orientation and object-orientation. And we need a new host language to enable all of this. Once that happens, this "middle tier" debate might go away. All business logic will be stored in the database. No questions, no philosophical struggles, no religious debates.

<end rant>

OK

R.Chakravarthi, August 10, 2003 - 8:23 am UTC

Dear Sir,
I have some questions for you.
1)Is there any parameter which automatically turns off redo
generation or we have to go for the command
Alter table emp nologging.
2)Do you know the names of procedural languages of other databases like Ingres,Mysql,Guptasql?If you know please inform me.


Tom Kyte
August 10, 2003 - 2:36 pm UTC

1) you have to go far far out of your way to disable it on a very small set of commands (things that do BULK operations). disabling of redo is not something we are "very into" as it sort of defeats the purpose of a database.

If you are doing a large bulk operation such as

o direct path load
o direct path insert
o create table as select
o alter move/alter rebuild
o create index

we can bypass redo generation (but you better coordinate with your DBA as they need to schedule a backup right away)

for pretty much every other command -- no way. redo is what databases do for you.

2) nope.

Contradiction?

Kimathi, October 29, 2003 - 5:35 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?

Tom Kyte
October 30, 2003 - 6:46 am UTC



my thoughts -- don't ask java programmers for best practices on database design and implementation?

ask database people (who can actually be java programmers as well).

depends on what school of thought you are coming from. If you truly believe that java is the last language, and j2ee is the the programming paradigm -- sure, hide all of your business logic in that code making it 100% sure that for you java is the last language and j2ee is the last programming paradigm (until some other generation of developer must rip the logic back out, like we did with green screen apps)

there are times when it makes sense to use stored procedures
there are times when it makes sense to use java


polymorphism question

vll, December 18, 2003 - 12:36 pm UTC

Hi, Tom!

I have one question/problem. Let's say we have a parent_type and a child_type under it. We create a table with a field of parent_type. Everything is fine at this point - we can insert objects of both types in a table. Now we create a new child_type1 under existing parent_type. And as soon as we are done with this - no way to insert in a table any of child_types - only parent_type. If we recreate the table - everything is fine again, we can insert all 3 types. At least this is what I'm getting. Is it OK or I'm doing something wrong? If this is how it should be - it's very-very inconvenient :-(((.
Thanks a lot for your help, Tom!!!

Tom Kyte
December 18, 2003 - 1:00 pm UTC

give me tiny, yet 100% complete, yet concise - example of your issue. I'll build on that.

polymorphism question

vll, December 18, 2003 - 4:12 pm UTC

Never mind, Tom!
The problem was that I was doing everything in one session. (I mean adding new derived child_type and trying to insert new values in an object table). It works fine! Oracle 9i is really great!!!
thanks a lot!

Java & Pl/sqlObjects

Govind Agnihotri, December 18, 2003 - 4:49 pm UTC

Tom ,
Thank you for the great service you provide for Oracle community.

Currently we are redesigning out System architechture. It is very clear that data validation and business logic belongs with pl/sql. We made a policy that the presentation layer should not handle any business logic what so ever but will "control" the end users errors.

So we have
Browser ->App Server -->JDBC --> Pl/sql ( Oracle).

We have designed "pl/sql type" hirearchy for most the data in the database and coded pl/sql objects which will be passed to the presentation layer API's written in Java. There will be JDBC calls for the object/data exchange.

Currently we have few users testing it, We have challenges keeping the pl/sql objects valid when underlying type definitions change, ( Calls for a robust change control)

Do you forsee any performance issues for us in the future? What do you think of the design? Your comments would be much appreciated.


Thanks
Govind.








Tom Kyte
December 18, 2003 - 6:15 pm UTC

what does this type hierarchy look like? what are we talking about exactly (too vague to say if I can forsee any performance issues)

Business Logic

Aakki, February 13, 2004 - 6:36 am UTC

Hi Tom,
Sorry to get my answer this way, but desperately need answer from the expert of database.

I am hard-core PL/SQL/ Oracle fella, I want to design an extension to existing system which is forms driven. This extension is developing an automated system to dispose records at load time itself. By doing this automation we will atleast decrease work-load of 30% where users need to go and take decisions thru front end (user intervension).

What I want to do here is
1. Load data record file everyday morning. (which I am doing for existing system)
2. Check if a particular reason code exists.
3. Do certain action like
a) Insert debit record if 6 digit auth present else
pass Represent decision record to Issuer
b) Send email
c) create letter to Issuer.

That is as simple as I can explain.

Been PL/SQL guy.. I dont want to write my rules in procedure which would need a change every time the business rule get added or removed. eg. on certain reason code - also create a history record etc.

I want this very generic.

I thought of business rule matrix table, but my idea did not perfectly got drawn on paper. (black/white)

I thought I will have table saying

group decription-ReasonCode-amount-action
eg. Auth -23,26,29- less then 10$- debit if 6digit auth present else represent.

Finally thought I will have one more column name call_proc and I will call a procedure for that action.

But then I need to change the procedure every time old rules need one more action item to be added.

Hope I explained the designing problem.

Please try to help me in this automation process and help a programmer who does not want to hard code business rule in programs.

Regards,
Aakki

Tom Kyte
February 13, 2004 - 10:05 am UTC

remember this

"generic = slow, hard to debug, things just seem to happen by accident"
"specific = fast, easy to debug, things happen explicitly for a reason"

I've never seen how putting a "rule that is pretty much like procedural code in a table that has to be parsed and dynamically interpreted" is easier then putting "a rule that is compiled code into the code"

Sorry, I'm not a big fan of your approach. Making things "table driven" -- sure. Using constraints on tables and ALTERS to change these constraints over time -- sure.

But inventing my own language -- no, not really.



PLSQL problems

Robert James Hanrahan, August 20, 2004 - 10:40 am UTC

hi all,

Oracle 8.1.7.4.0 - Tru64 UNIX 5.1a

I'm coding a package which has 4 procedures:

1. Underlying table:

SQL> desc TEST_c_plmno_usage
 Name                                                                   Null?    Type
 ---------------------------------------------------------------------- -------- -----------------
 PLMNO_CODE                                                             NOT NULL NUMBER
 MONTHS                                                                          DATE
 CHARGE                                                                          NUMBER(12,2)
 NC                                                                              NUMBER(10)
 LAST_UPDATE 

2. Parameter Table TEST_SYSTEM_PARAM(from which I get a value of "DEEP" which is NUMBER):

SQL> desc TEST_SYSTEM_PARAM
 Name                                                                   Null?    Type
 ---------------------------------------------------------------------- -------- -----------------
 ID_PARAM                                                               NOT NULL NUMBER(5)
 DESCRIPTION                                                            NOT NULL VARCHAR2(120)
 L_VAL                                                                           NUMBER(16) -- parameter value (eg: 3)
 S_VAL                                                                           VARCHAR2(80)
 EDITABLE                                                               NOT NULL CHAR(1)


3. CREATE OR REPLACE PACKAGE TEST_USAGE AS

PROCEDURE create_usage    ( plmno_code IN number);

PROCEDURE alter_usage_deep;

PROCEDURE add_plmno_usage ( plmno_code_in     IN NUMBER,
                            charge_in         IN NUMBER,
                            number_of_call_in IN NUMBER,
                            usage_of          IN DATE);

PROCEDURE usage_rollover  (plmno_code_in  IN NUMBER,
                           usage_of_in    IN DATE);

end TEST_USAGE;
/

CREATE OR REPLACE PACKAGE BODY TEST_USAGE AS

PROCEDURE create_usage ( plmno_code IN number) IS

vparam   TEST_SYSTEM_PARAM.L_VAL%TYPE;
vplmno   TEST_C_PLMNO_USAGE.PLMNO_CODE%TYPE;
vmonths  TEST_C_PLMNO_USAGE.MONTHS%TYPE;
l_start number;


BEGIN

select L_VAL into vparam from TEST_SYSTEM_PARAM where ID_PARAM = 16;

for i in 0 .. vparam-1
   loop
       insert into TEST_C_PLMNO_USAGE values (plmno_code, add_months(sysdate, -i), 0, 0, sysdate);
   end loop;
commit;

dbms_output.put_line('Total hsecs: '|| (dbms_utility.get_time-l_start));

end create_usage;


PROCEDURE alter_usage_deep IS

type refcur_alter IS REF CURSOR;
  rc refcur_alter;

vparam        TEST_SYSTEM_PARAM.L_VAL%TYPE;
vmonths       TEST_C_PLMNO_USAGE.MONTHS%TYPE;
vplmno_code   TEST_C_PLMNO_USAGE.PLMNO_CODE%TYPE;

BEGIN

vparam := 0;

   select l_val into vparam from TEST_SYSTEM_PARAM where ID_PARAM = 16;
   
   open rc for select plmno_code, months  
                 from TEST_c_plmno_usage;

loop fetch rc into vplmno_code, vmonths;
 exit when rc%NOTFOUND; 
    
   while rc%ROWCOUNT < vparam loop
     
     for i in 0 .. vparam-1 loop
     
     insert into TEST_c_plmno_usage values (vplmno_code, add_months(sysdate, -i),0,0, sysdate);
     
     end loop;
     
   end loop;
end loop;
close rc;

commit;  
END alter_usage_deep;

PROCEDURE add_plmno_usage ( plmno_code_in     IN NUMBER,
                            charge_in         IN NUMBER,
                            number_of_call_in IN NUMBER,
                            usage_of          IN DATE) IS

vparam     TEST_SYSTEM_PARAM.L_VAL%TYPE;                            
vplmno     TEST_PLMNO_DATA.PLMNO_CODE%TYPE;
tot_charge NUMBER;
tot_calls  NUMBER;
begin
end add_plmno_usage;

PROCEDURE usage_rollover  (plmno_code_in  IN NUMBER,
                           usage_of_in    IN DATE) IS

begin
end usage_rollover;
end;
/

----------------------------------------------------------------------------------------

The first Procedure does an insert on the TEST_c_plmno_usage:

SQL> exec TEST_usage.create_usage('21201');

PL/SQL procedure successfully completed.

SQL>@sel

PLMNO_CODE MONTHS                  CHARGE         NC LAST_UPDATE
---------- ------------------- ---------- ---------- -------------------
     21201 2004-08-20 16:12:45          0          0 2004-08-20 16:12:45
     21201 2004-07-20 16:12:45          0          0 2004-08-20 16:12:45
     21201 2004-06-20 16:12:45          0          0 2004-08-20 16:12:45
     
That is what I need... :) 3 records starting from today (sysdate) and 2 months in the past.
     
I have a problem resolving the procedure "alter_usage_deep" because what I have to do is:

1. read the parameter from TEST_SYSTEM_PARAM ( which is 3 )
2. for all the records inside TEST_c_plmno_usage it has to insert/delete the records which nonexist/over_the_deep

nonexist means:

if I have

PLMNO_CODE MONTHS                  CHARGE         NC LAST_UPDATE
---------- ------------------- ---------- ---------- -------------------
     21201 2004-08-20 16:12:45          0          0 2004-08-20 16:12:45
     21201 2004-07-20 16:12:45          0          0 2004-08-20 16:12:45

I have to insert 

     21201 2004-06-20 16:12:45          0          0 2004-08-20 16:12:45


over_the_depp means:

PLMNO_CODE MONTHS                  CHARGE         NC LAST_UPDATE
---------- ------------------- ---------- ---------- -------------------
     21201 2004-08-20 16:12:45          0          0 2004-08-20 16:12:45
     21201 2004-07-20 16:12:45          0          0 2004-08-20 16:12:45
     21201 2004-06-20 16:12:45          0          0 2004-08-20 16:12:45
     21201 2004-05-20 16:12:45          0          0 2004-08-20 16:12:45

I have to delete

     21201 2004-05-20 16:12:45          0          0 2004-08-20 16:12:45
     
Any idea? 

Thanks

rjh     
 

Tom Kyte
August 21, 2004 - 10:27 am UTC

not sure what this has to do with "plsql vs java". this is more like "please write my code for me"

ok

Robert Hanrahan, August 23, 2004 - 6:43 am UTC

ok, sorry for that...



Business Logic - PL/SQL Vs Java

Jayavanthi, September 16, 2004 - 4:00 am UTC

I have gone through the series of responses which are really helpful. I have few more queries on PL/SQL and BC4J.
Please suggest me where to put these quries.

My first query is what kind of business logic is ideal
to put in PL/SQL procedure?

My second query is : Is BC4J tightly tied with Oracle Database?

Third query is what happens to my PL/SQL procedures if my database changes from Oracle to Sybase or other databases.

Fourth Query is how BC4J code has to be re-written if the back end database changes.

Response on these queries would be of great help to me.

Thank you and regards


Tom Kyte
September 16, 2004 - 8:09 am UTC

any and all business logic is ideal to put in plsql.

PLSQL is just a language.
Java is just a language.
C is just a language.

If you can put business logic in Java, you can put it in C, you can put it in plsql.

BC4J can generate either Oracle optimized code (use rowid, use oracle'isms) or "generic ansi sql that doesn't take advantage of the database features".


If your database changes from Oracle to Sybase -- you must be jumping into a way back machine. But obviously, if you have stored procedures -- they would have to be ported to the other databases stored procedure language.

If you generate "vanilla sql, no use of the software you paid alot for", bc4j will generate vanilla sql.



Business Logic - PL/SQL Vs Java

Jayavanthi, September 16, 2004 - 9:57 am UTC

Thank very much for the quick response.

I want to develop an application using BC4J with Oracle
as back end and with an intension of migrating to other
RDBMS in future.

Can suggest me with an example when to use stored
procedure to put business logic and when not to.

This is required because I was stongly suggested not to use stored procedures if the database change is expected to change from one RDBMS to other. Is this true? Is it recommeded to eliminate stored procedure usage altogether.

Thank you

Tom Kyte
September 16, 2004 - 10:28 am UTC

do you have Expert One On One Oracle? I wrote about this extensively in the beginning of that book.

My advice is/was/will be very different based on the facts that

a) concurrency controls are massively different in db2, sybase, sqlserver, oracle -- we do things DIFFERENTLY

b) transactional control is massively different in <list>, we do things DIFFERENTLY

c) what works ok in database1 stinks in database2 and doesn't really work at all in database3. stick any name you want in for database1, 2, 3

d) database independence should be defined as "the ability to run poorly on one database and really bad, if at all, on the rest"


In my experience, the BEST way to achieve your goal (we want our middle tier gui logic to be repurposed on database X, Y and Z is to use ONLY stored procedures and implement the stored procedures in database X using *every last feature and technique that is correct and proper for database X*. That is, use what you paid for, what the customer paid for.

that is the only way I know to get

a) correct
b) performant
c) scalable
d) available

solutions.

Business Logic - PL/SQL Vs Java - Reg

jaya, September 22, 2004 - 9:59 am UTC

For a small unit of transaction is it expensive to use Application server (Oracle 9iAS) cache or (Oracle) Database cache?

Tom Kyte
September 22, 2004 - 10:24 am UTC

given that you already use the database cache, regardless....

but anyway, totally insufficient details here to say "anything". (i'm opposed to middle tier caches for virtually all applications on the grounds that -- hey, i cannot use my database anymore, they took transactional integrity, locking, concurrency control away)

moving sql into db

kula, October 26, 2004 - 6:33 am UTC

Tom

We are building a new application. Previously we have had all the sql in the middle tier written in java
accessed by jdbc with prepared statements. This has worked fine except when we need to maintain the sql. I have convinced the developers
that the sql should sit in the database so that it is far easier for us to change update etc..

Now my problem is that we now have to write procedures to select/insert/update/delete from all the tables etc..!
Whilst I was developing this code I noticed a fair bit of repetition which led me to think about writing SOME of it generically.
I know that this can make it harder to debug (and you're probably against this idea)
but I thought if I don't get too carried away I might be ok. It would save a lot of coding.


Please can you give me your opinion on the following:

1) Since we don't know until runtime what fields need to be updated or inserted I thought I could write a generic
update and insert procedure that uses bind variables. Then for each table i need to update or insert into i just need
to format the data into a table of records to hold the field name and its value eg outline is something like:


TYPE rec_Col IS RECORD (Name VARCHAR2(50),
Value VARCHAR2(1000));

TYPE tbl_rec_Col IS TABLE OF rec_Col INDEX BY BINARY_INTEGER;




PROCEDURE P_FORMAT_BATCH ( P_BATCH_ID IN BATCH.BATCH_ID%TYPE default null,
P_STAGE_ID IN BATCH.STAGE_ID%TYPE default null,
P_FILE_NAME IN BATCH.FILE_NAME%TYPE default null,
P_FILE_EXTENSIONIN BATCH.FILE_EXTENSION%TYPE default null,
P_FILE_LOCATION IN BATCH.FILE_LOCATION%TYPE default null,
P_FILE_REFERENCEIN BATCH.FILE_REFERENCE%TYPE default null,
P_USER_ID IN BATCH.USER_ID%TYPE default null,
P_ORGANISATION_CODEIN BATCH.ORGANISATION_CODE%TYPE default null,
P_ORGANISATION_TYPEIN BATCH.ORGANISATION_TYPE%TYPE default null,
P_FILE_TYPE IN BATCH.FILE_TYPE%TYPE default null,
P_SUCCESS_COUNT IN BATCH.SUCCESS_COUNT%TYPE default null,
P_WARNING_COUNT IN BATCH.WARNING_COUNT%TYPE default null,
P_REJECT_COUNT IN BATCH.REJECT_COUNT%TYPE default null,
P_TOTAL_COUNT IN BATCH.TOTAL_COUNT%TYPE default null,
P_STATUS IN BATCH.STATUS%TYPE default null,
P_DATE_FILE_CREATEDIN BATCH.DATE_FILE_CREATED%TYPE default null,
P_DATESTAMP IN BATCH.DATESTAMP%TYPE default null,
P_NATIONAL_CODE IN BATCH.NATIONAL_CODE%TYPE default null,
P_AWAITING_COUNTIN BATCH.AWAITING_COUNT%TYPE default null,
batch_array OUT tbl_rec_Col)

IS

-- formats the data ready to pass to the generic insert/update proc

BEGIN


batch_array(1).Name := 'BATCH_ID'; batch_array(1).Value := P_BATCH_ID;
batch_array(2).Name := 'STAGE_ID'; batch_array(2).Value := P_STAGE_ID;
batch_array(3).Name := 'FILE_NAME'; batch_array(3).Value := P_FILE_NAME;
batch_array(4).Name := 'FILE_EXTENSION'; batch_array(4).Value := P_FILE_EXTENSION;
batch_array(5).Name := 'FILE_LOCATION'; batch_array(5).Value := P_FILE_LOCATION;
batch_array(6).Name := 'FILE_REFERENCE'; batch_array(6).Value := P_FILE_REFERENCE;
batch_array(7).Name := 'USER_ID'; batch_array(7).Value := P_USER_ID;
batch_array(8).Name := 'ORGANISATION_CODE'; batch_array(8).Value := P_ORGANISATION_CODE;
batch_array(9).Name := 'ORGANISATION_TYPE'; batch_array(9).Value := P_ORGANISATION_TYPE;
batch_array(10).Name := 'FILE_TYPE'; batch_array(10).Value := P_FILE_TYPE;
batch_array(11).Name := 'SUCCESS_COUNT'; batch_array(11).Value := P_SUCCESS_COUNT;
batch_array(12).Name := 'WARNING_COUNT'; batch_array(12).Value := P_WARNING_COUNT;
batch_array(13).Name := 'REJECT_COUNT'; batch_array(13).Value := P_REJECT_COUNT;
batch_array(14).Name := 'TOTAL_COUNT'; batch_array(14).Value := P_TOTAL_COUNT;
batch_array(15).Name := 'STATUS'; batch_array(15).Value := P_STATUS;
batch_array(16).Name := 'DATE_FILE_CREATED'; batch_array(16).Value := P_DATE_FILE_CREATED;
batch_array(17).Name := 'DATESTAMP'; batch_array(17).Value := P_DATESTAMP;
batch_array(18).Name := 'NATIONAL_CODE'; batch_array(18).Value := P_NATIONAL_CODE;
batch_array(19).Name := 'AWAITING_COUNT'; batch_array(19).Value := P_AWAITING_COUNT;

END P_FORMAT_BATCH ;




PROCEDURE P_Batch_sql ( P_Action IN CHAR,
P_BATCH_ID IN BATCH.BATCH_ID%TYPE default null,
P_STAGE_ID IN BATCH.STAGE_ID%TYPE default null,
P_FILE_NAME IN BATCH.FILE_NAME%TYPE default null,
P_FILE_EXTENSION IN BATCH.FILE_EXTENSION%TYPE default null,
P_FILE_LOCATION IN BATCH.FILE_LOCATION%TYPE default null,
P_FILE_REFERENCE IN BATCH.FILE_REFERENCE%TYPE default null,
P_USER_ID IN BATCH.USER_ID%TYPE default null,
P_ORGANISATION_CODE IN BATCH.ORGANISATION_CODE%TYPE default null,
P_ORGANISATION_TYPE IN BATCH.ORGANISATION_TYPE%TYPE default null,
P_FILE_TYPE IN BATCH.FILE_TYPE%TYPE default null,
P_SUCCESS_COUNT IN BATCH.SUCCESS_COUNT%TYPE default null,
P_WARNING_COUNT IN BATCH.WARNING_COUNT%TYPE default null,
P_REJECT_COUNT IN BATCH.REJECT_COUNT%TYPE default null,
P_TOTAL_COUNT IN BATCH.TOTAL_COUNT%TYPE default null,
P_STATUS IN BATCH.STATUS%TYPE default null,
P_DATE_FILE_CREATED IN BATCH.DATE_FILE_CREATED%TYPE default null,
P_DATESTAMP IN BATCH.DATESTAMP%TYPE default null,
P_NATIONAL_CODE IN BATCH.NATIONAL_CODE%TYPE default null,
P_AWAITING_COUNT IN BATCH.AWAITING_COUNT%TYPE default null,
)



IS

V_SQL Varchar2(5000);
rec_data_array PKG_mypkg_FORMAT.tbl_rec_Col;
pk_array PKG_mypkg_FORMAT.tbl_rec_Col;


BEGIN

IF (P_LOCAL_CODE IS NOT NULL) AND (P_ORGANISATION_CODE IS NOT NULL) THEN

P_FORMAT_BATCH (
P_BATCH_ID,
P_STAGE_ID,
P_FILE_NAME,
P_FILE_EXTENSION,
P_FILE_LOCATION,
P_FILE_REFERENCE,
P_USER_ID,
P_ORGANISATION_CODE,
P_ORGANISATION_TYPE,
P_FILE_TYPE,
P_SUCCESS_COUNT,
P_WARNING_COUNT,
P_REJECT_COUNT,
P_TOTAL_COUNT,
P_STATUS,
P_DATE_FILE_CREATED,
P_DATESTAMP,
P_NATIONAL_CODE,
P_AWAITING_COUNT,
rec_data_array );


IF P_Action = 'U' THEN

-- initialise pkeys

rec_data_array(3).Name := NULL; rec_data_array(3).Value := NULL;
rec_data_array(4).Name := NULL; rec_data_array(4).Value := NULL;

pk_array(1).Name := 'LOCAL_CODE'; pk_array(1).Value := P_LOCAL_CODE;
pk_array(2).Name := 'ORGANISATION_CODE'; pk_array(2).Value := P_ORGANISATION_CODE;


P_UPDATE_TABLE ('BATCH',
rec_data_array,
pk_array );


ELSIF P_Action = 'I' THEN

P_INSERT_TABLE ('BATCH',
rec_data_array);



END IF;

ELSE
RAISE_APPLICATION_ERROR(-20001,
'P_BATCH_SQL: PRIMARY KEY MISSING',
true);


END IF;

END P_BATCH_sql;






-- now call generic update


PROCEDURE P_UPDATE_TABLE ( P_Table_Name IN VARCHAR2,
tbl_Columns IN PKG_mypkg_FORMAT.tbl_rec_Col,
tbl_Primary_key IN PKG_mypkg_FORMAT.tbl_rec_Col)



IS

V_SQL varchar2(5000);

BEGIN

PKG_mypkg_SQL.P_Initialise_Context ( 'MY_CTX' );




FOR i IN 1 .. tbl_Columns.Count LOOP

IF tbl_Columns(i).Value IS NOT NULL THEN

DBMS_SESSION.SET_CONTEXT( 'MY_CTX', tbl_Columns(i).Name, tbl_Columns(i).Value);
V_SQL := V_SQL ||', ' || tbl_Columns(i).Name || ' = SYS_CONTEXT( ''MY_CTX'', ''' || tbl_Columns(i).Name || ''') ';

END IF;

END LOOP;


V_Sql := LTRIM(V_Sql, ',');

V_Sql := ' UPDATE ' || P_Table_Name || '
Set ' || V_Sql || '
WHERE 1 = 1';




-- check primary keys

FOR i IN 1 .. tbl_Primary_Key.Count LOOP

IF tbl_Primary_Key(i).Value IS NOT NULL THEN

DBMS_SESSION.SET_CONTEXT( 'MY_CTX', tbl_Primary_Key(i).Name, tbl_Primary_Key(i).Value);
V_SQL := V_SQL || ' AND ' || tbl_Primary_Key(i).Name || ' = SYS_CONTEXT( ''MY_CTX'', ''' || tbl_Primary_Key(i).Name || ''') ';

ELSE
RAISE_APPLICATION_ERROR(-20001,
'P_UPDATE_TABLE: ' || P_Table_Name || ' : PRIMARY KEY MISSING : ' || tbl_Primary_Key(i).Name,
true);

END IF;

END LOOP;

EXECUTE IMMEDIATE V_Sql ;

END P_UPDATE_TABLE;






PROCEDURE P_INSERT_TABLE ( P_Table_Name IN VARCHAR2,
tbl_Columns IN PKG_NBOCAP_FORMAT.tbl_rec_Col)

IS

V_Sql varchar2(5000);
V_Values varchar2(5000);
V_Fields varchar2(5000);

BEGIN


V_Sql := 'INSERT INTO '|| P_Table_Name || '(
Batch_Id,
Batch_Record_Id, ';



FOR i IN 1 .. tbl_Columns.Count LOOP

IF tbl_Columns(i).Value IS NOT NULL THEN

DBMS_SESSION.SET_CONTEXT( 'MY_CTX', tbl_Columns(i).Name, tbl_Columns(i).Value);
V_Fields := V_Fields || ', ' || tbl_Columns(i).Name || chr(10);
V_Values := V_Values || ', ' || 'SYS_CONTEXT( ''MY_CTX'', ''' || tbl_Columns(i).Name || ''') ' || chr(10);

END IF;

END LOOP;



V_Fields := LTRIM(V_Fields, ',') || ') Values (0, 1, ';
V_Values := LTRIM(V_Values, ',') || ')';

V_Sql := V_Sql || V_Fields || V_Values;



EXECUTE IMMEDIATE V_Sql ;

END P_INSERT_TABLE ;




I have tested this with inserts and updates but its hard to benchmark a hundred users all inserting etc during the day. How do you think this approach would scale?
The reason for doing this is to save having to code hundreds of procedures which are basically the same, do you think
this is a valid approach or should i just code each procedure??


2) Would the above approach slow down due to all the data being converted from varchar2(1000) types to their proper datatypes??
I could go one stage further and pass in a table of varchars instead of separate IN parameters for each field, could this slow it down
further?

3) if this method stinks, what alternative way would you use? a separate insert update procedure for each table??

4) i could also use the format type procedures to do a generic SELECT type procedure but I would need to extend the
record type to also hold a datatype so that I know whether to build into the where clause a 'LIKE' for searched varchars and dates or
an '=' for integers. What do you think?

(we are on 9.2.0.5 by the way)
Many thanks


Tom Kyte
October 26, 2004 - 7:57 am UTC

i think you should write an API that represents your well documented, well thought out, well forms set of transactions

I don't like "TAPI's" (table apis), I like only "transactions".

I don't like the use/abuse of tons of dynamic sql.

No, i would not encourage this approach at all.



3) i would not have a separate update/insert per table. I would have transactional APIs with whatever DML is needed to perform that specific transaction.




more detail

kula, October 26, 2004 - 9:24 am UTC

Thanks very much for the response.

Please can you go into some details though as I am not familiar some of the terms you used in your reply eg transactional api?

1)
If you could give more detail with a simple example that would be very helpful. ie
you said I should write a "... transactional APIs with whatever DML is needed to perform that specific
transaction."

Can you give a simple example of what a transactional API that performs DML would look like. I'm not asking you to write the code for me just point me in the right direction.


2)
Also you have said you dislike the approach I used but can you comment on whether you think the way I have done it will scale or not. I have tested the procedures to the point of a few users calling the procedures but are you actually saying my approach could result in a system which is slow and unusable or performs badly?

Any help here is appreciated.

Tom Kyte
October 26, 2004 - 9:28 am UTC

1) </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:25405782527721 <code>

we just recently had a discussion on this topic.

2) you will parse like crazy on this system. that will effectively limit your ability to scale greatly. You will have data integrity issues dealing with everything in a string (date conversions and such).

no examples

kula, October 26, 2004 - 10:46 am UTC

Ok you have me convinced that tapi's are bad. I read the discussion but there are no actual examples.

Maybe I'm just not totally getting what you mean here but it seemed to me that that discussion implied that if you have a table which can be inserted updated and deleted from your java app then you need a package that has specific procedures or functions to statically peform each dml? one for inserting one for updating etc

But this would mean writing lots of procedures for each table wouldn't it?
If this is what it means then thats fine I can do that but if not then please can you explain with a simple example. I think that an example will show me clearly what you are talking about.
eg what does one of your api's look like that does updates but doesn't need to know what the table looks like??

Sorry if this is trivial but I would like to get it clear in my head before i begin writing code.

Thanks

Tom Kyte
October 26, 2004 - 10:53 am UTC

your java app is done wrong if it is doing single table inserts/updates/deletes.

If you are doing that, moving it into a plsql package isn't going to buy you anything.

I'm not suggesting you take your existing application and replace each insert/update/delete/select with a stored procedure call.

you should be implementing transactions -- apis -- so that where you had 500 lines of java doing inserts/updates/deletes -- you now a single stored procedure call (and it has the N lines of code to achieve the transaction).

You are not improving anything by moving the dyanmic sql via jdbc from java into plsql using native dynamic sql -- in fact, you are "de-improving" if that is all you do.

Re: transactional APIs

Tony Andrews, October 26, 2004 - 12:21 pm UTC

Another (analysis) term for this is an "elementary business function" (EBF). Suppose there is a process that must transfer money from bank account X to bank account Y, and consists of 3 steps:
1) insert into transfer (from_ac, to_ac, amount) values (:x, :y, :amount);
2) update account set balance = balance - :amount where ac = :x;
3) update account set balance = balance + :amount where ac = :y;

Those 3 steps form an "elementary business function" or a "transaction": i.e. you must either do all three, or do none. The RIGHT way to implement that in an API is as one procedure call:

ac_pkg.do_transfer (from_ac => :x, to_ac => :y, amount => :amount);

The WRONG way to do it is via 3 procedure calls:

ac_pkg.insert_transfer (from_ac => :x, to_ac => :y, amount => :amount);
ac_pkg.update_ac_bal (ac => :x, amount => -:amount);
ac_pkg.update_ac_bal (ac => :y, amount => +:amount);

Can you see why?

Tom Kyte
October 26, 2004 - 12:52 pm UTC

Thank you very much, well said.

thanks

kula, October 27, 2004 - 4:40 am UTC

Thanks for the replies.

However i am already aware that doing an insert to a table and update to another table as part of the same process should go in a one procedure as a transaction with the transaction control implemented by the client. That is a fundamental thing. I just wasn't understanding your terminlogy i think. (which is why i asked for an example)

Its was just that our application is mostly doing simple things like entering a record or updating a record in one table only (no other inserts or updates to do as part of the same transaction).

So I guess what you are saying is just write static sql stored procedures to perform these tasks. That is what i wanted to confirm, I was checking in case you had another method you were using that i wasn't aware of.
My dynamic sql was just saving me from writing lots of procedures that were basically the same.

so for example a static update procedure would only update the fields that were enetered into the procedure and not blindly update all fields in a table all the time:

ie instead of having a procedure like

procedure p1 (v1 in varchar2 default null,
v2 in varchar2 default null
..
vn in varchar2 default null)

is
begin

update table
set col1 = v1,
col2 = v2
..
coln = vn
where etc...;

end;

instead of that i would build the update dynamically depending on what is entered. I thought that it would be more efficient that way. Am i wrong? should i pass in all parameters and update all fields?



You said previously that doing it using dynamic sql will cause me to be parsing all the time?
but surely once my database has been open for a period of time, won't most of my sql get cached in the shared pool, so as long as it isn't aged out then it won't hard parse it again, is that not right??

If not how else can i code an update like above if not updating all fields?


Tom Kyte
October 27, 2004 - 7:48 am UTC

but you were building an API that is TABLE based -- not transactional based. So the thing you say is "obvious" is 100% counter to what you were trying to code if you ask me?


why would:

procedure p1( v1, v2, v3, .... vn )
is
begin
update_table( p_tname => 'T1',
p_pk_name => 'X',
p_pk_val => pk_val,
p_v1_name => 'c1',
p_v1_val => v1,
p_v2_name => 'c2',
......... )

be easier than:

update t1 set c1 = v1, c2 = v2, .... where x = pk_val

???



Every dynamic sql statement using native dynamic sql will be:


a) parse
b) bind
c) execute
d) close


over and over -- we just want

b) bind
c) execute

which is what static sql gives you for free.


ok

kula, October 27, 2004 - 8:56 am UTC

Ok then so what about if you have a form on the front end which pretty much directly maps to fields in a table.

The user retrieves a record from the database and sees it in the form. He then changes one field in the form and presses the update button.
What i was asking was whether it would be very efficient to take every single field from that form including the one thats updated and pass it to a procedure which issues

update t1
set c1 = v_col1,
c2 = v_col2,
..
cn = coln
where pk = v_pk1;


OR

alternatively dynamically build your update statement depending on what parameters the user had passed into the procedure eg he updates field number 37 so the procedure dynamically builds :

update t1
set c37 = v_col37
where pk = v_pk1;


Will this perform worse than the procedure that passes all fields to the procedure and updates all of them regardless of whether they have actually changed or not??

What if there were 100 fields your update would look like :

update t1
set c1 = v_col1,
..
c100 = v_col100
where pk = v_pk1;

is it more efficient to pass all this data over the network and update ALL fields rather than dynamically build a single field update???

That is what I am interested in knowing.



Tom Kyte
October 27, 2004 - 9:21 am UTC

when you update a row and set a column to itself, indexes are not involved (it won't update the index) -- just the table data.

So, the row will get updated, you'll generate a little more undo, a little more redo -- but consider the CPU you'll save

o no need to look column by column by column by little tiny column to say "hmm, did I actually change that" -- oh and unless you save the before and after images, how does your api even KNOW what columns are different?

o no need to have 3 copies of an update for a 2 column table, 6 for a 3 column table and so on. lots less parsing.

o the network doesn't have to be a 'factor here'. they only need to send the columns of interest, no reason your update couldn't be


update t set c1 = decode( <whatever you were going to use to figure out
column was needing an update or not>,
'TRUE', new_value, c1 )
.....


they need not send you every column.


i would avoid this dynamic sql like the *plague*

A reader, October 27, 2004 - 10:34 am UTC

9iR2's update/insert using %rowtype seems like a perfect fit here.

declare
x t%rowtype;
begin
select * into x from t where pk=...
<update fields of interest in record x>
update t set row=x where pk=...
end;

Done

Tom Kyte
October 27, 2004 - 12:21 pm UTC

the fields to be updated come from "java"

not sure how that fits in?

thanks

kula, October 27, 2004 - 11:12 am UTC

Thanks thats exactly the information i was after. ie it is worth the extra undo etc you get with updating all fields with static sql in exchange for the dynamic sql update statement. That has answered my question.

One point you said in your response:

"..no need to look column by column by column by little tiny column to say "hmm,
did I actually change that" -- oh and unless you save the before and after
images, how does your api even KNOW what columns are different?.."

my api only updates the columns that are passed to the procedure. However I do have a problem with nulls even when I change it to do things the way you said eg

update t set c1 = decode( <whatever you were going to use to figure out
column was needing an update or not>,
'TRUE', new_value, c1 )
.....


my decision on whether i should update or not was based on the whether the value passed in is null or not, but how do you decide whether the value you passed into the procedure was a null therefore no update or the user does actually want to overwrite the data in that column with a null??

How else do you normally decide whether to update or not?




Tom Kyte
October 27, 2004 - 12:28 pm UTC

<quote>
my decision on whether i should update or not was based on the whether the value
passed in is null or not, but how do you decide whether the value you passed
into the procedure was a null therefore no update or the user does actually want
to overwrite the data in that column with a null??
</quote>

that is left as an exercise for the reader -- for you see, that is totally 100% an implementation detail. Do you send a flag that says "hey, this field is just not being sent" for each field? do you use unreasonable defaults for all columns and look to see if the passed value is different from these defaults? (only works if you can come up with unreasonable defaults) do you avoid nulls in the data model alltogether?

insert whole row and default values

A reader, October 27, 2004 - 12:22 pm UTC

The same problem with inserts - if we are inserting the whole row (9i), or insert all fields (which is the same), no default values will be used.
Also if we have any update triggers on updating table, which check for updating columns (like if updating("column") then...) - all columns will be considerd "updating", which is not always what you want...

Tom Kyte
October 27, 2004 - 12:37 pm UTC

you have API's

transaction API's

they can deal with it.

Using %rowtype

A reader, October 27, 2004 - 1:54 pm UTC

I suggested using 9i's %rowtype as a alternative for the DECODE() on each column that you suggested earlier, thats all. All in the stored proc, of course. Just select out the whole row, change what you need (depending on what Java user passed in) and update the whole row back.

Tom Kyte
October 27, 2004 - 4:03 pm UTC

flesh it out -- how would that work -- given you have a stored procedure accepting N inputs for N columns, how would that help?

You'd have to select out the current values.


You'd have to go column by column (attribute by attribute in the record) and either

a) replace the value with the input
b) or not


so, i'm failing to see how this is "easier" (it seems like a ton more code and lots more work in the database)


A reader, October 27, 2004 - 4:10 pm UTC

Hm, you are right, that would be identical to your

update t set c1 = decode( <whatever you were going to use to figure out
column was needing an update or not>,
'TRUE', new_value, c1 )

Sorry, didnt think it through!

Thanks

kula, November 02, 2004 - 5:10 am UTC

Thanks for the replies it has taught me a lot about best practice.

Along the similiar lines to this I want to ask about procedures for selecting from tables to populate screens.

I have written the "SELECT" procedures by referring to another post on askTom :

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279, <code>


This has worked well from me in the past.
This post is about varying the WHERE clause of your select statement and building it dynamically.

However I want to ask about varying the columns that you select in the SELECT statement.
I have a front screen which needs to populate a drop down list of ids from a table. The user then selects an Id and then needs to see the whole record for that Id from that table.

I can think of a number of ways to deal with this:

1)you could write 2 procedures to do this:
one to populate the drop down list with ids and another to call the whole record?
This would involve 2 database calls but minimal data necessary going over the network.

2) Or you could just have one procedure that returns the whole record, call it to populate the drop down list ignoring all the other columns returned, and then when the user has picked the id they wish to see the page should already have the record. This would mean one database call but a lot of more data going over the network than 1)?

3) Use a table of varchars as an input parameter to the procedure containing a dynamic list of columns you wish to SELECT in your select statement. Loop through the table and build the SELECT statement at runtime.
I assume this would involve more parsing but 2 database calls, although less network traffic than 2).


I'm thinking that option 1) is the way to go even if it is 2 database calls, do you agree?
Would you use the same method ?



Tom Kyte
November 02, 2004 - 8:18 am UTC

i'd probably do #1 yes. probably...

depends on the number of rows, the width of the rows.

once again Tom Proved the usefulness of AskTom

Manish Upadhyay, November 02, 2004 - 2:44 pm UTC


Regarding the followup to "ok October 27, 2004"

Daniel, November 03, 2004 - 11:51 am UTC

Just out of curiosity, what caused your mind change compared with two years ago?

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2531562164953, <code>

Tom Kyte
November 04, 2004 - 1:41 am UTC

the ugly implementation. (although if you read the entire thing i did say "me, I'd probably opt for the disk hit", meaning single sql statement. the most important take away was -- USE BINDS (incredible but true:) )

excellent.

A reader, December 22, 2004 - 3:23 am UTC

I am myself a database programmer and like placing my logic in the database using pl/sql.

But now we have a situation where i want to know about the front end.
What will be your choice b/w Oracle Forms and JAVA swings given the fact that there is no special feature in the application which cannot be developed using Oracle Forms.
Can we please give your opinion on this.
And the application is a web based solution.

Tom Kyte
December 22, 2004 - 9:34 am UTC

I use neither


htmldb :)


</code> http://htmldb.oracle.com/ <code>





A Database-Centric Approach to J2EE Application Development

bob, December 22, 2004 - 2:33 pm UTC

A presentation from Open World on one of this site's favorite topics related to this thread.

</code> https://www.openworld2004.com/published/1091/1091_Koppelaars_updated.ppt https://www.openworld2004.com/published/1091/1091_Koppelaars.pdf <code>

A reader, December 22, 2004 - 7:01 pm UTC

Tom, i agree with you not using any of Forms or Java.
But with forms i get features like scrolling the records F7 & F8 features which are very essential of the application.

Tom Kyte
December 22, 2004 - 7:27 pm UTC

So? I have page forward and page back too?



Business logic: Where to put?

A reader, December 29, 2004 - 8:36 pm UTC

Suppose I need to implement a business rule that says that certain product categories need to be analyzed/reported further. Currently, these categories are, say, A, B and C.

I need to expose a view that incorporates this rule.

There are 2 approaches:

1. create view v as select .. from ... where category in ('A','B','C')

2. create view v as select ... from ... where category in (Select category from some_table);

If the business rule changes to include/exclude more categories, the first approach would involve changing the "source code" of the view, the second approach would involve making a change to the data.

i.e. the second approach is "table driven"

There is a school of thought that well-designed systems should be able to be adapt to changing requirements by least change to source code and everything possible should be table driven.

But a change is a change...both approaches change something. Why is one preferred over the other?

Also, the table-driven aproach, if implemented for all business rules would lead to a proliferation of tiny tables all over the place leading to a maintenance nightmare?

Comments? Thanks

Tom Kyte
December 29, 2004 - 8:40 pm UTC

changes to data are typically not percieved as a code change, but as a configuration change and many times people let configuration changes through without a 2cd thought -- but as you've pointed out -- they can be as dramatic as a change in code.

However, adding a row to a table won't have cascading invalidations either so there are some real advantages to the table driven stuff -- when it is as simple as this. (eg: don't take table driven stuff to an illogical extreme)

Illogical extreme?

A reader, December 29, 2004 - 9:36 pm UTC

Agreed, but just so we are on the same page, what would you consider an "illogical extreme"?

Tom Kyte
December 29, 2004 - 9:55 pm UTC

placing all code in clobs and using execute immediate to run it -- so that "code" becomes "a configuration change" and your code consists entirely of something like:

procedure run_block(p_block_name in varchar2,
p_args in some_collection_type )
as
l_code varchar2(32000)
begin
for i in 1 .. p_args.count
loop
set the application context...
end loop;
select code into l_code from configuration_table where name = p_block_name;
execute immediate l_code;
end;


that would be an illogical extreme.

Illogical extreme?

A reader, December 31, 2004 - 3:06 pm UTC

How about a little code fragment somewhere that goes
case something
when a then 1
when b then 2
...
end;

i.e. a simple mapping that no one else needs except this piece of code.

Would you put this mapping in a table and have the code use the table? If so, wouldnt this lead to a proliferation of tiny tables all over the place? For what benefit?

Tom Kyte
December 31, 2004 - 4:10 pm UTC

proliferation is in the eye of the beholder, to me it documents something important about the data.

however, if the lookup data is relatively static (eg: look at the views behind the Oracle data dictionary itself), no reason this would not be a column in a view -- the view has the case statement, the decode statement.

If the values CHANGE and change on the fly (not just at application upgrade time) -- for example you need to add "c" or make "b" become 3 all of a sudden -- yes, that should be in a lookup table.

Referential Data Vs Transactional Data

A reader, January 23, 2005 - 3:38 pm UTC

Tom

Recently in a interviw, I was asked the difference between Referential data Vs Transactional data. I have the idea, but I could not put it in words.

Can you please define both of them. Thanks

Tom Kyte
January 23, 2005 - 4:21 pm UTC


"referential data" is not one I'm familar with. "reference data" perhaps (lookup data).

best thing to have done in that occurence would have been to say "well, that's my defintion - what is yours, how would you define that..."

Transactional data

A reader, January 23, 2005 - 4:26 pm UTC

o.k reference data is like countries, states, zip codes etc

Can you discuss about transactional data, transactional tables.

Please.

Tom Kyte
January 23, 2005 - 5:21 pm UTC

transactional data should be "obvious"?

an "order" table would be a nice transactional example. lots of transactions applied to "orders" throughout the day.

Business logic is... code.

Billy, February 03, 2005 - 2:29 am UTC

I'm amazed at how buzz words can caused intelligent people go Homer (i.e. go "duh!" and get that vacant and drooling expression).

I asked a dev lead, why app server? Because of business logic he replied. I ask a J2EE architect about business logic and he very adamant that it must be in the app tier. never in the database.

If we tear the wrapper from this cookie, what is business logic in our (developer) terms? Code.

And code is.. yep. Code. Code is code is code is... Code.

Where did we put business logic in the code of yesterday? I remember writing a lot of Cobol stuff - which worked btw. Worked damn well too. Sure, it were tape merges and control breaks (how we wished for random access files and data). The more interesting stuff was writing re-entrant code. But code was code (still is!) and we programmers made it work. We wrote code. Not business logic.

What has change? Why the thinking these days that if code is labeled "business logic" it is somehow so different, so special, so whatever, it simply must be in Java. Worse, in a EJB? And never <giving me that horrified expression when I dare mention it> ever in PL/SQL?

What does business logic deal with? What is the purpose of business logic?

Fact. All roads in programming leads to Data. And where does that Data sit when dealing with Oracle? In the database.

And where is the best place to deal with Oracle data? PL/SQL.

So if you deal with an Oracle database, and you need to code business logic, I'll be very hard pressed to see any justification, any logic, any reason, for doing it anywhere else, but in PL/SQL.

Of course, I could be blunt and call you an ignorant fool if you do not do this in PL/SQL. So I won't. :-)


Tom Kyte
February 03, 2005 - 1:22 pm UTC

"control break processing", if I could get a dime for everytime I've used that to describe a "method for printing a report" to get the glassy eyed stare (they no longer know what that is...)




stored procedure vs dynamic sql

Alex, March 16, 2005 - 9:22 am UTC

Hi Tom,

I had a brief conversation with a co worker of mine who was telling me that "they" (probably our client) want to start moving away from stored procedures and use mostly sql in the Java to "lessen the load on the server."

Say you code your dynamic sql properly, using PreparedStatement and binds etc, are there still draw backs vs stored procedures?

I know you have said many times, keep DML out of the Java altogether. I'm just looking for the reasons this approach is a bad idea so I can better refute their claims.

Tom Kyte
March 16, 2005 - 10:04 am UTC

that would be a way to increase the load on the server in my experience. In theory it "sounds great", but the sql I've seen.....

For me, tuning becomes nightmarish -- "hey, this sql in v$sql -- who is running it? <i don't know, not me>" - try to find it when the sql is all dynamicall constructed.

Try to make it so that binds are used properly - can be done, don't see it being done in general.

Look at their stored procedures - what is the "ratio" of SQL to procedural code. The SQL will still happen -- unless they are doing a fourier transformation in plsql -- they won't be removing much, in fact they'll be adding lots of back and forth and back and forth.

I'd want to know what the percieved "load" they would be removing would be -- rather then make a simple single call to the server, with all of the inputs needed to perform the transaction and do all of the sql in one fell swoop - you'd be going back and forth and back and forth and back and forth.

Also -- you lose the dependency mechanism totally. "Sorry, I didn't know you used that" will become a common phrase.

Re: Business logic: Where to put? by "A reader"

Dan Kefford, March 16, 2005 - 10:40 am UTC

<quote poster="A reader">
There are 2 approaches:

1. create view v as select .. from ... where category in ('A','B','C')

2. create view v as select ... from ... where category in (Select category from
some_table);

.
.
.

There is a school of thought that well-designed systems should be able to be
adapt to changing requirements by least change to source code and everything
possible should be table driven.

But a change is a change...both approaches change something. Why is one
preferred over the other?
</quote>

<quote poster="Tom">
changes to data are typically not percieved as a code change, but as a
configuration change and many times people let configuration changes through
without a 2cd thought -- but as you've pointed out -- they can be as dramatic as
a change in code.
</quote>

I would argue further that by coding the view in the second manner, you empower people further down the food chain, thus saving cost to the organization. Instead of paying a developer x dollars per hour to make a code change, testing it, and redeploying it, you can pay an operator or end user (presumably) <x dollars per hour, and in far less time, to simply add or update a record in the database to enable/disable functionality.



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

empower is synonymous with "giving them the ability to shoot you in the foot"

configuration changes are as dramatic as code changes, have the same effects (eg: running untested code in production as not every configuration setting/combination was tested)

I'm not saying this is "bad" -- in fact, I encourage people to parameterize their code to allow trace information to be written in production by simply updating a row or adding a configuration setting, for the simple reason people won't let you drop in new code, but they will let you branch to different code for some reason.

Both are dramatic changes, one is generally not permitted and the other is generally allowed


And it takes a smarter programmer that cost more $$$ to write proper code that is easily configured without breaking in the first place (thus costing more to develop in the first place :)


10g performance tuning guide on caching

reader, May 09, 2005 - 1:31 pm UTC

From the 10g Performance tuning guide:

The most common examples of candidates for local caching include the following:

-Today's date. SELECT SYSDATE FROM DUAL can account for over 60% of the workload on a database.

-The current user name.
Repeated application variables and constants, such as tax rates, discounting rates, or location information.

-Caching data locally can be further extended into building a local data cache into the application server middle tiers. This helps take load off the central database servers. However, care should be taken when constructing local caches so that they do not become so complex that they cease to give a performance gain.

-Local sequence generation.

What do they mean by "Local sequence generation"?

The docs also claim:

"[...] However, the application server is the most common location for business logic."

That is in the context of Java and PL/SQL being well suited for business logic though. It doesn't say the best place is, just the most common is.

Tom Kyte
May 09, 2005 - 3:55 pm UTC

well, sysdate with time is hardly appropriate for caching and these days -- even just the date would be "not good to cache" if you think about it (24x7 -- connection pools, bad idea)

the current user name -- today is mostly constant (connection pool) or changing from call to call....


local sequence generation would be somewhere I don't want to go myself.




caching in pl/sql

A reader, July 25, 2005 - 7:00 pm UTC

how can I do java like chaching in PL/SQL ?

In java I build a market data cache and if the market is updated I can update bids/offers in my server cache. is this
possible with pl/sql ? or I ALWAYS have to query from databse ?

and even if I can do that can I integrate this updatable cache with xml ?

Thanks

Tom Kyte
July 25, 2005 - 7:47 pm UTC

well, plsql runs -- where?

the database. and the database comes with a phenomenal cache builtin.

You can use plsql table types (associative arrays or indexed by integer arrays) as well.

Java vs plsql

Reader, July 25, 2005 - 10:55 pm UTC

Well , Supporters of Java say that it is difficult to maintain code in PL/SQL and Java is much better ?

What will be your suggestion ?

Thanks ,



Tom Kyte
July 26, 2005 - 7:39 am UTC

Cobol is better than Java.

makes as much sense doesn't it? I mean, MOST of the code running today is still Cobol, Cobol must be superior.

I would find it very difficult to maintain code in java myself.

There are many programming languages, programmers would benefit themselves from learning many of them.

pl/sql vs java

A reader, July 26, 2005 - 4:54 pm UTC

so, basically I can not do the updatable cache (which will be very fast) in pl/sql and I can not replace pl/sql for java in any kind of important system which requires faster processing and availability

Tom Kyte
July 26, 2005 - 5:40 pm UTC

hah hah OUCH (sorry, fell out of my chair, just a second...)


Umm, no, that would not be what I said at all.

(and the system is only as available as the *database* -- adding pieces to a system can make it more fragile too so perhaps by layering things you are making the system less available than a system running in the database :)



thanks

A reader, July 27, 2005 - 1:59 pm UTC

this is getting interesting..

let us say that I have so called 3-tier application
oracledb -- appserver(our custom group of java prog) -- and java clients.. which is let us say the front end screen.

let us say we are doing equity trading.

1.) when the appserver start it cashes all the "reqired
data" in to the memory.

2.) all client connects to the server via internet...and
requests the "current view" of the market.
3.) if one of the client is putting any prices, that goes to appserver and automatically immediately made available to other clients and simultaniously inserted into db.
4.) the locking in this case in handled in custom java appserver so lockning is not an issue.
5.) so how do you achive this in database WITHOUT QUERYING it ? or using PL/SQL



Tom Kyte
July 27, 2005 - 2:51 pm UTC

1) why?

2) and if the market (in the database changes) what happens to these middle tier caches that are all over the place.

3) wow, how did that magic "just happen"?

4) sure it is. locking is hard to do in a scalable, correct fashion, database manufacturers have been working on it for years and years.

5) what is wrong with QUERYING IT. Quite a few systems do that (and work)


But there are database solutions even still, for example the time ten stuff we just added to the portfolio.

My goal is always to write as little code as possible.

My goal is to use the software to its fullest, never ASSUMING something won't work and designing hypothetical solutions to situations that may well never arise in real life.

My goal would be to see what doesn't work before implementing a fix for something that isn't broken.

thanks

A reader, July 27, 2005 - 3:14 pm UTC

Followup:
1) why? -- to server requests directly from the server memory

2) and if the market (in the database changes) what happens to these middle tier
caches that are all over the place. -- to insert/update anything market related "must" go thro' server there is no back door. and hance, the cache is updated.

3) wow, how did that magic "just happen"? --- if the cache is updated the prices for updated stocks are available for operation

4) sure it is. locking is hard to do in a scalable, correct fashion, database
manufacturers have been working on it for years and years.
--- Actually that comes with the JAVA FRAMEWORK there is nothing we need to do. just use transaction objects from framework..


5) what is wrong with QUERYING IT. Quite a few systems do that (and work) --
-- let us say i am interested in selling or buying.
the if I have querying approach ,
1.) I have to query it from db
2.) and then client can get the data
where as if it is in cache, it is one step process
and more convinient.

can you give me 1 application that does this with database.
check any trading/bank/real time system applicaiton they
will be java caching ...



Tom Kyte
July 27, 2005 - 3:43 pm UTC

1) and if you are plsql in the server.....

2) the cache is distributed, right.

3) in all of the caches all over the place, just like that, for free.

4) databases have been doing this stuff for years and years. Believe it, or not.

5) or just do it in the database, it is quite convienent. And frankly to a coder, they care not a whit if they have to

a) query cache
b) query database

to the coder, it is "six one way, 1/2 dozen the other" it is not any more or less "easy" one way or the other (the amount of code - the same).



One wonders what banks and other systems did before java came to save them?


(suggest you understand that read consistency thing we talked about on another page. It'll give you an appreciation for some of the complexity involved here and how the database sort of elegantly solves some sticky database problems. And please -- remember -- java is a new language, computer systems and "big stuff" predate it as the "answer of everything" by quite a long time. The financial systems I've worked on, been told about, seen have been -- well, database based. Why? Because I work with databases, they ask me to come in to look at them. Might your experiences be clouding your vision too? You think "has to be java because all I see is java", problem is, neither of us see everything.

I'm not saying "database is end all be all", just that database need NOT BE java. Database does what database does just well and goes a lot further than many give is credit for. You can get really big before you need to add distributed caches that need syncronization, and all of the complexity and overhead they bring to the table)

and finally.,

A reader, August 17, 2005 - 5:36 pm UTC

today, finally our "architect" opend a bugzilla bug(TASK)
for me ...

Get rid of PL/SQL

.. and convert it to java/jdbc/txObject/...


Reasons:

1. Debugging will be easier
2. Unit testing will be easier
3. Deployment will be easier



AM I surprized ? :(


Tom Kyte
August 18, 2005 - 3:26 pm UTC

good luck, i forecast many misadventures for you in the future.

oracle forms or JSP pages

mohannad, November 19, 2005 - 11:11 am UTC

dear tom
there is alot of companies who are using JSP pages with oracle database, and not oracle forms because they say that oracle forms are low in performance,i have made a lot of interviews with companies they told that oracle forms is slow ,and that i should improve me knowledge in JSP pages to be able to have a job.

Tom Kyte
November 19, 2005 - 1:40 pm UTC

Umm, well no - forms is not "slow"...

However, I myself prefer html interfaces and don't really give two hoots how the html gets to me (jsp, asp, htmldb, static pages, php - who cares).

The main difference is a jsp will return HTML. Forms - a java applet, reminders of client server "rich" interfaces.

mohannad, November 19, 2005 - 3:52 pm UTC

1.then when do you recomemd using the oracle forms server ,and why you prefer html interfaces is there a technical reason for this
2. and please explain what do you mean by
>>and don't really give two hoots how the html gets to me >>(jsp, asp, htmldb, static pages, php - who cares).


Tom Kyte
November 19, 2005 - 8:16 pm UTC

1) I like html interfaces, matter of personal preference. I don't use any applications other than

sqlplus
thunderbird
telnet
firefox

for hours on end - those "every now and again" applications, I like them in HTML - less interfaces to "learn".

2) don't know how to say it - I don't care if the developers use jsp's, or asp's, or php's, or psp's, or htmldb, or "programming paradigm X" - the underlying technology isn't interesting to the end user - the end result is.

mohannad, November 19, 2005 - 7:55 pm UTC

and please explain the fllowing sentense is details
>>The main difference is a jsp will return HTML. Forms - >>a java applet, reminders of client server "rich" interfaces.
Thank u

Tom Kyte
November 19, 2005 - 8:21 pm UTC

again, don't know how to say it differently.


a jsp will return an html page to a browser.


forms, you run a java applet - and that java applet looks a lot like an old fashioned client server application.

mohannad, November 20, 2005 - 2:14 am UTC

>>a jsp will return an html page to a browser.
>>forms, you run a java applet - and that java applet >>looks a lot like an old fashioned client server >>application.

then when do you recommend using oracle forms server ??

Tom Kyte
November 20, 2005 - 8:24 am UTC

when you don't want an html interface? when you want what looks like a client/server application with its "rich" interface?

mohannad, November 20, 2005 - 7:51 pm UTC

Dear tom
i have read a lot in your forum and from the papers published in the internet and i came with these point ,so i want to know how much it is true
1.that oracle forms sever is a java applets and in the sun web site they say that applets will not be used in the future,
2.and we should use applets(oracle forms server)only in intranet applications and that all the systems now is going to be web application where jsp pages is fully web application opposite to java applet(oracle forms),
3.then does this mean that oracle forms will not be used in the future when developing new applications web application,then where is oracle from this , i am sure that there is a technical issue behind that,
4.then do you recommend using the JDeveloper or to write jsp pages to create web application connected with oracle dataase

5.do you recommend to code the procedures in java and call them from JSP or to write them in pl/sql and also call them from JSP pages.

sorry for this long message but i need to understand these issues better.
Thank a lot


Tom Kyte
November 21, 2005 - 8:27 am UTC

1) oracle forms server is a server, a c program. The client that talks to the forms server running on the middle tier is written in java and is an applet.

I would be very surprised that applets "disappear". Do you have a link (when you reference something, it would be nice to have a pointer)

2) There are internet sites that do use the forms server, not usual - but it could be done.

3) ?

4) I recommend using htmldb myself for many things. However, jdeveloper is definitely a choice.

5) plsql, definitely, no question about it.



mohannad, November 21, 2005 - 12:03 pm UTC

thanks a lot
but i have a question about the fifth point ,i read in a lot of forums that if the procedure do not select,update,delete,insert record/s to the database it is better written in pl/sql as you mentioned,but if the procedure manipulate data "for example calculate the annual salary" is better writte in java.

and i have another question about the Jdeveloper .is the jdeveloper simply ( JSP pages calling pl/sql function and procedures )
thanks a lot
i ask you a lot of questuions because i always find the right answers.

Tom Kyte
November 21, 2005 - 12:28 pm UTC

in order to calculate the salary, you need to select and probably update - catch 22 no?

Plsql can do math very nicely.


jdeveloper is a full blown IDE (integrated development environment). Way beyond "a jsp generator". that is but one thing it can do.

It is also free, you can just download it and see if you like it.

David Aldridge http://oraclesponge.blogspot.com, November 21, 2005 - 2:21 pm UTC

>> Plsql can do math very nicely. <<

In fact Oracle does better math than many other tools.

Consider Informatica, for example. I have had occasion to need to flag cases where x*y=z, but with the floating point arithmetic that Informatica uses I would often find that it was comparing 31.4999999999999999999999 to 31.5, and therefore failing. That was a fun bug to track down.

The workaround for that turned out to be to embed the x*y=z check in an Oracle view and let the RDBMS sort it out for me.

mohannad

mohannad, November 21, 2005 - 5:40 pm UTC

>>I would be very surprised that applets "disappear". Do >>you have a link (when you reference something, it would >>be nice to have a pointer)

here is a part of a presentation held by a software company i copied and paste the part related to applets

"• Before Java Web Start, applets offered Java runtime in the browser
– Centralized code distribution
• Java Web Start does that now
• Limited use now because of browser security limitations
– The choice for rich client is “swinging” back to Java applications
– Small browser add-ins still use applets – stock ticker
• In short:
– Applets are “way last year "

what do you think about this


Tom Kyte
November 21, 2005 - 6:07 pm UTC

I think that doesn't say "applets are going away"

I don't even think that was Sun speaking.

I think that is someone offering their opinion on a furture direction (perhaps for their own product... the context is not at all clear)




You wrote:

"and in the sun web site they say
that applets will not be used in the future"

where - do you have a link?

mohannad

mohannad, November 21, 2005 - 5:48 pm UTC

>>I would be very surprised that applets "disappear". Do >>you have a link (when you reference something, it would >>be nice to have a pointer)

here is a part of a presentation held by a software company i copied and paste the part related to applets

"• Before Java Web Start, applets offered Java runtime in the browser
– Centralized code distribution
• Java Web Start does that now
• Limited use now because of browser security limitations
– The choice for rich client is “swinging” back to Java applications
– Small browser add-ins still use applets – stock ticker
• In short:
– Applets are “way last year "

what do you think about this


Nice discussion, liked the OO example

BradW, April 07, 2006 - 4:19 pm UTC

Hi Tom. Loved the example about OO development. I just wish that working with Object Types was as easy as working with PL/SQL or Java.... Very hard to work with, but the maintenance benefits are nice as you can use Lots of generic patterns for making your code more maintainable compared to "traditional" procedural coding. Last thought, for us Forms developers out there, sorry, no OO on the client, need to use Stored Procedures with no Object Type interfaces...

business logic

sam, August 01, 2006 - 10:44 pm UTC

Tom:

1. I would like to take your opinion on developing a web application with J2EE. What would be the simplest method of doing that? JSP alone, JSP+servlets, JSP+beans, JSP+struts.

2. Can htmldb/apex do almost everything in terms of business fucntions or you have limitations? my understanding you can use the gui but you can't write new pl/sql code or change the form code to interface with it.



Tom Kyte
August 02, 2006 - 11:29 am UTC

I suggest before we go further, you play with APEX - </code> http://htmldb.oracle.com/ <code>

You can use it to build the gui.

You can use it to interface to whatever business transactions you want (I do, I wrote a transactional API to do all modifications to my schema and Joel Kallman put "lipstick" on it in the form of APEX. asktom is 100% plsql)

You can modify virtually any aspect of it.

In fact, just get into it, build a simple application. The simpliest of applications.

And after you do that, take into consider that the interface you just used to build that application was itself written in APEX - APEX version X is written in APEX version X minus a little bit.

plsql

sam, August 02, 2006 - 12:26 pm UTC

Tom:

I am considering apex (strongly). But I dont want people to think i am biased toward oracle products. So I want to present several solutions like (CF, J2EE/jsp, apex) and list advantage/disadvantages of each.

I wonder if you or oracle did something like to save me all that research. I know for one thing that with apex you can develop a web page in a few hours (fast development environment).



Tom Kyte
August 02, 2006 - 12:43 pm UTC

Not that I am aware of - anyone know of anything out there...

real problem will be finding an unbiased look :)

Java, Web Application, Oracle

Greg Brett, August 23, 2006 - 5:05 pm UTC

Tom, pleasure to be exposed to your insight. Regarding web development, I have always been disappointed about the many Java promoted paradigms. I was briefly exposed to an oracle-centric web development platform a couple of years ago called Orbit (Orbit Web Development Suite). Though I never actually utilized this for any real life solutions (we were primarily an 11i Oracle Mfg ERP shop and also used some current java platform flavor), everything I read about Orbit seems impressive. And especially for an Oracle-centric shop. Do you have any perspective about this product. It almost seems to good to be true. I even wonder about it's potential to really take hold in the Web development market. Or at least create a new type of web development paradigm.

Tom Kyte
August 27, 2006 - 6:56 pm UTC

I don't know much about them - but if the concept of what they do excites you - the for free APEX (htmldb) that comes with the database will really float your boat.

</code> http://htmldb.oracle.com/ <code>


J2EE, BC4J

A reader, September 18, 2007 - 1:14 am UTC

"BC4J is a framework -- J2EE is a standard. We have products that conform to the J2EE standard --
BC4J is just a framework on top of that to speed development."

Tom, can you please expand on the above by giving an example. What standards constitute J2EE?

Is BC4J a set of another standards which sit on top of J2EE standards?

Tom Kyte
September 18, 2007 - 4:44 pm UTC

http://java.sun.com/j2ee/overview.html

... The J2EE standard includes complete specifications and compliance tests to ensure portability of applications across the wide range of existing enterprise systems capable of supporting the J2EE platform. In addition, the J2EE specification now ensures Web services interoperability through support for the WS-I Basic Profile. ...

BC4J - Oracle provided "framework/API"
J2EE - you can get that from anyone that makes it (given that they conform to the standard)

OK

Saravanan, March 12, 2008 - 5:12 am UTC

Hello Tom,
where do the Pl/sql objects like Package,Procedure,functions,
triggers etc get stored in the database?
Are they stored in SYSTEM tablespace?

Thanks for your time.

Tom Kyte
March 12, 2008 - 5:33 pm UTC

always in system

High Performance Design

hrishy, October 20, 2008 - 8:42 am UTC

Hi

I came across this link http://martinfowler.com/bliki/Transactionless.html

It basically says referential integrity is coded in applications and there are no transactions have you come across systems like that ?
Tom Kyte
October 21, 2008 - 11:59 am UTC

first of all - they are using database transactions - it is UNAVOIDABLE. They might autocommit (that is just stupid), but they are in fact using transactions.

Second - I'd like you to give me the logic, step by step, detail by detail, for N users concurrently accessing the data - that in Oracle allows the application to enforce referential integrity in the application.....

This logic should be
a) correct
b) scalable

just like "alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno)" is.

Unless it includes the lock table command, without a commit right after it, it is wrong.

Or unless it involves lots of tricky triggers (which most people will not code correctly) - it is wrong.


In short, you have to enforce some degree of serialization - else you are not enforcing data integrity.



Oracle EBS

Bhavesh, October 21, 2008 - 2:53 pm UTC

Dear Tom,

There are lots of applications out there that do not implement referential integrity into the database including Oracle EBS. I'm curious to know your opinion on the design approach.

Thanks,
Tom Kyte
October 21, 2008 - 4:02 pm UTC

is it not obvious what my opinion is on this?

Oracle EBS

Kevin, October 21, 2008 - 4:54 pm UTC

the vast majority of our day-to-day issues/bugs with EBS are caused by orphaned data. I wonder how much of this would be eliminated with simple db integrity.

PL/SQL vs Java - my opinion

Michal, November 17, 2008 - 2:55 pm UTC

Personally I prefer writing logic in Java and use Oracle as data storage only. Why?

(1) I have 3 application servers. It is easy to make changes simply deploying WAR on each of my servers one by one. While an application on one of the servers is restarted, conntected clients automatically move to the two remaining servers. In comparision - replacing pl/sql code in a database in practice requires stopping ALL traffic (because of locks). If something goes wrong, the break gets longer and nobody can work.

(2) I can easily debug Java code using open source software (like Eclipse or NetBeans).

(3) Every change made to code can be tracked (who made a change and when) because everything is stored in CVS

(4) A few SQL statements + commit can form a transaction as well as PL/SQL procedure does. And they also may be reused.

To be honest I must also say that I don't know Oracle solutions besides Oracle DB. Maybe they help resolve shortcomings mentioned earlier...

And I also know problems connected with solution preferred by me (for example greater network traffic)
Tom Kyte
November 18, 2008 - 7:35 pm UTC

1) it is easy to make the change in the database - once and for all - regardless of WHAT services access the database. Application servers are one way - there are lots of others.

It does not require stopping all services

And you know what, what happens when you are running N versions of your logic at the same time?

2) I don't really care if my software if open, closed, sideways, upside down - I just want to have it.

And I have plsql debuggers, I have had debuggers for many many years (before Java existed even). And they are free too.

3) so should plsql

4) by java (end your statement in "by java")


Business (il)Logic -> the Mythical Business Layer

Duke Ganote, January 27, 2010 - 10:14 am UTC

Alex Papadimoulis covers it well:
http://thedailywtf.com/Articles/The-Mythical-Business-Layer.aspx

business logic (n.) — any program code (“logic”) that pertains to the purpose (“business”) of a software application.

For example: "The Customers database table, with its Customer_Number (CHAR-13), Approved_Date (DATETIME), and SalesRep_Name (VARCHAR-35) columns: business logic. If it wasn’t, it’d just be Table032 with Column01, Column02, and Column03."

java vs pl/sql

Manohar, June 08, 2010 - 6:12 am UTC

Hi Tom,

R u really feel that the java is more faster than pl/sql.
I have confusion on this. i wanted to write business logic where shall i go? either pl/sql or java...

Look ..my data frequency is more that 4000 data per 3 min ..it may increase.. i deal with data which store in database oracle 10g.

one more interesting thing is i will always use select,update or insert sql statement between data processing..

if see then there is no computational code in between.
IS the better shall i write business logic in java.

Regards,
Tom Kyte
June 09, 2010 - 8:49 am UTC

"U" isn't available, "U" is dead as far as I know. Look it up, it is true.

http://en.wikipedia.org/wiki/U_of_Goryeo




my data frequency is more that 4000 data per 3 min

that means nothing, data does not have a 'frequency'.


It is highly doubtful that you want to put the sql in something that resides outside of the database to pull data from the server, send it over the network, process it and send it back.

It is highly probable that a store procedure would be beneficial in terms of performance and maintenance.

hibernate or pl / sql

George, November 19, 2010 - 7:24 pm UTC

Hi tom , i am facing the following problem and i am sure that you will be able to support me:-
1. I am a student in doing a master degree and i am required to develop a web application,
2. i have about 3 years of experience in developing application using Oracle forms and oracle reports and writing pl/sql procedure , function, trigger.
3. And the course instruction said that we should use java or .net for our 3 teir application ,
4. so i was thinking of using JSP pages and eclipse for the development environment and to write my business login using pl/sql stored procedure and pl/sql trigger and to call these procedures and functions using the JSP pages through the JDBC.
But i heard that this architecture are not up to date and writing the business logic in hibernate will be better as it is more up to date technology .
So i am really confused on this should i go with JSP / PL-SQL /Oracle database or with the hibernate approach , if we exclude my previous knowledge , i mean which is technically better.
HINT:- my web application will be to develop a social network system , (adding friends, upload photos, recommend friends,etc)
I would appreciate you help and support and many thanks in advance

Tom Kyte
November 20, 2010 - 5:26 am UTC

who said that the way most people do things, the way things have successfully been done, the proven scalable way to accomplish things is "not up to date"

I'm horribly out of date I guess :(

it is usually try that you will be most successful using tried and true technologies that you actually know.


https://shop.oracle.com/

if you want to buy something from Oracle - you'll be running an application that uses nothing but plsql... for example.


If your professor is guiding you towards a set of tools to implement with, you may well have to forgo the "best way for you" and do it "they way they think it should be done" in order to get the grade however. That might be the reality of the situation.

hibernate or pl / sql

A reader, November 20, 2010 - 9:11 am UTC

Thanks a lot tom for your kind reply and i appreciate it very much, but can you please be more specific in your answers , so do you recommend me to write my business logic in pl/sql function, procedures and trigger as i am going to use oracle database , and call these pl/sql program units from my JSP pages using JDBC. Actually my professor did not recommend any specific tools to use , but i have to select an architecture that are well build.
1. So will this approach be an excellent architecture to build my social network web site.?
2. and are there commercial web sites that uses this architecture, which i am sure that there are many?
3. Last question do u mean in your reply “if you want to buy something from Oracle - you'll be running an application that uses nothing but plsql... for example”; if i want to buy oracle ERP for example they uses pl/sql or Oracle BI they also use pl/sql, is this what u meant in your sentence?
Many thanks in advance.


Tom Kyte
November 20, 2010 - 9:37 am UTC

I, as a data processing professional, would go the pl/sql stored procedure route. As much of the data processing code would be as close to the data as possible - for maximum reuse (everything can call a stored procedure), clean separation from the UI, maximum performance and other reasons.

1) since you are familiar with the technologies and don't have to learn anything new to program with really, your chances of success go up measurably - yes.

2) yes, I pointed you to one - apex.oracle.com is a another, lots of sites do this.

3) I pointed you to the Oracle store, it uses this architecture. If you wanted to buy something from oracle, you'll be using that architecture to do so.



One last bit of advice, you are going for your advanced degree - probably expecting to enter the "real world", business, a job and all.

"U" isn't available, "U" is dead as far as I know. Look it up, it is true.

http://en.wikipedia.org/wiki/U_of_Goryeo

Take that to heart. Using "IM speak" in professional discourse only makes you look/sound like a 12 year old - time to start avoiding it like the plague. People that email me or write to me using it lose 50 IQ points right off the bat :)


hibernate or pl / sql

A reader, November 20, 2010 - 10:57 am UTC

thanks, then do u mean that i should not ask such questions in this forum ? if so them ok. but i though that this forum is suitable to discuss differences between two technologies without being a 12 years old .....

Tom Kyte
November 21, 2010 - 9:07 am UTC

I mean that by using "u" and IM speak, you lose IQ points and you will lose interviews, job offers, etc. It was just advice, take it

or leave it.

@READER: Hibernate or PL/SQL

Duke Ganote, November 21, 2010 - 7:19 am UTC

Asking questions is OK. Not searching the site for answers first, not OK:
1. "Regarding Database Design"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2048698000346552812
2. "oci or hibernate"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1001391300346444059

I also recommend this article which a) references a Tom Kyte question and b) the article's comments, which debate the answer:
http://thedailywtf.com/Articles/The-Mythical-Business-Layer.aspx

A reader, November 21, 2010 - 4:04 pm UTC

<quote>
"U" isn't available, "U" is dead as far as I know. Look it up, it is true.


http://en.wikipedia.org/wiki/U_of_Goryeo


Take that to heart. Using "IM speak" in professional discourse only makes you look/sound like a 12 year old - time to start avoiding it like the plague. People that email me or write to me using it lose 50 IQ points right off the bat :)

<quote>


I like that, whats your opinon where coworker use such acronym in the email?


Thanks




Tom Kyte
November 23, 2010 - 12:32 pm UTC

I like that, whats your opinon where coworker use such acronym in the email?


The loss of IQ points is immediate and permanent. I don't point it out (unless they also copied a customer or something like that) like I do here, but that is because on this site - everyone that posts has been warned that I will make fun of them. (the review page starts with that warning)

U? NO! , you know

Duke Ganote, December 06, 2010 - 9:59 am UTC

"Unfortunately, your carefully-worded requirements specification, which accompanied this question, has somehow become detached. Instead we found incomplete and disjointed IM-speak, which means we are struggling to understand the problem."

http://www.simple-talk.com/community/blogs/philfactor/archive/2009/01/09/71609.aspx
Tom Kyte
December 07, 2010 - 9:43 am UTC

I figure my fair warning - in bold italics - gives me the ability to state it as I see it :)

Business Logic - PL/SQL Vs Java--TDD

Nakul Garg, September 14, 2011 - 1:59 am UTC

Hi Tom,

Its just a suggestion as per my experience i beleive as of now that we do have limitations for test driven development approach in Oracle whereas in JAVA it plays a crucial role during code development cycle. If by any chance we can cover this thing in writing up our pl/sql code then it would be of great help to Oracle users .
Let me know if any tool or approach do exists for TDD in oracle that may be I am not aware of it .
Tom Kyte
September 14, 2011 - 7:19 pm UTC

there are lots of unit test thingys out there - sql developer has some, quest's products do.

What do you need for your test driven development process? You write a test (that fails) and then work to make it succeed. You need something to run your test cases.

PL/SQL vs java

YG, February 15, 2012 - 4:29 pm UTC

Hello,
I need your advice. I read this thread and it looks I am on a right track but I have a disagreement with my DBA.
I work with system where majority of code is in application (java) and database is used for storage (Oracle11gR2). Queries are dynamically generated in java. They are very complex, performance is far from good, troubleshooting is a nightmare. I developed a PL/SQL package. Application calls package procedure, passes it array (nested table of objects) with input parameters, procedure retrieves data from database and return application array of data (nested table of objects). PL/SQL code in package is static, there is no need to generate it at run time. Performance is better, troubleshooting will be much easier. My main problem - our DBA tells me that my change will cause scalability problems; database becomes a bottleneck; database will become a single point of failure. Can you help me understand it? If I have X application servers that send X generated queries with different parameters into single database; or I have X application servers that send X calls of the same procedure with different parameters to single database - where's scalability problem? Code in package is tuned and well written. I can easy expand it to add more queries. Application code becomes easier too. What's wrong with moving code from java to PL/SQL?
Tom Kyte
February 15, 2012 - 6:59 pm UTC

Can you help me understand it?

if you mean, can I help you understand why you need a new DBA - maybe I can.

Let us see....

You have something that uses less overall resources on the database.
It does presumably less IO
Uses presumably less latching
Is observedly faster
Uses less CPU

and your DBA thinks "that'll kill scalability".

You know what will kill scalability? A billion java threads throwing infinitely bad SQL at a database - doing things in a "non database" fashion.

You know what a DBA should be *killing* to have? A team of developers that embrace the database, that know that doing things in PLSQL that deal with data is simply THE MOST EFFICIENT (and scalable by the way) approach, that understand that being able to quickly and effectively track things (queries, etc) down and fix them is the way to go.


What's wrong with moving code from java to PL/SQL?

Unless it was something like a fast Fourier transform - nothing whatsoever. If you are doing data manipulation - there is nothing more efficient than the databases stored procedure interface (note: I did not say plsql, I am talking in general, all databases).


I am 100% in disagreement with your DBA. They are the anti-DBA.

fast fourier transform

Sokrates, February 16, 2012 - 4:32 am UTC

...
What's wrong with moving code from java to PL/SQL?

Unless it was something like a fast Fourier transform
...


are you kidding ? You would do a fast Fourier transform in java ?
not in a single sql ?

see
http://www.adellera.it/investigations/nocoug_challenge/index.html
for a howto

Tom Kyte
February 16, 2012 - 7:22 am UTC

hah, I remember that :) indeed. It has been years since I've looked at that query...

I need a new example I guess

try psp.web to develope full functional web app using pl/sql

Kaven276, April 17, 2012 - 11:34 pm UTC

All logic including db manipulation and http/html process can be integrated togother, beyond
oracle's ancient psp, I invent psp.web,
see https://github.com/kaven276/psp.web

PSP.WEB use PL/SQL programing language to do web development, It's a language of DB stored
procedure, that is different from most of the other web developing languages and platforms such as
J2EE .Net, PHP, RUBY. With PSP.WEB platform, PL/SQL can use PSP.WEB APIs to gain http request
info(request line parts, http header info, form submit, fileupload, request body and etc...), do
nature data processing (insert,update,delete,select and PL/SQL code), and print page (header info
and page body) to http response. It's the most proper way to develop database(oracle) based web
applications.

PL/SQL has natural advantages, using PL/SQL for developing web site/application is seductive. But
naturally, PL/SQL have no touch with http protocol, If we provide a http listener (such as nodeJS
based) that can communicate with oracle PL/SQL, we can invent a whole new stored-procedure based
web platform that is the most integrated and convenient platform taking both application layer and
database layer together. That is just the way PSP.WEB do. Now PL/SQL leverage it's power to web
development in it's most. All the sections below will tell you the unique features of PSP.WEB that
other web-dev techs do not reach.

Holy war? OR theory vs practical

Rick, September 26, 2014 - 3:58 pm UTC

Good questions yield answers (hopefully optimum answers):

Questions:

1 is your organization considering changing databases ?
1 Is the nature of your business relational ?
1 Do you wish a cost effective, practical architecture ?
1 Do you believe that new is new - not necessarily better ?

Answers: ( warning - opinions being expressed )

1 The most powerful tool for the architect/developer: the ORACLE database. Use it wisely. Use it to the utmost. Almost all organizations are not considering a database change.

2. Most businesses naturally lend itself to the relational model(customer must exist before a sales order, etc)
3 My experience with J2EE, JEE , java-XML, message centric architecture - not cost effective - not practical. Database independence is far too theoretical - not practical. 4 Follow Tom's counsel - perform as much as you can - within the ORACLE database shell. Follow his hierarchy
a SQL
b PL/SQL
c JAVA
d C
( I always preferred grades: a or b
REDUX: the most powerful tool for the architect/developer:
"The ORACLE DATABASE" Use it to the utmost.