Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question.

Asked: November 27, 2007 - 11:56 pm UTC

Last updated: March 13, 2009 - 2:48 pm UTC

Version: 9,2

Viewed 1000+ times

You Asked

Is PL/SQL Web Toolkit (which comes along with Oracle database) better or Application server (9ias) in terms of ease of developement, deployment, performance and security??

and Tom said...

well, you cannot really COMPARE them.

The plsql web toolkit (which I would use underneath APEX - not directly to write an entire application) is an API, a programming interface.

The Application Server is a platform that executes applications.

That is like asking 'which is better to develop with - assembler or unix'

So, that said - I would recommend you look at - that would be the right way to use the web toolkit. And you can host your applications using EITHER the Apache webserver and mod_plsql module that comes with the database - OR - you can host your application using the Application Server, which comes with lots of other things - beyond that which the Apache Server+mod_plsql give you.


  (45 ratings)

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



A reader, November 28, 2007 - 10:43 pm UTC


I have 50% of an application developed in Pl/SQL tool kit (Stored procedures). The remaing 50% is client server using Power Builder.

Would make technically any sense to migrate the 50% PB to web pages using APex instead of toolkit? Can that part be easily interated with the other 50% web toolkit code.

Tom Kyte
November 29, 2007 - 8:34 am UTC

APEX - definitely.

if you are using power builder, it is highly unlikely that your existing plsql is built with the plsql web toolkit - as that works only with mod_plsql and you are not using that.

So, your existing code is likely just "your transactions, written in plsql"

and APEX would be an excellent way to repurpose them.


A reader, December 28, 2007 - 9:58 pm UTC


The 50% powerbuilder application does not have anything in PL/SQL. IT is all PB. You are saying to convert this client/server app to oracle web application using Apex (new development using Apex).

Now, the remaining application exists/developed in web toolkit or Stored procedures. Would not I have integration issues when I have 50% of application in Apex and 50% customized stored procedures?
Tom Kyte
January 01, 2008 - 5:28 pm UTC

I'm saying if you are getting rid of your client server interface, APEX is definitely something to look at.

Asktom is 100% customized stored procedures for the transaction processing component and the rest is APEX out of the box functionality. APEX *is* plsql - integrating the two isn't integration, it is "just using the stuff"


A reader, January 02, 2008 - 1:05 pm UTC


Yes, but to make sure I will have an application with many existing pages (Stored procedures) that can be modified in either (TOAD or SQL Navigator or SQL Developer) AND new set of pages that can be edited by Apex GUI only (Code created by Apex can not be modified via IDE tool).

Is this the correct description for the end result and do you forsee any potential issues.

I assume you can submit/call one page to another and vice versa since this is more of HTML feature.


Tom Kyte
January 02, 2008 - 3:18 pm UTC

the stored procedures are stored logic - so what if you edit them in toad or whatever you want? Not sure what issue you see here.

Your stored procedures are your transactions, your APEX application invokes transactions - as long as the interface (inputs/outputs) to the transactions remains the same - so what?


A reader, January 02, 2008 - 9:31 pm UTC


In conclusion, i can have one page where my "presentation logic+transaction logic" in one stored procedure using Web toolkit


another web page using Apex that calls a user created stored for transaction logic or define the transaciton using apex if possible. The presentation logic is only editable using Apex GUI here.

I can have the the web toolkit page post/call the apex page wihtout any issues.

Tom Kyte
January 03, 2008 - 11:04 am UTC

you could, there might be issues with session state - but you already have those in spades with a 50% stored procedure producing html and 50% powerbuilder app.

You would end up with likely a really ugly looking application - as APEX will enforce a very common look and feel - and your existing application will not.

Doable - sure, assuming you send the right cookies around and fling the right urls.

Want to do - probably not, likely you want to bite the bullet, re-engineer your existing "let us put interface and logic all together" set of routines into a set of "let's just do logic" routines and call them from APEX.


A reader, January 03, 2008 - 2:09 pm UTC

Well now, the PB client/server app provide Functionality A,B,C. The webtookit web application provides Functionality E,F,G.
Each has its own main menu and login. Webtoolkit uses htp.print for all html output.

In reality, if integrated, they should be under one application and one login account.

So we will have issues with session management + appearance.

What do you recommend to do in this case?
- Convert the PB screens to web toolkit procedures and have one application all based on web toolkit
- Convert the PB screens to Apex and convert existing web toolkit stored procedures to Apex and have one application all based on Apex.
- Use Oracle PL/SQL Server pages (PSP)
- go to J2EE architecture using Java or Coldfusion or something else?
Tom Kyte
January 03, 2008 - 3:42 pm UTC

and it doesn't sound like PB client application was integrated with web toolkit (lovingly hand written, taking lots of time) application.

I would convert all to APEX.

PSP is just the web toolkit, but harder. I would not recommend that at all, ever.


