Skip to Main Content
  • Questions
  • Shall we continue to write PL/SQL stored procedures ?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Amine.

Asked: November 10, 2012 - 9:34 am UTC

Last updated: July 31, 2013 - 4:11 pm UTC

Version: 9.2.8

Viewed 1000+ times

You Asked

Hi Tom,
Last week, I've got an Oracle University course about building ADF Faces clients for EJB and JPA.
Our company is using Oracle Forms since 2000. We have a big investment on it, we have developped huge packages (about 20 packages)
to handle business logic on them. And it works fine. We are studying the opportunity to go to J2EE in order to lighten our DB server.
From the course we had, I've understand that the middle tier (the app server) will handle the business logic (all business logic, not data logic (eg PK, FK, CK)) and in fact the DB will be considered as a black box.

I see things like "Transactions". I don't understand why we handle transactions when Oracle does that perfectly.

I see things like Named Queries. We can call stored procedures from Session EJBs, but then, we will stay in a client/server architecture, the app server will do nothing for us.

I think we have these alternatives if we want to move to a more than 2 tiers architecture :

1. continue with Forms 11g : we're still in a client/server even we have an app server, cause Forms interact directly with DB objects,

2. continue to write business logic into PL/SQL packages and invoking them from Session EJBs : not a pure client/server architecture but not really far from,

3. move the business logic to the app server (Re-write it in Java) : 3 tier architecture but there will be cases when we will re-invent the wheel. And if it's the case, How about all the optimizations made on the RDBMS, all new features ?

Which is the best in your point of view to build really maintainable applications ?

and Tom said...

... We are studying the opportunity to go to J2EE in order to lighten our DB server. ...

rethink that - You will undoubtedly find the opposite to be true.


I disagree with your conclusion in #2 and #3. You can have a 3 tier architecture with a single computer and just the database (application express for example, no app server even needed). 3 tier is an architecture - it doesn't mean you have to have three computers or even three pieces of software. It just says "you have a client, you have logic and you have data and they shall remain logically separate"

If you have an EJB calling a stored procedure - you are three tier, completely.


I've been consistent in this regards since I started in this industry in 1987. data logic close to the data. business logic (making things look pretty) in the application. data in the database. put the data logic as close to the data as possible (security, transactions, etc) and you'll find them to be infinitely reusable.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2143974700346554115

Rating

  (19 ratings)

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

Comments

data

A reader, November 12, 2012 - 6:33 pm UTC


Toon Koppelaars, November 13, 2012 - 2:50 pm UTC

"We are studying the opportunity to go to J2EE in order to lighten our DB server"

You will put more burden on your db-server if you are going to use it in a row-by-row approach, which is what JEE is all about.

The bad news is, you'll never be able to convince the new kids on the block of this fact.

business logic making things look pretty

George Joseph, November 13, 2012 - 8:21 pm UTC

Tom
You mention that business logic(making things look pretty) should be done by the application layer.
Does this mean if i have a requirement to show a comma/pipe/; separated values(say account numbers of customer id) in a single column, you would prefer it need to be done at the application layer rather than write a hierarchical query.

Could you elaborate on what is business logic

Tom Kyte
November 14, 2012 - 8:21 pm UTC

no, you have asked for the data to be in a specific format, you were not asking for it to be in red.

(I'm not convinced this thing called 'business logic' exists as a standalone entity - a lot of it seems to come down to "data logic").


There is the user interface, there is stuff that manipulates the data, there is the data.

User interface shows something in red.

Manipulating data involves connect by sometimes.

Data is the database.

What is business logic code?

Toon Koppelaars, November 14, 2012 - 12:11 am UTC

Tom Kyte
November 14, 2012 - 8:25 pm UTC

well said

Good article, but I don't agree with this as a standard

Martin, November 14, 2012 - 4:47 pm UTC

•DL-code can also be called by the DBMS via database triggers that fire as a result of the execution of DML-statements originating from wBL-code

As Tom states, put your code in a package where people can see it.
Tom Kyte
November 14, 2012 - 8:32 pm UTC

uh oh ;) the other TK will probably be back... and that is good.



