Java Stored Procedures

[Hawkeye]

I am investigating the Java Stored Procedure, first to gain better
understanding on what this is,
and also if it would be suitable for a new project which will be running on
Sun One AS7
(Client is Swing)

I have spent about a day reading through many docs, mostly in Oracle site
and also the
past newsgroup discussions. Still, I am not clear really on who this Java
Stored Proc
technology is intended for. Most of the example are toy examples really.
Any consensus on who this technology is aimed at and who should really be
using it?
Would you use Java Stored Proc and have it contain lots of JDBC calls inside
it and have it be
called either by a client or a EJB session bean?

Any info based on experience would be helpful

THANKS

[Carlos]

"Hawkeye" <[e-mail]> wrote in message news:<iYVXb.409 26$yE5.160182@a ttbi_s54>...

I am investigating the Java Stored Procedure, first to gain better
understanding on what this is,
and also if it would be suitable for a new project which will be running on
Sun One AS7
(Client is Swing)

I have spent about a day reading through many docs, mostly in Oracle site
and also the
past newsgroup discussions. Still, I am not clear really on who this Java
Stored Proc
technology is intended for. Most of the example are toy examples really.
Any consensus on who this technology is aimed at and who should really be
using it?
Would you use Java Stored Proc and have it contain lots of JDBC calls inside
it and have it be
called either by a client or a EJB session bean?

Any info based on experience would be helpful

THANKS

The power of including java in the DB is difficult to explain but easy
to understand for those who know Oracle and have experience in working
on it. Basicaly, it opens a new world of functionalities that can be
implemented inside the database just there, where PL/SQL lacks.
It DOES NOT NEED to make JDBC calls to work, it can work without them
(nevertheless, the built-in JDBC driver works quite fast).

I will give you an example that is currently working OK in our DB:

We needed a CRC to secure output XML files. Did we progammed a PL/SQL
code to implement it? No. We used a java class that does it for us
(CRC) and loaded it into the DB. Then we wrapped the class in a PL/SQL
package and now we can call for the CRC for any object we need
(varchar, clob, etc...). It took us 20 minutes all the process.

Note that this is how many built-in Oracle procedures are stored.

Regards.

[Hawkeye]

"Carlos" <mi************ **@netscape.net > wrote in message
news:1d******** *************** ***@posting.goo gle.com...

"Hawkeye" <[e-mail]> wrote in message news:<iYVXb.409 26$yE5.160182@a ttbi_s54>...

I am investigating the Java Stored Procedure, first to gain better
understanding on what this is,
and also if it would be suitable for a new project which will be running on Sun One AS7
(Client is Swing)

I have spent about a day reading through many docs, mostly in Oracle site and also the
past newsgroup discussions. Still, I am not clear really on who this Java Stored Proc
technology is intended for. Most of the example are toy examples really.
Any consensus on who this technology is aimed at and who should really be using it?
Would you use Java Stored Proc and have it contain lots of JDBC calls inside it and have it be
called either by a client or a EJB session bean?

Any info based on experience would be helpful

THANKS

The power of including java in the DB is difficult to explain but easy
to understand for those who know Oracle and have experience in working
on it. Basicaly, it opens a new world of functionalities that can be
implemented inside the database just there, where PL/SQL lacks.
It DOES NOT NEED to make JDBC calls to work, it can work without them
(nevertheless, the built-in JDBC driver works quite fast).

I will give you an example that is currently working OK in our DB:

We needed a CRC to secure output XML files. Did we progammed a PL/SQL
code to implement it? No. We used a java class that does it for us
(CRC) and loaded it into the DB. Then we wrapped the class in a PL/SQL
package and now we can call for the CRC for any object we need
(varchar, clob, etc...). It took us 20 minutes all the process.

Does your java code contain any calls to the DB? if so, does it use JDBC
and/or SQLJ?
Or is it just java "helper" code

Note that this is how many built-in Oracle procedures are stored.

Regards.

[John]

My experience with JSPs is that JDBC calls to the databse are about
3.5X slower than the same SQL implemented in PL/SQL. Oracle Support
confirmed that these numbers are what you should expect when running a
side by side test between PL/SQL and JSPs. If you are going to write
SQL code that will need to perform optimally, JSPs may not be the
answer. If your code does not perform SQL or the overhead of the JDBC
call is acceptable, then java makes a lot of sense.

