Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, David.

Asked: September 28, 2003 - 10:46 pm UTC

Last updated: February 24, 2022 - 7:27 am UTC

Version: 10

Viewed 10K+ times! This question is

You Asked

Tom -

I read a little bit about the new features in 10g for HTML DB. I'm pretty excited about it if it effectively delivers on these new features (e.g., the whole meta-data driven framework, the Application Builder, Session Management, Templates, Security, etc.)

I have been thinking for awhile that there is opportunity in smaller markets to role out rock solid applications with a single Oracle database running mod/plsql. I was considering all the infrastructure services that would need to be built in order to quickly build one or more applications using mod/plsql. With HTML DB, it is looking like I don't have to worry about building these services on my own anymore, and that is great news.

A feature that I didn't see that I would also consider very useful...
A security framework where you can map Users to one or more Roles (I know this is in Oracle), then further mapping one or more Roles to one or more defined "Transaction codes" (e.g., CreateOrder, ViewOrder, DeleteOrder), and finally associating a group of "HTML Pages" to a transaction. This kind of framework could apply to many information system applications. It would both enforce at the server as well as influence the look of the application (e.g., limited menu options based on Roles.)

Anyway, seeing these new HTML DB features along with 10g's ability to transparently scale in its clustering/grid configurations, Oracle 10g with HTML DB could be a solution for any size application.

Finally some questions....
1.) Does Oracle have any plans to build out-of-box applications/products on top of HTML DB?
2.) Do you see any potential confusion from your customers as to whether they should use HTML DB vs. the iAS?

I did Java development for a few years and I like using core java a lot. I still struggle to decide whether or not the EJB architecture is that good. I think I've come to the conclusion that if you are building an "information system" application where most of the work is selecting,inserting,updating,and deleting from the database, then a simpler architecture is better, and HTML DB may be onto something here. At the end of the day I would think companies want to get ROBUST applications working QUICKLY, and J2EE is not necessarily the right choice in all cases.

A couple more questions...
3.) Will HTML DB be available in the Standard edition? As I said before, I see opportunity in smaller markets for HTML DB as replacements for spreadsheets, MS-Access, and SQLServer applications, however, the cost of Oracle could prohibit this. Does Oracle have any interest in this market?
4.) Maybe I am just paranoid, but I see a strategy here. An alternative to .NET or J2EE, simpler and faster approach (for certain types of "information system apps"), it gets Oracle in the door, and allows them to sell additional licenses (10g grid) as the customers' applications need to scale further. Is this a stretch?

Always curious,

and Tom said...

I asked Sergio Leunissen, the product manager for HTML DB, and he said:

Thank you for your question, Dave:

1) Not as of yet.
2) I don't see a lot of potential for confusion. I think it's a matter of the skills and experience of the person or team building the application. Oracle already serves customers very well with JDeveloper, allowing professional developers to build applications using such technologies as J2EE and web services. HTML DB complements Oracle's offering by catering to those who don't develop applications for a living, don't have a computer science degree but still have to build something.

The nature of the application, as you are correctly point out, is also important. How long are we going to use the application? When does it need to be up and running? Tomorrow? Next year sometime? Do we need to integrate many legacy data sources or call existing chunks of logic running on existing systems? Will the world as we know it fall apart when the application is not available one day? In other words, is the application absolutely strategic and mission critical? Is it really worth it to assign a few professional developers working on this for a few months? The answers to these questions may automatically guide you to the appropriate technology.

3) Yes, HTML DB will be availabe with standard edition. I can't really speak to Oracle's aspirations in the market you describe, but, last time I checked, we've never been allergic to growing market share.

4) I agree that HTML DB is an alternative to J2EE and .NET and we hope that those folks that are overwhelmed by these and other technologies will consider HTML DB.


  (287 ratings)

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


Robert, September 30, 2003 - 11:02 am UTC

I am curious as to why Oracle "seems" to downplay or being "conservative" about the capability of HTML DB in a way that's rather un-Oracle-like.

I've listened to the OW2003 guru sessions by people like Mendelsohn & Dwight and they seem to position HTML DB as something that's perfect for simple, small, workgroup projects, something that's meant to compete/replace with dev tools like Access, Excel, ASP...something quick & easy & like no serious programmers required.

Someone back from OW also told me that he specifically asked around & talked to the Marvel people and he got the same impression.

Is this because this is the very first prod release & Oracle
chooses to be conservative until the thing has gone thru a real "baptism of fire" ?
Tom, your thoughts please.


Tom Kyte
September 30, 2003 - 11:13 am UTC

Well, I'm sort of one of the marvel people myself (they are right down the hall from me, been working with some of them for going on 11 years now)...

that is what html db is all about -- but it can go much much larger. For example, we wrote an entire calendar/pim in it -- hosted for the entire company. Gets tons of traffic (we are switching off of that to collaboration suites calendar now) but it gives you an idea of the scale this can go to.

tell me, do you consider asktom "small"? html db is perfect for it -- it is the quinessential html db application. It is fairly small in scope (just a couple of pages, nothing huge - single developer sort of application). It does just one thing. I do about 1,000,000 transactions a month (and asktom is just one of about 5 applications written in html db running on that machine). Is that smallish?

It is my belief that easily 80-90% of total applications developed in Java or some other 3gl could (should) be developed in a non-3gl environment like this. That is because 80/90% of applications really are "small" relatively speaking. They will never need to scale to 15 app servers.

About AskTom Transactions...

Robert, September 30, 2003 - 12:06 pm UTC

>>I do about 1,000,000 transactions a month

Tom, transactions on AskTom are all INSERTS, each message to/from AskTom being a single automic record ??
Do you have to guard against things like "Lost Updates" ?
If you also UPDATE, what kind of locking mechanism , optimistic ?
AskTom never DELETEs any record, does it ?


Tom Kyte
September 30, 2003 - 12:34 pm UTC

html db does optimistic locking "out of the box", it does it for me, i don't have to deal with it. lost updates are taken care of by html db. just like forms.

The transactions are larger then that -- each page -- even this one you are looking at -- had a couple of inserts/updates in it. for auditing, performance tracking.

this page has:

5 inserts
2 updates

for example

I do not delete, correct.

You've got options

Dave, September 30, 2003 - 12:14 pm UTC

Just noticed that you can download it now, so I did that last night and installed it. I may have more comments as I get to know it.

One thing I forgot to mention...Oracle is providing a more "wizard driven approach" here. This is good for non-technical people, but it is also good for technical people in terms of rapid application development.

The thing I like about Oracle (versus other vendors at times) is that they are providing this RAD without taking away access to the underlying technology, so that you can get the benefit of rapid development in combination with the power of Oracle technology.


Where can I download HTML DB ?

A reader, September 30, 2003 - 1:19 pm UTC


where did you find HTML DB download?
I cannot find it in OTN download page:
</code> <code>

This is where I found it

David, September 30, 2003 - 1:43 pm UTC

</code> <code>

Then choose: "Download Preview Release"

HTML DB vs. Oracle Portal

Jens, September 30, 2003 - 1:53 pm UTC

One Question about HTML DB:

It looks like the old WEB-DB or Oracle Portal. Is it so much new or is it a Portal-Light without the need of 9iAS? How will Oracle seperate it from Portal?

Tom Kyte
September 30, 2003 - 1:58 pm UTC

Portal is an integration/content management tool.

HTML DB is an application development tool

You might use portal to aggregate the front page of html db generated applications.

You could use html db to write your own portal tool if you so wished (portal being an application)

they are somewhat orthogonal. Web DB was the precursor to HTML DB (written by the same group as marvel, but the guys who took it over liked the portal idea better then an app development tool -- they sort of let it "stay the same for years").

Thanks David for link!

A reader, October 01, 2003 - 3:31 pm UTC

Marvel Native-compilation

Robert, October 02, 2003 - 11:48 am UTC

Tom, is AskTom's running on native-compiled Marvel engine ?
If not, why (fast enough?) ?


Tom Kyte
October 02, 2003 - 4:38 pm UTC

fast enough... just never turned it on, ran faster then fast enough.

Criteria for an HTML DB App candidate

Robert, October 07, 2003 - 4:55 pm UTC

>>It is my belief that easily 80-90% of total applications developed in Java or
some other 3gl could (should) be developed in a non-3gl environment like this. <<

Tom, surely you've just about seen all kinds of apps - can you provide some pointers on what make an (Oracle-centric, of course) app a good canidate for HTML DB implementation ?

I mean when you analyze an existing app or business requirements, security requirements - what outstanding qualities make you say "Sure, Yes, HTML DB can deliver the requirements" or conversely "Hmmm, this should better be written in 3gl/other web technologies" ?


Tom Kyte
October 07, 2003 - 6:17 pm UTC

Ok -- opinion coming

all apps that you would develop for internal use at your company are HTML DB candidates.

I make that bold statement because I firmly believe anything "really really really big" should be "bought", not "built" (eg: HR, finapps, crm, etc etc etc)

Would I use it to build probably not.

Would I use it to build something on the scale of -- we absolutely did (among many many others)

draw the line...

Robert, October 07, 2003 - 7:17 pm UTC

anything "really really
really big" should be "bought", not "built" (eg: HR, finapps, crm, etc etc etc)

Not if you're the prez of TomMart - the big chain retail stores - and the financial app code is untouchable & doesn't use bind & the model is kludgy & tries to be "platform-independent" & totally complex & vendor wants a blank check....other sins...?

How to decide?

Mark Wooldridge, October 07, 2003 - 11:35 pm UTC


Your comment about amazon, 'Would I use it to build probably not' is interesting.

What is it that does that would not be suited for mod_plsql?

We are currently looking to shift focus from j2ee to mod_plsql on our project and your input would help (Wendy is familiar with our goals and can give you and idea of what we are trying to accomplish).


Tom Kyte
October 08, 2003 - 6:35 am UTC is of a certain scale. It has more then just 10's of thousands of users. It is really "big" -- way way way beyond what most of us will encounter for most all of our applications.

Dave's opinions follow....

David, October 08, 2003 - 12:39 am UTC

Mark -

I will offer my opinions if you don't mind, though I must admit, I am still investigating HTML DB.

To shed some light on any bias I might have...I have done some J2EE before, I love using Oracle, I've used mod_plsql, and I am very interested in learning how to best utilize HTML DB.

I'd also be interested in hearing a "real world" example of why someone would move away from J2EE to mod_plsql. Can you provide some reasons in your case? It would be really interesting to here.

So why wouldn't you build with mod_plsql? Well here are some things that come to my mind:

1. Dynamic content - HTML is always dynamically generated with mod_plsql. In a high volume system you want to exploit caching and static content as much as possible. This may be easier in a J2EE architecture.

2. Media content - Amazon uses a lot of media on the site (images, sound, etc.)...perhaps this is possible in HTML DB (surely Oracle can store this information or reference a file name), but I think HTML DB is more geared toward delivering traditional textual and numeric information typical of applications such as asktom or an order entry system. (Though Tom did mention an email system Oracle built with it I think...which might include attachments.)

3. Resources - It may be easier to put together a large team of Java developers with a couple of database developers versus a large team of database developers. When I joined a product company a few years back, I was surprised to see how many programmers treat the database as a black box...or worse they fear it!

4. External Interfaces - Integrating with external systems (e.g., credit card verification, UPS tracking, external security authentication) is probably easier to do in J2EE architecture rather than in HTML DB.

5. Code organization / maintainability - People may argue code organization for a large project is better maintained in an OO world rather than in procedural (PL/SQL) code (or the metadata layer of HTML DB.) I'm not sure I can back this up with proof, but I'm sure this opinion is out there.

6. Look and Feel - HTML DB provides you with some standard templates. I'm not sure yet how custom you can make the look and feel of the apps (maybe you can develop your own templates, I don't know.) But my guess is that the further you get from the standard layouts, the more work is involved, and the less benefit you might get from the RAD of HTML DB. So...for very custom layouts that you may need in complex applications, HTML DB might not be the easiest approach.

7. Testability - In large applications, you might want to build test clients that simulate user activity. For instance, you could setup programs to send XML documents containing transaction data that by-passes the JSP code and interacts directly with EJBs to simulate users executing transactions. You don't have this type of ability/flexibility with HTML DB.

8. Asynchronous - It is relatively easy to develop threaded code in Java. You can do it in Oracle with DBMS_JOBS, but if you have some complex asynchronous requirements, Java is probably better.

9. Scalability - How does prefer to scale, by buying multiple Oracle instances in a cluster (running HTML DB), or using one Oracle instance with many application servers (which are probably cheaper.)

So are you building something on the scale of or are you building an internal application for your company?

If you have a business problem where you can envision the data model behind it, and you basically need the ability to allow users to intuitively view, create and update the data in some sort of transactional application, then HTML DB is probably just fine and may be better than J2EE.


Tom Kyte
October 08, 2003 - 6:47 am UTC

1) disagree -- the java environment would dynamically generate the page as well using dynamic content here and there (personalization) and ESI (edge server include) tags for cached data. mod_plsql can do the same

2) the rich media is just a LINK -- an href or whatever tag. the programming language used to generate the page is not relevant -- the "programming language" could be vi to generate a static page for what its worth here.

3) i've yet to see a team of java developers with database developers :) but in the end -- it is all about who you hire.

4) soap? web services? we were doing this with utl_http over ssl a long time ago.

5) thats debatable, we'll let that one slide...

6) thats correct -- it is the predominant strength of HTML DB in my opinion. every app looks "the same". Every Oracle application (hr, etc) could have been build using HTML DB UI wise -- and they would all work the same

7) kidding right? asktom for example is built using html db on an API. the logic is in packages (submit a question -- thats a package that does that). HTML DB is just providing in this case state mgmt, navigation, all the yucky stuff that is just a pain to code over and over. the core of asktom is an API, testable in a standalone fashion. Sure, if I create a simple CRUD screen using HTML DB -- it'll not be an API but then again -- it'll work as advertised (does what it does). the code you need to test is your code and that'll be separated out as it would be in ANY good program

8) that is not async -- that is multi-threading. the end user is still waiting. Most (many) programmers cannot build reliable threaded programs (and frankly i don't see it being used that much -- not in a jsp environment anyhow). If you mean ASYNC -- you would be talking something like JMS which in Oracle is built on top of AQ which is implemented in PLSQL and fully accessible to PLSQL -- meaning PLSQL is quite adept at doing it as well!

9) exactly - is of a scale beyond where most of us will ever go. at that point, alternatives are to be looked at.

At the end of the day -- you have to just keep saying "java is just a language, java is just a language, java is just a language (and so is plsql)"

You have to get back to the architecture -- what should the architecture be, simple small and more then scalable enough for what you are doing or big, distributed, lots of moving pieces that can potentially (when done right) scale really big.

Good info

David, October 08, 2003 - 9:11 am UTC

Figured I would get feedback.

1) Yes, dynamic but some times coming from cached objects rather than database calls. I'm not familiar with this functionality in mod_plsql.

4) Web services would certainly make integration more open.

7) Ok, developing your API outside of HTML DB is a good point, I was thinking from the perspective of using the HTML DB to build the whole app via the web gui.

8) I meant threading. I suppose you might not see much need for building threaded code into your web site. Perhaps you are correct, many programmers cannot build reliable threaded code, but if you need to build it, then you better find some. Threaded coding in Java is much easier than in C in my opinion, but the same perils of deadlocking always exist regardless of language.

Thanks for the info.


Tom Kyte
October 08, 2003 - 11:14 am UTC

Tyler, friend of Tom and HTML DB developer here,

Just wanted to follow-up on a few of your points here.

4) We include a number of sample applications you can install, then reverse engineer. As soon as you login to HTML DB, look on the right side in the "Tasks" region for "Review Demonstration Applications". One of them is "Web Services". Just click on install. You can then disect a working web services app to see how it is constructed.

6) Templates: You can acheive ANY HTML based look in HTML DB. Our templates are simply HTML with #SOMETHING# substitution strings. While a lot of the built-in ones look the same, they are COMPLETELY customizable. Below is some sample code for a region template similar to the "Information" region found on the right side of this page:

<table style="border:1px black solid;border-collapse:collapse;">
<tr><td style="background:red;color:white;">#TITLE#</td></tr>
<tr><td style="padding:4px;"># BODY#</td></tr>

You can see examples of other looks at and

Hope this helps,

Do we need to pay extra?

A Reader, October 08, 2003 - 9:22 am UTC

Hi Tom,
Is HTML DB included in the database itself (say, when we buy Oracle 10g Enterprise Edtion or Standard Edition) or we need a seporate license?
Thank you.

Tom Kyte
October 08, 2003 - 10:33 am UTC

This is Tyler, friend of Tom and one of the HTML DB developers

It will be included as a (drum-roll please) NO COST option with 10g, both Standard and EE edition. It will also be formally supported back to 9iR2 when 10g ships, though you can download and install it now from </code> <code>

I would STONGLY suggest you signup for a "Workspace" on which will give you your own virtual private database and development area where you can "kick the tires" before you have to go through the effort of installing it (though the install is 30 min or less). This also demostrates the hosting capabillity of HTML DB. Several large companies and goverment organizations are hosting it on internal servers now where their employees can signup and develop in their own area without installing new client or server-side software: All you need is a WEB BROWSER!


RE: Do we need to pay extra?

robert, October 08, 2003 - 10:17 am UTC

Similar Q was asked on OTN forum (Pricing with Oracle 9i ). Here is the answer from
the monitor Raju Mattamal of Oracle:

"my current best understand of this is that Oracle HTML DB is to be free of charge. after it's released with oracle 10g, you should be able to download a copy w/o charge to run against oracle database versions or better"


Dave, October 08, 2003 - 12:53 pm UTC

Tyler -

Thanks for the info, I will check out the web services demo.

Also good to see the sample sites to demonstrate how customizable the look and feel can be.

When will the be up? ;)


Tom Kyte
October 16, 2003 - 10:00 am UTC

We're still debating on which tool we should build it in ;) But seriously, we had exactly that, but thought it would be best if we moved to the OTN Forum as we went from "pet project" to product. Raj Mattmal does a great job of answering any questions you may have. He's not a support analyst, he's actually a developer on the HTML DB team so he knows it as well as any. Since we're a relatively small development team, only 13 people, he knows exactly who wrote what piece and can call on the rest of us for help should the question be outside of his area of expertise. Actually, until 10g is released, Raj IS our suuport organization.


html DB

A reader, October 15, 2003 - 2:22 pm UTC

Can Oracle HTML DB be used on an oracle db? What tools are required for using Oracle HTML DB? Is there a white paper from Oracle on HTML DB?

Thank you

Tom Kyte
October 15, 2003 - 7:37 pm UTC
yes, works on 9203 and up and will be supplied for 9203 and above.
Tyler here,

Tom is correct on the version, though we may be moving to 9204 as our lowest supported version as there are some LOB issues with different character sets that limit importing and exporting your application in 9203 to the SQL PLus, rather thatn being able to upload it through a web browser. Stick with 9204+ and you will be fine.

