External Procedure or Java Stored Procedure

External Procedure or Java Stored Procedure

Post by Steve Johnso » Wed, 14 Nov 2001 03:19:40



I have a program written in either (C++ or Java) which I need to call
from an Oracle Stored Procedure.

Is it more efficient to call the program as an external procedure (C++)
or put the Java into the database and call a Java Stored Procedure?
This program will get called very frequently, possibly thousands of
times a day.  I'm using Oracle 8.1.6.3 and HP 11.

What are the Pro's and Con's?

Any help would be appreciated.

Thanks...

 
 
 

External Procedure or Java Stored Procedure

Post by Howard J. Roger » Wed, 14 Nov 2001 05:37:05


Don't necessarily rely on this until you et confirmation from someone else,
but a new feature of 9i is the ability to have external procedures called
via dedicated agents.  Until 9i, all external procedure calls were handled
solely by the one agent, EXTPROC.  That's not a very scalable solution, and
it's not very robust either (one dodgy external routine can bring down
EXTPROC, and thus bring down all others).

For that reason alone, I would have thought Java Stored Procedures were the
way to go.

<sits back and awaits the contradictions! :-)>
Regards
HJR
--
Resources for Oracle: http://www.hjrdba.com
===============================


Quote:> I have a program written in either (C++ or Java) which I need to call
> from an Oracle Stored Procedure.

> Is it more efficient to call the program as an external procedure (C++)
> or put the Java into the database and call a Java Stored Procedure?
> This program will get called very frequently, possibly thousands of
> times a day.  I'm using Oracle 8.1.6.3 and HP 11.

> What are the Pro's and Con's?

> Any help would be appreciated.

> Thanks...


 
 
 

External Procedure or Java Stored Procedure

Post by Spence » Wed, 14 Nov 2001 07:25:08


FYI

in preliminary testing of "external procedures" (written in C)  with
Oracle 8.0.6.0.0 (32-bit) on HP-UX 11.0, the memory used by a
session increased over 10MB.  this was 10MB memory for each
session that made a call to the external procedure. yikes!

things may have changed with 8i (and 9i)... the biggest change
being, of course, the introduction of java.



> Don't necessarily rely on this until you et confirmation from someone
else,
> but a new feature of 9i is the ability to have external procedures called
> via dedicated agents.  Until 9i, all external procedure calls were handled
> solely by the one agent, EXTPROC.  That's not a very scalable solution,
and
> it's not very robust either (one dodgy external routine can bring down
> EXTPROC, and thus bring down all others).

> For that reason alone, I would have thought Java Stored Procedures were
the
> way to go.

> <sits back and awaits the contradictions! :-)>
> Regards
> HJR
> --
> Resources for Oracle: http://www.hjrdba.com
> ===============================



> > I have a program written in either (C++ or Java) which I need to call
> > from an Oracle Stored Procedure.

> > Is it more efficient to call the program as an external procedure (C++)
> > or put the Java into the database and call a Java Stored Procedure?
> > This program will get called very frequently, possibly thousands of
> > times a day.  I'm using Oracle 8.1.6.3 and HP 11.

> > What are the Pro's and Con's?

> > Any help would be appreciated.

> > Thanks...

 
 
 

1. Calling a Java Stored Procedure from another Java Stored Stored Procedure

Hi,
I'm using the stored procedure builder of DB2 UDB v6.1 on NT to create a
Java Stored Procedure to call another Java Stored Procedure. Both of
them belong to the same project in stored procedure builder.
The sp that calls another sp has the code as follows:
// Calling another java sp -- ErrorHandler
ErrorHandler err = new ErrorHandler();
err.execute("Test #1", 100, "Testing #1");
When I want to build the sp which calls another sp from within, it gave
me an error as follows:
C:\IBMVJava\ide\tools\com-ibm-db2-tools-dev-spb-ivj\spb\bld953747549650\
com\intertrac\datamart\sp\SelectBillShipAddr.java:18: Class
com.intertrac.datamart.sp.ErrorHandler not found in type declaration.
ErrorHandler err = new ErrorHandler();
Does anyone have any clue of how this can be properly done ? Please let
me know.
Thanks,
ra

Sent via Deja.com http://www.deja.com/
Before you buy.

2. SHOWCONTIG output "stutter"

3. Gettting SQL0751N trying to call java stored procedure from a java stored procedure

4. .. Y2K and databases

5. External Stored Procedure vs. SQL Stored Procedure

6. Paradox AutoInc Field - Issue

7. Java stored procedure and calls to external JDBC source

8. Short-term Project Opportunity

9. Calling Java Stored Procedure from SQL Stored Procedure

10. trigger problem in calling java external procedure

11. calling and passing data from database to the external procedures written in C,C++ or java

12. Calling Java code on disc from a Java Stored Procedure