A reader, January 03, 2008 - 10:19 pm UTC


1. Would apex have any limitations or you can do anything you can do with webtoolkit?

2. Does it have a solid market share or it would become obsolete like WebDB and ORacle Designer?

3. What would be Apex selection factor against something like JSP/Servlets or ColdFusion (if you want to convince a client)? Is it ease of use and faster development (1/3 of time less than using java).

Tom Kyte
January 04, 2008 - 11:38 am UTC

1) no, it is simply a framework that will enforce a common look and feel, support maintaining a state for you, and increase the speed with which you can develop an application hugely.

2) - it is built using apex. for example.

3) do you know java? if not, add at least a year to development of a J2EE application (and remember your first real application in a new language, especially one as huge as this, will not be one you are proud of in 5 years time) or consider yourself obsolete and to be replaced by another team that claims to know J2EE.


A reader, January 04, 2008 - 3:19 pm UTC

yes, let us say i know the basics of java programming.

What would be the argument then of using Apex over Java/JDevoloper.
Tom Kyte
January 04, 2008 - 3:23 pm UTC

knowing the basics, ok, add 11.5 months to getting from here to a j2ee application.

If you have many developers that need to work as a team building an application (10's of programmers), you might need to invest in the infrastructure of a full blown j2ee environment.

If you have a rather small development team, you will not.

Metalink was built entirely in APEX by between about 4-7 core people, rapidly. With the ability to change on a dime.

APEX excels in its simplicity. That is the point. j2ee is anything but simplicity.


A reader, January 04, 2008 - 5:19 pm UTC

The problem with APEX or the way it was used to build metalink/asktom is the invalid HTML that it generates.

I don't know if it can be fixed by changing the APEX templates. Valid HTML is something one must consider these days when selecting a framework.

Just go to and plug to see how many errors/warnings are there.

HTML is just like any other language -- it needs to be validated using the latest standards.

It's all about the templates.

Carl Backstrom, January 04, 2008 - 6:41 pm UTC

I've built several APEX applications that are 100% XHTML compliant in APEX so it can be done and fairly easily, it's mainly in the templates, sometimes there is a hicup from generated HTML but when there is we fix it.

Some of the errors being thrown are intentional to get around crossbrowser issues, I know because I built most of this theme.

Most the errors are from this the parser throwing errors about xhtml compliant syntax.

You should take a look at this article , by Douglas Crawford, one of the main web technology people at yahoo.

the good part says this

XHTML is not the solution to a problem that concerns anybody except the guys who have to write parsers that convert markup into DOM trees.

RE: APEX vs. "Java"

Stew Ashton, January 05, 2008 - 5:29 am UTC

Talking about Java, Tom is so right :
- first, because he says "J2EE" and not just "Java". J2EE is a whole other beast than just the Java language, and "just Java" won't get you anywhere.
- second, because the investment (in skills, time, hardware and maybe software) is way too high for a "one-shot" project.

J2EE is OK as a strategic Enterprise application platform where you develop and deploy multiple applications over time; that gives you better payback on the initial investment. It also is well equipped for developing "rich client" interfaces when the browser is not good enough for you. Finally, it's a very good choice for "integration" projects that mix applications and purchased software, because lots of them are compatible with J2EE.

For plain old standalone applications, even J2EE shops like mine often use simpler alternatives.

I would go for a technology and skill set that played to my strengths and that I would be likely to reuse.

Re: the "problem" with APEX

Joel R. Kallman, January 05, 2008 - 10:38 am UTC

1) Firstly, you have almost 100% control over the HTML generated from an Application Express application. It's all HTML template driven. So if the templates contain malformed HTML, then the resultant application will as well.

2) The HTML in a large number of the supplied HTML themes of Application Express have been corrected, and approaching compliance with strict XHTML.

3) If you take your same test, as you did for asktom, but this time validate, you'll see they "supposedly" have 1,487 errors. Many consider the W3C parsers broken.

So to recap - you have control over the HTML generated from any Application Express application.



A reader, January 05, 2008 - 3:11 pm UTC


1. The above brought a good point. Does Apex create HTML applications that comply with 508 standards if the application is to be accessible by disabled people. As you know government requires that all web app be 508 compliant. Does apex support all the extra HTML attributes for that?

2. From reading several articles, I think like you said it is much easier than JSP/servlets solution where the end result will be HTML anyway.

However, the only advantage of JSP/servelet where code will reside on application server is that you can:
a. do load balancing when you have millions of users. You cant do that when code is in database (you might but you have sync databases and run into all kinds of issues).

b. The other advantage I see is "SEcurity". When you place the code on a different server than database you have better control of application. IT is more like your propsed approach of building several schemas instead of one for users.

c. THe third is that you are not using proprietary language (pl/sql). If a business decides to move away from ORacle then they are stuck in the Mud. THey have to rebuild the whole application. However, most people with Java applications ending up using Stored Procedures which defeats the purpose of portability anyway.

