Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Object Types and Java (migrating from mod_plsql)

Re: Oracle Object Types and Java (migrating from mod_plsql)

From: Thanatos <>
Date: 20 Feb 2005 15:09:04 -0800
Message-ID: <>

Dear Tom,

I've been itching to talk to you about this, thanks for picking it up.

> ahh, but what isn't scaling out?

To be honest the server (some 8cpu Sun monster) is not running too badly.

And admittedly there is still a lot of work that can be done to improve it's performance. We run alot of generic sql (via execute immediate) and it's also been a habbit to write every query as execute immediate. So I'm sure we could get more life out of the old dog yet.

What executive management is worried about is that sometime in the future we will not be able to run any more sql or serve any more html from the server.

They are fearful that we will simply have to buy a larger server, which they view as prohibitively expensive. Buying Oracle-AS is also seen as too expensive. So we have been exploring alternatives.

One alternative is the j2ee framework and the company already has invested a lot into this project. The tag line of the project seems to be:

"We will simply buy some more small, cheap computers to serve the application as we need too."

Given that the research and implementation of this project has taken over a year already, with the purchase of *5* extra computers for an application not 5% as large (or complex, or accessed) we seem to be heading in a very dangerous direction.

>is it "we cannot run anymore sql", well, moving to java isn't going to
make the
>sql run any faster (in fact, my experience is the opposite is true,
the java
>programmers tend not to write the best sql, the database needs become
>then when we had database programmers in there).
>have you identified what isn't "scaling" about your situation? before
>propose a fix, you need to identify exactly what is "broken"

I would say that the need for the server to generate and serve the entire application is what is "broken." We need to split the http serving and the data access.

>> Re-write stored procedures as Oracle Object Types.
> that doesn't make sense -- what do you mean by "rewrite stored
procedures as object types"?

Well currently we have a stored procedure which generates a page via htp.

I am proposing we generate the page data in an object. This object can be initialized from a stored procedure that again uses htp to display the application *or* a java application could initialize the object and display the application using "print."

The point being it would be easier to move pl/sql code into objects rather than rewrite everything in Java. I suppose I am suggesting the application has an API defined through Oracle Object Types.

create or replace type result_t as object ( result_list_html web_arr,
member function display_summary return varchar2, member function display_results return varchar2)


my_results result_t := result_t();


--display results


Or we could write this in another language that can access Oracle Objects (ie Java)

I suggest the benefits of this are:
Very simple Java component - that can be deployed on another server to handle the web serving of the application.

Easier to move pl/sql from an application (stored procedures) into another pl/sql container (Oracle Object Type) the re-write the entire application (in Java, PHP or whatever.)

Does this make anything clearer? As you probably can tell I'm grasping...

Assume the server is at maximum capacity, what would Oracle say about our dilema? Buy a bigger server? Buy Oralce-AS? Both options would appear to be out of the quetsion for our company.

Kind Regards,
James Received on Sun Feb 20 2005 - 17:09:04 CST

Original text of this message