An example of business logic in PL/SQL

Kim Berg Hansen, November 15, 2012 - 1:40 am UTC

7-8 years ago my company started selling wheels. We built an application to find for a given car which rims and tires in our assortment will fit that car.

It is built in a PL/SQL procedure (packaged) that accepts a car id and returns a cursor variable with all necessary data about the rims, tires, stock level, delivery time and so on. (Actually the stored procedure basically is just an "open out_cursor for <huge select statement>" :-)

That procedure is called from two widely different environments:

- Our old legacy unixbased system calling the database via OCI. Salespeople use this in VT220 characterbased terminal emulation to sell wheels to customers in the physical shop.

- Our webshop on classic VBscript ASP in IIS using OO4O to call the database. Here the asp code calls the procedure and creates HTML and script code for a graphical WheelGuide(R) letting customers view the possible wheels on drawings of their car model.

The webshop WheelGuide(R) was originally written in JavaScript, but that was rather slow image manipulation. So after a few years it was re-created in Flash (much quicker.) Changes needed in the PL/SQL: None!

Now the WheelGuide(R) is re-written yet again to use HTML only with a completely different layout making it much better on iPads. Changes needed in the PL/SQL: None!

The PL/SQL has had just a few tiny refinements over those years - either to improve the logic or add extra functionality.

But the logic is easily reused in more than one GUI environment and can survive the changes to the web environment.

I'll go on writing SQL and PL/SQL for my logic - it'll survive whatever is the newest buzz in GUI application design ;-)
Tom Kyte
November 19, 2012 - 9:09 am UTC

I've said many times - if you want to really achieve portability and code reusability, you'll use the heck out of the stored procedure language in your database of choice - you'll put as much as you can in there - simply because the UI (user interface) languages like Java, PHP, etc change so often as does the architecture (from three tier in the 80s and before, to client server, back to three tier (but with too many types of middle tiers to count), to mobile, to ....)

Harmful triggers...

Toon Koppelaars, November 15, 2012 - 7:35 am UTC

> •DL-code can also be called by the DBMS via database triggers that fire as a result of the
execution of DML-statements originating from wBL-code
>
> As Tom states, put your code in a package where people can see it.
>
> Followup November 14, 2012 - 8pm Central time zone:
> uh oh ;) the other TK will probably be back... and that is good.

Short answer: triggers have several use-cases. Some of them are harmful, one of them isn't. Unfortunately the latter use-case (using them to implement DL-code) is rather difficult to be executed correctly: need to deal with transactions executing at the same time, etc. But it *can* be done efficiently and correctly.

Btw. you need to deal with TX's executing at the same time in your package too, do not forget that.

I agree with Tom on the former use-cases.
Tom agrees (albeit somewhat reluctantly I sometimes think :-) ) on the latter use-case.