Do you agree with the above points?

APEX and 508 (and DDA)

Carl, January 05, 2008 - 10:35 pm UTC

comply with 508 standards

APEX can create 508 compliant code, the main stumbling block is that out of the box APEX requires javascript to be enabled, though the developer can get around that.

What APEX doesn't do is enforce 508. The developer needs to makes sure that things like tabindex's are correctly set and that the HTML and CSS set properly for contrast, things like that. These are usually set based on a specific company/agency/etc accessibility requirements, if APEX tried to do some of these things automatically it could make things worse so it is left to the end developer.

Take a look at Sergio's blog posting here



A reader, January 15, 2008 - 7:09 pm UTC

--- Carl Backstrom wrote ---
> Some of the errors being thrown are intentional to get around
> crossbrowser issues, I know because I built most of this theme.

Carl, are you talking about AskTom theme? If so then how do you explain
that it doesn't even have the Doctype? How is that helping you with
"crossbrowser issues"? As of today it reads the following:

<html lang="en-us" xmlns:htmldb="" >

--- Joel R. Kallman wrote ---
> 3) If you take your same test, as you did for asktom, but this
> time validate, you'll see they "supposedly" have
> 1,487 errors. Many consider the W3C parsers broken. was launched in 1995 so how can it be held to the standard as
AskTom which was just recently rebuild from ground up? And why is it that
we always try to "measure up" to the lowest denominator?

As to "Many consider the W3C parsers broken." well, there are many that
consider it the standard by which to build.

Tom Kyte
January 16, 2008 - 3:15 pm UTC

Did you understand their fundamental point?

You can do whatever you like with the templates, you control 100% the html generated.

And do you think there is even one page on amazon that is from the 1990's let alone 1995?

No one is trying to measure up the lowest common denominator - we do have to work with lots of browsers (my rule). xhtml was not a priority for me. Having a working website - was.

"Flush the shared pool periodically"

A reader, January 15, 2008 - 11:12 pm UTC

Did you see this note?

Note:416131.1 Intermittent HTTP 404 errors in Apex

"Flush the shared pool periodically"

This really belongs on the daily WTF not on metalink!
Tom Kyte
January 16, 2008 - 3:20 pm UTC

I agree, that the note is deficient. The suggestion is not a good one. It solved nothing.

A 404 can happen for many reasons, the analyst in this case extrapolated one case into "every case".

I've started the process to get it pulled. thanks for pointing it out.

yes I do

A reader, January 16, 2008 - 4:48 pm UTC

> Did you understand their fundamental point?

Yes - one can make what-ever one wants with APEX.

My point is that asktom and metalink are the largest (known to me)
representations of what is possible using mod_plsql/APEX and it out to be
the best representation of it.

Trust me, I am the biggest proponent of mod_plsql you will find. I've
worked with webdb/htmldb ever since I got my hands on it in 1999 and I
immediately started promoting the concept in my environment.

Then, webdb became oracle portal which I despise to this day - it's a
bloated piece of software that is jack of all trades and master of none.

I am very glad that htmldb is making it's comeback in the form of APEX -- I
just hate to see it being done to the standards of last decade.

PS: I am glad we agree on the lousy note - nice to see you care.

Making a comeback?

Joel, January 17, 2008 - 8:17 am UTC

>> I am very glad that htmldb is making it's comeback in the form of APEX

There is no comeback to be had. HTML DB was renamed to Application Express. That's all.


is that right?

A reader, January 17, 2008 - 12:02 pm UTC

> HTML DB was renamed to Application Express. That's all.

"That's all" ... really? How about it now having:

a) stronger brand name with a huge following
b) companies building a business model on top of it's framework
c) Packaged Applications are being developed for it
d) hosted frameworks are being offered

That's all!
Tom Kyte
January 19, 2008 - 10:22 pm UTC

you make it sound like "htmldb was the cat's meow - fell out of favor and is making a comeback via the new APEX"

You wrote:

.. I am very glad that htmldb is making it's comeback in the form of APEX ...

The point Joel made was that htmldb = APEX = htmldb. There was no "comeback" involved, it was a product rename, that is all.

You can run the show.

Carl Backstrom, January 17, 2008 - 3:09 pm UTC

>> Carl, are you talking about AskTom theme?

Yes it's a modified version of Theme 18 which is provided with APEX.

>> If so then how do you explain that it doesn't even have the Doctype?

Because I specifically didn't put it in to force every browser I know, and more importantly any I don't know into Quirks mode.

The main point being and what Tom/Joel/Me pointed out you have control of all HTML in your theme and templates and if your company/agency/department demands is compliance to a validator or standard then please have at it, we won't stop you. In fact if there is some HTML the engine outputs that you can't change and breaks validation post on the APEX forum and we will fix it as soon as we can.