As far as tools, all you NEED is a web browser. This allows any of your developers to type in the URL and get going immediately. I love it for teaching classes as there is NO setup on the client side. I can suggest some other 3rd party tools that I find generally useful though:
UltraEdit ( is a great text editor if you are not a vi person (like Tom).
CSE ( is amazing for html and accessibility compliance testing.
We use CVS ( for our source code control with various clients, including tortoise (

We are currently working on White Papers and tutorials which should be posted soon. Until then, you can check out:
and <code>
for more information.

Hope this helps,


A reader, October 16, 2003 - 8:37 am UTC

HTML DB - can't download or register for a workspace

Phil, October 16, 2003 - 11:39 am UTC

This looks great, I'm really excited about this and the true RAD capabilities but I get an error trying to register for the workspace and can't find the download either (:

How to diff CLOB-s?

msc, October 16, 2003 - 6:08 pm UTC

Hi Tyler and Tom,

I installed HTML DB and it looks great! Major step forward after webDB. Really nice.

This is probably not the right place to ask but...

We have internal wiki-wiki-web (PHP & mySQL) and we are considering moving it onto Oracle (like all other systems we have).

I can't find a way to make diff between two CLOB-s. I would like to save only version deltas into database and we really need [visual] diff to see the changes. Workspace manager is not the right tool (tracks row changes) and I have not found java implementation eigther.

I could use existing PHP code but I would like to use HTML DB for this application. Then I need probably diff implementation inside database?

Maybe there is other ways and I am just blind.

Tom Kyte
October 16, 2003 - 10:17 pm UTC

Tyler here,

Is your goal to save on disk space? If so, have you done the math on the cost of implementing the dif code, then combining the deltas with the original to get the current version again? Then take that cost and compare it to the cost of additional disk that you will actually need? Considering you can buy a terabyte for under $1000, maybe the simplest solution is the best. Of course maybe there is a business need to store deltas and I'm just barking up the wrong tree here.



Max, October 17, 2003 - 5:51 am UTC

Hi Tom, hi Tyler.
Thanx for your answers, would you, please, answer one more question?

I haven`t seen HTML DB yet, so, may be, the question is lame ;-)
What about using HTML DB for building WAP sites? I do understand, that we can set up some kind of mapping between HTML and WML and recieve WAP pages insted of HTML, but is it the only way? Does the HTML DB team cares about WAP developers? Thanks.

Tom Kyte
October 17, 2003 - 8:18 am UTC

Hi Max,

You are correct, we do not care about WAP developers. OK, the real reason HTML DB does not do WAP is that we chose to do one thing and do it well. We've worked very hard for 3+ years on doing this with a small number of developers (3-13). Doing WAP would have diluted our talent pool working on the primary product. Does it mean we will not do WAP in future realeases? No, but it has not been one of primary goals. Adding WAP would mean just adding a feature, it would be a re-write of a subset of our code, a fork in the road if you will. This would likely mean tossing aside one of our critical success factors as well, which is to "Write the tool in itself.". Since we use the tool every day to build the tool, we tend to 1. Catch more bugs before they make it to the public and 2. Add more shortcuts and features to increase productivity, as we all want to do more in less time.

You should look to 9iAS Wireless for your WAP needs.


Which version does HTML support?

A Reader, October 17, 2003 - 9:08 am UTC

Hi Tom,
I want to test the HTML DB and I downloaded the HTML DB. Before I start, I read the read me file and the following confused me.
"Server Hardware Requirements
Any computer capable of running an Oracle or better database. Installing with less than 128M of memory is not recommended.
Software Requirements
HTML DB requires the following software:
Oracle 9.2.0 or better database
Apache with mod_plsql"
One says or better, the other says 9.2.0 or better. I currently have an Oracle version of
Can I do HTML DB or cannnot? Should I upgrade first? I tried to find download of and failed to.
Thank you for your help.

Tom Kyte
October 17, 2003 - 10:27 am UTC

they only test on 9202 and above (i think it will be 9204 and above in the end) is where you get patch sets.

Fred, October 17, 2003 - 10:16 am UTC

Hi Tom,

I tried a bit HTML DB on </code> <code>and that really rox ! really intuitive, really easy to use and as i already know Oracle (datatype, SQL, PL/SQL, ...), i think i will make some things with HTML DB rather than PHP as i did before !

But how make some web site with HTML DB because i dont think that space provider will install HTML DB wheras they already had PHP installed...

Is it possible to subscribe some space at (as i did) and make a web site with HTML DB on ?

Thx by advance,


Tom Kyte
October 17, 2003 - 11:23 am UTC

Hi Fred,

Thanks for the feedback. As a developer, it's good to hear that all of the hard work over the past 3+ years is helpful to others.

You can develop applications on If you need more space, request it through the admin section. People are running production apps there, though I don't reccomend this as we are not providing a Service Level Agreement (SLA).

I see you are from France. If you have are going to be at OracleWorld Paris next week, look for the HTML DB pod in the Database section of the Demo Grounds. Mike Hichwa (VP and creator of HTML DB), Sergio Leunissen (Product Manager), and Marc Sewtz (developer) will be manning the pod. You could not ask for a better group of people to learn from, so I encourage you to seek them out.

Bonne Chance,

A reader, October 18, 2003 - 4:07 pm UTC

Hi Tyler,
I installed did the installation on Oracle @ linux - there were no errors during the installation. However, when I try to login on page .../htmldb_admin I can see in the lower left corner of the Internet explorer (accessing from windows Int explorer version 6.00) - "Error on page" - login button is disabled and nothing happens. Have you experienced something like this.
Thanks and regards.

HTMLDB it´s a marvel ;)

Cristian Perez, October 19, 2003 - 11:18 am UTC

Well, after seeing some of the open world presentations and reading this thread, I decided to give HTMLDB a try. I´m really glad that a I did.

Great work guys !!!!!!!!!!

I can see a lot of potential here. If for example it´s included when you buy "Standard Edition One" as with the Standard and Enterprise, you'll have a lot of bang for your buck.

Tom Kyte
October 19, 2003 - 6:20 pm UTC

it's included in SE -- and available for 9iR2 as well.

Fred, October 20, 2003 - 4:40 am UTC

Hi Tyler,

I can't go to OW in Paris, my boss dont *think* it's a useful thing :-(

And as i am a DBA, it's difficult for me to ask time to work on HTML DB (boss are a bit strange in France, they are not *strategic*, they are afraid to try new things => that's a huge difference between USA and France).

But no problem, i will work it by myself (as usual in France :-)), finding ressources on HTML DB on this marvellous web site, in the documentation and probably recommand using HTML DB on the future to some customers...

Thx for the great job and the informations :-)



Tom Swift, October 20, 2003 - 6:53 am UTC

To the reader who got "Error on page" when they tried to run HTMLDb, I got the same problem. It's because you haven't set up a virtual directory in Apache to the Docs folder. (See the install documentation).
As soon as you do this and restart the HTTP server it will work.



"Error on page"

msc, October 20, 2003 - 7:34 am UTC

To Reader,

I got same "Error on page" with IE6 but it worked fine with FireBird and Opera.

for Tom Swift - "Error on page"

A reader, October 20, 2003 - 9:46 am UTC


how t report bugs on html db

A reader, October 21, 2003 - 12:48 pm UTC

I ran into a sql error when attempting to create
an application.

Tom Kyte
October 22, 2003 - 11:14 am UTC

I would suggest you try the OTN forum for now at:
</code> <code>

When 10g is released, you can use MetaLink or call support (assuming of course you have a valid CSI), but until then, Raj on the OTN forums is our support organization.


I figured out when the bug is shown

A reader, October 22, 2003 - 11:23 am UTC

It is not a common case - so you are ok.. i will
try to post it in otn when i get a chance..

thanc Tyler, Tom!

How can we install mod_PLSQL?

A Reader, October 22, 2003 - 4:17 pm UTC

I have installed Oracle database and Apache server. But I don't have mod_plsql installed. Can you please direct me to where I can find more information about installing mod_plsql, or can you please list the steps for installing mod_plsql on Windows NT platform?
Thank you for your help.

Tom Kyte
October 22, 2003 - 4:24 pm UTC

Actually, if Apache was installed from the Oracle distribution, you have mod_plsql installed. You can verify this by navigating to $ORACLE_HOME\Apache\modplsql\ . If that directory exists, you have mod_plsql. You cannot easily add mod_plsql to a non-Oracle Apache distribution.

Tyler Development

Robert, October 22, 2003 - 6:56 pm UTC

Have you actually been doing AskTom development
on Marvel/HTML DB yourself or you got people doing
it for you and you just answer the questions ?

Tom Kyte
October 22, 2003 - 7:22 pm UTC

I wrote the first asktom (it was "blue")

Joel Kallman wrote AskUS -- the framework asktom is built on (a generic application, with my template). I touch things up here and there -- I wrote the underlying API that touches the data, but Joel did everything you "see"

mod_plsql on non-Oracle Apache distribution

msc, October 23, 2003 - 2:24 am UTC

You cannot easily add mod_plsql to a non-Oracle Apache distribution.

I use mod_plsql with different Apache distribution and had no difficulties to get it work. I still have Oracle Apache distribution installed into hard-disk but it is not running.

I had to copy following line into Apache httpd.conf file:

include "d:\oracle\ora92\Apache\modplsql\cfg\plsql.conf"

As you can see this is a Windows distro, maybe undex Linux it is complicated.

your HTML DB expertise

Robert, October 26, 2003 - 2:07 pm UTC


1)Is AskTom running on the latest release of HTML DB ?

2)Are you familiar & comfy with HTML DB to the degree that you can build an app by yourself from scratch w/o calling the guys down the hall ?
Hypothetically, you think you can handle ?

3) Did you do regions of the type "Function returning SQL"

This (in Region Source) does not work for me:
return mytestpkg.testsqlstring ;

where mytestpkg.testsqlstring returns just 'select * from emp'

I get :
"report error:
ORA-01403: no data found"

Tom Kyte
October 26, 2003 - 2:17 pm UTC

1) yes i believe it is -- Tyler Muth/Joel Kallman would know for sure...

2) I don't do gui's anymore :) i build server stuff -- the apis. So, for asktom, I built the original api's that deal with the data and Joel built the gui.

3) yes, we use them heavily here. do you have an emp table? does it have any data?

1) Yes, AskTom is running the latest and greatest. It's actually been part of our development process to upgrade our all of our internal production systems 1-2 weeks before we release an upgrade to the public. Since the tool is written in itself, it's actually been used/tested on our development instances for substancially longer. We really do "Eat our own dogfood", and we believe that it's critical to delivering a quality product to you, the customer.

2) I'm not going to touch this one, other than to say Tom knows more than he takes credit for. He actually wrote some of our most complex code, such as how we parse SQL and PL/SQL.

3) Try creating a region on the same page that is of type SQL Query and test your query that way. Function Returning SQL Query regions are great for dynamic predicates, but if you don't have a specific need for them, start off with the basics, it's much easier.

Hope this helps,

Some HTML DB questions

A reader, October 29, 2003 - 3:57 pm UTC

I downloaded HTML DB and set it up all on one computer for trial. It works great, right out of the box. I have few questions for a more structured implementation:
a) We do not want to install everything on one computer. Our databases are already installed on Sun boxes and we want to use them. We want to install Apache and everything else on a Windows box. Can we do this without installing the entire Oracle server software?
b) I want to bring data in from multiple database in my application. It is possible or am I restricted to designing against the database I connect to using the DAD?


Tom Kyte
October 29, 2003 - 5:29 pm UTC

a) Yes, you should be able to do this with a custom installation.
b) HTML DB is made for one database. You applications execute as a db user in a single database. The only way to consolidate data from multiple instances is to use db links, which as you know aren't the most performant way of doing things. You could do materialized views over db links to improve speed.

You should consider using the OTN Forum for HTML for future questios at:
</code> <code>

Good luck and glad it is working well for you,

HTML DB looks very powerful framework

Mihail Daskalov, October 30, 2003 - 12:29 pm UTC

Is there any intention to generate HTML DB Application from Oracle Designer?

Thank you Tom
for always providing the best answers.

HTMLDB looks really good

A reader, October 31, 2003 - 1:55 pm UTC

This product looks really good. I agree with you, Tom that most applications could be created in this environment.

What I think it needs now is more reporting capabilities. Many applications have reporting requirements that could not be met, or would be very tough to meet without a development tool specifically built for reporting. Like Oracle Reports.

So I think the combination of HTML DB and Oracle Reports would handle a big percentage of application development requirements.

Now the problem is, in order to use Oracle Reports you have to license and install the whole Oracle 9ias Enterprise Edition.

Expensive, complicated, high maintenance, Just to have reporting capabilities.

I am looking into other reporting tools (Crystal, Webfocus) as an alternative.

Do you or does anybody have any suggestions for reporting tools with the capability of Oracle Reports but a simpler environment and lower licensing costs?

AskTom & HTML DB

Robert, October 31, 2003 - 2:13 pm UTC

>tell me, do you consider asktom "small"? html db is >perfect for it -- it is the
>quinessential html db application......I do about >1,000,000 transactions a month (and asktom is just one of
>about 5 applications written in html db running on that >machine). Is that smallish?

Just re-read this thread...
I agree that the AskTom app is the perfect demonstration of what HTML DB can do - in terms of dynamically publishing data from db to the web readers/users.

Because except this form page I am typing into, it does not have the data entry, entry validation requirements like a typical C/S app.

What I'm saying is that AskTom is really cool but does not really "test" HTML DB in the area of user interactivity, data input...that sort of thing.

Tom Kyte
November 01, 2003 - 12:20 pm UTC

sure it does -- what do you think i use??

what are you using here?

how do you ask questions? (so it is not "except for this form page"). there is alot of interaction here. even just the search screen. most every page accepts some form of input or another.

we built an entire web calendar/pim system in earlier versions of html db. very very client serverish type of application.

our employee review system is written in it.

among dozens of others. it works dandy for user interaction. In fact -- due to the 100% consistency of the look and feel and the simplicity of the html generated, it is perhaps phenomenal for that. It works on all browsers (like amazon and yahoo -- but not most MS websites :), it's interface is somewhat intuitive (no one goes to training, no one goes to training), and it is simple to deploy and re-deploy.


A reader, November 02, 2003 - 11:04 am UTC

This product looks really good. I agree with you, Tom that most applications
could be created in this environment.

What I think it needs now is more reporting capabilities. Many applications have
reporting requirements that could not be met, or would be very tough to meet
without a development tool specifically built for reporting. Like Oracle

So I think the combination of HTML DB and Oracle Reports would handle a big
percentage of application development requirements.

Now the problem is, in order to use Oracle Reports you have to license and
install the whole Oracle 9ias Enterprise Edition.

Expensive, complicated, high maintenance, Just to have reporting capabilities.

I am looking into other reporting tools (Crystal, Webfocus) as an alternative.

Do you or does anybody have any suggestions for reporting tools with the
capability of Oracle Reports but a simpler environment and lower licensing

Tom Kyte
November 02, 2003 - 12:27 pm UTC

so, what kind of report could you not accomplish with HTML DB and analytic functions in sql??

give me a "for example"

"pls" for mod_plsql call

Robert, November 02, 2003 - 6:52 pm UTC

></code> <code>

Tom, are you aware if it's possible to configure the http listener or something else so that one can use a different alias than "pls" in mod_plsql call ? any way to mask that ?

It's like hanging a sign out to the world saying "this app is running against Oracle DB, come and ...."


Tom Kyte
November 03, 2003 - 7:04 am UTC

absolutely you can -- it is simply a configuration setting in the apache virtual paths.

but -- so what about the "sign hanging out there". they'd see it is Oracle, not a .asp and go away ;)

but pretty much every single website out there is running a database and many of them are in fact Oracle. The databases themselves are not getting hacked in as much as the APPLICATIONS in front of them are.

about url's rewriting

Andrey Prokopenko, November 03, 2003 - 3:37 am UTC

Since we're supposing here Apache as a web server, there is a very useful feature, contained in one of many Apache modules. I mean mod_rewrite.
By using it you can completely alter url to any diffirent location you want.
See more at </code> <code>

An Example Report

A reader, November 03, 2003 - 8:50 am UTC

Purchase order.
Requirements - Based upon number of pages- sometimes up to 20, margins have to be different on middle pages, signature has to be exactly 1 inch from the bottom of the lower right corner.
If there is a word processing document attached, possibly Word, possibly PDF, possibly Visio, it has to print starting at the next to last page, but the signature and some other info has to print on the last page in a specific location.
1st page prints landscape out of 2nd bin in printer, the rest print portrait out of 1st bin
If all reports could be handled by HTML, there would be little demand for products like Oracle Reports, Cognos, WebFocus, Crystal.
Talk to the developers in the Oracle Reports group, I am sure they could give you lots of other examples.

Tom Kyte
November 03, 2003 - 2:22 pm UTC

All reports should be handled by HTML, printed stuff is so 1970's :)

You are talking about format -- form, not content. As a report generator, for people serious about getting information, html db is awesome

for pretty printing in PDF, you are going to have to go beyond and buy a tool. I consider this less a reporting issue then a formatting issue. There isn't any report you cannot generate in HTML, you just don't like the way it looks.

Working with

Gus Spier, November 03, 2003 - 2:50 pm UTC

This promises to be a terrific feature.

Does everyone fumble around on the first page of and try to login with incomplete/contrived credentials? It seems almost as if there is an entrance exam (let's see if they are smart enough to click on the tasks in the correct order before we let them in) before getting close to requesting a service.

Report Formatting

A reader, November 03, 2003 - 5:25 pm UTC

>for pretty printing in PDF, you are going to have to go >beyond and buy a tool.
>I consider this less a reporting issue then a formatting >issue. There isn't any
>report you cannot generate in HTML, you just don't like >the way it looks.

It's not that I don't like the way it looks that counts. It's whether or not the people who sign my paycheck like the way it looks that counts.

That may be customers, business partners, CFO's, board of directors. Philosophy and theory are nice, but they won't pay my mortgage.

Tom Kyte
November 04, 2003 - 8:09 am UTC

then they have to pay the bills for the extra software :)

What i've discovered is that people can be QUITE flexible when they understand all of the factors.

"I want X"

"well, X means this, Y seems to satisfy everything and Y means this other thing"

"Oh, well of course, Y it is, 50% of the cost and it does meet my basic needs, go for it"

I see this over and over (might not apply here, we are just talking about a reporting tool after all). I remember trying to help someone tune a multi-table join of 10's of thousands of records all to get a random 6 records. We could not use SAMPLE since it was a complex query. So, the query basically had to assemble 10's of thousands of records -- sort it randomly and get the first six.

The reason? A manager thought it would be cool to have the home page hilight a random 6 employees. This query would be executed for every hit on the home page. the person tasked with this just tried to implement it. Not until someone showed that 99% of the capacity of the server would be spent executing this query over and over and over (for every view of the home page) and explained that to the requestor of this "feature" did they say "well, obviously, we shouldn't do that. it was a nice idea but hey, lets be reasonable here"

This I think goes beyond philosophy and theory and into the CRUX of what we should be doing -- not blindly following requirements developed by people who "don't understand the ramifications of such things entirely", but rather gently pushing back with provable, scientific information that says "hey, look at this, this is the COST of this function -- do you really really want it? you are after all paying for it"

More on Reporting (formatting + distribution of printed material)

A reader, November 04, 2003 - 9:14 am UTC

I am a consultant. Reviewing the applications that I have helped developed or worked on over the last several years, I think that HTML DB would have been able to satisfy the data manipulation requirements of over 90% of them. The other 10% probably could have been redesigned or done in another way.

However, the reporting ( formatting and distribution of printed material ) requirements would be extremely challenging and I suspect costly ( in development time ) to accomplish with HTML DB.

Oracle has a product ( Oracle Reports ) that in conjuction with HTML DB, would have satisfied probably 95%+ of all of the requirements for all of these applications.

It's too bad that you have to license the Enterprise Edition of 9iAS in order to get that functionality. Aside from just the licensing cost, there is a pretty hefty maintenance cost of 9iAS Enterprise Edition, if you include learning and management time. It also throws a complicated infrastructure of hardware and software into the mix that can and does break periodically.

I compare this situation with what I am perceiving in Oracle's embracement of Linux. If companies are able to spend less on hardware and operating systems, they will have more available for Oracle licenses.

Well, if companies spend less on Oracle licenses, which they could if applications could be entirely developed in HTML DB, they would have more available to spend on me, in application development.

I am looking into other reporting tools. So far, Crystal Reports is looking the best to me. From what I have seen so far it would meet most of the formatting and distribution requirements and the total cost of deployment appears to be about 10-20% of 9iAS Enterprise Edition.

If Oracle sold Oracle Reports as a standalone product, it would be a no brainer for me. HTML DB + Oracle Reports.

Sorry for rambling.

Again, I really like this product.

Your opinion is important, November 04, 2003 - 10:21 am UTC

Hi Tom, could you give brief a comparison between the PHP and HTML DB (About main features)
Thanks :)

P.D. Reeaaally Sorry Tom for this, but about the previous post I agree I have started a post and continued with other loooong post there is is otn formus, I think there should be a license only to run reports and forms runtime in IAS, 5,000 U$ is too much to pay when you only need runtime for forms and reports 9i :) sorry for the post but we'll not surrender easily I would like to use forms 9i, but I cant because the previous $$ problem. :(
I you think as us, please do a post here
</code> <code>
There seems to be good news in 10G, but...

Tom Kyte
November 04, 2003 - 10:26 am UTC

I cannot compare html db to php as I've never looked at php.
I recently began developing in PHP a bit to put together a personal web site. The reason I chose PHP is that is what is supported by the majority of the free hosting companies. After working with HTML DB for almost 4 years, it was really hard to go back to static files. My biggest problem was navigation. I ended up writing a function in php file that I included on every page to do my navigation, that way I could change it one place, not on every page. In HTML DB, you define a template for your navigational elements, then declaratively create them.

Next problem was the look and feel. There were several family memebers opinions involved in how the site would look. I tried to mock-up on page and get right there, but people wanted to interact with the site to get a feel for what they wanted it to look like. I ended up changing a lot of HTML on a lot of pages, no matter how much UI I did in CSS. In HTML DB, every UI element is controlled by a template: pages, regions, items and they're labels, reports, lists, menus, etc. When you want to change the look of something, you change it's template and instantly your change is reflected in every element that uses that template

Then I got into handling form data. I had to create a file to handle my form and declare variables to accept the data that was submitted to it. In HTML DB, you simply create an item, such as the "text area with spellcheck" that I am typing in, then you can reference it's value anywhere else in the application using bind variable syntax. The process that handles this followup I'm typing might look like:
update followups
set message_text = :P5_MESSAGE_TEXT
where id = :P5_MSG_ID;
Assuming P5_MESSAGE_TEXT was an item on page 5 that captured this text and P5_MSG_ID was a hidden item that contained the primary key of this followup. Also notice I did not include any database connection info. You don't have to connect to the database, HTML DB lives IN the database, so you can issue SQL statements at will without managing any connections.

Display conditions HTML DB are very powerful. They allow you to conditionally display items, regions, buttons, etc base on some other logic; maybe you only display the employee fields once a user has selected a department. This would all have to be hand-coded in PHP.

Validations are another powerful feature of HTML DB. Validations allow me to define the values that a user must enter in an item. Yes, you can do this in JavaScript, but how about checking to make sure the value exists in a 1 million row table. Are you going to pull all the rows down to the client? (I hope not). Once you see them in action, you'll understand their power.

I could go on for hours on this topic, but my best suggestion is just to try HTML DB and see for yourself.


:) Thanks

A reader, November 04, 2003 - 11:45 am UTC

Agree with post on reporting

A reader, November 04, 2003 - 11:58 am UTC

Hi Tom, any possibility on getting comment from someone in Licensing on the issues raised in that post?

Do educate us, Tyler...

Robert, November 04, 2003 - 4:44 pm UTC

>I could go on for hours on this topic, but my best suggestion is just to try
>HTML DB and see for yourself.

Tyler...we wanna hear it...put that into documentation & maybe create some more demo apps with features you want to show us ;)

Tom Kyte
November 10, 2003 - 9:06 am UTC

Also see my comments in this thread at: <code>

This is just the beginning. We will be posting more content as soon as we complete it.


Thanks Tyler too for your explanation

A reader, November 04, 2003 - 5:38 pm UTC

Tom, You have been lying on your resume

A reader, November 04, 2003 - 7:43 pm UTC

>This I think goes beyond philosophy and theory and into the CRUX of what we
>should be doing -- not blindly following requirements developed by people who
>"don't understand the ramifications of such things entirely", but rather gently
>pushing back with provable, scientific information that says "hey, look at >this,
>this is the COST of this function -- do you really really want it? you are
>after all paying for it"

I know now that you were the marketing director for Apple back in the 80's.

Don't give customers what they want, give them what's best.


A reader, November 04, 2003 - 7:54 pm UTC

Can someone please point me to where I can find documentation regarding HTML DB? More like users guide or something.

Thank you in advance

Tom Kyte
November 05, 2003 - 8:05 am UTC

it's forthcoming -- new feature of 10g that will be supported for 9iR2.

</code> <code>

download the preview release, most of the documentation is there as integrated documentation.

users guide

hannibal, November 05, 2003 - 1:21 am UTC

look at here

</code> <code>

and if you download html-db from oracle it also comes with some documenation about administration and installation.

hope this helps


how about regexps ?

Andrey Prokopenko, November 05, 2003 - 5:01 am UTC

HTML DB indeed is a long awaited option for Oracle.
But i very interested in one IMHO very important fearure.
I mean reqular expressions.
Work with text data became very light and robust, since regexps stepped into a play.
Now in oracle exists PL/SQL implementation of regexps functions named OWA_PATTERN, but it's awfully slow and has many limitations.
Dear Tom, since you IMHO no doubts are most skilled and competent in upcoming HTML DB release plans, could you please answer, does Oracle have any plans to implement more workable solution for regexps among other HTML features ?

Tom Kyte
November 05, 2003 - 9:15 am UTC

there is 100% full regular expression support in 10g -- native, compiled into the kernel, fast regular expressions.
In HTML DB you can use regular expressions in validations. You will only see this option when you install in a 10g database of course. Below is an example phone number validation included with HTML DB:

^\(?[[:digit:]]{3}\)?[-. ][[:digit:]]{3}[-. ][[:digit:]]{4}$

Try that one in PL/SQL ;)


Where to get Oracle requiremet for html db

A reader, November 06, 2003 - 9:37 am UTC

Hi Tom, where you can get Oracle database,

"Any computer capable of running an Oracle or better database"
or it is plus patch to upgrade to .
or which is the real database requeriment.

:) Thanks

Tom Kyte
November 06, 2003 - 10:01 am UTC

</code> <code>

There you can download the patch to get to

:) Thank you.

A reader, November 06, 2003 - 10:15 am UTC

Move OTN Forums to HTML DB

A reader, November 06, 2003 - 12:13 pm UTC

What are the chances of this happening?

Performance on the OTN forums site lately has been horrible.

I look at the url and see jsp.

I understand.

Tom Kyte
November 06, 2003 - 2:03 pm UTC

Tyler here,

We have considered (as has been reccomended several times before) creating a version of AskTom for HTML DB. The format is great and between you and I, I think he gets a little annoyed with all the HTML DB traffic here :) It's a great suggestion and I'll be happy to credit "A reader" with suggesting it if we impliment it.


I agree!!!!!!

A reader, November 06, 2003 - 12:18 pm UTC

At least move the HTML DB forum to

RE: Move OTN Forums to HTML DB

VT, November 06, 2003 - 12:49 pm UTC

Now a days part of E-bay is running on J2EE. If J2EE is good for E-bay it would be good for anything. Do not say JSPs are slow without knowing the stuff. It depends on several factors. ( bad design/coding/un-tuned etc..). I wonder which one is true for OTN forums?


A reader, November 06, 2003 - 4:41 pm UTC

I don't think J2EE is slow. I just think the environment is very complex. I would guess that the people that work on the forums site are scrambling today trying to figure out the problems.

There is probably finger pointing, the problem is with the database, no the problem is with one of the appservers, no a different appserver, no the infrastructure, no its hardware, no its something else.

If they were using HTML DB, there would be one place to look. The Database, and we all know how reliable that is.

Another Plug for

A reader, November 07, 2003 - 11:30 am UTC

I posted a question on asktom this morning (Your new book : Expert one-on-one performance by design) about the Web Seminar he did on Nov. 5th. The site was saying it hadn't started yet.

Within an hour, maybe less, that's how long it was from the time I posted until I checked back, my question was answered and his home page changed reflecting that the URL would be posted soon.

I wanted to post a question on the HTML forum. Sure enough, I get 'Error: Document not found'.

HTML DB HowTo's Posted

Tyler D. Muth, November 10, 2003 - 9:02 am UTC

We have posted the first round of "HowTo's" for HTML DB on OTN. We are working on more and will post them as they are completed. You can find them at:

If you have others you'd like to see, please request them on the OTN forum at: <code>

I know we have a number of White Papers under way as well. I'll let you know when and where they are posted.

Thanks for all the great feedback (and praise),

workspace, download link

Igor, November 10, 2003 - 3:39 pm UTC


I'm a bit lost.
What is workspace on
How could I get one?
Also, I don't find where is link to download...

Tom Kyte
November 10, 2003 - 4:16 pm UTC

A workspace is basically your little slice of the development environment. It allows the environment to be "hosted". So, you can sign up for the workspace "IGOR_INC" on </code> <code>. You can only see YOUR applications and your schema. Customers can also host this internally, thus allowing different development groups to work on applications independantly. So, the HR group can be developing their HR apps in the HR workspace, and on the same machine in the same db instance, the financials group can be developing thier apps in the financials workspace.

The download link has been removed again because we cut a new build and upgraded We will run it for a few more days before we release this code to the world. It allows to test it with a REALLY large population and still catch any bugs before people have the code.

You should really try HTML DB on the hosted site before you download and install it. This way you can focus on evaluating the important aspects of it such as its ability to built the type of application you are looking for and not have to focus on installation details such as modifying your httpd.conf file. Not trying to scare you off with the install (I did it in 4.5 minutes on a fast machine 2 weeks ago), just want to show you the quikest path to success.


Workspace and DB Schema

robert, November 10, 2003 - 4:49 pm UTC

Are the following statements corrert ?

1) "A HTMLDB Workspace mapes to ONE Oracle Schema"
2) "A schema can be associated with different Workspaces"

Tom Kyte
November 10, 2003 - 5:19 pm UTC

1) Almost. More than one schema can be associated with a workspace.
2) Correct.

Essentially you have a many-to-many mapping, though the typical use is a one-to-one mapping in both cases.



Igor, November 11, 2003 - 3:35 am UTC

error list?

hannibal, November 13, 2003 - 7:30 am UTC

first i must place my praise to the new HTML-DB. This is a real greath feature ... i it very helpfull to us.

i would like to ask if there is a list of error-codes the html-db is returning to me available (like the error-list for oracle ORA-<number>) so we can find out what the software wants to tell me.

f.e. currently i try to pass over values from one page to an other.
i call from one page:
</code> http://PC:PORT/pls/meins/f?p=109:9:2366173999985397015::::EMPNO:1307: <code>
and get
Error ERR-1002 Unable to find item ID for item "EMPNO" in application "109".
from the other instead of using the :EMPNO in my
select * from emp where empno = :EMPNO

but i cant figure out what i am doing wrong ...
a list of the ERR-<number>'s would be very helpfull i belive.

thx in advance


Tom Kyte
November 13, 2003 - 9:40 am UTC


I suspect you are not using the EXACT name of the item you are trying to set. Typically item names default to P#_NAME, so if this item appears on page 9, it's name is likely P9_ENAME. We do not have a list of error codes available right now, but I will file this as a bug.

If you have not found it already, you can find the URL sytax documentation by clicking on the help icon > index > f > f?p syntax.

Hope this helps,

working on tab

hannibal, November 13, 2003 - 11:06 am UTC

i am working on a table -> emp as you surely guessed. and i
want to use the column EMPNO directly by calling an url...
from my query in the report
select fname||' '||lname name, empno from emp
i create a table and i set in
HTML DB Home>Builder - Application 109>Page Definition>Report Attributes>Column Attributes
for the column ENAME
in the section 'Column Link'
the values for
application : &APP_ID.
link text : #NAME#
all other fields are blank in this section('Column Link'). ( i want to create links on the names to call an other URL with the EMPNO as attripute/item ).
i looked for my link and i think it should be o.k. and complete (?)
may be it would help me if you could explain this error number to me. i am not sure, does this error come up because the #EMPNO# is unknown at the calling site (8) or the :EMPNO
does not fit to anything at the called site (9). for my experience if the bind varaibale (:EMPNO) is not found, it gets a NULL value for the next excution - so i guess the error comes up before the html-db gets to the parsing - may be before the site 8 gets called (?).
i also tried to run in debug mode but by calling the page
i get nothing more but the short error message but no
trace from the debug (which is very good and helpfull in all the other situations where i had to use it BTW).
error message :
Error ERR-1002 Unable to find item ID for item "EMPNO" in application "109".

Unexpected error, unable to find item name at application or page level.
is there a way to look into the application to see what item he expects or would be ready to accept?


hannibal, November 13, 2003 - 11:23 am UTC

by creation an explicit hidden item of the same name in the same page it worked :-) . so i guess the application does check if there is such an item defined in the application.
do i always have to create such an hidden item when passing a value i want to us as bind-variable on the next site?
thanks in advance

Tom Kyte
November 13, 2003 - 1:22 pm UTC


I'll try and cover both of your posts with this response.

You are setting the value of an item through the URL. If the item does not exist, you will get this error. Keep in mind that item names MUST BE UNIQUE for your application, which is why we default them to P#_XXX. This way you could have P9_ENAME and P38_ENAME.

It does not matter whether the item is hidden or not. You could change it's type to "Display as Text (saves state)" and it would work just fine.

The best thing I can tell you to do to understand these concepts better is to click on the "Wizards" tab in the builder and choose the "Report with links to form on a table (2 Pages)" wizard to build a two page application on emp. You can then reverse engineer these two pages to see how they work. Basically, the report on the first page will have a link to a form on the second page. The link will populate a hidden item (for empno in this case as it is the primary key) on the second page. There will be a "FETCH" process on the second page to query the row automatically to populate the other fields (where empno = :PX_EMPNO). There will also be a "Process" process (no way to make that sound right) that will insert/update/delete a row based on the button you click.

Also check out the forum at:

and some "How To's" at: <code>

Hope this helps,

New version of HTML DB

A reader, November 13, 2003 - 11:37 am UTC

Sorry, I would have posted this on the Forum, but again I cannot access it.

Besides, I like this site so much I check it more often.

I see there is a new download, is it possible to get the bug fix list and enhancements for this version, much like in the readme's for patches on Metalink? Just want to know if I should upgrade or not.

By the way, I LOVE this product. It has become somewhat like a religion to me. Everywhere I go, I preach about this "next great program language". Too bad that the converts are again going to completely convert all of their middle tier code again. Oh well, I think that when the "next great program language" after HTML DB comes along, it will also be storing it's code in the database, so my guess is the conversion will be easier.

Tom Kyte
November 13, 2003 - 1:35 pm UTC

"A Reader",

We have not yet generated a list of bugs fixed, etc. with any of our releases. I will add this as an enhancement request and see where it goes as I beleive it to be a great suggestion. Not sure what version you are running, but from 1.4.x on it has been nothing but bug fixes. I don't believe we've added a single feature since 1.3.9. A lot of the bugs have been internationalization issues, but there have been some others that you may experience. I would suggest upgrading, as it puts you one step closer to the version that will be formally supported with 10g. The upgrade process is reasonably painless and is reverseable if anything goes wrong. Basically we:
- Install the newer version in a new schema
- Copy all of the meta data over from the old version
- Run a report showing the number of rows in each table, comparing the old schema to the new (old: 4 applications -- new: 4 applications = good, old: 193 pages -- new: 190 pages = BAD).
- At this point you can choose to recreate the public synonyms for HTML DB which would then point to the new schema. That's it.
- If things aren't working as expected, such as a tablspace filling up when the meta data is being copied over, you simply call a package to point the public synonyms back at the old schema.
After a couple of days, you can drop the old schema as it is no longer needed.

Glad you like the product so much! Our team enjoyed your quote, it was a nice break from fixing obscure multi-byte character set bugs ;)


New Version of HTML DB

A reader, November 13, 2003 - 1:04 pm UTC

Sorry, I meant "next great programming paradigm"


Joerg, November 14, 2003 - 5:12 am UTC

Sorry,I'm not finding the download link.
Cann someone help me about this?

Tom Kyte
November 14, 2003 - 9:38 am UTC


If you haven't tried building anything already on the hosted site, I would start there. It saves you the time of downloading, installing, configuring Apache, etc. So you can get to what you really wanted to do, which is evaluate the product. The code that you download and the code that is running on is EXACTLY the same. The only thing we change is to add a link or two on the login page.

Good luck,


Joerg, November 14, 2003 - 7:12 am UTC

OK I found it out now it was not available because
my primary language in the browser was german and
the translation option of the website(HTMLDB??)
is cutting some links allthough the download link

Tom Kyte
November 14, 2003 - 9:19 am UTC

are you sure? they HTML DB guys just tested that and had no issues? does it still reproduce?

Just think HTML DB should be at the top of all lists

A reader, November 19, 2003 - 2:05 pm UTC

Can I publish webservice using HTML DB?

msc, December 03, 2003 - 5:49 pm UTC

Hello Tom,

is it possible to receive SOAP message via mod_plsql and then process it in PL/SQL (Oracle 9i)? It seems to me that it would require SOAP support from mod_plsql part?

All OTN examples of publishing SOAP-binded webservices use App Serve Java based approach but I would like skip this part.

The HTML DB seems to support only consuming webservices (and then rendering them into page region)?

Tom Kyte
December 03, 2003 - 7:21 pm UTC

Unfortunately there is no way to publish web services using HTML DB, only consume, as you mentioned.

Sorry for the lack of help here,

Thanks Tyler!

msc, December 03, 2003 - 7:35 pm UTC

webservice != SOAP

msc, December 05, 2003 - 4:24 am UTC


you mention:

Unfortunately there is no way to publish web services using HTML DB, only consume, as you mentioned.

The WSDL specification </code> <code>states:

.... in this document describe how to use WSDL in conjunction with SOAP 1.1, HTTP GET/POST, and MIME.

and chapter "4. HTTP GET & POST Binding" shows how to make use webservices without the SOAP envelope.

So... the SOAP is *not* required to publish the web-service. It is just one way of doing it.

This implies that the HTML DB and mod_plsql/owa can be used as web-services provider for HTTP GET & POST binding.

We just cannot use the HTML DB based web-serviec to accept SOAP *request* but still we can provide SOAP wrapped response.

Do I get it right? Or do I leave something out? Why this SOAP is useful (or so much used) anyway :-/

Tom Kyte
December 05, 2003 - 3:08 pm UTC

Hiya msc, Sean Dillon here.

Yes, it's true, SOAP is all about Web Services but Web Services are not necessarily all about SOAP. Therefore, a web service could indeed be implemented using some other form of HTTP- (or other protocol believe it or not) -based infrastructure. Nothing is keeping a savvy developer from writing such a custom web service that is not based on the SOAP >standard<.

The issue with that, however, is that it isn't based on standards... This is okay if you only ever plan on talking to yourself or to other business partners, customers, etc. that plan on implementing your service architecture... but for the other 99.9% of the world, this is unacceptable.

Oracle builds it Web Services support based on standards. Even the HTML DB implementation of the Web Services consumer model is based on SOAP.

I don't think that SOAP not being required to publish web services IMPLIES that HTML DB and the PLSQL Web Toolkit can be used as a web services provider for HTTP GET & POST binding. I think that it implies that Apache's HTTPD listener along w/ some crafty custom development using the PL/SQL Web Toolkit could indeed implement a custom web services architecture/implementation. HTML DB has nothing at all to do with that.

Now, this doesn't mean we can't expose Web Services in the database that you build through HTML DB. In 10g, we are going to have the ability to publish and subscribe to web services from within the 10g database instance. No iAS... no HTML DB... no .NET... just the database. It sounds like this is something you should look into once the docs are available or you can get a public release of the 10g database.

Why's SOAP used so much/useful? Again... standards. Everybody agrees on standards, then whenever I do something, you can take advantage of it easily, quickly, lower cost, etc.

Hope that helps!

Sean Dillon

Thanks Sean!

msc, December 06, 2003 - 5:05 am UTC

In 10g, we are going to have the ability to publish and
subscribe to web services from within the 10g database instance. No iAS... no HTML DB... no .NET... just the database.

This is interesting :) One good reason to wait for 10g...

Tom Kyte
December 06, 2003 - 10:11 am UTC

Along with:
- Flashback ANYTHING
- Regular Expressions
- Install in 20 minutes from 1 CD
- PL/SQL Performance

The last one had a big impact on HTML DB as it is written entirely in PL/SQL. Same hardware 9i vs 10G we saw around 30% increase in performance. We would have been happy with 5, or even 10, but 30! Of course my favorite feature is HTML DB, as we've been working on it for almost 4 years now. Everyone who tried it loved it, but there was no marketing behind it to get the word out to the masses.


How to uninstall HTMLDB?

Saminathan Seerangan, December 06, 2003 - 11:40 pm UTC

Could you please tell me how to uninstall HTML DB which I installed over my DB running on Windows NT.
First installation did not go thru successfully so I tried to re-install 2nd time. But is is giving me the following error.

>> Which directory should be used for the log file? [./] htmldblog
>> Is this a (1) New install or an (2) Upgrade? [1] 1
>> What is your connect string (Enter for none)? [] sami
>> What is your Oracle SYS password? [CHANGE_ON_INSTALL]

It appears that this version of Oracle HTML DB already exists in the database.
To upgrade Oracle HTML DB, you must use a newer version than you currently
have installed.

ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 11

Disconnected from Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production



Tom Kyte
December 07, 2003 - 7:44 pm UTC

You just need to drop the two schemas associated with it. They are:
FLOWS_0104XX where XX is the minor version number and

So, if you are running version 143, you would connect via SQL Plus as system (or some other privildged user) and execute:
drop user FLOWS_010403 cascade;
drop user FLOWS_FILES cascade;

That's it. Simply perform a new install and your all set.

Good luck,

htmldb passwords

Mark A. Williams, December 08, 2003 - 12:37 pm UTC

Are passwords submitted via the htmldb login screen passed as clear text across the network? If so, what recommendations for securing them? I have been tinkering with the Apache directive SSLRequireSSL...



Tom Kyte
December 09, 2003 - 5:31 am UTC

SSL is the way to encrypt this stuff yes.

creating DAD for another database

Saminathan Seerangan, December 08, 2003 - 2:36 pm UTC

Thanks for your earlier reply.
I did setup HTML DB on my Oracle db which is running on Windows NT. Everything is working fine.

From here(where I installed HTML DB),

(1) Is it possible to create DAD for other database where we DO NOT have HTML DB installed.

(2) Is it possible to create DAD(where we provide username,password and connect String) for another database (version

I like to access all our databases thru SQL Workshop.

Thanks to Tyler & Tom.

Tom Kyte
December 09, 2003 - 5:46 pm UTC

1) Creating the DAD is not the problem, that's easy. All a DAD (Database Access Descriptor) does is determine what database to connect to and which credentials to use (plus a few additional attributes). If the target database does not have HTML DB installed, you are out of luck.

2) Not sure I understand what you are asking, but I doubt this will help as I understand your end goal. The target database MUST have HTML DB installed.

I know these are probably not the answers you wanted, but it is what it is. SQL Workshop is really just an enabler, allowing a developer building a web application to manage db objects as well. Is it the replacement for TOAD or EM? No. Is it an easy way to manage objects in a database which has HTML DB installed? Well, that is subjective, but I think it is.


Client-side scripting in Framework

David, December 09, 2003 - 9:39 am UTC

Are there any plans to incorporate client-side scripting into the framework in future releases? (I know I can write my own now.)

For instance, within the same framework of showing an item conditionally, you could choose server-side vs. client-side. Of course there are trade-offs to both but the obvious advantage on the client-side would be that you are not re-executing the whole page. On the flip-side, complexity increases and page size increases.



Tom Kyte
December 09, 2003 - 2:01 pm UTC

I'll give my 2cents (tom).

java script is evil.
java script is what makes things work in IE but not netscape, netscape but not mozilla.

I hope "client side scripting" is not paid much attention to.

just my 2cents. nothing makes me madder then going to a site and getting an error message cause I'm not running IE with patch 55 -- but rather that i have th audacity to run mozilla or something else that runs on something other then windows.

Followup on client-side scripting

Dave, December 09, 2003 - 2:38 pm UTC

I hear you.

I'm not very up on all this versioning, etc. but could you run into the same thing with HTML or is that more standards-based than java script? If so, client-side scripting could really benefit from standards, like most stuff I guess.

Actually, I'm a little surprised you are so against it given your interest in performance/scalability. (Awaiting your rebuke.)

Hey, how about giving the user the ability to choose client vs. server-side on the fly. Or HTML DB checking the browser version and switching to server-side when client-side is not supported. Now that would be cool. ;)


Tom Kyte
December 09, 2003 - 5:39 pm UTC

Tyler here. My opinion may differ a bit from Tom's, but I get equally annoyed at scripts that only support one browser. There are standards for JavaScript which most browsers support, but there are exceptions. I personally code to Mozilla which has 2 benefits. 1) I am not tempted to use IE specific functions or attributes 2) The errors returned by the JavaScript console in Mozilla (and variants such as Firebird) are much better than those returned by IE.

As far as including some client-side options in HTML DB, we've talked about it several times, but we always reach the same conclusion: It's a nightmare trying to support all of the different browsers out there and we'd rather have it work 100% of the time.

We do have conditions available to developers based on which browser the end-user is running. You'll find them in the conditions list along with "Item not Null" etc.


Re:creating DAD for another database

Saminathan Seerangan, December 10, 2003 - 8:38 am UTC

Thanks for your response. You made it very clear.
(1)HTML DB is not a replacement for TOAD or OEM
(2)The target database MUST have HTML DB installed (Implicitly the tage DB must be 92.03 or higher). So I acnnot connect to my 8i db.

Thanks again.

What kind of configuration do I need?

A Reader, December 10, 2003 - 3:44 pm UTC

Hi Tom,
I installed HTMLDB on, succsessfully, according to the log. When I get to localhost I cannot get to admin page as stated in the manual. Instead, I was directed to the Apache's home page. I tried to type the admmin's page on the address and get error, page cannot be dispalied. From here, what kind of configuration should I do?
Thank you for your help.

Tom Kyte
December 10, 2003 - 4:25 pm UTC

I would suggest you move these types of support questions to the OTN forum at:
</code> <code>

I suspect the source of your problems lies in either your DAD configuration or Apache configuration. Sounds like you didn't create a DAD for HTML DB yet. Have you read the installation guide yet? It talks you through each step of the process. It can be found by opening readme.html from the HTML DB source directory you unzipped. Then click on "Installation Guide".

Hope this helps,

Let's Bet !

Robert, December 15, 2003 - 6:18 pm UTC

I'm doing research on whether my co should move on to
9iAS/10gAS from 8i/OAS4 or none at all if HTMLDB fulfills our
authentication requirements. (we are in the research info publishing business via pl/sql web app).

My head is *spinning*...but I am willing to bet my savings right now that the next reincarnation of HTMLDB is gonna end up in the next AS like WebDB...Portal...
Anyone wanna bet against me ? :)

Tom Kyte
December 16, 2003 - 7:03 am UTC

I will.

Personalization/Customize Feature

Robert, December 16, 2003 - 10:12 am UTC

Please comment on how a "Personalization/Customize" feature can be built into an
HTMLDB app. Not on the level of sophistication as "OracleAS Personalization"'s
predictive, real-time, data-mining model but somewhat like Yahoo's "pick your choices"


Tom Kyte
December 16, 2003 - 3:15 pm UTC


I see you cross-posted in the forums (again), so I'll just provide a link to your post and Raj's answer ;) :

</code> <code>



Matt, December 16, 2003 - 1:10 pm UTC

I have just installed HTMLDB pre-release, and have had my first play about with the software.

Can I just say first of all the installation notes, and installation itself were excellent - HTLMDB flew in first time. No messing.

I have used Portal in the past and the whole experience was quite different - especially with the bulky architecture.

Excellent job HTMLDB chaps - I look forward to posting many questions in the near future (on the technet forum of course !!)


Tarun Babu, January 06, 2004 - 7:59 am UTC

Hi tom,
Thanks for updating us on HTML DB. But what is this URL signify? I didn't get it. What are the pls/ask/f?p= etc. Can you elaborate.

</code>, <code>

Tom Kyte
January 06, 2004 - 9:09 am UTC

it is just a url, a uniform resource locator. it contains sufficient data for me to find what you are asking for.

You don't have to "get it" :)

f is a procedure in my database (part of html/db actually).

It is taking a single input -- that input name is P

P is set to that long colon delimited string that F will parse and process. The first number, 4950 is the application (asktom), the second is the page (page 8), then the rest is just inputs into that page (the big number -- thats the display id, primary key, of the data you wanted)

A reader, January 06, 2004 - 12:23 pm UTC


You really have lot of patience, though the question that "Tarun Babu" from India asked has been answered n number of times, you answered it patiently rather than pointing him to links. :-)


Tarun, January 07, 2004 - 6:08 am UTC

Hi Tom,
Thanks for your answer. Its very clear now. I do not know that its answered n no of times. Sorry, if I troubled you.

uninstall htmldb

Stewart W. Bryson, January 15, 2004 - 4:05 pm UTC

I downloaded the preview version of htmldb and didnt' realize I had to have all the Java and XML packages in place before installing. So I got several errors when I ran the install script. Now that I have corrected all those issues, I want to reinstall htmldb, but there is functionality in the "ins.sql" script to detect if it's ben run before and disallow another install. Any ideas?

Thanks Tom.

Tom Kyte
January 16, 2004 - 8:25 am UTC


Assuming you have only one version of HTML DB installed in your instance, your best bet to fully uninstall Oracle HTML DB and start again is to drop the two schemas created by the HTML DB installation. You'll want to drop the FLOWS_FILES schema and also the FLOWS_01XXXX schema. If you installed Oracle HTML DB Preview Release, the second schema name will be FLOWS_010404.

re:uninstall htmldb

Sami, January 16, 2004 - 9:18 am UTC

look at "How to uninstall HTMLDB?" in the same thread.
You just need to drop the two schemas associated with it. They are:
FLOWS_0104XX where XX is the minor version number and

mod_plsql in 10g

Sami, March 10, 2004 - 10:16 pm UTC

Is mod_plsql available in 10g? if so where can I see the configuration part?

Tom Kyte
March 11, 2004 - 8:21 am UTC

yes, it is on the "extra stuff" (companion) cd with apache and htmldb.

What config info were you looking for? I'd be happy to post any aspect of the mod_plsql config for 10g, as it has changed, but please be more specific as to the file, etc.


Do we have DAD configuration as part of HTMLDB?

Sami, March 13, 2004 - 12:51 pm UTC

I am specifically looking for DAD configuration in MOD_PLSQL. I have "upload file" application in 9i and like to do the same in 10g.
Do we have DAD configuration as part of HTMLDB?

Tom Kyte
March 13, 2004 - 1:12 pm UTC

100%, htmldb sits "behind" mod_plsql


In 10g, the Oracle Universal Installer takes care of Apache configuration for you. Here is how it configured my machine:

- A call in httpd.conf to include:
- A call in oracle_apache.conf to include:
- The following is everyline from marvel.conf:
Alias /i/ "c:\oracle\product\10.1.0\db_2/marvel/images/"
<Location /pls/htmldb>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername HTMLDB_PUBLIC_USER
PlsqlDatabasePassword @BQmKDACx01VE/f53/s3rN9k=
PlsqlDatabaseConnectString tmuth-pc:1521:orcl ServiceNameFormat
PlsqlDefaultPage htmldb
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDocumentPath docs
PlsqlDocumentProcedure wwv_flow_file_manager.process_download
PlsqlAuthenticationMode Basic

Not sure if this is of any value, but it does show the new format for mod_plsql dad configuration. But, as I said before, this is all take care of by the OUI if you are installing in 10g and you obviously already know how to do it 9i.

Hope this helps,

Page like

Sami, March 17, 2004 - 12:08 am UTC

How do I get page like </code> <code>using my 10g DB.

Installed 10g HTMLDB and my marvel.conf looks like below.

Alias /i/ "/u01/app/oracle/product/10gc/marvel/images/"
<Location /pls/htmldb>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername HTMLDB_PUBLIC_USER
PlsqlDatabasePassword @BaaBwwUA/3UDOmKmvRD1lTY=
PlsqlDatabaseConnectString ServiceNameFormat
PlsqlDefaultPage htmldb
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDocumentPath docs
PlsqlDocumentProcedure wwv_flow_file_manager.process_download
PlsqlAuthenticationMode Basic

I like to access my db thru browser. What would be my url. My Pracle HTTP server is running on port # 7777.

What is the URL to configure DAD (from browser)

Tom Kyte
March 17, 2004 - 1:24 pm UTC


First of all, these specific setup questions should probably be directed to the OTN forum for HTML DB found at:

To answer your first question, you should be able to get to your installation at:

http://yourservername:7777/pls/htmldb/htmldb <code>

As for your second question, I was not able to find a web interface to create a DAD in the build of 10G I am running. It is not a production build, but I suspect it may not exist anymore. I suspect the dad that was created for you will work fine. There is also a file called dads.readme in the Apache/modplsql/conf directory. It's very detailed and should answer most if not all of your questions. You might also want to read dadtool.readme as it describes the dadtool app which is used for obfuscating passwords in your dad.


Schema browser

A reader, March 17, 2004 - 9:11 pm UTC

Is it possible to expose a graphical interface to the data dictionary in a schema using HTML DB?

In other words, generate a tree-list of all my tables, views, columns, etc. Clicking on a column name will take me to a list of all tables having that column. Clicking on a FK constraint will take me to the refernced PK, etc.

There is a lot of metadata (including COMMENTS on tables/columns) buried inside the data dictionary that I would like a easy way to expose via browser.

Is this possible using HTML DB? Thanks

Tom Kyte
March 18, 2004 - 9:45 am UTC

Hi Vikas,

I believe your question was answered over on the OTN forum at:
</code> <code>

I will follow-up Scott's answer with more details there.


Thanks, Tyler

A reader, March 18, 2004 - 9:57 am UTC

Thanks, Tyler, I did put a watch & email notification on OTN but didnt get it so I was not aware that my question had been answered.

I will continue the discussion on the OTN forum, thanks again.

Oracle HTTP Server Administrator's Guide

Joel, March 19, 2004 - 1:54 pm UTC

The Web interface to directly create/configure a Database Access Descriptor for the Oracle HTTP Server bundled with 10g is not available.

Documentation for the definition of all of the DAD parameters can be found in the Oracle HTTP Server Administrator's Guide 10g Release 1. A link to this documentation that works for me is:

</code> <code>

Fantastic thread

Mike, March 20, 2004 - 4:51 pm UTC


Much earlier in this thread you wrote:

> We use CVS ( for our source code
> control with various clients, including tortoise (

I'm sorry for this tangent question but I really value your opinion and Tom's so I'm wondering if I could ask why you use free CVS server and client tools vs. a commercial source configuration management (SCM) product? I'm not judging your decision to use CVS, rather I wish to learn more from you since we are shopping around for an SCM tool. Is your CVS choice predominantly a cost saving choice, a feature choice, both, other?

Also, do you use the original CVS server or a CVSNT server (as Tortoise recommends using)? Thanks for any elaboration you might have time to share.

Tom Kyte
March 21, 2004 - 7:36 pm UTC


Well, I can't take credit for choosing CVS. I believe this one was Joel Kallman's decision, and I believe it to be a exceptionally wise one.

One of our key requirements was that we needed a cross-platform solution for the client. We have developers running windows, Linux, and OS X, all are supported. We also wanted to host it on a Solaris machine as none of our windows or Linux machines were "professionally managed", ie backed up on a regular basis. CVS fit well with this, and when our server went from Solaris to RedHat, it was no big deal.

We also wanted ease of use, as the more trouble something is to use, the less likely people are to use it. Tortoise CVS was a great windows client that integrated seemlessly with the OS. I also used the WinCVS client to get a graphical roadmap of changes and associated comments for a file.

CVS was also very scriptable, both on the client and server side. I set up my textpad of choice, UltraEdit, such that I could highlight a comment I wrote in the head of a file and press a function key. That would call cvs with some command line parameters to checkin the file and use the text I highlighted as the comment in CVS. On the server-side, Joel wrote scripts to export our HTML DB applications and check them into CVS on a nightly basis.

To sum up the benifits (IMHO):
- Cross platform, both client and server side
- Extremely easy to use on the client side
- Easy to script, both client and server side
- Free. I don't know how much this played into the decision. Our team is pretty frugal (good for the stock price), but I think if there were a product out there that were significantly better, we would have paid for it.
- You don't need to lock files to use it effectively
- Web interface to show map of files and all changes to them
- Lots of client-side third-party support
- Well documented and tested. This is not some hot new tool with a bunch of bugs to work out. It's been around and it works.

We are using the original CVS server. I have not used, nor heavily researched CVSNT. Since I haven't used I can't say much about. My knee-jerk reaction is that the tradional version of CVS is the safe solution and IMHO, does everything we need and more. I still use it on a daily basis for any project that involves more than 1 developer.

Sorry this response went on so long, but I hope you find it useful.


Thank you

Mike, March 21, 2004 - 10:01 pm UTC

> Sorry this response went on so long, but
> I hope you find it useful.

Tyler. I found it VERY useful and helpful and I'm thankful you found time in your busy schedule to reply. Thanks for sharing all this great information -- we shall pilot CVS first, I think, given its $0 cost and praise from respected folks like you. Thanks again!

better product

Mark A. Williams, March 21, 2004 - 10:23 pm UTC

> I think if there were a product out there that were significantly better

Guess the Oracle SCM team needs to get on the ball then?

- Mark

Tom Kyte
March 22, 2004 - 12:08 am UTC


As embarassing as it is, I have never used SCM. My Oracle knowledge does not extend much beyond the datbase and HTML DB. I looked at the specs and a number of the viewlet demos and it looks pretty good. I will not reccomend a solution that I have not tried before, but I am a big fan of products built on the database. This also has a huge advantage over CVS in that when you back your database, you also back up your source code. Another compelling advantage is that it is integrated with JDeveloper.

I don't want this thread to spiral into an SCN discussion forum, so I would reccomend that you check out other peoples experience on the OTN forums at:

or the product page at: <code>

Thanks again for keeping me on my toes Mark,

mod_plsql scalability

A reader, March 22, 2004 - 1:30 am UTC


You said mod_plsql is probably not suited for a website such as Amazon due to it's high number of transactions/users. When I used to support a mod_plsql application a couple of years ago I used to see hundreds of session in the database (the app had around 500 concurrent users), it seemed to me that mod_plsql kept an constant session in the database and not able to work with connection pools. May be this make mod_plsql not scalable?

I guess HTML DB would have same problem (high number of sessions if there are high number of users), am I correct?

Tom Kyte
March 22, 2004 - 6:54 am UTC

that you saw mod_plsql keeping constant sessions shows that mod_plsql in fact implemented connection pooling!!!.

It is what makes mod_plsql scalable in fact.

Thanks Tyler

Mark A. Williams, March 22, 2004 - 9:27 am UTC


I understand about you not wanting this thread to spiral off course, and I agree with that... I have used Designer/SCM (as well as Visual SourceSafe), but not CVS...

Always a pleasure,

- Mark

where oh where oh where...

Dave, March 30, 2004 - 3:50 pm UTC

Is the book on HTML DB? I need a for dummies like tutorial to slowly help me move away from pl/sql to this new (and better from what I can see) way of developing...

Tom Kyte
March 31, 2004 - 6:13 am UTC


There is no book yet on HTML DB, although a couple are in the works (that I'm aware of).

In addition to the documentation (which is not a tutorial), there are a number of HOWTO documents that explain how to accomplish some simple and some sophisticated tasks in HTML DB. They are available at:


There is also an Oracle By Example series for HTML DB, which is quite detailed.  You may wish to check this out at: <code>

Just follow the path of Oracle Database -> Oracle Database 10g -> Application Development.

Hope this helps.



A reader, March 31, 2004 - 9:30 am UTC

I need a more structured approach though maybe using a case study from start to end.
Also, I am looking at making a system that allows users to add in fields but in a way above HTML DB if that makes sense? They would be able to add in company specific properties to a page but not at the html db level. Would this be technically feasible? EG via an api call via pl/sql?

Tom Kyte
March 31, 2004 - 11:18 am UTC

"A reader",

More information and whitepapers about HTML DB will be made available on the HTML DB corner on OTN in the upcoming months.

It's unclear what you mean by "add in fields" or "add in company specific properties to a page." HTML DB does support region types of PL/SQL, and you can emit any HTML using the PL/SQL Web Toolkit (e.g., htp.p, htp.prn, etc.).



Dave, March 31, 2004 - 3:20 pm UTC

Thanks - I am looking to replace an app that is used by various companies which allows some customization, eg adding in a few new fields and changing titles and so on. Would this be possible in html db? I presume as the data is defined seperately to the presentation layer this may not be the best technology but wonder if you could elaborate?

Tom Kyte
March 31, 2004 - 4:18 pm UTC


Your assessment is quite accurate. Being a declarative development environment, HTML DB is well suited for this style of programming where you "drive" a browser to build your application. The result is a definition of a page stored in the database (and the defintion contains things like your reports, your items on the page, the labels for the items, your templates for the page, regions, reports, etc).

HTML DB does also contain APIs which enable you to render items on a page programatically. If you take a look at the Query By Example page in the SQL Workshop in HTML DB, this is a query page dynamically built in HTML DB from the definition of a table.

But HTML DB does not currently publish APIs for you to programatically *define* the attributes of a page. That is, you could not build an application yourself that collects input from the user and then when they hit 'Apply', they've built a new page in their application.

So...could I build an application in HTML DB that renders items on a page, given the definiton or preferences from a user? Yes. Could I change the region title at runtime, based upon the preferences of a user? Yes. But while you can conditionally show regions at runtime, you can't dynamically *create* regions at runtime. I'm not sure if this covers the "and so on."


How to capture checkbox value and store into tables

Sami, April 01, 2004 - 11:25 pm UTC

I am trying to use checkbox in my application like below.
could you please show me how I should write "write_info" procedure to store checkbox value into table. I am having difficult time handling checkboxes.
<FORM enctype="multipart/form-data" action="plsqlapp/write_info" method="POST">
<p>Author's Name:<INPUT type="text" name="who">
<p>Book Name:<INPUT type="text" name="book"><br>
<p>topic covered:<INPUT type="checkbox" name="rman"><br>
<p>topic covered:<INPUT type="checkbox" name="performance"><br>
<p>topic covered:<INPUT type="checkbox" name="network"><br>
<p>topic covered:<INPUT type="checkbox" name="replication"><br>

<p><INPUT type="submit">

Tom Kyte
April 02, 2004 - 9:58 am UTC

you would be getting an array.... (since you can have 0, 1, or in your case 4 values) and you would want to use the same name for all with different VALUES typically (else you have to define a variable for each -- you are missing a value to be sent when the checkbox is checked -- you need to add that regardless.

if you don't use an array -- but keep the names as you have them (add values!!! so you can tell when they are checked) your prototype could be:

write_info( who in varchar2, book in varchar2,
rman in varchar2 default null,
performance in varchar2 default null,
.... and so on ... );

strongly encourage you to prefix your parameters with P_ so as to distiguish them from COLUMNS in tables!!

How to get the usename from Basic Authentication

Sami, April 04, 2004 - 7:26 pm UTC

Dear Tom,

I did setup an Apache Basic Authentication for mod_plsql application and it is working fine. Now I have to display different page based on the username(like how metalink works).

How do I capture the username(who logged in) in my mod_plsql application?

It would be really helpful if you provide me code snippet/sample/hint.

Tom Kyte
April 05, 2004 - 9:35 am UTC

create procedure p
l_user varchar2(30) default USER;
htp.p( 'hello there ' || l_user );

passing checkbox value using array

Sami, April 07, 2004 - 2:47 pm UTC

Dear Tom,

Thanks for your previous reply.

I have large/unknown number of check boxes in page_A, how do I pass all dynamically generated "check box value" to page_B. You advised to use ARRAY but ...

how to get the check box value and store it in an ARRAY while I am in page_A?

The below example works fine with 4 checkboxes. Could you please explain me(code sample will be really greatful) how to user ARRAY to handle this unknown number fo checkboxes?

Looks like this kind of stuff is not documented anywhere(passing checkbox value using array).

htp.title( 'test');
htp.formOpen( curl => 'page_B', cmethod => 'POST', cattributes => ' enctype="multipart/form-data"');
htp.print( 'Author''s Name:' );
htp.formText( cname => 'p_who');
htp.print( 'Book Name:' );
htp.formText( cname => 'p_book');;
htp.print( 'check box 1:' );
htp.formCheckbox( cname => 'p_check1', cvalue => '1');;
htp.print( 'check box 2:' );
htp.formCheckbox( cname => 'p_check2', cvalue => '2');;
htp.print( 'check box 3:' );
htp.formCheckbox( cname => 'p_check3', cvalue => '3');;
htp.print( 'check box 4:' );
htp.formCheckbox( cname => 'p_check4', cvalue => '4');;
htp.formSubmit( );

htp.title( 'test');
htp.print( 'Who:'||p_who );;
htp.print( 'Book:'||p_book );;

htp.print( 'check box 1 status:'||p_check1 );;
htp.print( 'check box 2 status:'||p_check2 );;
htp.print( 'check box 3 status:'||p_check3 );;
htp.print( 'check box 4 status:'||p_check4 );;


Tom Kyte
April 08, 2004 - 9:43 am UTC

they all have the same name if you want to use an array -- don't name them differently as you are doing.

make them all be "P_check_box"

pass one variable, USE PACKAGES ONLY (packages are the only real way to code)

so your package spec in part would be:

create or replace package my_pkg
type array is table of varchar2(4000) index by binary_integer;
empty_array array;

procedure page_a;

p_book VARCHAR2,p_check1 IN VARCHAR2 DEFAULT '0',
p_check_box IN array default empty_array );

and the body in part:

p_book VARCHAR2,p_check1 IN VARCHAR2 DEFAULT '0',
p_check_box IN array default empty_array );
htp.title( 'test');
htp.print( 'Who:'||p_who );;
htp.print( 'Book:'||p_book );;

for i in 1 .. p_check_box.count
htp.p( 'you picked check box ' || p_check_box(i) );
end loop;


What brings the future for HTMLDB?

Jens, April 19, 2004 - 8:48 am UTC

I'm an old mod_plsql fan (I am working as a freelancer) and that's the reason that I love this new tool HTMLDB.

But we all know these J2EE-fanatics, they were only satisfied when they have a bundle of different servers and a complicated architecture and lot of open source stuff around them. And that's what the market wants!

Oracles strategy is not very clear here, because they were also strong in the J2EE-Market: They say, that you could use HTMLB in your department, it's so easy for your secretary to build your own app (it's not so simple, and this is good, because I need work ;-)), but do not use it for really big or critical apps. I don't like this argumentation. I think, it's (with mod_plsql) an alternative to the most J2EE apps.

Do you think, HTMLDB has the chance to survive? How many companies think about it? Should I invest my time to learn it, or should I go Oracle's J2EE-way?

I'm asking this question because I remember that old Oracle Tool "Power Objects" which was a good product against VB/Access 10 years ago. In those days it was the only alternative from Oracle for the big Forms and Reports solutions. I liked the product but after one or two years Oracle desupported it. It was the same with IFS WinUI (the reason I lost a customer!).

Tom Kyte
April 19, 2004 - 9:37 am UTC

I'm an HTMLDB fan(atic).

I know the team behind htmldb (been working with them for years).
I know the motivation behind htmldb

I don't think it'll disappear personally.

Replied on OTN as well...

Joel, April 19, 2004 - 9:55 am UTC


For what it's worth, I replied to your identical posting on OTN at:

</code> <code>

My thoughts

Meyer, April 19, 2004 - 12:41 pm UTC

I have been using HTMLDB since before it was in production and before it was called HTMLDB (I was a beta tester on project marvel).

I am glad I mastered the basics early. HTMLDB works great on large applications and HTMLDB works better then other alternatives for most web based projects. Everything needed for typical programs are included and where you need anything special it is simple to integrate.

like the wheel to early man, HTMLDB is a blessing to developers that makes life easier. It is a profound approach that works incredibly well.

Once you try can not live without it.

There is a small learning curve, and also a set-up-and-customize-for-my-company curve... but after you get it going... unless you like wasting time, you will
use it for your new applications.

Based on what it does and how well it does it, HTMLDB will be here to stay for a long time.

To the HTMLDB team, awesome job!!!

Meyer Tollen


A reader, April 19, 2004 - 2:26 pm UTC


OTN forum is down again

>>404 Not Found
>>Resource /forums/thread.jsp not found on this server


READER, May 12, 2004 - 8:07 am UTC

Can HTML DB replace IFS ?


Tom Kyte
May 12, 2004 - 6:36 pm UTC

no, htmldb is an application development environment (sure, has file upload/download to/from the database but that is not a filesystem)

ifs is a file system, versioning, checkin, checkout, ftp, smb, etc. totally different.

HTML DB for Oracle 9iR2?

A reader, June 13, 2004 - 8:01 pm UTC

Is HTML DB available for Oracle 9iR2? I know that Oracle sometimes ports newer features to older releases.

If not, why not? How is HTML DB implemented? As a bunch of PL/SQL packages or does it rely on some core kernel enhancements in 10g itself?


Tom Kyte
June 14, 2004 - 7:41 am UTC

it is available and supported for 9iR2 SE and up.

</code> <code>

Sell HTMLDB to Oracle!

Jens, July 16, 2004 - 10:42 am UTC

I'm coming from the OTN-forum. Five minutes ago it crashed when I want to give the answer of somebodys question. Not the first time! Why is it so unstable (Ok, it's not so slow than in earlier days))?

When I look at their URL, I found the reason: ".JSP" ;-))

Hey Tom, they should use HTMLDB! It's fast, it's stable, it's lean, it's cool!

Docs please

Gabriel, July 21, 2004 - 11:17 am UTC

Hello Tom,

I am trying to develop an application with Pl/SQL server pages but I am at kind of a loss as far as documentation goes. I have a book "Oracle web application programming for pl/sql developpers" by susan boardman which is good but I need more how to types of documentation not just reference books.
I downloaded oracle 9i application server mod_plsql users guide and pl/sql web toolkit reference but these are reference books with not to many examples. Are there any other books from oracle or some other web sites, or even on paper that show how to build an app with psp?
Any source would be welcomed.


Michael, August 20, 2004 - 11:32 am UTC

Just letting the User know that there will be book published on HTMLDB by TUSC release date November 2004.

HTML DB vs Oracle Discover

A reader, September 09, 2004 - 6:52 pm UTC

If I want to generate reports from data warehouse, what are the benefits of using html db or using discover?
Thank you.

Tom Kyte
September 09, 2004 - 7:18 pm UTC

well, htmldb is not an end user tool, discoverer is.

htmldb -- an application development environment you can use to build a web based application with any number of canned reports you want to build.

discoverer -- an end user tool to ad-hoc the database, visualize the data, interact with the data.

Can we download companion CD ?

Adrian, September 10, 2004 - 8:24 am UTC

In a response to Sami ( some way up this thread ), it started "yes, it is on the "extra stuff" (companion) cd with apache and htmldb."

Any idea where I can download this companion ? I have downloaded from OTN but can't find any mention of a companion. Guess this is how they reduced it to 1 CD now...


Tom Kyte
September 10, 2004 - 9:34 am UTC

</code> <code>

you ultimately get to a download page with stuff like:

Oracle Database 10g Release 1 (
Enterprise/Standard Edition for Linux x86

Download the Complete Files
ship.db.cpio.gz (611,574,473 bytes) (cksum - 306399691)

1. Run "gunzip <filename>" on all the files. Eg.: gunzip ship.db.cpio.gz
2. Extract the cpio archives with the command "cpio -idmv < <filename>"
Eg.: cpio -idmv < ship.db.cpio
3. Installation guides and general Oracle Database 10g documentation can be found here.

Get Oracle Software on CD
OTN TechTracks are a 1 year subscription to Oracle software on CD. You also get access to KnowledgeBase, an online resource of technical support information updated daily by Oracle's support organization. Go here to learn more about the tracks and start a subscription.

Oracle Database 10g Companion CD Release 1 ( for Linux x86
ship.ccd.cpio.gz (463,800,411 bytes) (cksum - 751240738)

Oracle Database 10g Client Release 1 ( for Linux x86
ship.client.cpio.gz (395,637,828 bytes) (cksum - 4182799520)

Oracle Cluster Ready Services Release 1 ( for Linux x86 (122,563,964 bytes) (cksum - 526731488)

Instant Client
Instant Client allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME. OCI, OCCI, ODBC, and JDBC applications work without modification, while using significantly less disk space than before. Even SQL*Plus can be used with Instant Client. No recompile, no hassle.

connecting to my database

P.Karthick, September 10, 2004 - 1:04 pm UTC

i have got a login for html db from the site

i have developed a web page.

i have a set of sql's. so now is it possible for me to estblish connection with my database and access the data in my tables.

or only i should create my tables in htmldb itself.

Tom Kyte
September 10, 2004 - 1:07 pm UTC

you would have to create a database link -- which isn't going to happen on that machine :)

normally, htmldb in "database1" can access data in "database2" using dblinks and views. However you will not be able to do that from this "shared" "for testing" machine on the internet.

So, to try htmldb on that machine -- you'd be creating the tables on that machine.


P.Karthick, September 10, 2004 - 1:15 pm UTC

then... there is an option create application with existing table.... what is that.....

will it work only if htmldb is on my machine....

Tom Kyte
September 10, 2004 - 1:30 pm UTC

htmldb can access any table

a) in your local database, the one htmldb is installed on
b) that a database link can be made to

on, we own the networking, it is in a DMZ, you cannot create a dblink from there to your machines.

Need to let user enter multiple values

robert, September 13, 2004 - 6:11 pm UTC

Hi Tom,
Per general Web/Plsql app - What is the beat way to
solicit multiple values ?

I need to let user enter multiple values, preferably w/o having them delimit the value.

Can a Textbox like this one I am typing into be used to do it so say the user types a value on each line :


and the whole thing be passed as an array like you do with
multi-select box, check box group ?


Tom Kyte
September 13, 2004 - 9:00 pm UTC

do you mean a textarea?


robert, September 14, 2004 - 9:42 am UTC

Yes, Tom I mean TEXTAREA.

Tom Kyte
September 14, 2004 - 11:10 am UTC

you would have to parse it, it is easy

something like:

n := instr( l_string, chr(10) );
exit when (nvl(n,0) = 0);

array(array.count+1) := substr( l_string, 1, n-1 );
l_string := substr(l_string,n+1);
end loop;

does that.

10g and Apache

robert, October 13, 2004 - 7:11 pm UTC

Does 10g EE come with Apache server ?

Tom Kyte
October 13, 2004 - 7:16 pm UTC

on the companion cd with html/db, yes. (so does SE)


A reader, October 22, 2004 - 10:20 pm UTC

Maybe this is better asked on the HTMLDB OTN forum but here goes

I downloaded the 10g Companion CD, used it to install HTMLDB on my database. Everything worked like a charm, just as documented. Very impressive.

I used the Tabular Form wizard to put a simple update interface on a table. Works fine.

1. That page with the Tabular Form, it is Page 3 along with all the other provided sample pages. How do I go about deploying this one page to my users? They need a URL, a id and a password?

2. How does the development cycle work using htmldb? Say I develop a application on a development database, how do I go about deploying this on Production? How does the build and release process go? Can I make a deploy package from devel and push that to Prod?

3. How about Configuration Management? How can I save, checkpoint the state of my htmldb application so that I can undo, create branches, etc? Typical things one would do when developing a GUI application?

Thanks for a great product!

Tom Kyte
October 23, 2004 - 9:40 am UTC

you are right -- better asked on the htmldb otn forum, I would just be asking one of those guys to come in and answer it here.... but

1) they need a url (just like you needed the url </code> <code>

whether they need a user/password is up to you -- you didn't need one here, but I could certainly make it so you did.

2) you export the application and import it using htmldb. it is builtin functionality and works very nicely.

3) for that -- i'll recommend you use the forum for an in depth answer.

A reader, October 23, 2004 - 3:03 pm UTC

"you export the application and import it using htmldb. it is builtin functionality and works very nicely"

How can this export/import be scripted so it can be automated?

Tom Kyte
October 23, 2004 - 4:50 pm UTC -> htmldb forum would be the best place on the planet to ask really in depth questions about htmldb. I can personally vouch for the veracity of the answers you'll get there as I know the people who answer them from the oracle side...

Oracle Consultant

Dawar, October 29, 2004 - 1:52 am UTC

OS: SuSE Linux Enterprise Server 8

Hello Tom,

I have Oracle running on SLES 8.
I installed HTML_DB on this server.

On the installation process I got error on configuration Assistants window as below.

Some of the configuration assistants failed. It is Strongly recommended that you retry configuration assistant at this time. Not successfully running any "Recommended assistant means your system will not be correctly configured.
Select the failed assistant and click the 'Retry' button to 'Retry' them.

I retry three four times and its start installing again.
I ran

In the end its says The of Oracle Database 10g companion Product was successful but some optional configuration assistant failed were cancelled or skipped.

Now I am not able to use URL.
Please note: I installed HTML_DB on different home.


My database is installed on



Tom Kyte
October 29, 2004 - 8:20 am UTC

installation, configuration => please use support.

I don't install every product. i don't use every OS. You don't have all of the information there (eg: which assistant -- NOT that i want you to supply it here, support will need that information). this is something best worked with support.

A reader, November 01, 2004 - 11:02 am UTC


I have a requirement in which i am supposed to setup a central server for generating audit reports for our different database servers and then those reports will be emailed as html format or attachment to different managers.

I am pretty impressed by HTML DB and i am thinking to implement it. Please let me know your opinion on this and if yes then do i need to buy seprate licenses for HTML DB like partitioning.


Tom Kyte
November 02, 2004 - 5:44 am UTC

html db is a feature of standard edition and up in 9ir2 and 10g.

if html db meets your needs, I'm all for it.

Oracle Consultant

Dawar, November 03, 2004 - 1:08 am UTC

OS: SuSE Linux Enterprise Server 8
Oracle DB:

Hello Tom,

I have two Oracle home on my sigle Linux box.
Is it possible to remove one oracle home?

For e.g:
I have two oracle home on single box.

My existing Database

I have installed HTML_DB on ccd home.


I'd like to remove ccd home.

I have deinstall ccd home product from HTML_DB companion cd but still >/opt/oracle/product/10.1.0/ccd and its child objects are exits.


Tom Kyte
November 03, 2004 - 7:04 am UTC

if you are not using any of the software under there -- sure, you might rename it or tar it up just to be sure you are "not using" anything under there (you'd need to shutdown all things oracle to be sure and restart them)

Oracle Consltant

Dawar, November 03, 2004 - 12:28 pm UTC

I am installing HTML_DB on the server box which is already runing DB.
OS is SuSE Linux Enterprise Server 8.

</code> <code>

Please see page 9 (Configuration Assitants Screen)
Starting HTTP Server has a problem.

here is the error in details section of the window.

-- default linked-in boot block used
XML parser init: error 201.
opmnctl: opmn start failed
Configuration assistant "Starting HTTP Server" failed


Oracle Consultant

Dawar, November 04, 2004 - 11:33 am UTC


My issue has been solved.

I need to unset ORA_NLS33 prior to installation.
Secondly I ran comapnion (HTML_DB) cd from the same session I unset NLS33.

And its work.

here is the link for installation HTML_DB.

</code> <code>


modplsql parameter : PlsqlSessionStateManagement

robert, November 06, 2004 - 5:21 pm UTC

OAS 10g (with OHS 1.3.28) on Linux

modplsql config in dads.conf :

when setting
PlsqlSessionStateManagement StatelessWithFastResetPackageState

the browser displays raw HTML. :(
What gives ?


<Location /discuss>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername discuss
PlsqlDatabasePassword discuss
PlsqlDatabaseConnectString ServiceNameFormat
PlsqlAuthenticationMode Basic
PlsqlDefaultPage discuss.discussionweb.home
PlsqlDocumentTablename discuss.documents
PlsqlDocumentPath docs
PlsqlSessionStateManagement StatelessWithFastResetPackageState
PlsqlErrorStyle DebugStyle

Tom Kyte
November 06, 2004 - 6:24 pm UTC

sounds like the mime type is getting munged up -- coming as text/plain instead of text/html

Please contact support for help on that one.

10g Database Web Service Provider

Jack Wells, November 19, 2004 - 2:02 am UTC

(To Sean Dillon),

You said above that, "In 10g, we are going to have the ability to publish and subscribe to web services from within the 10g database instance. No iAS... no HTML DB... no .NET... just the database".

I have 10g (and OHS) installed and am looking for the documentation on how to *publish* a webservice using only the database. Is it available yet?


Tom Kyte
November 19, 2004 - 10:24 am UTC

10gr2 is what he meant to say :)

release 2. (it was slated to be earlier but did not make it. time will tell if it is 10gr2 -- at OOW we'll know)

HTML DB on Personal Edition

Ahmad, November 21, 2004 - 1:49 am UTC

Hello Tom,

Recently, I have installed HTML DB on Oracle 10g Personal Edition and I really like this simple and great product. My understanding is that there is no licenses fee on HTML DB; does this apply to using it with Personal Edition? And is it supported by Oracle on the Personal Edition.


Tom Kyte
November 22, 2004 - 8:58 am UTC

Please see the response to your duplicate posting at:

</code> <code>

A reader, November 23, 2004 - 11:14 pm UTC

you caught me :)

Thanks Tom

Tom Kyte
November 24, 2004 - 6:56 am UTC

Actually, Joel Kallman caught you cause I simply asked him for comment and he said "deja vu all over again"

Web services in 9iR2

A reader, January 17, 2005 - 2:56 pm UTC

All the docs on OTN now refer to 10g, but we are not on 10g yet.

What is the easiest way to publish stored PL/SQL code as web service using Oracle 9iR2? Can you point me to the relevant docs?


Tom Kyte
January 17, 2005 - 5:50 pm UTC

I asked Sean Dillon, my XML/Web Services guy, to take a look at this and here's what he had to say:

Hi 'A reader',

There are a variety of ways to do this, obviously, but the easiest right now is probably via JDeveloper. I'm not sure if you're familiar w/ OTN, but if you go to: </code> <code>'ll find ALL KINDS of information related to Oracle's Web Services product support & strategy. If you look in the "Tutorials" section on the right hand side of that page, you'll see a link to "Creating and Using Web Services". Click on that, and it'll take you to a list of Web Services tutorials you can walk through. One option is "Creating and Using a PL/SQL Web Service", which will walk you through using Oracle JDeveloper to deploy PL/SQL code to a Web Service. You can subsequently deploy this Web Service to an application server, then use the deployed Web Service to create a Java stub which can then be used to test the Web Service. Alternatively, you can go to a predefined web page that's built in the Web Service that allows you to test it directly.

Hope that helps!

Sean Dillon

JDeveloper 10g

A reader, January 17, 2005 - 8:53 pm UTC

1. Can JDeveloper 10g be used against a 9iR2 database?

2. All the steps in that OBE seem too complicated! Isnt there a traditional CLI (command line) method for doing the same thing?

3. I dont really need UDDI to "discover" anything. I have a bunch of PL/SQL functions that given a input, produce a output , sort of like lookups.

I was hoping there was a simple, way to expose these functions using the Apache httpd server that comes with Oracle, sort of like how HTML DB does it? Why get into the whole iAS app server thing just for exposing a webservice?


Tom Kyte
January 30, 2005 - 6:15 pm UTC

I asked Sean Dillon, my XML/Web Services guy, to take a look at this and here's what he had to say:

Hi "a reader",

Yes, JDeveloper can connect to 9iR2 databases, no problem. As far as a command line interface to create the wrapper, yes and no. Basically, the steps you take in JDeveloper create a wrapper in Java that is deployed to a J2EE server. This wrapper is responsible for listening for a Web Services call, calling the PL/SQL interface, then returning the result.

From the sounds of what you're trying to do, I would probably create a controller PL/SQL function that was capable of returning different results based on the input to the web service. It could take all the various parameters you need to send in, along with the name of the database function you need to call. Then within this controlling function, you call the appropriate plsql code based on that parameter. The return value(s) would be relevent to the calling routine based on the procedure/function call passed in. You'd use JDev to wrap this controlling process, and call that from a web services client.

Now, all that said... It sounds like you'll struggle once you need to CALL this web service. I'm not sure how you plan on employing this code. If you're just trying to call a web-based procedure you could use the PL/SQL gateway (and HTP/HTF in the database) to return values on an HTTP request. You could also look into using DBUri's to get XML documents out of the database by calling into it with a URL. (There's a DBUri servlet deployed with the database that is able to look into the database and return XML data). This XML result set could be parsed by the calling routine and the values used accordingly.

Without more information on what you're trying to do, it's hard to make a solid recommendation.


To Sean

A reader, January 24, 2005 - 5:10 pm UTC

Tom, can you please ask SMD to respond to my questions above? Thanks a lot

Tom Kyte
January 24, 2005 - 5:19 pm UTC

jdev can be used against db2, anything really. yes.

but the above details how to deploy the service to the application server -- I talked to sean and he says that is the way he knows to do it.

HTMLDB users ...

Reader, February 02, 2005 - 8:35 pm UTC

I read here that Htmldb works in only one database .If I need to bring data from other tables then I use dblinks.
My concerns are ,
They will not show in my available tables ,I will have to write a sql to access them.(pl confirm)
The users which are created in htmldb are maintained in the repository .If I wish to grant DBA access to a user then how can I do this .I am asking this because to startwith I would like to have a simple( though looks an impossible task now ) application with tablespaces and datafiles .

I have started learning htmldb today so I apologize for my ignorance.


Tom Kyte
February 03, 2005 - 1:46 am UTC

create view other_table as select * from table@remote;

and the remote table appears as a local view.

why would you want to grant DBA (super powerful) to just anyone? rethink that.

user in htmldb..

Reader, February 03, 2005 - 6:31 am UTC

Thanks for your help .

1.Accessing a remote object is now easy.--Thanks again.

2.As mentioned earlier I wish to create a quick demo to understand the ease of using HTMLDB.I wish to have a page which will list all tablespaces and then the datafiles in them.

I am now able to get all the tablespaces from user_tablespaces but how do I get the data_files .

Also ,how can I click on tablespace_name and then know about the datafiles (something like href..)


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

see below...


Tyler D. Muth, February 03, 2005 - 9:17 am UTC

I searched for "data_files" here on asktom. The very first result has your answer. There are a bunch of How-To's on HTML DB's section of OTN at:
</code> <code>

Look in the How-To's under "Reports and Charts"


Connect to other database

Randy, February 22, 2005 - 1:35 pm UTC

I have been researching on HTMLDB of a month. I liked it and we are going to use it for report generation. But I cannt figure our how connect various databases from single instance of HTMLDB. I dont want to go for webServices yet, but just looking for acccessing various schemas of dirrent databases. So far I am managing by db link but I believe there must be a better way. I hate to install htmldb in every databases. Any thoughts ?


Tom Kyte
February 22, 2005 - 5:14 pm UTC

it would be best to install htmldb in the separate databases so you can avoid the overhead of a dblink (and a dblink in this case is an overhead you can avoid)

short of that you would be creating views (better than synonyms - imports the view metadata into the local instance) and using dblinks, but watch out, that'll be expensive.

htmldb is just a bunch of plsql (I long for the day when it too is just there like htp/htf are)

Connect to remote database

Randy, February 23, 2005 - 10:28 am UTC

I would expect htmldb to work at least like RMAN which is also nothing but bunch of sql but can connect to remote database while storing configuration info a local repository. Is it really difficult to use schemaname from remote database using tnsnames ?

Tom Kyte
February 24, 2005 - 4:39 am UTC

htmldb is installed and runs in a database

rman is a client program that lives not in a database.

so, if you put htmldb into database1, and the data in database2, htmldb would have to open a dblink from database1 to database2 in order to query any data. So, http-> mod_plsql/apache -> sqlnet to database1 -> sqlnet to database2

you can cut out the dblink by putting htmldb code (just some plsql) into database2.

Or you can use dblinks. You can hide the dblink with a synonym, you can hide the dblink with a view (i prefer views to hide dblinks).

But you will pay a price in runtime performance by doing so.


Randy, February 24, 2005 - 9:44 am UTC

One of the major disadvantage of using dblink with view is you may end up with ORA-22922 where you cannt create view on remote database if you have BLOB column. It's a big obstacle for us as we are dealing with BLOB columns in few major tables.


Phil, March 06, 2005 - 5:02 pm UTC

1.6 of HTML DB totally rocks! I have a question that is truely fundamental. I have a few years pl/sql experience and some app development/maintenance history. But the prospect of using HTMLDB to create a modest 30 page application is a little daunting. What should my approach be? I am thinking of designing the schema, then the interfaces using package(s) and only then the UI. It all seems a little disconnected and I was wondering if there is any tools (possibly workflow??) that could help.

Tom Kyte
March 06, 2005 - 10:40 pm UTC

Tyler here. Personally, I think your approach is great. I would use cvs or subversion to check-in versions of your files, but that's about all I would add.

Good luck,

views vs synonyms - small follow-up

Doug, March 07, 2005 - 2:19 am UTC

Why do you prefer hiding links with views instead of synonyms? Wish I was at hotsos.

Tom Kyte
March 07, 2005 - 8:02 am UTC

it imports the metadata into your local database. so a compilation of a procedure, a describe, many things don't have to hit the dblink.

You can compile a procedure dependent on a dblink that is hidden by a view even if you are not on the network - everything you need is there.

it speeds operations like compiling and makes it so that tools that query the data dictionary to find column names, etc -- work.

Tyler / Tom

Phil, March 07, 2005 - 3:53 am UTC

Thanks for the reply - it was Sunday, don't you guys ever stop? OK, so my approach is about right but I'm wondering from al the tools available and ones you've touched, which you consider would be a good choice to help with the design? Sure, I could use pen and paper for the schema but this is always fine at first and then takes increasing amount of time to redesign and modify. I've used MS Word before to manage listings of functions etc but there must be some toolset out there to help with this that I have not explored?

Tom Kyte
March 07, 2005 - 8:15 am UTC

Well, a 30 page application is not really that big - that is the size of this particular application.

To develop it -- the only tools we used were htmldb, and an editor. I worked out the API (i'm a back room developer, I do database - not so good at the gui). I knew what API's we needed -- save_question, update_question, create_review, search_questions, etc. The GUI guy laid out the flow on a whiteboard. I developed the pretty small schema (did not need a tool beyond a whiteboard, it was way tool small but any small ER tool would do).

He built the shell of the application while I built the API, as API's became available - he would hook them in (for example, the entire 'gather a questions information experience' could be coded way before 'save question' was written.

Took about 3 days to get it going for real and hasn't changed much since.

Best tool = Knowledge (and Firefox (and RSS) )

Tyler D. Muth, March 07, 2005 - 10:03 am UTC

Tyler here,

I would grab the WebDeveloper Toolbar (extension) for Firefox. It's a huge help in UI. The CSS menu allows you to point to any element on the page and see what classes and inline styles are determining its appearance.

It also has an "Outline" menu that allows you to outline block elements such as table cells, divs, spans which is what HTML DB pages you for page and form field layout.

There's also the Venkman JavaScript debugger which is the single most valuable tool for developing JavaScript functions in any web app.

The ColorZilla extension gives you an eye-dropper color picker so you can figure out the hex values of colors on a page (great for copying another site).

One of the newest extensions I've added to my list is "MeasureIT" which allows you to draw a width-height pixel ruler right on a page. Great for when you need to figure out how wide a column is or how wide you want it to be.

</code> <code>has a great tool for validating HTML. Nothing will consume more time in web UI design than an HTML error which throws off the look of your site. It's the VERY first step I take whenever looking at the UI of a site.

I'm a big fan of UltraEdit for a text editor. It's highly configurable and you can download the word file for PL/SQL such that it does syntax highlighting and lists functions and procedures.

When I design a site, I start with one component, design whatever tables are necessaary, then design the API's for them, then the UI. So, if I were designing AskTom, I would start with the form to enter questions and get the questions table, API then the HTML DB form done so I have some way to enter data. Then move on to the search screen, etc.

Honestly the most important tool is your brain. If you've read Tom's "Effective Oracle by Design", you know that one of the key concepts is to design for performance from the beginning. Think about how the data will be accessed. Do you have an insert / update intensive app or is it more focused on queries (like asktom). In the case of this site, it's mainly queries, so the insert API is a little more expensive in that it does some formatting of the text, but you pay the price for that only on insert, not every time a row is queried. If you are doing a HUGE number of inserts, maybe you use a heap table with only a few (or one) indexes. If it's query intensive, maybe you use an IOT and a number of indexes on the table. Do you need to display a lot of aggregates? Perhaps some materialized views are the way to go.

The point is, it's much easier to think about these things ahead of time and design your data model and UI around them than to do it after the fact. If you need to change your whole table structure to achieve your performance goals (you do have performance goals, right?), you'll end up changing every HTML DB page you wrote. This is very time consuming and annoying, not to mention prone to errors. Work with real datasets (1,000,000 rows not 10) in design and testing.

The same goes for UI. The best sites using the coolest CSS and DHTML (,, etc.) aren't built using some tool, they're built by smart people who take the time to immerse themselves in the technology. The HTML generated by most, if not all gui tools is bloated and difficult to read.

Actually, one of the greatest tools I've found is an RSS reader. You subscribe to the RSS feeds from asktom, Sergio's blog on HTML DB,,,, etc. and constantly improve the most important tool, your mind.

Good luck,

Display images in HTML DB

Tanmoy Choudhury, March 15, 2005 - 7:33 am UTC

HTML DB Version - 1.3 (i am not sure) don't have access right now to check the same but it was the age old html db version

Database Version -
OS - Win NT Server (SP-2)

I was trying to develop an application in HTMLDB where based upon the user choice program will show up the images.

i have 3 tabs , Home /Display and About.

In Home page user will select their favourite comics (say) Asterix and Cleopatra. Now as soon as user clicks go button application will move to display page where 1st page of the comics will be shown. It will also have next and previous option based upon the user selection.

Right now whenver user selects and submit their request the system displaying the requested page. But the problem here is i am not getting the standard header and footer of the page. The images taking up the whole screen and even i am not getting next and previous button too.

The images has been saved as a BLOB in database.

I think their is some problem from the HTML DB point of view where i might have to clarified my HTML region settings.

I need your advice on this. If this is not the proper place please let me know i will add a new question. I have asked the similar things in HTML DB forum but havnt got any reply.


Tom Kyte
March 15, 2005 - 8:28 am UTC has a good discussion forum for html db related stuff.

Aready submitted the same in otn..still no reply .. :-(

Tanmoy Choudhury, March 15, 2005 - 11:42 pm UTC

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

well, march 15th was just a single day after all. give it a second or two.

Dynamic queries in HTMLDB

VA, March 16, 2005 - 4:10 pm UTC

I need to write user-input driven dynamic queries in a HTMLDB report region. Suppose the SQL has 3 inputs

Is it more efficient to write queries of the form

select ...
from ...
where 1=1
and <some mandatory predicate>
and (:ITEM1='foo' or nvl(:ITEM1,'%null%')='%null%')
and (:ITEM2='bar' or nvl(:ITEM2,'%null%')='%null%')
and (:ITEM2='xyz' or nvl(:ITEM3,'%null%')='%null%')

or use a anon PL/SQL block returning a SELECT statement with only the predicates the user selected?

Does the CBO optimize them differently?


Tom Kyte
March 17, 2005 - 8:05 am UTC

Here is my query block for the "advanced search" region on this site.

Note that I see which inputs are "not null" and bind in those values. This results in more than one query in the shared pool for this single page -- but not too many of them. Each query is optimized for the predicates involved. Each set of input predicates will be optimized as well as it can be:

l_query long := NULL;
trim( wwc_ask_api.parse_search_string(v('P70_PAGE_CONTAINS')));

l_query := 'select rownum rownumber, x.* from ( select /*+ FIRST_ROWS */ ';

l_query := l_query || ' htf.anchor(''f?p=4950:8:''||
:FLOW_SESSION ||''::'' ||
:DEBUG || ''::F4950_P8_DISPLAYID:''||
i.displayid ||'','' || wwc_ask_api.urlencode(
htf.escape_sc( substr( q.subject, 1, 70 ) ||
decode( sign(70-length(q.subject)), -1, ''...'', null )),
null, '' title="QUESTION: ''||
||''...''||chr(13)||chr(13)||''ANSWER: ''||
) thelink, ';

if :P71_X_MARKS_THE_SPOT is not null then
l_query := l_query || ' ''Score('' || score(99) || '') '' || ';
end if;

l_query := l_query || ' to_char(q.status_ts, ''DD Mon YYYY fmhham'') thedate,
to_char(q.timestamp, ''DD Mon YYYY fmhham'' ) submitted,
case when sysdate-timestamp < 1/24
then round(24*60*(sysdate-timestamp)) || '' minutes old''
when sysdate-timestamp < 1
then round(24*(sysdate-timestamp)) || '' hours old''
when sysdate-timestamp < 14
then trunc(sysdate-timestamp) || '' days old''
when sysdate-timestamp < 60
then trunc((sysdate-timestamp)/7) || '' weeks old''
when sysdate-timestamp < 365
then round(months_between(sysdate,timestamp)) || '' months old''
else round(months_between(sysdate,timestamp)/12,1) || '' years old''
end age ';
l_query := l_query || ' from wwc_ask_submitted_questions$ q,
wwc_ask_indexed_questions$ i
where i.displayid = q.displayid ';
if :P71_X_MARKS_THE_SPOT is not null then
l_query := l_query || ' and contains(i.text, :P71_X_MARKS_THE_SPOT, 99) > 0 ';
end if;

if ( :p70_email_equals is NOT NULL ) then
l_query := l_query || ' and = lower(:p70_email_equals) ';
end if;
if ( :p70_last_updated_1 is not null ) then
l_query := l_query ||
' and q.STATUS_TS >= to_date(:p70_last_updated_1,''dd-mon-yyyy'') ';
end if;
if ( :p70_last_updated_2 is not null ) then
l_query := l_query ||
' and q.STATUS_TS <= to_date(:p70_last_updated_2,''dd-mon-yyyy'') ';
end if;
if ( :p70_subject_like is not null ) then
l_query := l_query ||
' and upper(q.subject) like upper(:p70_subject_like) ';
end if;
if ( :p70_version_like is not null ) then
l_query := l_query ||
' and upper(q.version) like upper(:p70_version_like) ';
end if;
if ( :p70_timestamp_1 is not null ) then
l_query := l_query ||
' and q.timestamp >= to_date(:p70_timestamp_1,''dd-mon-yyyy'') ';
end if;
if ( :p70_timestamp_2 is not null ) then
l_query := l_query ||
' and q.timestamp <= to_date(:p70_timestamp_2,''dd-mon-yyyy'') ';
end if;

if ( nvl(:p70_order_by,'NONE') = 'NONE' or (:p70_order_by = 'SCORE' ) )
if :P71_X_MARKS_THE_SPOT is not null then
l_query := l_query || ' order by score(99) desc ';
l_query := l_query || ' order by q.status_ts desc ';
end if;
if ( :p70_order_by = 'LAST_UPDATED' ) then
l_query := l_query || ' order by q.status_ts desc ';
elsif ( :p70_order_by = 'SUBJECT' ) then
l_query := l_query || ' order by q.subject ';
elsif ( :p70_order_by = 'TIMESTAMP' ) then
l_query := l_query || ' order by q.timestamp desc ';
elsif ( :p70_order_by = 'VERSION' ) then
l_query := l_query || ' order by q.version ';
l_query := l_query || ' order by q.status_ts desc ';
end if;
end if;

return l_query || ' ) x ';


VA, March 17, 2005 - 8:59 am UTC

Thanks, thats what I thought too. But someone suggested that I do something like default all input parameters to "%" and then use predicates like

where 1=1
and col1 like :col1
and col2 like :col2

This way, if the user doesnt specify any search criteria, it will go in as

and col1 like '%'

which is like effectively leaving out that predicate.

What do you think about this approach? Is a predicate like "col1 like '%'" optimized the same as "1=1" i.e. effectively ignored by the CBO?


Tom Kyte
March 17, 2005 - 9:33 am UTC

ouch, that hurts doesn't it.

it is effectively always HAVING a like predicate -- all of the time, every time. A single plan, not the best plan for the set of inputs.

it would not be "col1 like '%'"
it would be "col1 like :x"

(and it would be as if "col1 IS NOT NULL", not 1=1...)

and you DON'T WANT IT ignored by the cbo, you want the cbo to use a predicate to pick an access path when it can.

Not effectively the same

David Rydzewski, March 17, 2005 - 9:32 am UTC

col1 like '%' does not satisfy null entries in col1 so it is not the same as leaving the predicate out.


VA, March 17, 2005 - 11:22 am UTC

Tom, just curious, do you actually put that query block in the HTMLDB region source or do you put in a function/package and just use

return pkg.fn(p1,p2,p3);

as the HTMLDB region source?

I prefer putting it in a package because I can use my favorite IDE to write the code instead of typing it in the HTMLDB textarea widget. Comments?


Tom Kyte
March 17, 2005 - 11:37 am UTC

Tyler here. It's coded into the query itself. Writing it as a function would context switch to PL/SQL for every row returned and could significantly impact performance. Whenever possible, don't call out to functions in queries. The case statement and in 10g, regular expressions, make this possible across a much broader spectrum of situations.

As the next post indicated, if you are running HTML DB, the function is built-in. Technically, HTML DB fetches your rows into an array, then loops over the rows that will be displayed (10 in this case) and applies the template and formatting info to your row. Hence, it's still very fast. Another advantage is that column heading sorting works on the date itself and NOT the string "x minutes/hours ago".


SINCE format

A reader, March 17, 2005 - 11:27 am UTC

Instead of doing all that stuff with the date, you can use the new HTMLDB 1.6 SINCE date format feature

</code> <code>

Package vs. Inline code

David Rydzewski, March 17, 2005 - 12:43 pm UTC

Tyler -

I didn't understand your explanation. I believe the original question was about including the code inline for a "function returning a sql query region" vs. writing the code in the database and having the region code just be "return pkg.fn(:1,:2);".

I don't believe this would call out to the function per row...the function is returning the query text that will be executed, unless I read the question wrong.


To Tyler

A reader, March 17, 2005 - 12:46 pm UTC

"Writing it as a function would context switch to PL/SQL for every row returned"

Tyler, not sure I understand. If I specify my HTMLDB region type as "SQL Query (Pl/Sql Function Body Returning Sql Query)" , it shouldnt make any difference if I write the


right in the HTMLDB textarea widget or if I write the code in a package and do

return pkg.fn(p1,p2,p3);


In both cases, HTMLDB just gets the query when it renders the region and executes it.

Why do you say it would involve a context switch for each row?

[Are you confusing my question with the situation where I have say a PL/SQL function called in a SQL query?]


Tom Kyte
March 17, 2005 - 1:10 pm UTC

Tyler again. Sorry, I mis-read the thread. I read the one about "Since" then scrolled up 1 and thought it was the same topic.

Yes, you can put your code for a Function Body Returning SQL Query in a package. Honestly, I typically copy the text into my favorite text editor. The disadvantage I see to the package solution is that it's one more object to move with your application. Also, there is metadata stored for your the query attributes (columns, formatting, etc.). This is updated when you make changes to the query THROUGH HTML DB. So, add a column, click save, the metadata now contains info about the new column. Now think about the package solution. If you change the package through SQL Plus, what would trigger HTML DB to update the metadata? Nothing.

Before you go very far down this path, I would create a test function that returns 'select ename,sal from emp'. Then create your region and call this function. Then test the page. Now, change the function so it returns 'select email,sal,deptno from emp' then re-run the page WITHOUT editing the region. I'm honestly curious as to what it will do.

On a somewhat related note, if you haven't seen the "Database Object Dependancy" report, you are missing a great new feature of 1.6. To get there, click:
Application Builder > View Application Reports (on right) > Database Object Dependencies. It takes a bit to build the list (dependant on the size of your app and the objects in your schema), but it's worth the wait. I can't take a bit of credit for writing it, but it's very useful.


Good info

David Rydzewski, March 17, 2005 - 1:30 pm UTC

To Tyler

A reader, March 17, 2005 - 2:29 pm UTC

"Now, change the function so it returns 'select
email,sal,deptno from emp' then re-run the page WITHOUT editing the region. I'm honestly curious as to what it will do"

The report picked up the new column just fine!

To your point about the metadata and stuff, yes, I kind of agree. If I modify my function to add/remove a column, I would need to go back to HTMLDB and make sure the column aliases C01..C60 are still "in sync" with what the function returns!

Its a personal preference at this point, I guess. Thanks.

Tom Kyte
March 17, 2005 - 3:44 pm UTC

Thanks for the reply, I was curious about that. Agreed, it makes little difference how you do it.


Parsing textarea

A reader, April 06, 2005 - 3:59 pm UTC

n := instr( l_string, chr(10) );
exit when (nvl(n,0) = 0);

array(array.count+1) := substr( l_string, 1, n-1 );
l_string := substr(l_string,n+1);
end loop;

How can I modify the snippet above to take into account

1. Last line might not have a chr(10)
2. Each line might have multiple "items" separated by comma or space. I want to capture all of them in the array


Tom Kyte
April 06, 2005 - 6:53 pm UTC

simple coding exercise?

one approach to #1 is to make sure the string DOES end in chr(10) (that is what I normally do)

for number 2, sounds like once you get a line, you parse the replace of that line's comma's with whitespace -- around the whitespace.

Parsing textarea

A reader, April 06, 2005 - 4:27 pm UTC

OK I figured this out. Thanks

n integer;
l_string varchar2(4000) := translate(orig_string,chr(10) || chr(13) || chr(32) || ',' , '@@@@');
l_element varchar2(100);
if (l_string not like '%@')
l_string := l_string||'@';
end if;
n := instr( l_string, '@' );
exit when (nvl(n,0) = 0);
l_element := substr( l_string, 1, n-1 );
if (trim(l_element) is not null)
array(array.count+1) := l_element;
end if;
l_string := substr(l_string,n+1);
end loop;

Tom Kyte
April 06, 2005 - 9:21 pm UTC

"A reader"? Vikas, people may find your code useful, you should at least take credit for it ;)


"Your Questions"

reader, April 26, 2005 - 10:31 am UTC


Can you explain how the "Your Questions" page is set up to conditionally display the results based on the submission of your email address?

Is the conditional display based on the request value being set or something the session state? I am trying to recreate something similar to this and struggling.

I notice the URL after submit is: </code> <code>

so you are not really branching to a new page in the app after submit? What's going on?

Tom Kyte
April 26, 2005 - 10:39 am UTC

Tyler here. When you press go, it submits the page and sets the value of an item (P60_SOME_EMAIL) and branches back to the same page. There is a second region that is conditionally displayed when that item is not null. The source of that region is simply a query (select * from questions where email = :P60_SOME_MAIL).

Hope that makes sense,

HTMLDB wizard & primary key

Ronald Chan, May 02, 2005 - 2:31 pm UTC

Dear Tom and Tyler,

I have just upgrade my HTMLDB installation from 1.5 to 1.6 whoo waa, the interface changed a lot!

in 1.5 when I used the wizard to generate an application base on an existing table, It gave me the chance to simply enter what ever for the primary key with a text area where I can then customize it to a drop down, etc. later.


But int 1.6 I have to give it a sequence or function in order the go throught the wizard, just wonder if you can kindly show to me how to do this in a way like 1.5 (the version 1.5 way)?

I have noted if I have this table as a synonym or from a db link, the wizard will not work at all, especially with a table using db link, I still cannot "work around" it.


Could you please also tell me if there is a way to access table using db link (using the standard pages like those generated using the wizard on local tables) ?

Tom Kyte
May 02, 2005 - 7:55 pm UTC

suggest you use the htmldb forum on -- the experts on that are over there and they respond.

"really really really big" apps

Eric, June 13, 2005 - 10:32 pm UTC

Tom, in a follow up you said:
>> firmly believe anything "really really really big" should be "bought", not "built" (eg: HR, finapps, crm, etc etc etc)<<

Do you think HTML DB would be suitable for the vendor that is developing those types of "really really really big" apps?

Tom Kyte
June 14, 2005 - 9:46 am UTC

probably not, html db can build applications that scale to huge communities of end users.

what HTML DB would have issues scaling to is a huge development staff. To write an HR application, you would probably have 10's to 100's of developers. A massive implementation. You need a serious "framework" (not a java framework framework -- but a development framework) in order to accomplish anything. You need configuration managment, source code control, check in check out, excellent versioning, unit testing, etc etc etc -- and that should all be second nature in the development tool.

html db isn't that particular tool.

So, html db - can build that registration application that 1,000,000 people use. But it would be hard to build that HR application built by 150 developers simultaneously.

But it would be a great tool to add customizations to the developed application in the field (adding a feature here or there by relatively novice programmers/end users)

"html db isn't that particular tool"

Tony Andrews, June 15, 2005 - 7:53 am UTC

Do you think there is any chance that HTMLDB will develop in that direction, though? It is such a great way to develop applications, I would love to be able to use it in a large-scale development team.

Tom Kyte
June 15, 2005 - 9:58 am UTC

I don't see it happening in the next release or two, no. It would take a lot to get all of the infrastructure in place you would want to have for 100 developers to all work on the same thing.

regarding Export in htmldb

crimsonmintz, July 13, 2005 - 9:06 am UTC

HI tOM...this thread was --as always...very good.

I have a Q -- I know the way to export Applications using the HTMLDB interface. Is it possible to do the same using some sort of a procedure/fucntion/cron job wherein all the Applications are automatically extracted(given their ids)?

Your knowledge is solicited.

Tom Kyte
July 13, 2005 - 12:52 pm UTC

I'll recommend the excellent htmldb forum on for this, I know the people who monitor it, they are top notch.

HTML DB studio

VA, July 13, 2005 - 1:03 pm UTC

There is a Oracle HTML DB Command-line Export Utility available on the HTML DB studio at

</code> <code>
under the Utilities section

that might do what you want

A reader, August 04, 2005 - 12:40 am UTC


Does Html DB needs Oracle txt to be installed before running the htmldins.sql?


Tom Kyte
August 04, 2005 - 9:13 am UTC -> forums, the HTMLDB forum would be the best place to get answers....

When is HTMLDB 2.0 expected ?

reader, August 20, 2005 - 12:06 am UTC

I would appreciate if you could tell us about the release date of HTMLDB2.0 and Release date of oracle 10gR2 on solaris .

Thanks a lot !

Tom Kyte
August 20, 2005 - 5:06 pm UTC

10gr2 - end of august/september 2005.

I'd post in the htmldb forum on otn for the other bit, none of the htmldb guys were online right when I read this.

Suppressing the URL Links

Venkat, September 19, 2005 - 6:16 am UTC


When i click a URL/button, I am calling a proc/fuction. Now How do i suppress the diaply of the the actual parameters that are being passed to the function, along with the function/procedure name.

what i mean is ....the link in the address bar displays the procs that r being it possible to suppress the display the names of these procs?


Tom Kyte
September 19, 2005 - 11:44 am UTC

use POST for the button (the button is in a form), that'll hide the parameters from easy view (not from view, for they are in the form, i can still see them with view/source)

the function name is going to be there, but you could use frames to hide stuff too. the url bar would have the name of the page with the frameset, the frames urls would not be visible.

HTML DB 2.0 Security

Tyler D. Muth, September 19, 2005 - 3:30 pm UTC

Hiding the parameters is really "security through obscurity". People can still save the page locally, chage the vaules of hidden form fields, and submit the form. In fact with the "Grease Monkey" extension to Mozilla FireFox, it's even easier.

With the latest version of HTML DB, version 2.0, there are new security features built-in, such that people CANNOT change the parameters that are passed via URL. Essentially, it creates a checksum with a proprietary algorithm, then validates the parameters using the same checksum on the next page.

Tom Kyte
September 19, 2005 - 4:56 pm UTC

I just learned about that in Raj's presentation this morning at OOW ;)

OOW Presentation

A reader, September 22, 2005 - 7:57 pm UTC

Dear Tom,

Sorry for asking non-technical question.

Could you please tell us where can we download your Oracle Open World-2005 presentation? I went to
</code>, <code>but I could not see any of the presentation download page.

Could you please help us here?

Tom Kyte
September 22, 2005 - 9:54 pm UTC

I'll have the latest greatest on the files tab next week (we modified the slides a bit and I need to fix the whitepaper)..

</code> <code>

has a link to Mark Rittmans blog that has the exact url to the otn content - but it'll be superceded soon.


mo, October 05, 2005 - 10:15 pm UTC


Is htmldb a IDE or gui for creating web screens like Dreamweaver.

We have a large infrastructure of oracle web apps using mod_plsql and using htp.p calls. We are looking for a product that can make development easier and to separate the presentation from business logic using oracle ias.

woudl html db help here. Does it create procedures in the database.

Tom Kyte
October 06, 2005 - 7:37 am UTC

htmldb would definitely help - it creates metadata in the database (information used to render a screen at runtime).

htmldb is an IDE that is a GUI you use to describe how your application flows, it then renders the application at runtime using your description.

goto and try it out.


mo, October 06, 2005 - 8:12 am UTC


I requested a workspace to try it out.

1. Is there a tutorial or instructions on how to build a simple app to see how it works in app development?

2. Can you list how it is different and better than mod_plsql?

3. Would the code be at the 9IAS app server or database? Do you have access to app server functionality available.

Thank you

Tom Kyte
October 06, 2005 - 8:18 am UTC

1) see the boxes on the right hand side.

2) it uses mod_plsql, it is not better than, it is not comparable to. It is a development tool that uses mod_plsql.

3) it is all in plsql. you use apache and mod_plsql. You can use any app server functionality you want (whatever that means?) that makes sense. EG: it would not make sense to use OC4J - an app server feature, since you are not writing java in this case)


mo, October 06, 2005 - 9:27 am UTC


I saw the sample application and it really looks awesome. you can create pages in minutes that would take days in dreamweaver/pl/sql procedure. However:

1. Does it provide session mangement and security at the application server meaning would i be able to create app server account for each user and give them access to certain URLs/pages like J2EE app.

2. Can i start using this to build pages in 8i or 9i database?

3. When was this product released?

4. Would I be able to change the layout of the screen like putting a field to the right of another or I have to do this manually.

5. Are all pages stored in a package in the database?

6. Is it free?

Tom Kyte
October 06, 2005 - 11:53 am UTC

1) session management - yes, absolutely, one of the cornerstones.

You have full access to secure anything you want, j2ee buys you "nothing" really special in securing "a page".

2) 9ir2 and above.

3) with 10g r1, but you've been using it for years (see asktom, it is htmldb generated)

4) you do it in the environment

5) no, like I said, it stores METADATA in the database and at runtime htmldb reads that metadata and draws a page.

6) it is a feature of standard edition and above, it comes with the database, if you have Oracle, you have this.

A reader, October 06, 2005 - 9:51 am UTC

Tom, how can I know if htmldb is installed in my database? In 10g is it part of the EE or is it still a separate product in companion CD?

Tom Kyte
October 06, 2005 - 11:54 am UTC

it is on the companion cd and installed separately.


mo, October 06, 2005 - 4:22 pm UTC


1. We have several 8i applications. Can I use HTML DB to create any pages? Do I have to upgrade to 9iR2?

2. can I use it to update/reformat existing pl/sql htp.p pages?

3. What do you mean by metadata?

4. Are you saying I can only manually edit the code using the tool itself and not in TOAD or SQL Navigator or textpad.

5. I see that the thread started in 2003. does that mean it was availale for developers all this time or it was only released with 10g (i year old).

Tom Kyte
October 06, 2005 - 6:29 pm UTC

1) it is for 9ir2 and above only.

2) you would be reimplementing, you can reuse some of your code, try it out - you'll see how it works.

3) data about data - metadata. It doesn't generate a procedure to draw a screen, it inserts rows into a table and at runtime htmldb uses this metadata to figure out what to do.

4) there isn't really "code" - it is a declarative environment, it is like "forms"

5) it was released with 10gr1 but supports 9ir2.

I've been using it for years however, before it was released as a product.


mo, October 06, 2005 - 9:53 pm UTC


1. Would I be able to create new pages in HTML DB and merge/call it in an existing application of pl/sql procedures.

or it is only for new app development meaning that whole application have to be created using the tool.

2. If the answer is no, can i copy the source from "view source" and convert to pl/sql procedure.


Tom Kyte
October 07, 2005 - 8:01 am UTC

1) doesn't make sense.

you can reused your data logic from the old code, assuming you separated it out.

htmldb puts "lipstick on the pig" as the saying goes. It does the interface, navigation, state management.

You do the complex data logic (or use it's built in wizards to whip out something simple, fast)

Alternatives to HTMLDB for large project teams?

Tony Andrews, October 07, 2005 - 9:38 am UTC

Tom, you said earlier in this thread:
> So, html db - can build that registration application that 1,000,000 people use.
> But it would be hard to build that HR application built by 150 developers simultaneously.

We have a situation where we have a team of about 50 developers working on a commercial product based on Oracle Forms, with most of the business logic in PL/SQL packages. There is now a desire to consider alternatives to Oracle Forms as the client.

Since the client-side code is quite simple, HTML DB would work extremely well (we have prototyped it), but the let-down is the code management issues with a team of this size.

Assuming we don't stay with Forms, do we really need to go to J2EE (perhaps using ADF) - given that we currently have zero Java skills? Or as there some other, easier alternative we should consider?

Your thoughts, or those of any other readers, would be welcome!

Tom Kyte
October 07, 2005 - 8:20 pm UTC

I'm not sure the htmldb environment would scale up to 50 concurrent developers *on the same application* - it would be "tricky"

you would have to break that app into say 20 or 30 "small apps" and subdivide it out.

Or, go with a "bigger tool". there are 'easy' java approaches too (just use jsp's and it can be fairly "small" in learning curve)

I'm sure that other readers will inject their 2cents (thats what this is all about)


mo, October 07, 2005 - 10:05 pm UTC


1. Can you use JSP (Presentation layer) and PL/SQL for business functions without using Java. I doubt it?

2. In your opinion what is the easiest J2EE configuration? e.g. JSP/Servlets, JSP/EJBs, JSP/Struts , JSP/Strusts/plsql, etc.

3. I did understand your answer about creating some pages with HTML DB and then merging it into an existing pl/sql application. Basically I like some of the templates with tabs and I want to build some maintenance pages with it. However my app has about 100 links. I cant recreate everything. Can I create 5 - 10 pages in HTML DB and incorporate them in my existing app by a simple URL call.

THank you

Tom Kyte
October 08, 2005 - 8:51 am UTC

1) the j in jsp sort of stands for "java". so yes, in order to do a JAVA server page, you need, well, java

2) htmldb ;) you should have seen that coming... beyond that - probably jsp/servlets with lots of stored procedures.

3) yes you can - but I think you'll find that creating 100 pages in htmldb is pretty fast and then you have a consistent interface to everything.


mo, October 08, 2005 - 1:00 pm UTC


1. It seems HTML DB will eliminate many pl/sql web developer positions as coding will not be required. Companies can figure out how to use the tool and there is no need to hire programmers. Is this true?

2. It sounds HTML DB end product is like Oracle Form Builder. You create a form based on a template, place buttons/objects and write a script attached to the buttons and the form generator will create the code for the form but you can't edit the code. Is this correct?

3. It seems Oracle Forms was not successful in Web Apps. I dont see a lot of companies adopting it. Is it because it creates JAVA screens and not HTML screens? os is it because it is slow in terms of keeping a connection between the app server and database server?

THank you,

Tom Kyte
October 08, 2005 - 2:15 pm UTC

1) that is not true, some coding is always going to be there - it quite simply makes you much more productive and (best to me) makes you produce "consistent" applications - applications that behave and look alike.

2) you can ADD your own code, but no, you do not "edit" htmldb code since there isn't any -- it is data.

3) that would "not be true", I get tons of questions about it - our apps use it, many people use it. But it builds heavy weight, totally connected client/server/server applications - it is not html, it is like a client server application.


mo, November 01, 2005 - 3:16 pm UTC


1. Can you use the oracle workspace to connect HTML DB to an existing remote 8i or 9i databases using Service Name?

2. If not, do I need to install the HTML DB on my pc to do it?

Tom Kyte
November 02, 2005 - 4:54 am UTC

1) no, you would use a dblink.
2) that would be best, dblinks would not be the "fastest" way.

You might be interested in XE
</code> <code>


sam, November 03, 2005 - 10:08 pm UTC


1. I created a form page based on "DEPT" table. When I tried to save a record I got "ORA-01400: cannot insert null into DEPTNO". DO I have to create and use a sequence or there is a bug somewhere.

2. It does not seem I can create some pages with HTML DB and merge or call it from my other pl/sql pages. I looked at the source and it is not purely HTML. Copying the code under View source and pasting it in another page does not retrive the same result.

How can I create a page and call it via a URL from my other application?


Tom Kyte
November 04, 2005 - 3:34 am UTC

</code> <code>

would be the best source for htmldb information....

what is a "FRAMWORK"

A reader, November 08, 2005 - 10:01 am UTC


What is HTML DB? It is a Framework but I dont understand what is a "FRAMEWORK". Is it same as an API? Can someone explain?


Tom Kyte
November 08, 2005 - 10:16 pm UTC

A framework is a "fast way to get from here to there", a prescribed method of doing stuff.

typically includes api's, utilities, other things.

It means the same thing as framework does for anything else. "A methodolgy, a way of doing something - with the tools to do it"

html db is a browser based application development to. </code> <code>lets you try it out without installing anything.


Seshadri, November 18, 2005 - 4:10 am UTC

Dear Tom,

I had a requirement to store and retrieve documents with extensions like doc, bmp, xls, Pdf etc.

I just Wanted to know If we can do it through HTML DB. The idea is to open the file and show first few pages to the user.

Thanks in Advance


Tom Kyte
November 18, 2005 - 2:56 pm UTC

html db to store and retrieve - absolutely.

to show the first few pages - no, adobe would do that, or word would do that.


Muhammad Ibrahim, November 21, 2005 - 4:13 am UTC

Dear Tom,

I have few questions regarding HTMLDB :

1) HTMLDB is same as FORMS with 9ias? Will it replace FORMS
in future?
2) Can we use HTMLDB for applications like Insurance,
Banking etc with min number of developers (may be
8 or 10).

May i know what is the reason your reply to Tony Andrews from London, UK

" I'm not sure the htmldb environment would scale up to 50 concurrent developers
*on the same application* - it would be "tricky" "

Is this because of splitting the task between several developers
or you meant for heavy transactions like insurance companies or
banking etc???


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

1) no, it is not even remotely the same. you are using an html db application right here, right now. html db is HTML. forms is not - forms is a java applet.

2) sure. absolutely. html db applications scale very nicely.

if I had more than a handful of developers working on it, I would opt for an "enterprise" development tool - with source code control, cradle to grave development. That is where HTML DB starts to "not" scale, when you have too many developers all working on the same exact application. It just wasn't designed for dozens of coders all working at the same time. HTML DB applications scale out to very large user communities, but the development environment isn't designed for dozens of developers working on the same set of pages. Not that you cannot have 100 developers using HTML DB - you just wouldn't want to have them all working on the same large application.

Application Express - htmldb_admin page shows blank/empty page

Sami, February 23, 2006 - 10:36 am UTC

Dear Tom,

I did post the same issue in Application Express technet forum but I couldn't get any useful response to address my issue. I am expecting either you or some one can shed some lights on this.

Oracle 10g R2,Solaris 2.9

The installation went through fine without any problem.

works fine. 
What I mean by this is that - able to see workspace,username, password text boxes and login button. 
However I don't see imsage.


when I type the below 
shows the image on my browser.

URL gives an empty/balnk/white page in the browser.

Here is the access log and error log from Apache log directory.

Access log
=========== - HTMLDB_PUBLIC_USER [21/Feb/2006:15:51:52 -0600] "GET /pls/htmldb/builder/small_button_middle2.gif HTTP/1.0" 404 352 - HTMLDB_PUBLIC_USER [21/Feb/2006:15:51:53 -0600] "GET /pls/htmldb/builder/small_button_middle2.gif HTTP/1.0" 404 352

Error log

[Tue Feb 21 15:51:52 2006] [error] [client] [ecid: 1140558712:,0] mod_plsql: /pls/htmldb/builder/small_button_middle2.gif HTTP-404 \nbuilder: PROCEDURE DOESN'T EXIST\n
[Tue Feb 21 15:51:53 2006] [error] [client] [ecid: 1140558713:,0] mod_plsql: /pls/htmldb/builder/small_button_middle2.gif HTTP-404 \nbuilder: PROCEDURE DOESN'T EXIST\n

SQL> select object_type,count(*) from dba_objects where owner='FLOWS_010600' group by object_type;

------------------- ----------
INDEX             358
PROCEDURE          28
TABLE             160
TYPE               5
FUNCTION          10
VIEW              26
TRIGGER         175
PACKAGE BODY         141
SYNONYM          11
LOB              45
PACKAGE         148
SEQUENCE           4

Here is my dads.conf :

phildcap0r:oracle:orcl:/dtscbkus/dbexp0001/10g/HTMLDB/db_1/Apache/modplsql/concat marvel.conf
Alias /i/ /dtscbkus/dbexp0001/10g/HTMLDB/db_1/htmldb/images/

<Location /pls/htmldb>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_manager.process_download
PlsqlDatabaseConnectString phildcap0r:1522:orcl ServiceNameFormat
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername HTMLDB_PUBLIC_USER
PlsqlDefaultPage htmldb
PlsqlDatabasePassword @BdH/034ZUrlq+/glinIgUNg=
Allow from all

I could see 1084 images under images directory as below. 
phildcap0r:oracle:orcl:/dtscbkus/dbexp0001/10g/HTMLDB/db_1/htmldb/images> ls -l|wc -l

I am using Netscape comminicator 4.7 since on my solaris box? Is that could be reason for not showing imsages?

Did anyone encounter similar kind of issue?


Tom Kyte
February 23, 2006 - 10:43 am UTC

netscape 4.7???????? you are kidding right?

before we even look at this, can you use a piece of software written in this century first to test with?

HTML DB Reports

Thomas, February 23, 2006 - 3:29 pm UTC


I have read numorous articles on how to interate through a manually built SQL report for updating multiple row using the htmldb_item and htmldb_application packages.

I have one reocurring problem.

The htmldb_item package does not provide for a standard read only column that has no tab stop. The htmldb_item.hidden property still shows a column - albeit a skinny one if you change the header to &nbsp; - but then it aslo seems that you can reference the cell in a loop like so...

for i in 1..htmldb_application.f_g01.count

update sometable
set somefield = somevalue
where somefield = htmldb_application.f_go1(i);

end loop;

Now granted, in the above scenario you could use a standard MRU process, but we are updating tables with 4,5, and 6 keys.

How can I make these cells appear to be standard column cells with no tab stop and still reference them in a custom PL/SQL process?

Tom Kyte
February 23, 2006 - 7:21 pm UTC

I'll recommend you use the htmldb forum on The best answers for htmldb will be obtained there! (by far, not kidding )


A reader, February 23, 2006 - 3:31 pm UTC

I have used the htmldb_item.text with a 'readonly' specified and then added inline style to make it "look" like it is a standard report column, but it still accepts a tab. How can I remove the tab stop?

Go To the Forums

Scott, February 23, 2006 - 5:09 pm UTC

Hey, I've been using AppEx (HTML DB) for about 2 years now, and I saw this was a popular thread again on Tom's site. I reccomend going to the forums for these questions. The guys who wrote HTML DB answer those quite frequently, and would be the best to answer. Even some users are there to help out.

Tom Kyte
February 23, 2006 - 8:11 pm UTC

exactly, the quality of the htmldb forum is second to no other forum on otn.

html db vs. oracle portal

Ryan, February 23, 2006 - 5:19 pm UTC

Can you provide some pros and cons of html db vs. portal. Does one do something the other doesn't? Is one better for certain things than the other?

Tom Kyte
February 23, 2006 - 8:14 pm UTC

htmldb builds applications.

portal mostly integrates existing applications, does certain things (comes with applications)

which do you need to do?

Htmldb 2.0(or Express)

Larry Berge, February 27, 2006 - 12:13 pm UTC

The review was very good. However, my issue with Htmldb is moving to a new version. I cannot just export the schema and import to the new release on another box. I have to export each program and import individually on the new release. The alternative when moving to a new release is to migrate upward and place on the new release. The risk is possibly lose all of the htmldb work on the box.


A reader, March 27, 2006 - 3:25 pm UTC

i am trying to pass the parent id of a page through the url to an Item Type based on a PL/SQL stored proc and eventually the pl/sql is suppose to take the parent id as a parameter as parameter. I am able to create a custom attribute and set the default value in the Attribute section of the Item Type based on a stored proc...but i am not able to set pass in the parentid parameter throught the url by appending it to the URL like...

</code> http://localhost:7778/portal/page?_pageid=111,76889&_dad=portal&_schema=PORTAL&p_parent_id=76889 <code>

Tom Kyte
March 27, 2006 - 3:33 pm UTC

sorry, i've never programmed portal at all - I'll recommend the discussion forums on

Converting this to oracle html

sat, March 30, 2006 - 1:09 pm UTC

I am creating the html string by passing the below information. if I pass more than 255 charcters the HREF is bouning back.
<font size="+1"><A HREF="javascript:popup_price_entry20(entryscreen.'||c.tx_field||'.value,'''||p_customer||''',''entryscreen'','
|| ''''||l_d1_name||''',entryscreen.'||l_d1_name||'.value, '
|| ''''||l_d2_name||''',entryscreen.'||l_d2_name||'.value, '
|| ''''||l_d3_name||''',entryscreen.'||l_d3_name||'.value, '
|| ''''||l_d4_name||''',entryscreen.'||l_d4_name||'.value, '
|| ''''||l_d5_name||''',entryscreen.'||l_d5_name||'.value, '
|| ''''||l_d6_name||''',entryscreen.'||l_d6_name||'.value, '
|| ''''||l_d7_name||''',entryscreen.'||l_d7_name||'.value, '
|| ''''||l_d8_name||''',entryscreen.'||l_d8_name||'.value, '
|| ''''||l_d9_name||''',entryscreen.'||l_d9_name||'.value, ' .... like this I have to hold 160 values

" NAME="LEFT" >Click here to get Pricing</a></font></td>'

I have a screen bulit uing the HTMl CODE and also a string containg "Click here to get Pricing" it should popup a screen so that I can enter the pricing. Could guide how to we create this using oracle htp .

Tom Kyte
March 31, 2006 - 11:36 am UTC

please define "bouncing back"

there is no such thing as "oracle html"

patch no.

anurag, April 14, 2006 - 4:52 am UTC

Dear Tom,

can you provide exact patch no. to upgrade from to


Tom Kyte
April 14, 2006 - 12:35 pm UTC

you want the 9203 patchset - metalink has them all. please use that resource, it'll let you search for it and get it.

A reader, May 03, 2006 - 7:01 pm UTC


If i build/modify an htmldb application on my desktop and i want to port those changes to the server how could that be acheived please direct me to any link or provide your valuable feedback.


A reader, May 04, 2006 - 11:26 pm UTC

Thank You.

HTMLDB Security

Reader, June 03, 2006 - 2:36 pm UTC

We have been able to sucessfully deploy an HTMLDB application.We are using VPD for security (thanks for your help and guidance).
Now ,the requirements are changing ,we have about 300 pages and 500 users .
I would appreciate if you could guide us to find a neat implementation/solution to achieve the following ---

How do we limit access to some of the pages for some users .


Tom Kyte
June 05, 2006 - 9:15 am UTC

I asked Joel Kallman - one of the lead developers behind APEX - and he writes:

This can be easily implemented in a table or two that you create and manage. Ultimately, what you want to be able to do is create an Access Control List, that takes into account the user, any group they may be associated with, the application ID, and the specific page ID.

You can create a generic PL/SQL function and use this in an authorization scheme. This authorization scheme should be applied to every page. At runtime, you can use the substitution variables APP_ID and APP_PAGE_ID to determine the current application and page.

As a very *simple* example, here is an authorization scheme of type PL/SQL Function Returning Boolean:

l_result boolean := false;
for c1 in (select null
from my_acl
where userid = :APP_USER
and application_id = :APP_ID
and page_id = :APP_PAGE_ID) loop
l_result := TRUE;
end loop;

return l_result;



HTMLDB Security

Reader, June 06, 2006 - 8:05 am UTC

Joel ,

This helps me but we need some more clarification .
Today ,what we have is a user is verified by LDAP authentication.
So the implementation will be --
During this authentication we will know thru a table that he has access to which pages (may be thru groups)
Once we know that then we create his menu .
The Data is already secured by VPD .

Do you think that the above implementation is OK ?

What and where exactly we can use the Pl/SQL function which you have provided ?


Tom Kyte
June 06, 2006 - 8:48 am UTC

insufficient data here. LDAP authorization happens *once*

Ok, so we know it is "george" now, at runtime - what do you do to figure out if george has access to this table? (tell us that, and we can fill in the blanks)

Joel did tell you were to use the function

...and use this in an authorization
scheme. This authorization scheme should be applied to every page....


sam, August 02, 2006 - 11:57 am UTC


1. Why does oracle keep changing the name of the product from webdb to portal to htmldb to apex. Is not this the same product.

2. I am considering HTML strongly for a new application. However does it have any limitations? Is pl/sql web tool kit more powerful/flexible? Can i create a form with apex and then write my own API to save all the data in the database?

3. Would there be integration problems with apex application and pl/sql mod_plsql application?

4. I am considering to buy this book? Do you think it is a good tutorial on apex or there is a better reference.

</code> <code>

5. Does apex require 9IAS and does it use MOD_plsql too?

6. Does oracle provide any list of how many companies are using Apex for application development. Do you have any powerpoint presentations on it. I need to put something for management to let them use the tool instead of CF, J2EE etc.

Thank you,

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

1) webdb was part of portal. webdb is not portal, portal is not webdb.

htmldb was brand new, not "son of webdb", htmldb is a brand new thing.

As for "why called apex", don't ask me, I see the product names come and go....

2) The plsql web toolkit just emits HTML. It is not really correct to say it is more or less powerful, they are not really comparable.

HTML coupled with ajax, dhtml, css and the like is extremely powerful - and all of it can be done with virtually anything, including plsql.

3) no - no problems - apex is a mod_plsql application.

4) haven't personally read it, cannot really comment on it.

5) no, yes. It only requires apache with mod_plsql but can run under iAS of couse. (or AS 10g)

6) see </code> <code>and visit the htmldb forum on!


sam, August 02, 2006 - 3:19 pm UTC


Is apex an infrastructure or Application development tool or both?

I see you refer to it as development tool but one reference said that it is like .NET and J2EE or Cold Fusion.

2. It seems here you do not have to touch code if I am correct. More like Oracle Form Builder where you drag and drop objects and the tool will create the page/form for you. Correct?

Tom Kyte
August 03, 2006 - 8:58 am UTC

It is an application development tool that implements a "framework" that includes state management, navigation, look and feel (skins) and so on.

you do not have to touch the code if you do not want to. It is "declarative", but you have full reign over it should you desire.

Apex Forms build

Jas, August 04, 2006 - 5:38 am UTC


Can Apex forms be built from the command line? Is there a way of automaticllay building set of simple forms/reports without using the Apex web interface. As Apex is built on PL/SQL presumable it is possible to call the underlying pacakges directly?

Tom Kyte
August 04, 2006 - 8:04 am UTC

There is an APEX api you can call from your application, but your application to APEX is just a bunch of metadata.

In theory - could you? sure, we have the ability to export and import applications after all.

In practice - could you? no, not really, it is not practical (nor really sensible, cannot imagine the use case where you would do this)

Apex Forms build

A reader, August 04, 2006 - 8:35 am UTC

Thanks for the follow up, obviously its not currently feasible at the monment. Not quite sure what you mean by:

"[I] cannot imagine the use case where you would do this"

Do you mean specifically doing it the way you describe i.e. by
somehow generating an equivalent script to the export/import or you don't see the point at all. The reason I asked is that it would be useful to be able to automaticlly generate a set of simple data entry forms for the database especially during the development process. Since all the metadata is in the data dictionary it would seem that this should be technically possible & Apex an ideal tool for doing this.

Apex 2.2 SVG bar chart label length

Dessi, August 23, 2006 - 3:57 am UTC

Hi Tom,
I'm working with APEX 2.2. I'm seeing the 15 char limitation in SVG chart label. My charts are SVG vertical and horizontal bar charts. The x-axis labels are all truncated after 15 characters.

Tom Kyte
August 27, 2006 - 3:21 pm UTC

suggest you ping the forum on for support type issues.

Apex 2.2 SVG bar chart label length

Dessi, August 23, 2006 - 4:20 am UTC

Hi Tom,
I'm working with APEX 2.2. I'm seeing the 15 char limitation in SVG chart
label. My charts are SVG vertical and horizontal bar charts. The x-axis labels
are all truncated after 15 characters.
I want to ask is it possible for the size of x-axis labels to be increased?

htmldb and workflow engine

A reader, September 20, 2006 - 11:47 am UTC

Tom, thanks a lot for all your help! Generic question for you.
Recently started to use HTMLDB and workflow engine. It was for two different projects, but after playing a bit with both - I think I don't need ANYTHING else but Oracle! Really: building with HTMLDB is very fast and productive, workflow engine (though not so easy to install and configure) provides with an exceptional power of building whatever complex business logic. My two cents here: I think both products would benefit a lot if they are more tightly integrated. For example - HTMLDB is already able to handle users internally, just create a user - you are done! Workflow is not as easy to implement users outside of OID or native database users. Do you by chance know - is some additional integration planned? May be in 11G :-)?
Thanks a lot!

wrong way to go

A reader, September 21, 2006 - 10:02 am UTC

The same day I posted question above (about how great are APEX and workflow) I found out that oracle standalone workflow will be desupported. I cannot even describe the feeling I have! It means that for half a year our team was moving in a wrong direction. And it doesn't mean that we were going in the wrong direction from technical perspective - we are completely satisfied with the results we got. But there will be NO WAY to get an approval for implementing what we have done in production - every manager that I talked to is now saying: NO. We are not going to implement anything that is desupported. And lot of them are very happy for some reason - majority of managers likes "cool" Java solutions and are very satisfied with what they heard from Oracle. And all of them are now saying - hey, exactly the same will happen with APEX. And you know what? I think they are right, though I hate to say this! You can love Oracle and PLSQL, but the reality is - less and less solutions are implemented based on Oracle applications solutions. This impression is based on what I see around me and what I hear from other people on different forums. I don't mean Oracle database - thanks God, no other database is even close to what Oracle offers. But applications - no. I love APEX, I think it is absolutely perfect. But will I insist on implementing anything based on it in our company? No. The main reason - I already did it with workflow and failed based on recent news from Oracle. There is no way for our company to spend more money on buying BPEL and investing who knows what level of effort in learning and implementing it. I'm very sad to say this, but it looks like I'll HAVE to switch to learning Java. Not because I like it, but because pretty soon I'll not be able to justify my position as PLSQL developer/architect in my company. For the last two years there was no ANY new person hired in our company (and believe me - it's a big one) for doing real development in Oracle. And there are at least 50 people hired for Java positions. Does it tell you anything?
Thanks and sorry for bringing this to your absolutely great site!

html db

samy, November 29, 2006 - 3:30 pm UTC


I installed the http server (no database) on my machine. I noticed some of the prompts and sql plus come up in arabic. I did not request any installation in arabic. I only had to install a CD once that requried arabic support before.

What do i need to do to switch the window oracle prompts to english. Is there a setting change or do i need to uninstall the http server, change windows setting and reinstall again.

Tom Kyte
November 30, 2006 - 9:25 am UTC

please use support for something like this


samy, November 30, 2006 - 4:57 pm UTC


I dont have a support contract. Trying to evaluate the tool to see how it works.

Tom Kyte
November 30, 2006 - 5:51 pm UTC

sorry, try Oracle XE then, free to download, has the bits you are looking for, support via the forum.

I've never had "arabic" just sort of pop up.


samy, November 30, 2006 - 6:51 pm UTC


It is not just poping up. When you login in to sql plus the window label for "User Name" "Password" and "Host String" are in Arabic. Does oracle usually ask you what language support you want? Wondering if i did something wrong when I installed it.

Tom Kyte
November 30, 2006 - 8:39 pm UTC

Yes, when you installed it did.

html db, December 01, 2006 - 12:27 pm UTC


To uninstall, do you use the oracle companion cd or go through windows remove programs?

Tom Kyte
December 01, 2006 - 12:49 pm UTC

you use the installer, yes.


samy, December 04, 2006 - 4:46 pm UTC


Oracle has 200 pages for installing 10 g rel 2 on windows. Do you really need to read this to install a database to play with HTML DB. Or you just start the installer and hope that everything will work fine.

Tom Kyte
December 05, 2006 - 9:41 pm UTC

you may do whatever you choose to do here.

maybe you want to use Oracle XE, comes with all of this stuff pre-installed out of the box.


A reader, December 06, 2006 - 11:48 am UTC


What would XE NOT do that enterprise or standard do? You think XE is sufficient for HTML DB testing.

Tom Kyte
December 07, 2006 - 8:46 am UTC

sure it is, and the features are all documented, see


sam, December 09, 2006 - 10:20 am UTC


1. Can you explain when is it best to use standard, enterprise or XE? IF XE is free, why would people buy standard or enterprise?

2. Does HTMLDB/apex get installed when you install 10g XE?

3. How long in your estimate it would take to get proficient in apex if you already know pl/sql web toolkit?

Tom Kyte
December 09, 2006 - 2:42 pm UTC

1) when you tell me what your needs are, we can discuss which edition makes sense.

2) yes

3) depends on the individual doesn't it.... not long.

html db

A reader, December 09, 2006 - 9:11 pm UTC


I used the installer to uninstall the http server but even though the installer says it was uninstalled, the direcotry Oracle and all subs are still there with files. Should not those be deleted or you have to manually do it.

html db

sam, December 10, 2006 - 11:39 pm UTC


My needs are inventory management system for a few warehouses doing 50 - 100 transactions/day with about 50 users. Is this enough info or you need more?

Tom Kyte
December 11, 2006 - 7:29 am UTC

not really - do you need partitioning for example.... 50-100 transactions per day says virtually nothing about what you need.

html db

sam, December 11, 2006 - 4:44 pm UTC


Can you summarize the questions that you need answers for so you get an idea of needs?

No partitioning is needed.

Tom Kyte
December 11, 2006 - 7:19 pm UTC

you need to specify in some detail (eg: won't happen here) what you need and then you can review the license guide to see what you would want.

basically - there are a set of features functions available with each - you need to find out what you need.


sam, December 19, 2006 - 12:50 pm UTC


Is there a link for those <features functions> for each DB so i can look at?

Tom Kyte
December 19, 2006 - 12:59 pm UTC

Regarding reuse of APEX features

Max, December 20, 2006 - 2:54 am UTC

By tracing APEX I've observed it uses "WWV_Flow_Item.MD5( <column_list> )" to generate checksums in order to handle concurrent data changes. I'm tempted to use this the same way for an application.

What are your thoughts about doing so?
Why did one choose MD5 (amongst other options)?

Tom Kyte
December 20, 2006 - 8:20 am UTC

It is a good idea - I wrote a package a long time ago - owa_opt_lock (part of the database now) that did a simple checksum for the same purpose.

If you have access to my book Expert Oracle Database Architecture - I describe this method in detail.

They chose a checksum function (md5) that exists in the database as a native function - that is all....

Max, December 20, 2006 - 12:05 pm UTC

So, I'll check your book for that (can't remember that topic).

One "challenge" remains: The abovementioned APEX routine doesn't process BLOBs (and DBMS_LOB.ConvertToCLOB is undocumented until 10g).

How would you address this (conversion of BLOB to CLOB)?

Tom Kyte
December 20, 2006 - 1:22 pm UTC

if the blob data is already in the right characterset, utl_raw.cast_to_varchar2 would cover it 32k at a time.

Just in case APEX isn't available ...

Max, January 21, 2007 - 7:46 am UTC

... one could generate MD5-digests.

But could you decribe how APEX's function WWV_Flow_Item.MD5 does process the set of (multiple) columns it takes as input to produce a (single) result value per call as output?

A reader, February 07, 2007 - 6:43 am UTC

I've implemented VPD based on sys_context( 'userenv', 'os_user' ) and a global application context (to "register" valid OS users). Currently there are only two database roles defined: Both have read access to all the data but privileges to insert/update/delete are only granted to one of these roles. OS users log on to the database with one of these roles and get access to the data according to their VPD restrictions.

With APEX a couple of questions come up:

In an APEX application using "database account" as authentication scheme calls to "sys_context( 'userenv', 'os_user' )" return "SYSTEM" although there 's no such OS account on my local machine (running WIN XP) where database client and server reside as well as the HTTP server. The database user (returned by calls to database built-in function "user" from within APEX) is "APEX_PUBLIC_USER" regardless of the database account used to logon.

HTTP server connects to the database "on behalf" of the database account I've used to logon to the APEX application as database user APEX_PUBLIC_USER, but where does "SYSTEM" come from?

And how would you use/combine the options available with APEX/DB to organize accounts/roles/privileges in general?

Is it a valid/feasible approach at all to attempt to apply the existing scenario (using OS accounts) to the stateless APEX environment?

Furthermore the database side of the application uses (non-global) application contexts.

Do they have have to be replaced by global application contexts (using session identifiers) in the stateless environment?

Tom Kyte
February 07, 2007 - 6:41 pm UTC

I would not use os_user for much of anything in a network environment - it is sort of meaningless. How can you be using that?

A reader, February 08, 2007 - 2:17 pm UTC

Yes, you are right: Since all APEX users "share" the same database session it wouldn't make much sense ...

What 's the most efficient way to set client identifier for database session for every database request issued by APEX "on behalf" of the end user (without having to recode it over and over again)?

apex 3.0 installation, April 16, 2007 - 4:01 pm UTC

Hi Tom, I don't want to critize the installation of apex 3.0 I only want ot understand

When I installed apex 3.0 It took more time than I expected.
I got the impression there should be used more efficient methods to install apex, for example importing remaping schema, etc.
Or becuase it's an upgrade there is not another way.

Problem running asktheexpert on apex 3, April 17, 2007 - 4:34 pm UTC

Hi Tom I got this problem when trying to run ask the expert application, could you helpme to solve it,

ORA-06550: line 4, column 23: PLS-00201: identifier 'ATE_API.PARSE_SEARCH_STRING' must be declared ORA-06550: line 4, column 5: PL/SQL: Statement ignored

A reader, April 18, 2007 - 9:09 am UTC

Hi Tom, I found the problem
1) I had to open the script and replace security group id
Based on the value got on the view SELECT security_group_id, alias, owner, name
FROM wwv_flows.
I run it as FLOWS_030000 and ATE ( I couldn't import the script )
There I got the problem I ask, But searching searching I found on Installation Scripts a task: Install Supporting Objects.
I run it and this installed the object and get the application working.

Tom Kyte
April 18, 2007 - 11:55 am UTC


The packaged applications should be imported through the Application Builder web interface, not as a SQL script (since they require installing Supporting Objects). We'll try to make this clearer in the readme for the next release.

Marco from the APEX team

A reader, April 18, 2007 - 12:21 pm UTC

Thanks Marco I tried to import it on workspaces, but I got an error saying invalid script that's the reason I did in this way.
If in the invalid script you could explain the reason why it couldn't import it I could have solved in other more correct way.

By the way Marco, ;) are you fixing the problem to use embedded plsql gateway on (including some clarification on the instalation guide of apex 3) I found a way to install it an use it, the only problem is I got an additional logon for XDB, I log as an ordinary database dba user, and I can get it work. I read about other who couldn't I made a post in the forum.
Thank you again and congratulations from apex 3, apex is become realy nice.
Tom Kyte
April 18, 2007 - 2:45 pm UTC

I've installed it several times without seeing an "invalid script" error. Did you go to Application Builder > Import, then select the ask_the_expert_installer_0.9.sql file?

Glad to hear you like APEX 3.0, but I don't think running it on with the EPG is a supported configuration. The APEX forum on OTN has more information, but for now I would continue using Oracle HTTP Server with modplsql.


A reader, April 18, 2007 - 5:28 pm UTC

Hi Marco, no, I went to import workspace I didn't knew about that option, I think that was the mistake but the error message I receive didn't helped me to find the correct way and the installation guide is too brief without details for stupids like me.

I'm trying to get the expert run (not the demo else my own expert site) and I must tell you as a newbie on apex this is really really difficult, to get it work can't you include the step to step to configure your own AskTheExpert. For example I can't find the way to login in the administrator page.
:) Thanks

Another comment, only a comment, about the script to install apex 3.0, it took me about 1:00 to run in a not so bad computer 1gb memory Celeron CPU 3GHz (and compresed disk).
As a noexpert OCP DBA I got the impression the maximum time should be about 0:15 mn in total, well that's only a comment hope not to bother you.

Tom Kyte
April 18, 2007 - 6:37 pm UTC

1gb with a celeron (and add to that the sluggish compressed disk) is not so good a computer for a database server.

A reader, April 19, 2007 - 8:50 am UTC

It's not a server, the idea is I think using expdb impdb and other tools you could use to improve the install performance, I had run other process in my computer like import, export, and they were faster. This is only a comment you don't have to worry about. But you have the fastest database and I don't think you are not using fully, but maybe I'm wrong.
Tom Kyte
April 19, 2007 - 9:49 am UTC

that was me, Tom.

Oracle is a database server.
Whatever you install on - that is a database server by definition.

All apex is doing to install is - run some sqlplus scripts. You have a tiny machine, they run a lot of code. It takes time to compile. That is all.

A reader, April 19, 2007 - 9:49 am UTC

One example how I think you could improve performance, you could transport a tablespace, but if the customer want to include in a tablespace already exists, then they have to suffer the wait.
In the same as creating seed databases or creating your own custom database.

Tom Kyte
April 19, 2007 - 12:20 pm UTC

this has been Tom, not Marco

you cannot transport SYSTEM, which is where most of the activity is happening. It is compiling PLSQL.

Juan Carlos Reyes, April 19, 2007 - 12:59 pm UTC

Thanks Tom and Marco

configuration management and htmldb?

Ryan, April 19, 2007 - 2:17 pm UTC

We are using htmldb for part of our application and we are preparing to scrap it since we can't version manage it. We expanded from 3 developers to about 10. At 10 developers its essential that you can check code in and out of some CM tool.

Designer has version management built into the tool. Are there any plans to add this to htmldb? If not, it cannot be used on teams that grow above just a few people. Especially if you have multiple parallel development activities.
Tom Kyte
April 19, 2007 - 4:55 pm UTC

you can export your application, it is a "plain text file - just like code" at that point.

apex security

abc, April 24, 2007 - 3:53 pm UTC

hi marco/tom

we made our preference to use "database account credentials" as our authentication schema
we tried to lock schema APEX_PUBLIC_USER but then application didn't work
1. what is the purposes of this schema if such authentication schema is used?
2. after login into the application using database accounts what happens behind the scene ... database account session vs parsing schema vs apex_public_user
Tom Kyte
April 24, 2007 - 4:08 pm UTC

please use the apex discussion forums on - the developers of apex monitor that group very actively.

Use of ate example

A reader, May 03, 2007 - 10:20 am UTC

Tom I think I misunderstand but I thought the AskTheExpert example for apex is for people who want to help other people, and upload to the are that oracle gives free and help from there, or this is only anothe example to analyze.

By the way, congratulatiosn apex is really great and easy but I don't find enough intuitive as ms access, I'm not sure if it is because web based development take out facilities you get on windowized development environments. But we found the learning curve to use as we want, was going to take enough time (about 2 months )to decide not to use in apex, and use instead that time on jdeveloper.

a SQL question about the 'Ask the expert' application

A reader, August 19, 2007 - 3:28 am UTC

Hi,Tom. In the ate application,I found a database package named ate_api. In this package, a procedure named update_administrators( p_administrator_list in varchar2 ) has a statement below:

delete from ate_administrators t1
where t1.userid in
(select t2.userid
from ate_administrators t2
(select column_value from [b]the[/b] (select cast (l_admintbl as ate_tabletype ) from dual)));

I tried to recompile this package,and it successed . But I do not know '[b]the[/b]' is what.And I can not find the anwser in the ORACLE database document.Can you kindly explain this to me?

Tom Kyte
August 22, 2007 - 10:22 am UTC

it is deprecated - was the way to do it (briefly) in 8.0, the TABLE clause changed the syntax.


A reader, January 06, 2009 - 9:38 am UTC


Can apex or any other oracle tool be used to develop cross-platform (Windows, MAC, Linux) client application that can be shipped to various vendors to use.

Some tool like this

I need to develop a tool and ship to hundreds of vendors who may have different platforms. The tool will read and store all the emails that were sent to them from company XYZ Inc.
It will store the emails and files attached and allow vendor to search and retrieve all of them using the tool. it should be able to have some database engine for storing these emails and files.
Tom Kyte
January 06, 2009 - 10:38 am UTC

umm - smk - think about this....

APEX displays in a browser. Any browser. It does not care what browser. I can run APEX applications on my palm treo (I do). On firefox on linux (I do). On IE on windows (i sometimes do). On <any browser> on <any OS that runs that browser>

Now, the database is Oracle - so that already is known to run on <your OS of choice>.

And the 'middle tier' is Apache - OR the database (no need for a separate middle tier if you don't want it)

So the client (browser) - anywhere, any OS
The database - anywhere, any OS
The middle tier - anywhere, any OS

You have Oracle XE (free) if you want as well. It comes with APEX at the core - it would need Windows or Linux - but the client can be anything in the world that runs a browser.


A reader, January 06, 2009 - 11:02 am UTC


I forgot that apex is just web development tool.

1. Like you said, I can ship oracle express with some code and let them install on their machines and use the browser to run it. correct? However, oracle express will not run on macintosh.

2. If they insist to use browser and have an EXE bundled application with database engine, is there any oracle tools that i can do that with.

Tom Kyte
January 06, 2009 - 12:47 pm UTC

1) correct, you would need (as stated) a windows or linux machine to host XE - the client can be anything you want.

2) we don't do client server tools. You can write a java application of course - they run on many platforms as well - in that case - our tool would be jdeveloper.

You say "exe", but linux and mac don't do "exe", that is windows...

APEX a repository in every DB?

Stuart, January 29, 2009 - 3:08 am UTC

Hi Tom,

Can an APEX environment point to different databases, or must you install the repository in every environment?

I'm planning on installing 10gR2 HTTP Server in our Dev environment along with APEX 3.1.2. Must I install HTTP Server seperately on our Prod box along with APEX again, or can I put them on a seperate server and do development against any db environment in my network?

Tom Kyte
January 30, 2009 - 2:00 pm UTC

it can point to different database (a single APEX install in a single database), but you probably don't want to.

If you install in one database, you could use database links to other databases - but then every query would be a distributed query. I'm doubtful you would be impressed by the raw performance of such an installation.

You only need one HTTP server, with as many DAD's (data access descriptors) as you have databases, but you would want the APEX code installed in each database.

Oracle Forms in different name ?

Daniel, January 31, 2009 - 1:05 am UTC

I am totaly confused now. All are talking about HTML DB(Apex).
We are using oracle 8i and forms6i.
I am thinking of upgrading to a new look or a new version.
What is that Oracle 10g Forms?
What is that Oracle Apex ?
What is that J2EE ?

Oracle forms 6i is the last version in Client/Server environment. Forms 9i onwards it is Web base.

Wich of the above product i should use it ?

Thank You

Tom Kyte
January 31, 2009 - 8:57 am UTC

Well, the last bits have been about APEX - it was mentioned by name in the commentary.

And, well the question was about HTML DB to begin with - so sure, we are talking about that.

... Wich of the above product i should use it ?...

depends, if you just want to upgrade, you would stick with forms.

if you want to re-engineer and build a browser based application, you would learn (probably hire someone that knows) about APEX and J2EE to evaluate which one best meets your needs and skillset.

J developer

Daniel, February 01, 2009 - 4:19 am UTC

Today i have come across another product ie jDeveloper 11g.

Is J2EE & Jdelveloper is same ?.

Tom Kyte
February 02, 2009 - 10:19 am UTC

j2ee and jdeveoper are like:

C++ and a compiler

j2ee is a "platform", a set of standards and common API's, sort of like extensions to the java language.

jdeveloper is an IDE (integrated development environment) you can use to code j2ee applications (or just java or just sql or just plsql...) in.

A reader, February 04, 2009 - 12:47 am UTC

Thank you very much.
I have downloaded JDeveloper with Oracle ADF and going to start using it.
I read from some discussion forum that the JDeveloper is easier for ex-oracle forms6i developers. is it right?.

Can you send me few sample program link for JDeveloper.

At moment we are using Forms6i & Reports 6i. How can we upgrade our existing application to Jdeveloper. which is the easiest way?.
Tom Kyte
February 04, 2009 - 11:27 am UTC

... I read from some discussion forum that the JDeveloper is easier for ex-oracle
forms6i developers. is it right?. ...

that would be like saying a unicycle is easier for ex-car drivers.

You are talking about going from a 4gl like experience, where the amount of code you write is 'snippets here and there' to a Java J2EE world. It is going to take a bit of work to get from there to here.

It is not an upgrade, it is called "re-engineering from the ground up"

Java access HTML DB htp

Ian, February 26, 2009 - 1:32 pm UTC

hi Tom

We have application using HTML DB, we created store procedure, pass url to server and get html pages, How can I call the storeprocedure, and get HTP in java?

Tom Kyte
March 03, 2009 - 8:07 am UTC

htp has api calls:

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 IROWS                          NUMBER(38)              OUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THEPAGE                        TABLE OF VARCHAR2(256)  OUT
 IROWS                          NUMBER(38)              IN/OUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THEPAGE                        TABLE OF VARCHAR2(256)  OUT
 IROWS                          NUMBER(38)              IN/OUT
 CHARSET                        VARCHAR2                IN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THEPAGE                        TABLE OF RAW(256)       OUT
 IROWS                          NUMBER(38)              IN/OUT


Sergio Coutinho, March 11, 2009 - 11:44 pm UTC

Hi Tom,

About APEX/HTML_DB technology, do you think it's a good plataform do build complex applications? I'm thinking about ASKTOM site, and my opinion that's APEX is a good tool to develop small aplications, but i think that it's very difficult/hard to build complex applications without good metodology/best practices to organize an complex APEX project. Do you agree? Could you recomend some articles about how to plan and develop complex applications on APEX (HTML_DB)?

Tom Kyte
March 12, 2009 - 12:43 pm UTC

Have you used APEX itself to build something?

Would you believe that APEX is written entirely in APEX?

Is APEX itself, the application development environment, your idea of a simple or complex application?


A reader, March 18, 2009 - 10:34 pm UTC


is apex installed automatically with 11g or you have to select it specifically when you install? Heopfully they cant install it without it
Tom Kyte
March 19, 2009 - 10:15 am UTC

you can install whatever you want or don't want, yes, a DBA could install without it - it would be their choice.

by default - installed.
doesn't have to be installed, but would be by default in a 'stock install'


A reader, March 19, 2009 - 10:58 am UTC


What do you usually install or request with 11g Enterprise Edition (i.e apex, XML DB, XDK, PL/SQL toolkit, else).

Tom Kyte
March 19, 2009 - 11:05 am UTC

there is only one answer to that

it depends.

what features do YOU need? That would answer this question for you quite nicely.


A reader, March 19, 2009 - 1:13 pm UTC


at least the same features in 9i. is there a way to check from 9i database what features we currently have installed?

PLUS any nice features in 11g like apex. Not familar with other new features so i cant tell.
Tom Kyte
March 23, 2009 - 9:43 am UTC

select * from dba_registry;


A reader, March 23, 2009 - 2:10 pm UTC


Thanks i got all the 9i products. I will add apex. anything else new in 11g that is not installed by default?

------------------------------ --------------------------------------------------
CATALOG Oracle9i Catalog Views
CATPROC Oracle9i Packages and Types
OWM Oracle Workspace Manager
JAVAVM JServer JAVA Virtual Machine
XML Oracle XDK for Java
CATJAVA Oracle9i Java Packages
XDB Oracle XML Database

Which Technology and tool to go for it

Emad Kehail, April 06, 2009 - 4:33 am UTC

Hello Tom,

Out department is in a debate about which technology to use. We are currently using Oracle Forms6i and Reports 6i as well. The database has been upgraded to Oracle 11g 11.0.7.

Now we are planning to go for web clients instead of CS model. Some of the developers vote for APEX as an alternative for Forms Applications. Others are voting for JDeveloper and ADF.

I have read about both of them and I am confused. I can see APEX is nice and easy to learn, but is it targeted to the enterprise level applications??
Also, Oracle is moving its eBusiness applications from Forms to JDeveloper as I have read is some posts.
I also read the link
which helped a lot...

Finally, I like to mention we are a university with more than 20,000 students and during peak working hours we have more than 2000 concurrent users

I am really in a need for an advice from an expert like you. This is really a critical decision for my department.


Tom Kyte
April 13, 2009 - 9:57 am UTC

Metalink "classic" is written in APEX - it is sort of enterprisey.

This is not a question that can be answered 'simply', no one can say "you should use tool X", rather you need to evaluate which road going forward is right for you. Do you have really really really good programmers that know the database inside and out and have mastered java in detail? Forms programming and Java development are as different as night and day - if you have some hard core forms developers, making the switch to a Java/J2EE/ADF environment is going to be a bit of a shock - expect to have a learning curve measured in - well - a long time. The first things they do (probably for the next couple of months) should definitely be considered "disposable" as they will learn and re-learn and re-re-learn what to do over and over. Not saying that is the wrong path, just that - going from a 4gl-ish environment like forms into Java programming is going to be a bit of a dive into the deep end of the pool.


A reader, April 09, 2009 - 6:38 pm UTC


Can you explain what is the emedded pl/sql gateway that is required to run apex that is installed with 11g

We are using mod_plsql in the IAS (diff machine from DB).
do we still have to configure it after insalling 11g by running a script.
Tom Kyte
April 13, 2009 - 4:12 pm UTC

if you use mod_plsql in apache, you do not need the embedded plsql gateway. the plsql gateway allows you to run apex WITHOUT a web server - browser goes straight to database, nothing in between.

If you are running mod_plsql and want to continue running it in apache, just do that.

html db

sam, April 13, 2009 - 11:08 pm UTC


So with mod_plsql the flow is
browser ->http server-->mod_plsql--->database

with emedded pl/sql gateway
browser ---> database

is this correct? are there any benefits to embedded?


<<going from a 4gl-ish environment like forms into Java programming is going to be a bit of a dive into the deep end of the pool. >>

Is not JDEveloper 4gl tool. I thought it would make java coding much easier.

Tom Kyte
April 14, 2009 - 10:15 am UTC

yes, that is the flow - though you don't really need to say "http server -> mod_plsql", mod_plsql is sort of in the http server, it services the request, there is the equivalent of mod_plsql in the embedded plsql gateway.

basically, think of mod_plsql as something that converts a URL into a stored procedure call - calls the procedure - reads the output (stuff you htp.p'd) and writes it to the client. It is in the embedded gateway too - the logical equivalent of it.

the benefits to embedded? less moving pieces, Oracle XE runs on it - the only administration interface to the XE database is via APEX (htmldb does not exist, that was a 'code name', the name is application express). XE out of the box comes with the embedded gateway up and running - less moving pieces, less to go wrong, easier to install.

2) jdeveloper is a tool to code java. Does it automate the generation of some of your java - sure, do you need to know java to use it? Yes, absolutely. The learning curve for a forms developer that doesn't know java will be somewhat "large"

Marcus, April 15, 2009 - 3:56 am UTC

Hello Tom,

you said
<Metalink "classic" is written in APEX - it is sort of enterprisey.
So what is the reason for Oracle to turn "classic" off? Is it a technical reason regarding APEX or is it not modern enough for "My Oracle Support"?

I cannot say that "classic" is at the top of usability but at least it can be accessed easily by mobile devices since it is HTML with a bit of JavaScript, compared with many many bytes of Flash which is not available everywhere (lack of computing power for mobile devices or security reasons)

Tom Kyte
April 15, 2009 - 9:36 am UTC

Classic isn't off?

Classic is there - it is the only interface I use. There is that 'flash interface', but I sort of hate flash - so I do not use it at all. I find it hard to use.

for a long time, classic was the only interface. Then they rolled out this snazzy jazzy 'flash' interface (can you tell what opinion I have of it....). Most people I work with use - well - classic.

My favorite internal site inside of Oracle is like metalink (a support database). It is 100% black and white, virtually image free, and uses the courier font. It is perfection.

Oh, and - enterprisey, brand spanking new, and written in APEX...

Classic MetaLink to be Retired

Marcus, April 16, 2009 - 6:49 am UTC

Classic is there - it is the only interface I use. There is that 'flash interface', but I sort of hate flash - so I do not use it at all. I find it hard to use.

This could be my words, but Metalink says
"Later this year, Oracle will retire the Classic MetaLink support portal and provide a single support interface through My Oracle Support."

And in document 784634.1
"We are pleased to announce that later this year My Oracle Support will be upgraded and the Classic MetaLink pages will be retired..."

So Oracle is pleased. All of Oracle?

Tom Kyte
April 16, 2009 - 9:55 am UTC

developers are developers - some people *love* flash

others despise it (guess where I fall)

Connect to more than 1 firebird database and transfer data

bernard, May 02, 2010 - 5:38 pm UTC

Good day,

I am sitting with a situation that I have to combined (3 years) for 26 site of 29000 databases files where there is 1 table in each data base called RECDATA, I need to combined all that data
Into one database file called RECDATA

For example there is a database called DB1 with a REDATA table inside it, and then DB2 with a RECDATA table inside it, DB3 with a RECDATA table inside it est.
So I need to put all the Recdata into 1 Database file, or to copy all the RECDATA from all the database file’s to 1 RECDATA they all have the same columns
All I need is to get all that data into 1 RECDATA table.

The program where this database comes from is a voicerecording system creating a new database every day.
There is 1 database just for indexing (to view the recordings) and then the daily database is where the recording lies in, that Colum is called RECFILE.
I will give you a example of the 2 database, I would really appreciate it if you can help me on this one or if you know of somebody that can help me, I have tried everything but a lot of them can only do
1 at a time.
Fbexport and Fbcopy do not work or i do not nou how it works.

Kind Regards
Bernard van Biljon

Apex with Standby database

apex reader, May 03, 2010 - 5:45 pm UTC


I am looking into a way of implementing Apex read-only (canned) reporting against Oracle 11.1 Active Standby database. I know that Apex writes in the background so I don't think there is a direct way deploying it. Do you know of any other way that this can be achieved? Maybe having repository pointing to read-write database but read data from Standby.

Thank you in advance
Tom Kyte
May 06, 2010 - 12:43 pm UTC

best to ask this in the apex discussion forum, but since apex uses a database table to store session state, it is likely the only answer will be 'database link to standby from a read write database'


sam, January 25, 2011 - 4:28 pm UTC


i want to test a few things with apex in 11g.

When i hit the apex URL i get


I assume userid/password is the same ones for the database userid/password i want to log into.

What do I enter for workspace. Don;t i need to log in first to create one.
Tom Kyte
February 01, 2011 - 12:12 pm UTC

click the sign up button first.


A reader, February 01, 2011 - 4:12 pm UTC


I am refering to our own test database (not oracle website).

There is no signup link. I just go to ther 11g application server link and i get the login page.


sam, March 12, 2011 - 10:16 am UTC


three small questions on APEX.

I am finally getting upper management support on this tool.

1) currently my production DB is If i requested to install APEX with this would it work?

If not, do you recommend to patch it up 9204 or 9208

2) When iasked to use it once with 9202 they tols me there is a separate APEX server on diff machine. Does it make any sense to have APEX application on one machine and DB on another machine (use db links i assume).

3) I have an 11g test database with apex installed.

The URL for it shows me


Is this for the database account I have? i dont have any workspace yet, what shall i key in to test creating a small application using some tables in TEST.
Tom Kyte
March 12, 2011 - 5:07 pm UTC

1) ask on the APEX forum, that would be the best place to get support information - or better yet, the bestest place of all would be my oracle support...

2) I don't know what an "apex server" is - that is not our terminology. I'm not sure what you are trying to say.

3) see #1


sam, March 14, 2011 - 1:04 am UTC


1) Do you have a list or link for public websites using APEX?

2) What is teh best way to demo an application using apex? is there anything built in for EMP/ DEPT that I can demo for users to show them the look, feel, navigation, etc.

Tom Kyte
March 14, 2011 - 8:13 am UTC

1) see -> discussion forums -> APEX.

Or see

Of course no one would have a comprehensive list of all sites, it is not like they are required to register or anything. No more than anyone could produce a list of all sites that use java.

2) see (1)

There are LOTS of links off of that answer all of your questions.

To Sam: Re: Apex

Kim Berg Hansen, March 14, 2011 - 2:23 am UTC

Hi, Sam

There you will find examples, demos, forums, and lists of public websites using apex.
You can even sign up for trying out apex online without having your own installation :-) - that is a very good site for apex information.

Tom Kyte
March 14, 2011 - 8:14 am UTC

(it isn't like I haven't sent him there before... more than once)


sam, March 14, 2011 - 11:32 am UTC


OK, i will take a deeper look at the
I thought you were already an expert in it since you developed your site using it.

But quick question.

Is it possible in apex to change the look of the HTML page or you are stuck with the templates built-in the product.

let us say you have a bunch of paper survey forms and users many want the webpage to look very similar. can you meet that requirement with apex or not
Tom Kyte
March 14, 2011 - 12:11 pm UTC

but, if I were expert in it - why would that make me be the goto guy for *any* of the above totally non-technical questions? Sam, the problem I have is that you ask so many questions - every day - I'm trying to open your mind to the fact that there are MANY places for information out there - start using them too.

Is it possible in apex to change the look of the HTML page or you are stuck
with the templates built-in the product.

stuck is a funny word, it sounds so negative. Having a common look and feel is pretty good isn't it.

APEX is written in APEX. There is your best demo (to show what can be done) and the best answer to "am I stuck with a single look and feel".

You can develop your own templates to look like anything you want.


sam, March 14, 2011 - 12:23 pm UTC

<<You can develop your own templates to look like anything you want. >>

Ok, great. some customers may have neck issues and want the page upside/down. I have to please the customer.

in a month, i will be an expert in this tool.


sam, March 14, 2011 - 10:09 pm UTC


Can this be true.

APEX will not support table that have PK more than 2 columns? how does this affect development and SQL.

and tables that have more than 100 columns?
Tom Kyte
March 15, 2011 - 7:56 am UTC

Sam - time for you to discover the wonders of:

You might even try a google search with something like:

apex primary key more than two columns

Please - before posting on otn, make sure you did some due diligence and SEARCH before you speak.


sam, March 14, 2011 - 10:22 pm UTC

Tom Kyte
March 15, 2011 - 8:00 am UTC

those are actually consulting partners that are willing to build your site. Not necessarily APEX sites.

but tell you what - just keep it as I had it above, pointing to APEX - under which you can get a ton of information about...... APEX.

best server

Sara, April 19, 2011 - 5:57 am UTC

Hi Tom,
Is HTML-DB is a Multi-threading Application ?? and what do u recommend for servers ??

Thanks a lot :)
Tom Kyte
April 19, 2011 - 6:43 pm UTC

html-db is a code name from many many many MANY years ago, like a lot of years ago (might need to upgrade your current view?)

APEX is multi-threaded/multi-process, yes, absolutely.

I do not recommend anything for "servers", that is too ambiguous. I don't know what "servers" means.


sam, May 28, 2011 - 5:36 pm UTC

Here is a good listing of the commercial and public sites using apex.


A reader, October 17, 2011 - 10:34 pm UTC


Was there anything changed in apex 4.0 in regards to "debugging" and "version control"?

I think the older versions were difficult to debug and lacked any version control features.

Do you see any weak areas in the apex framework compared to others?
Tom Kyte
October 17, 2011 - 10:41 pm UTC


best to be asked on the forums for apex. go there.

APEX public websites

Adam, January 24, 2012 - 11:43 am UTC


Do you have a list or link for public websites using APEX?

Here is one:

apex authentication

A reader, February 19, 2012 - 8:02 pm UTC


It seems you made a big jump with APEX 4.0 over the older versions.

Apex 4.0 supports 3 preconfigured authentication schemes and one of them can use a custom function for authentication using USER accounts stored in a table.

If I have some web pages developed using pl/sql web toolkit and new web pages using APEX,can I integrate both using under one authentication and session management function.

I was wondering if the custom authentication is ONLY for initial authentication and does not include session management for every page.

What i want is a COMMON custom session table to store and validate user sessions that accesses both type of pages.

apex authentication

A reader, February 19, 2012 - 8:02 pm UTC


It seems you made a big jump with APEX 4.0 over the older versions.

Apex 4.0 supports 3 preconfigured authentication schemes and one of them can use a custom function for authentication using USER accounts stored in a table.

If I have some web pages developed using pl/sql web toolkit and new web pages using APEX,can I integrate both using under one authentication and session management function.

I was wondering if the custom authentication is ONLY for initial authentication and does not include session management for every page.

What i want is a COMMON custom session table to store and validate user sessions that accesses both type of pages.

Apex session information

A reader, April 18, 2012 - 5:42 pm UTC


Do you know the tables/views where APEX 4.1 stores all the session information internally?


Assuming I add a link for another modplsql web appliction to an apex page where all users authenticate using the apex page, can I have all the non-apex pages (other app accessible using apex) use the apex session information for session management.

Can this be done where apex pages and non-apex pages use the same session info in apex?

Tom Kyte
April 19, 2012 - 5:16 am UTC


A reader, April 22, 2012 - 10:39 pm UTC


I never had any luck with this forum. I posted the question for 3 days and not one single comment. I do not think it is run by experts.

I will check the documentation. Does oracle normally list all the tables and views and the column descriptions and how they store things internally.
Tom Kyte
April 23, 2012 - 1:21 pm UTC

Umm, Sam, trust me - the guys that write APEX monitor that. If your question is reasonable - they will answer it.

No, we do not document our internal structures.

that's probably ...

A reader, April 23, 2012 - 4:02 pm UTC

why they're called "internal "


A reader, April 25, 2012 - 2:52 pm UTC


I found it. It is this table.

The interesting thing I found is that apex leaves "EXPIRES_ON" blank and does not even update the LAST_CHANGED timestamp when I navigate to the application. All page visits are logged into APEX_WORKSPACE_ACTIVITY_LOG so the last activity time can be fetched from that view.

How do you explain that? Does not apex update the expire session time with every visit to an apex page or is it FIXED time from the time you log in?

I was also able to update data using the SYS account which means another non-apex pl/sql application with proper grants can update the apex session table.

desc apex_040000.wwv_flow_sessions$
Name Data Type
------------------------------ ------------------------------
Tom Kyte
April 25, 2012 - 3:13 pm UTC

I'm not going to talk about undocumented features Sam, you know better.

do not use sys. just stop that right now. seriously. what are you thinking?

do not update this table. just stop that right now. seriously. what are you thinking? stop it. You'll break things in new and exciting ways.


A reader, April 25, 2012 - 3:29 pm UTC


I am just playing with this on my own PC. Nothing formal.

I agree with you 100% I should not have another app update this table. However, are you OK if another pl/sql (non-apex)application used the APEX API to update session info.

I want to use the Apex cookie to validate pl/sql web toolkit pages so that users do not have to login to two separate applications. Users then can log into the new APEX application and can still use the old non-apex application using apex session id.
Tom Kyte
April 25, 2012 - 4:31 pm UTC


I am just playing with this on my own PC. Nothing formal.

does not matter.

you can use the apex forums to ask this question, as stated above.


A reader, May 01, 2012 - 5:42 pm UTC

what do you think of the idea of apex running on its own database on a machine (shared environment) and having applications connect to the main databases using a DB link?

I do not like it because it separates the apex application from the data and makes everything runs over the db link.

DO you always recommend for apex applications to run inside each Database so if a machine has 10 databases you would have 10 apex engines.
Tom Kyte
May 02, 2012 - 12:40 pm UTC

I think that would be a really bad idea for performance reasons.

If you have a machine that has 10 databases - they would each be running apex yes - I wouldn't call it an "engine", they would just all be running code.

You'd only need one apache mod_plsql set up.


A reader, May 04, 2012 - 10:42 am UTC


Would every sql statement in apex using db link be opening a new connection/session or just the first call?

it sounds also there might be a security hole since all the links in apex would have userid/password for each database.
Tom Kyte
May 06, 2012 - 2:43 pm UTC

first, database links do not have to have a userid/password associated with them - never have.

Second - the database link would be associated with a connection - if mod_plsql started a new connection - a new dblink would be opened and stay opened with that connection typically until you destroy the session or manually close the dblink.


A reader, May 07, 2012 - 11:55 pm UTC


I lost you! Look at the syntax to create a db link. you need the userid/password for target remote database.

If apex uses mod_plsql, every new webpage call is a new session in oracle. Does that mean a new connection with every webpage call that uses a db link.
Tom Kyte
May 08, 2012 - 11:14 am UTC

they are optional sam, learn to read the write diagrams. When you see the little train tracks going up - off the main line - they are optional.

scott%ORA11GR2> create database link current_user@ora11gr2 using 'ora11gr2_2';

Database link created.

scott%ORA11GR2> select * from emp@current_user@ora11gr2;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300


sure seems to work without a username and password.

It uses the credentials you are logged in with to log into the other databases.

If apex uses mod_plsql, every new webpage call is a new session in oracle.

umm, no - no it is not. mod_plsql uses a connection pool.

A reader, May 08, 2012 - 11:47 am UTC

Just curious ( may be dumb ) question ... how do you know the reviewer was Sam ? All I see is reviewer is a reader...

they are optional sam......
Tom Kyte
May 08, 2012 - 12:32 pm UTC

People that review leave behind cookies, ip addresses, email addresses....


A reader, May 08, 2012 - 10:52 pm UTC

Does not your connect sting have userid/password in it?

If not, what kind of security is this? any one can create a link to target database using connect string??
Tom Kyte
May 10, 2012 - 3:03 am UTC


please read the documentation.

please try it out

show me how a connection string can have a password in it?

the site you pointed to is showing how to log in using various programming languages. It is not even remotely relevant to anything here.

I demonstrated a database link with NO username, with NO password, right above. Cannot you see that????

If not, what kind of security is this? any one can create a link to target
database using connect string??

I told you how this works, it uses the CURRENTLY LOGGED IN username along with the CURRENTLY LOGGED IN password and uses that to connect to the remote database.

apex dev/test environment

A reader, May 22, 2012 - 10:29 am UTC


I need a recommendation(pros/cons) on setting up a development and reporting environment for apex 4.1.1. I have it installed and configured successfully in 11gR2 database.

For example, if i have one database schema with 200 tables and 5 developers want to develop web pages and reports against that database schema, would you do #1 or #2:

1) Create 5 workspaces (joe, mike, cindy, tom, henry) and assign each user his own workspace for development.

2) Create 1 workspace for application and create 5 apex user accounts and assign them to that workspace. All users will log in to same workspace.

Tom Kyte
May 22, 2012 - 10:59 am UTC


workspace for an application.

possible workspaces for developers to play in.


A reader, May 22, 2012 - 11:11 am UTC

if I understand your answer correctly, you would setup one workspace for HR application (for example) and create 5 users development accounts associated with it?

On top of that, you setup 5 individual workspaces for each developer to play with?

Is this what you mean?

Tom Kyte
May 22, 2012 - 2:32 pm UTC

Sam -

I said one workspace, one application

you might set up 'play' workspace(s) for the developers to test ideas in.

so, set up A workspace for the application proper.

maybe, if they need them, if they want them, workspace(s) for the developers to "play" in.


sara, January 21, 2022 - 6:27 am UTC


Would every sql statement in apex using db link be opening a new connection/session or just the first call?

it sounds also there might be a security hole since all the links in apex would have userid/password for each database.
Chris Saxon
January 21, 2022 - 11:42 am UTC

Would every sql statement in apex using db link be opening a new connection/session or just the first call?

DB links in APEX work the same as every other environment. Exactly when they'll open new connections depends on whether you're using shared/dedicated links, whether you're closing links explicitly, and how your connection pool is configured.

it sounds also there might be a security hole since all the links in apex would have userid/password for each database.

Why would that be the case? The passwords are securely hashed, not stored in plaintext! You can also create public DB links and current user links that have no associated password. Again, this would be a general problem with DB links, not specific to APEX.

That said you do need to be careful with DB links. They enable more to access your database, often in non-obvious ways. So can introduce security loopholes if you're careless (e.g. if you have a DB link from test/dev to production databases).

sara, January 21, 2022 - 6:29 am UTC

what do you think of the idea of apex running on its own database on a machine (shared environment) and having applications connect to the main databases using a DB link?

I do not like it because it separates the apex application from the data and makes everything runs over the db link.

DO you always recommend for apex applications to run inside each Database so if a machine has 10 databases you would have 10 apex engines.

Chris Saxon
January 21, 2022 - 11:54 am UTC

I don't like the idea of doing all access via DB links either. This introduces complexity making the applications harder to understand, debug, etc.

Instead I'd configure APEX to use ORDS and use that to connect to the databases

How To Download Tabluar format data from a static website?

denial, February 22, 2022 - 6:37 am UTC

I want to download the data from the and

Select * From wp_post where id="Post_id"?

Is it possible through it or other solutions are available in php and python?
Connor McDonald
February 24, 2022 - 7:27 am UTC

I'm a bit lost - I don't see any tabular data on those sites - just free text and html.

Typically if a site wants to offer data it will have a REST interface, otherwise you can still download it as html, but then you need to parse the html to extract the data.