Long answer: harmfultriggers.blogspot.com (I know, I know it's not done yet).

Final answer: Oracle to support the ISO/ANSI assertion concept.


mfz, November 15, 2012 - 8:33 am UTC


<quote>
( The bad news is, you'll never be able to convince the new kids on the block of this fact. )
</quote>

I agree to this statement 100 % .

Even last week , I was in a project kick off meeting . I was trying to convince the dev. team to use stored procedures ( as XAPI ) for business/ data logic . Use Database functionality as much as possible .

But in the end , they are reluctant to use DBMS features; instead they are going fot MVC 4 architecture ; being that is the latest trend in the application development.

I really wish , these developers understand the concept / longevity of the data ; not just few buzz words .

my head hurts

Michael Kutz, November 15, 2012 - 8:51 am UTC

Toon,
As neat as 'assertion' seems, I will never use it.
It seems to be worst than triggers.

How would it even work?

Due to 'transactions', you really can't 'assert' something (that crosses tables and rows) on a single DML statement.

I mean, what happens if I a large data set and I need to 'insert' the 'manager' before the 'clerk'?

Do i get the 'assertion fail' at the row level?
Or do I get it at the 'commit' time?

If at the row level, FROM EXPERIENCE, this will be slow=true.
(god forbid if the assert's select statement does a full-table scan on a 30GB table)

If at the 'commit' level.. what happens if it fails?
does it do a 'rollback'? and do you really think the 'new kids on the block' are actually going to catch that error and handle it correctly?

Heck, what would happen if someone said "hey, all clerks need to have a manager"?? Instant DoS via Catch 22.
You can't insert a 'clert' without a 'manager', you can't insert a 'manager' without a 'clerk'.

The only SAFE way i see is:
begin
-- bulk load data.. THINK IN SETS!!!
insert ... select * from new_dept_temp;
insert ... select * from new_employees_temp;
assert_api.managers_need_clerks();
assert_api.clerks_need_managers();
-- depending on application
commit;
end;

Tom Kyte
November 19, 2012 - 9:47 am UTC

.... If at the row level, FROM EXPERIENCE, this will be slow=true.
(god forbid if the assert's select statement does a full-table scan on a 30GB
table) ...

do you use constraints?

because you just described them...

think "unindexed foreign key" and a delete on the parent table (with a 300gb child table..)


assertions would be statement level - and probably permit for deferring just like a constraint - if they were implemented.

they are a rather cool idea in my opinion.



why would clerks and managers be a catch 22? Unless you had a rule that said every manager has to have at least one clerk and every clerk has to have a manager - in which case either

a) a single sql statement to create both the manager and clerk
b) a deferrable assertion

business logic

George Joseph, November 16, 2012 - 12:43 am UTC

Thanks Toon and Tom.

The blog was of excellent reading material of what is business logic.(anything not UI or Data logic is business logic)

I am curious why you would call your blog Helsinki declaration :-)

Why it's called like that?

Toon Koppelaars, November 16, 2012 - 8:50 am UTC

@Michael Kutz

S, November 16, 2012 - 9:39 am UTC

Michael, you state that you'll never use assertions and then go on to show how you would enforce a condition. Questions:

1) Why don't you trust that the DBMS that you paid a substantial amount of money for cannot do it the right way?

2) Why do you think you can enforce a constraint better than your DBMS in a general case?

S

Amine, November 17, 2012 - 6:47 am UTC

...If you have an EJB calling a stored procedure - you are three tier, completely...

Could you explain me in detail how does this work ? Because our teacher said that when using SP you're still in C/S architecture...

THanks in advance Tom,

Tom Kyte
November 19, 2012 - 10:21 am UTC

client server is when a program running on a client machine connects directly to a database - there would be a one to one relationship between instances of the program running on the client and database connections. People would connect to the database as themselves - using their own username and password.

How in the world could a web browser - connecting to an application server - that connects via a connection pool to a database - be confused with client server like that???


running a stored procedure is no different than running a sql statement physically - web browser -> app server -> database, I don't care of the sql the app server submits is 'select * from emp' or "call get_emp_data()" - you are still three tier.


Three tier is an architecture that lets you take a large client base and multiplex them down into a small set of connections to a shared resource such as a database.

It is something we did in the 80's and before - ISPF green screens -> CICS -> IMS/IDMS/whatever.

client-server means "client connects to server, one to one"

three tier means.... client connects to transaction monitor connects to database.





but the really cool thing is - you could be three tier or client server with a single code base - if only you actually use stored procedures!!! because you can call the same bit of data logic from your app server, that you can from your phone, that you can from your client server app and so on..

that is - if you want portable reuseable code, you would use the heck out of your database of choice's stored procedure language!!!


Three Tier

djb, November 18, 2012 - 2:04 pm UTC

Amine, You are still three-tier because:

Client <-> EJB <-> DB Stored Procedure