PS. Since missing DOCTYPE bugs you , I'll put it on my todo list and rank it appropriately among everything else I need to do.

Alessandro Nazzani, January 20, 2008 - 3:29 am UTC

Carl from Las Vegas, NS wrote:

> APEX can create 508 compliant code, the main stumbling block
> is that out of the box APEX requires javascript to be
> enabled, though the developer can get around that.

That's interesting; I once had a quick look at this possibility but it soon appeared to me very hard (if at all possible).

Do you have any pointers I can follow to learn more about it?




A reader, January 29, 2008 - 3:04 pm UTC


we have a pl/sql application using mod_plsql and oracle 9ias and oracle 9i db.

Now, if a decision was made to upgrade the 9ias to 10g ias but not the database (stay on 9i).

Would that create any issues? so you have to retest the whole app and what tests do you perform?

2. Can oracle certify such a thing of using 10g IAS with 9i database?

3. I assume we are onyl using the apache web server and mod_plsql components only. so it should be simple right.
Tom Kyte
January 30, 2008 - 9:33 am UTC

... so you have to retest the whole app and what
tests do you perform? ..

you have to test what you would test as you make any change

o functionality - does it work
o load/scale - can I still do X tps like I used to.

2) contact support for something like that. 9i is supported, application server is supported, it should all be supported and supportable.

3) then you are not using the application server, you are using the stuff from the companion CD, you don't even need the application server for that.

app server

A reader, January 30, 2008 - 4:20 pm UTC


1. what is X tps: transactions per second?

2. You mean that when you install the 10g IAS (mod_plsql and hhtp web server) are not even considered as part of it.

I am looking at the installation 9IAS instructions.

When you select Enterpsise Edition, it includes "Oracle HTTP server" etc.

I assume mod_plsql is part of it. Crating DADs is also done on the app server.
Tom Kyte
January 31, 2008 - 3:14 am UTC

1) yes, sorry, tps is transactions per second.

2) you do not need to license or install the application server if all you need/want is apache and mod_plsql, apache and mod_plsql come with the database (and with app server)


A reader, January 31, 2008 - 4:27 pm UTC


Yes, i knew that. But I think we are using the http server that comes with the IAS (not the one with DB).

Is there a way to tell if we are using the one that comes with the DB versus the ones that comes with IAS.

Tom Kyte
February 04, 2008 - 3:27 pm UTC

ask the person that installed and configured the software.... they would absolutely know.

Which Apache/mod_plsql in use?

Richard Smith, February 01, 2008 - 9:38 am UTC

The first way you can tell is to find out where the apache you are running is installed. If it's in the database oracle_home it is probably the one shipped with the database. If it's in it's own oracle_home directory, it could be either the one from the db media, installed separately, or it could be from the iAS media. You'd have to fire up the gui installer to see what's installed in all your oracle_home's.

Start or Build

A reader1, February 03, 2008 - 9:16 am UTC

So Tom,
if we decide to go ahead with building the application using Oracle pl/sql and Apache Server+mod_plsql what is your suggestion:

- start with Apex or
- build custom pl/sql.

After experiences with webdb, portal, htmldb and Aplex some developers are concerned about supporting existing modules/applications.
They stay with Oracle, but they are still in mud.
Tom Kyte
February 04, 2008 - 3:48 pm UTC

APEX, only, I would not even consider in the year 2008 sitting down with a text editor to hand code a web site like that.

APEX Security

tkl, May 16, 2008 - 6:37 am UTC

1. How does one harden the APEX config? Anything Tom or Oracle can suggest?

2. I have to generate PDF as output from APEX. Any way for it?

3. Can I make it work along with PHP? I mean I configure PHP also in the same Apache server along with APEX and call the output from PHP also. Theorectically possible.. Sort of a mix of PHP and APEX - probably may not be nice...What is your take on it.

Tom Kyte
May 19, 2008 - 3:00 pm UTC

you'd want to use the apex forum for this, monitored by lots of people that use apex a lot.


A reader, June 14, 2008 - 12:49 am UTC


two question on the web toolkit:

1. what is the fastest way to create an html page based on a table (ie like EMP).

2. how can I have one procedure initiated by web to store files posted via html form into two different databases connected via database link.
Tom Kyte
June 16, 2008 - 11:35 am UTC

1) depends on what you want, doesn't it. what does it mean to create a page based on a table?

2) you'd need/want two different procedures, one each on each of the target database and use a different URL for each to indicate "put in database1, put this in database2"

I think you want to learn all about APEX (application express), it is a rapid application development tool. It can build many kinds of pages on 'emp' (reports, CRUD - create read update delete) pages, master/detail, pagination, export to excel, etc etc etc - without having to write code. It can do the file upload stuff easily


A reader, June 16, 2008 - 4:45 pm UTC