"Hawkeye" <[e-mail]> wrote in message news:<iYVXb.409 26$yE5.160182@a ttbi_s54>...

I am investigating the Java Stored Procedure, first to gain better
understanding on what this is,
and also if it would be suitable for a new project which will be running on
Sun One AS7
(Client is Swing)

I have spent about a day reading through many docs, mostly in Oracle site
and also the
past newsgroup discussions. Still, I am not clear really on who this Java
Stored Proc
technology is intended for. Most of the example are toy examples really.
Any consensus on who this technology is aimed at and who should really be
using it?
Would you use Java Stored Proc and have it contain lots of JDBC calls inside
it and have it be
called either by a client or a EJB session bean?

Any info based on experience would be helpful

THANKS

[x]

I read somewhere in documetation that PL/SQL is better for
database-intensive operations, and JSP for logic-intensive operations.

I can tell you what I use JSP for. We have some XML exchange with our
vendors. Every time new order is created, trigger calls JSP that creates XML
and sends it to servlet on Apache/Tomcat server (outside firewall) which
forwards XML to our vendors. With java classes, it's very easy to program
things like that. Possible that it can be done with PL/SQL, but this way
it's easyer.

Right now, I'm planing to connect my oracle with non-oracle database. Since
I'm on UNIX, lack of ODBC driver prevents me from using heterogeneous
connectivity. I'm trying to load JDBC driver for this non-Oracle database,
and connecto to it through JSP.

[Jay Grubb]

What is the DB you need to connect to? I work for an ODBC Driver
vendor (Openlink Software) and we are used in Oracle HS on a regular
basis. The drivers can be less expensive than you might think. Visit
our Website www.openlinksw.com , Or email me directly if you need any
answers.

"x" <[e-mail]> wrote in message news:<c0******* ***@ls219.htnet .hr>...

I read somewhere in documetation that PL/SQL is better for
database-intensive operations, and JSP for logic-intensive operations.

I can tell you what I use JSP for. We have some XML exchange with our
vendors. Every time new order is created, trigger calls JSP that creates XML
and sends it to servlet on Apache/Tomcat server (outside firewall) which
forwards XML to our vendors. With java classes, it's very easy to program
things like that. Possible that it can be done with PL/SQL, but this way
it's easyer.

Right now, I'm planing to connect my oracle with non-oracle database. Since
I'm on UNIX, lack of ODBC driver prevents me from using heterogeneous
connectivity. I'm trying to load JDBC driver for this non-Oracle database,
and connecto to it through JSP.

[Galen Boyer]

On Mon, 16 Feb 2004, [e-mail] wrote:

I am investigating the Java Stored Procedure, first to gain
better understanding on what this is, and also if it would be
suitable for a new project which will be running on Sun One AS7
(Client is Swing)

I have spent about a day reading through many docs, mostly in
Oracle site and also the past newsgroup discussions. Still, I
am not clear really on who this Java Stored Proc technology is
intended for. Most of the example are toy examples really. Any
consensus on who this technology is aimed at and who should
really be using it?
I can tell you how we are using it. We are converting an applet
based application into a J2EE with Oracle backend. The old
applet actually has alot of data coded in its java code (They
later tried to retrofit this into a relational store). Instead
of rewriting all of this hardwired code, we are judiciously
loading key java code into Oracle and calling it directly.

Would you use Java Stored Proc and have it contain lots of JDBC
calls inside it and have it be called either by a client or a
EJB session bean?

I don't think I would recommend the java making jdbc calls. I
would recommend using it when PL/SQL doesn't support your needs.

--
Galen Boyer

[David Morse]

FirstSQL/J uses Java as the SP language - static methods are SPs and can be
called anywhere from standard SQL.

See www.firstsql.com
Dave M.
"Hawkeye" <[e-mail]> wrote in message
news:iYVXb.4092 6$yE5.160182@at tbi_s54...

I am investigating the Java Stored Procedure, first to gain better
understanding on what this is,
and also if it would be suitable for a new project which will be running on Sun One AS7
(Client is Swing)

I have spent about a day reading through many docs, mostly in Oracle site
and also the
past newsgroup discussions. Still, I am not clear really on who this Java
Stored Proc
technology is intended for. Most of the example are toy examples really.
Any consensus on who this technology is aimed at and who should really be
using it?
Would you use Java Stored Proc and have it contain lots of JDBC calls inside it and have it be
called either by a client or a EJB session bean?

Any info based on experience would be helpful

THANKS