The stored procedure is, among other things, a layer of protection to the actual data.

Amine, November 19, 2012 - 10:34 am UTC

clearly and well said Tom, Thanks again !

Thought-provoking

Michael, November 21, 2012 - 5:23 am UTC

Listen to what Amine said:

"Last week, I've got an Oracle University course about building ADF Faces clients for EJB and JPA."
...
"Could you explain me in detail how does this work ? Because our teacher said that when using SP you're still in C/S architecture... "

So, that is what Amine was told at Oracle University!
Might be an isolated case - but anyway, apprehensive and disturbing that is...
(And wrong)...

Assertions

Toon Koppelaars, November 21, 2012 - 6:19 am UTC

Michael,

I explain in detail in "Applied Mathematics for Database Professionals" (chapter 11 I think), how a DBMS vendor would have to support assertions for you (and me) to use them.

Your preassumption is that the assertion *text* as is, is used to perform the check. That of course should not be the case. The DBMS should be smart enough to a) given an assertion, and b) given a transaction, determine 1) if at all the assertion needs to be checked, and 2) if so, how to do that in an efficient manner. And in a manner that supports simultaneous transactions.

Checks, primary keys and foreign keys, are just special cases of assertions. They are types of constraints that are so ubiquitous in every database design, that the SQL standard has introduced special case shorthand notation for them. This has enabled DBMS vendors to produce special case constraint-validation code inside their engines for them.

A check such as CHECK(job='MANAGER' or SALARY < 9000), can be asserted as:
create assertion CHECK_EXAMPLE as
(not exists(select 'a violation'
            from EMP e
            where not(e.job='MANAGER' or e.SALARY < 9000))
)
/

A primary key such as constraint EMP_PK primary key (empno), can be asserted as:
create assertion EMP_PK as
(not exists (select 'duplicate rows'
             from EMP e1
                 ,EMP e2
             where e1.EMPNO = e2.EMPNO
               and e1.rowid != e2.rowid)
)
/

A foreign key such as constrint EMP_DEPT_FK foreign key(deptno) references dept(deptno), can be asserted as:
create assertion EMP_DEPT_FK as
(not exists(select 'an emp'
            from EMP e
            where not exists(select 'a matching dept'
                             from DEPT d
                             where d.DEPTNO = e.DEPTNO))
)
/

When declared as assertions, I would expect the DBMS to produce the same efficient validation code as currently it does with the 'special case' syntax. This requires sophisticated parsing and analysis of the assertion text. The real challenge (for DBMS vendors) is to produce the special case constraint-validation code in-flight when an assertion is declared.

If Oracle ever gets around implementing this: a bright future would lie ahead. There would be light at the end of the current midtier centric Java tunnel. And SQL DBMS's would have evolved a significant step towards being a relational DBMS's.

Toon

CREATE ASSERTION is a solved problem

E. Smout, July 22, 2013 - 10:13 am UTC

CREATE ASSERTION is a solved problem.

But the world doesn't really seem to want to believe that.

Anyhow, with CREATE ASSERTION solved, the need for coded SPROCS for the purpose of database constraint validation goes away, as does the need for materialized views for the same purpose, as does the problem of existing limitations on materialized views.
Tom Kyte
July 31, 2013 - 4:11 pm UTC

ok, i'll bite.

how is it a solved problem?

How CREATE ASSERTION is a solved problem.

Erwin Smout, October 02, 2013 - 7:28 pm UTC

Sorry about the delay. Not been checking here for a while for personal reasons.

Solved as in "I know how to enforce any arbitrary database constraint efficiently in a relational DBMS.". Getting to the solution has taken me several years, so don't expect me to just go give it away here and now.

I recently submitted a somewhat elaborated article on the subject to the NOCOUG journal, and the result of my work is publicly available as a fully operational system for anyone to try out.

Is there anything more specific (other than "give me the algorithm") covered by that "how" question you would like to know ?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library