1. What I want is a simple dump of the EMP table columns in HTML tabular format. Similar to what you do in SELECT * from EMP in sql*plus except it will be a web page link.

I use mod_plsql and stored procedures.

2. Yes hopefully for new apps we will use apex. But now, we use pl/sql web toolkit.

I do not see how you do this. can you show a small code example.

One database X is open for the web via mod_plsql. The second database Y is not.

When user posts the file via html form, it gets saved into database X. How do i save same file into database Y from database X.

Tom Kyte
June 16, 2008 - 4:50 pm UTC

1) APEX, install it, use it, it is what you want.

2) APEX uses that too.

How you can do what???? code example of what???

you do not say how database A and B are allowed to talk...

(and yes, I know you are the book guy from the other thread.... best to be consistent with your identity, I can tell - and it only gets annoying to get variations on the same question from the same person on different pages trying to pretend to be different people)


A reader, November 13, 2008 - 5:53 pm UTC


Quick question on mod_plsql appplication.

I have a columnar data entry page (pl/sql stored procedure) list of items and quantity field. After user enters some quantities/numbers, he hits "save/refresh" and the page updates the table and page refreshes itself and display numbers from DB.

Problem is that when user hits "Back" the previous recalculation take place and I do not want that? How do you get around this issue in your systems and would you block the "Back" button in some cases.

Tom Kyte
November 14, 2008 - 5:19 pm UTC

when they hit back - it tells them "we'll be resending", so you'd need to sort of be able to detect that - one way is a cookie and a hidden field.

you set the cookie and fill in the page - they are the same. You expect them to be the same and will fail if they are not.

So, when they submit normally - cookie = hidden field.
When they hit back - cookie is whatever you just set, but hidden field is something else

(eg: make cookie be a timestamp or a seq.nextval)

there are other approaches - and I'm sure someone else will chime in with theirs.


A reader, November 16, 2008 - 3:21 am UTC


I did not fully understand your solution using hidden field and cookie. I would like to try that.

I have Page "Order Selection" where user enters selected quantities and hits "Save/Refresh". This submits the data to "Save/recalc" page which stores data in DB and then takes him back to "first selection page" with updated numbers and quantites available. HE can keep keying in quantities and hitting "save/refresh" and recalculate totals.

So if I am in page 1, will I have a hidden field set to "seq_no" and a cookie set to same sequence number. When he submits to page 2, it will check the number in cookie against number in hidden paraemter and then decide to run or not. But if he hits back is not it running the previous page submittal and the sequence and cookie are getting set again.

Tom Kyte
November 18, 2008 - 7:09 pm UTC

when you generate page 1, you

a) put a hidden field with a special number
b) set a cookie with that same number

every page in fact, resets that special number in the cookie....

so, when you go to page two, it'll set the cookie to SOMETHING ELSE

when they hit 'back', the cookie will be whatever page 2 set it too - but the hidden field on page 1 will be the old value.

so, if they submit again - cookie <> hidden field => you fail.

tool kit

A reader, February 16, 2009 - 8:19 pm UTC


How do you usually estimate a development task in pl/sql web toolkit.
let us say the task is to create one table and create web pages/APIs to allow the users to CREATE, READ, UPDATE, DELETE on the table.

DO you usually have a formula or do you break it into tasks: table design, web page development ,testing, deployment.

Tom Kyte
February 16, 2009 - 9:40 pm UTC

15 seconds for that one.

all of them spent saying "we shall not do TAPI's (table API's) - ever, never ever ever, they are bad"

but it really takes about 15 seconds to build that interface using APEX. I would not consider in the year 2009 writing that in the plsql web toolkit from scratch - less than 0% chance of that.

Now, ask a real question like "ok, I want to build an application that does <15-100 pages of specifications here>" and the we can start talking about how to build a project plan for software development.

and it depends on whether you are going "rad-ish", waterfall like, have to use 100 programmers or just 2, ..................


A reader, February 16, 2009 - 11:15 pm UTC


I did not mean API as a table API. I meant the code that will be used to INSERT or UPDATE or DELETE or RETRIEVE the data from the database. You need a procedure for eeach process right!

Concerning APEX, there are a few issues. One is that I do not think it is installed in 9iR2. Is there a way to check? I believe you have to be on 9iR8 or higher to be supported.

Also, I do have the Oracle press HTML DB handbook. IT will take you a few weeks to learn everything about it. Y ouare saying it will take you 15 seconds to do it assuming you have all the knoweldge on how to use the product. But you have to build time for people learning the product if they never used it before.

also, this is not standloane application. The links will be part of an existing menu system built using the toolkit.
Tom Kyte
February 17, 2009 - 8:29 am UTC

ummm, you said:

... the task is to create one table and create web pages/APIs to allow the users to CREATE,
READ, UPDATE, DELETE on the table.

that is the very DEFINITION of a table api. A silly subroutine to do an insert, an update, a delete, a read of the table.

To me, once you have a table created, you already have your CRUD routines, we call them:


done, I don't see any reason at all to write code to do that - it is *done*, make a transactional API whereby you modify N tables in a well formed transaction - great, but to have a silly routine to insert into table T - bah, hate it.

You can install in 9iR2 - it is supported in that release. I don't know what 9iR8 is :)

If you have to incorporate education into a schedule - OK, then I'll do this

1) year for kindergarden
2) six years for elementary school
3) six years upper schooling
4) four years college
5) two years to get experience (to unlearn what you learned in 1-4)
6) Now we can start

I mean - come on.....

what is a "toolkit" - you have lots of assumptions and such that no one else other than you knows about.

so, again, my estimate:

15 seconds.

I have to assume you have developers that know a tool - if not, that is not part of the project plan for developing the software, that is part of their individual development plan - you get people that know the tool or you get your people educated.

After they KNOW the tool, you can start planning your project.

web kit

A reader, February 17, 2009 - 12:22 pm UTC


<<I don't see any reason at all to write code to do that - it is *done*, make a transactional API whereby you modify N tables in a well formed transaction - great, but to have a silly routine to insert into table T - bah, hate it. >>

I am totaly confused. I have a web page for data entry screen that allows user to enter NEW employee. I need another API/page that validates the data and saves it into the employee table.

That means I need two procedures: one for blank form, one for saving data (INSERT). Same thing applies for EDIT: i need one page to display/SELECT data and one to UPDATE.

Am i missing something? How do you see no need to write any code when you create a table.

In your opinion how long would it take a pl/sql developer to learn HTML DB (i.e a few days)

Tom Kyte
February 17, 2009 - 2:33 pm UTC

... I have a web page for data entry screen that allows user
to enter NEW employee. I need another API/page that validates the data and
saves it into the employee table.

first of all - the database VALIDATES data, you don't - you have declarative integrity. Additionally, APEX will suck out a lot of that stuff and put field level edits on your page so the end user won't even have to submit to get many validations does (it would be bad form to actually submit a page to the server without making basic validations that dates are dates, strings are strings, number are numbers, not null fields have a value and so on)

second of all - in the year 2009 - there is a 0% chance I

o would write code myself
o would allow someone that is paid by a company I work for write code themselves

to do something that is "point and click" - it is DONE.

Get APEX. I won't discuss how to write gobs of unnecessary code, it is just wrong

It would take you literally an afternoon to be tooling around in APEX and as with any environment - days/weeks to become proficient in it.

How about this: Less time than doing it manually, even for someone experienced in the toolkit API's


A reader, February 17, 2009 - 3:02 pm UTC


How do you check if the DB has APEX installed or not? I have to ask the DBA to install it if not. not sure how long would that take.

It seems you favor all form validations to be done at the client (not server via pl/sql) unless you are assuming that the db will do automatically at the server if you enforce the constraints at the table level.

Does APEX do it by generating Javascript code?

The oracle press book describes Apex as a tool for indivdual or departmental applications (not enterprise).

Tom Kyte
February 17, 2009 - 4:35 pm UTC

... It seems you favor all form validations to be done at the client (not server
via pl/sql)...

absolutely NOT

Validation must take place at the server. And it would not be in plsql, it is declarative.

It makes for a better end user experience IF you do some form validation. But if you noticed - above - everything I described to validate - can be read from the DICTIONARY. You can read the constraints out and do a lot of validation - just be aware the database always wins, it does the final validation - and might have even more rules to enforce.

Define to me what an enterprise application is. Is it one that does 2 million plus transactions a month for 100,000 users?

that would be this site, written in APEX.

I have to laugh when I hear "it is not enterprisy enough" - for no one can *define that*

Do you think your lovingly hand coded bit of htp.p code is 'enterprise'?? More than a tool like APEX

Wait till you get inside apex, apex was written in...... apex. Is that enterprisey enough? (I hate that term "enterprise", it is like "cloud" or "web 2.0" or .... )

ops$tkyte%ORA11GR1> desc v
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_ITEM                         VARCHAR2                IN
 P_FLOW                         NUMBER                  IN     DEFAULT
 P_SCOPE                        VARCHAR2                IN     DEFAULT
 P_ESCAPE                       VARCHAR2                IN     DEFAULT

if you have a function V and it's signature looks like that - you have apex installed.


A reader, February 17, 2009 - 5:07 pm UTC


We need to ask the two guys "lawrence Linnemeyer" from TUSC and "Bradley Brown" from tusc who wrote this book "Oracle HTML DB Handbook" what they mean by enterprise. Check the matrix on page 15 if you have a copy of this oracle press book. They compare HTML DB to web toolkit and JDeveloper and portal and designer.

I did desc V and nothing was found.

The database version we have is Can DBA still install APEX with this . book says you need to run the examples.

Is there a script you just run for the install.

Tom Kyte
February 17, 2009 - 8:16 pm UTC

I do not have that book, so why don't YOU describe enterprise.

How about this -

would you describe that site as

a) enterprise.
b) not enterprise.

You have to pick (b) if you say Apex cannot develop "enterprisey" applications.

Apex can be used to develop web based applications that service from 1 to millions of users, performing hundred, thousands, 10's of thousands of transactions per second (because Oracle can).

You can use it to code really slow stuff that doesn't scale.
You can use it to code something that screams and scales to infinity and beyond.

Just like most any programming environment.

I would like those guys to explain just what "enterprise" means and why apex cannot do it.

Please - begging YOU - describe/define/explain to us what "enterprise" means. I think you might think "enterprise means we have to find the lowest level hardest way to do something, then and only then is it enterprisey"

Enterprise. I hate that word. Unless we are talking about Star Trek - then it rocks. Outside of Star Trek - it has no concrete definition that everyone agrees on

You need or above - implies you have support, you should be on by now.

Please consult *documentation* for installation and configuration.


A reader, February 17, 2009 - 5:15 pm UTC


It looks like we can;t install HTML DB with unless we upgrade to 10g or DO you think it would still work
Tom Kyte
February 17, 2009 - 8:21 pm UTC

going to 9203 would not be an upgrade, it would be performing work to stay still far away from the supported patchset on the database you are running (9208 is where you want to be)


A reader, February 17, 2009 - 10:49 pm UTC


Obviously what they mean by "Enterprise" application is an application used by many users like 100s.

Individual or deprtmental are either single user or a group of 20-50 people.

It seems these two experts copied this information from somewhere since you prove them wrong with Metalink and asktom which are used by hundreds of thousands of users.

I got to tell the dba to upgrade to until we go to 11g. DO I really need to re-test all our 9.2 applications for this. that would a big task for now.

Tom Kyte
February 18, 2009 - 7:27 am UTC


How do you get OBVIOUSLY.

Ok, so say that is what they meant (and I seriously doubt that it is) - that it is an application used by hundreds, thousands, 10's of thousands or hundreds of thousands.

Then - *obviously* glaringly obviously, the statement that apex is not for enterprisey applications is wrong.

I gave two examples, and there are many many more. For you see, APEX generates an application that runs on Oracle, in Oracle and - well - when properly coded to - Oracle supports "enterprisey" things.

Look - you were going to build something *by hand* using htp. You know what apex does? It generates an application that uses.... htp.

And you know what, I don't think enterprise means "lots of users", I totally disagree with that.

To mean, an enterprise application is part of your core infrastructure, an application that - if unavailable - basically shuts you down. In Oracle - one of our enterprise applications is - support. Another is Order Entry. Another is HR/Payroll/Finance. Those are a subset of our enterprise applications. Some of them support a few users, some a few hundred thousand. They are called "enterprise" simply because...... It is what we use to run our "enterprise", without them - we don't move.

... DO I really
need to re-test all our 9.2 applications for this. that would a big task for

the answer to that - if you upgrade from 9iR2 over 10gr1, 10gr2 up to 11gr1, do we need to test - that is one thing on this page that is obvious.

of course, you need to test when you upgrade, of course.


A reader, February 18, 2009 - 8:55 am UTC


ok, i agree

Enterprise application = Mission Critical Application
and APEX can be used to develop those.

Here is one thing i do not understand and you may provide an explanation.

APEX origin is WEBdb which is mentioned as non-scalable and was leaving many dead processes (has issues). Why did oracle stop supporting These products: Portal, designer, Webdb.

Another if APEX is very easy as it sounds why do companies mostly think JAVA,.NET, PHP, RUBY. That would require a lot of time and skill. if you have a very easy tool to develop with why not use it if you have an oracle database. is it because it proprietary solution and it will only work with Oracle DB. .NET depends on Microsoft, JAVA depends on Sun, etc.
Tom Kyte
February 18, 2009 - 3:50 pm UTC

APEX's origin is HTML DB, HTML DB is a as close to webdb as rocks are to flowers in the grand scheme of things.

webdb, something from over a decade ago, was not widely adopted. I don't work with the various portals. But please - do not say "webdb is the forefather of APEX"

their only similarity is - they both use htp.p

The adoption of APEX is pretty large - I cannot tell you why some companies pick the tools they pick. Mostly it seems to be driven by the developers and what they know.

There are entire consulting companies however that are built on "we deliver apex applications to you - and fast"

people willing to host it and manage it

A whole community behind it:


A reader, February 18, 2009 - 1:34 pm UTC


I found that APEX is available on another central server. they do not install it in the 9i database.

If i get an account for apex on that other server would i still be able to develop apps and connect to the database on another machine or it would not work?

I thought pages developed with APEX are stored in the DB as metadata. so I might end up with some pages on server A and pages on server B (where the DB is and other pl/sql pages.)

Tom Kyte
February 18, 2009 - 4:05 pm UTC

you could, but you would not want to.

You would not want to have to use a database link for every single query/modification and 2 phase commit for every transaction.


A reader, February 18, 2009 - 6:43 pm UTC


I thought they can have it as part of an application server.

It seems I am stuck now with PL/sql web toolkit. For now i can only access APEX via that server and you say do not do it that way because of the DB link in every SQL. Not only that but if there are problems with that server we are out of work too.

What do you recommend?

I was impressed with the list you posted about consulting wok in APEX. It gave me a strong incentive to learn it. The issue is that Oracle is making development and dba work easy that may put developers and dbas out of work.
Tom Kyte
February 18, 2009 - 7:22 pm UTC

I recommend getting the environment you need to be productive - installed and configured.

You will have mod_plsql as part of your application server/web server. This is a module that will convert a URL into a stored procedure call (an APEX stored procedure call). APEX itself lives *in the database*.


A reader, February 18, 2009 - 11:54 pm UTC


Would i be able to install the APEX packages using a regular account with dba priviledge or it has to be under system/sys account?

is not having a stored procedure (APEX) on one server connecting to Server (B) where database is similar the 3 tier level where your code is all the app server and data in database server.

would this slow application considerably? what is a two phase commit
Tom Kyte
February 19, 2009 - 7:26 am UTC

you need to have your dba install and configure this, please refer to the documentation.

I've already said "you probably don't want to use dblinks for everything" - think about it, think about the work involved.

A two phase commit is a commit between two or more databases - it is a classic transaction processing concept. Google it, you'll see quite a bit of material out there.


A reader, March 13, 2009 - 10:10 am UTC


I have some confusion on oracle caching mechanism with mod_plsql application. When you run a procedure 10 times do you usually compile/run every time.

is the code or data cached in databse or mod_plsql or browser or does it depend on how code is written.

can you explain how it really works. Obviously if the application does not utilize caching it will be very slow.

Tom Kyte
March 13, 2009 - 11:01 am UTC

you do not do a compile every time. A plsql procedure is compiled into the database and then executed over and over and over.

The code is compiled into dictionary tables, the plsql block to run it is parsed and stored in the shared pool, the binary code that is the plsql stored procedure (that is compiled at compile time) is cached in the SGA as well.

Running a stored procedure via mod_plsql is not any different than running a stored procedure via sqlplus. The shared pool caches stuff, the buffer cache caches stuff, the dictionary cache caches stuff - and none of that is really affected by "mod_plsql" versus "some j2ee application" versus "sqlplus" - it all works the same.

... Obviously if the application does not
utilize caching it will be very slow.

that statement is not obvious to me. Asktom is an application. Asktom does no caching. Metalink (written also in APEX, mod_plsql) is an application, metalink does no caching. APEX is an application, APEX does no caching..... And so on. Applications do not have to cache to perform.


A reader, March 13, 2009 - 12:02 pm UTC


Do you agree that dynamic web pages that have a query in them should not be cached at web server or browser? Some people think if you do not utilize caching then you do not know how to design systems or systems wont perform.

I have had several issues in users getting a web page that does not reflect the data in the database. I found that the apge was getting retrieved from browser cache. I told them to set the browser to "check for newwer versions" of the page with every visit to the page. Still some do not do it.

is there a way to control that in oracle to ensure the URL retreives a fresh page.
Tom Kyte
March 13, 2009 - 2:48 pm UTC

... Some people think if you do not utilize
caching then you do not know how to design systems or systems wont perform.

and some people thought the earth was flat once too.

However, if the table your web page queries changes so infrequently and the currency of the data on the screen is not relevant - you may cache it.

If the data your web page queries is changing fast, and the currency of information is relevant - do not cache it. Ask that person "so, you want your page that shows current stock prices - the one you as a stock trader decide to buy or sell with - to be cached from 8am on for the rest of the day ???? really?"


a) why? why do you want to do that (so we can evaluate whether we should respond or just walk away)

b) IT DEPENDS <<<=== this is the answer to caching, isn't it, just think it through - some things can be cached. Other things - *cannot*

read about the html specification and the headers you can emit, one of them controls (tells the browser) whether caching is to be allowed - or not.


A reader, March 14, 2009 - 4:40 pm UTC


you are right. i had many issues with users having pages cached and i want to block that.

What do you usually code in the procedure to block caching in the browser or is this really a browser feature. Do you do this in the PL/SQL?

<META HTTP-EQUIV="Pragma" CONTENT="no-cache">

There are also several setting in IE and mozilla where user can set his browser to check the cache for the URL before sending request to the web server. Can you do something in the page to ensure they always get a fresh page from database.


A reader, March 14, 2011 - 12:32 am UTC