I have a java object
March 17, 2003 - 6am Central time zone
Reviewer: Abhijit from Mumbai, India
Hi Tom.
I have an object inside my Oracle DB for logging errors to a text file. Since I had a ready java
class handy, I have decided to put it in. If I call that stored procedure through the stored procs
inside the DB, it logs correctly.
Now I made one change in the java class and asked it to log the errors in a table instead of a log
file.
I have one question. Can I call this java class (jErrLogger) from my java application, directly?
By directly I mean, without interacting with any other DB object.
How do I do that?
Thanks & Regards,
Followup March 17, 2003 - 9am Central time zone:
you could using rmi and other stuff but -- it would be easiest just to call it as a stored
procedure. I wouldn't use the other methods.
It would be perhaps even more performant to, oh i don't know, use PLSQL to log things into a table?
Java Stored procedures
December 10, 2003 - 9am Central time zone
Reviewer: Dan White from Philadelphia, PA
Tom,
Is it possible to call a java stored procedure directly from a serverlet? The reason I'm asking
is that our developers would like to have the return value of the java stored procedure a java
object(i.e. a hash table)
Followup December 10, 2003 - 3pm Central time zone:
not in 9ir2 and before, not really.
Can all java files be converted to stores procedures? limitations any?
July 7, 2004 - 8am Central time zone
Reviewer: A reader
Tom,
I searched few of your links, could not get what i want. Can you please tell me when we should go
for stored procedures.
Can all java files be converted to stores procedures?. Does it have any limitations?.
Heard that java sp will improve performance, but never got chance to come across near real time
situation to code my java files to java sp.
thanks for your valuable site
Followup July 7, 2004 - 9am Central time zone:
you heard wrong as far as performance goes.
PLSQL is by far the fastest, most efficient, effective, correct and proper language for data
processing inside the Oracle database.
Java is handy when you encounter something that PLSQL cannot do, which is very rare -- given the
needs of stored procedures.
As long as the java code is not "gui based", you can probably load it -- but my question would be
"why".
do not run out and replace all of your plsql with java.
if all you have are java programmers, you need to get people that are "data oriented, not coders".
I'm not against java, I'm against a "there is a single language". Java is a good language for
professional coders writing complex logic outside the database. PLSQL is perfect for dealing with
data and you'll find that the speed with which you are able to develop code in PLSQL doing data
stuff is many times that of coding java.
How to get JAVA objects returned to PL/SQL invoker?
July 30, 2004 - 9am Central time zone
Reviewer: j.
hi tom,
can you please give an example on how to return a JAVA object back to an invoking PL/SQL routine?
in our case the JAVA call is wrapped by a stored function and the JAVA object just contains a
Double and a String.
for us there are at least 3 questions left:
1: do we have to use object types on database side for that?
2: how does one get a JAVA object mapped to such user defined object type?
can you PLEASE help?
3: can PL/SQL functions return such objects at all?
Followup July 30, 2004 - 4pm Central time zone:
you can easily return a double and a string as "scalar outputs" -- no "object" needed. you would
just return them as a number and a varchar2.
the java subroutine would assign its class variables to two out parameters is all.
1) no you don't have to, you can but you need not
2) jpub - see the documentation for jpublisher, but I think it is overkill here.
3) plsql works naturally with object types and plsql types but cannot "see" java types any more
than java can see a plsql record type.
do you have access to "Expert one on one Oracle" -- there i demonstrate how to easily pass:
q Strings (up to 32k)
q Numbers (of any scale/precision)
q Dates
q Integers (binary_integers)
q Raws (up to 32k)
q Lobs (for all data > 32k)
q Arrays of Strings
q Arrays of Numbers
q Arrays of Dates
between plsql and java stored procedures easily (and discuss jpub...)
yes, i've bought & read your great book ...
July 30, 2004 - 5pm Central time zone
Reviewer: j.
... but wasn't sure, whether there would be other options.
meanwhile we've decided to "serialize" the java object into a string and reconstruct the structure
as needed on the database side.
would "jpublisher" help to let the following function accept a java object as return value from the
java call (and map this java object to its corresponding database object)?
function GetResult(<some_string>) return varchar2 as language java
name 'OurPackage.OurClass.OurMethod(java.lang.String) return java.lang.String';
please let us add some questions on what you 've answered:
<quote>
you can easily return a double and a string as "scalar outputs" -- no "object" needed. you would
just return them as a number and a varchar2.
</quote>
yes, but complex (user defined) java objects - apart from that you mentioned in your book - can
*NOT* be returned "as is" from JAVA to PL/SQL, right?
<quote>
the java subroutine would assign its class variables to two out parameters is all.
</quote>
we 've really no clue how to apply this to our case where we want to establish an interface between
our custom java method returning an java object and the wrapping PL/SQL function.
<quote>
1) no you don't have to, you can but you need not
</quote>
can you *please* provide us with an example how database object types could meet our abovementioned
requirements?
<quote>
3) plsql works naturally with object types and plsql types but cannot "see" java types any more
than java can see a plsql record type.
</quote>
... or does all data transfer between JAVA and PL/SQL has to be performed by using the object types
you 've mentioned?
Followup July 30, 2004 - 6pm Central time zone:
just have a procedure return a STRING and a NUMBER?? you have two out parameters -- don't over do
it!
otherwise, for complex structures, you would be using object types and jpublisher.
see the jpublisher docs -- they are in there, examples and all.
but ...
July 31, 2004 - 9am Central time zone
Reviewer: j.
lreturn an object (containing a double and a string) from JAVA back to the invoking PL/SQL routine
is what we are trying to achieve ...
there is no database access (no JDBC and such) at all involved here. it 's all about the interface
between our plain JAVA-method and the wrapping PL/SQL-code.
would you agree, that our abovementioned approach (returning a string containing the attributes of
the JAVA-object instead of the object itself) is the way to go here?
Followup July 31, 2004 - 12pm Central time zone:
no, i've said over and over:
a procedure that returns a number and a string
Your java subroutine -- with its exceedingly simple bits of data -- would return them as two OUT
parameters. period.
anything else would be overkilll. it would be very very simple to just return these two scalars --
a number and a string -- as a number and a string.
really can't imagine HOW ...
August 1, 2004 - 6am Central time zone
Reviewer: j.
sorry, maybe i'm just not familiar enough with that JAVA stuff ...
can you please have a final look at the following sample case?
consider "MyClass" as the one we are going to wrap with our PL/SQL-routine:
public class MyClass {
public static void TransferData() {
MyData data = new MyData();
}
}
method "TransferData" is called from within the PL/SQL-wrapper and is declared to be of type void
since we can't return our user defined type "MyData" from JAVA to PL/SQL. "TransferData" in turn
creates an object of type "MyData", including our number and string values:
public class MyData {
private Double mynum;
private String mystr;
public MyData() {
this.mynum = new Double(1);
this.mystr = "xyz";
}
}
question is how to get these two values (instead of the object itself) returned to the calling
PL/SQL-routine?
as far as i know the only way to get data transferred from JAVA to PL/SQL at all is to use
JAVA-methods of types other than type void. i can't even imagine a JAVA-method "TransferData" that
would "return them as two OUT parameters" ...
could you ***please*** provide the ultimate hint?
Followup August 1, 2004 - 10am Central time zone:
well, in Expert One on One Oracle -- i demonstrated how to pass ALL of these types and more (in
tons of detail). An entire chapter on Java External Routines. I sort of thought that was the
ultimate "hint". but anyway. here is the spoon:
ops$tkyte@ORA9IR2> create or replace package demo_passing_pkg
2 as
3 procedure pass( p_string in out varchar2, p_double in out number )
4 as
5 language java
6 name 'demo_passing_pkg.pass( java.lang.String[], java.lang.Double[] )';
7
8 end demo_passing_pkg;
9 /
Package created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace and compile
2 java source named "demo_passing_pkg"
3 as
4 import java.io.*;
5 import java.sql.*;
6 import java.math.*;
7 import oracle.sql.*;
8 import oracle.jdbc.driver.*;
9
10 public class demo_passing_pkg extends Object
11 {
12
13 public static void pass( java.lang.String[] p_str, java.lang.Double[] p_num )
14 {
15 p_str[0] = p_str[0] + "Hello World";
16 p_num[0] = new Double( 55 );
17 }
18
19 }
20 /
Java created.
ops$tkyte@ORA9IR2> show error
No errors.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable s varchar2(255)
ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> set autoprint on
ops$tkyte@ORA9IR2> exec :s := 'test '; :n := 42;
PL/SQL procedure successfully completed.
N
----------
42
S
-------------------------------------------------------------------------------
test
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec demo_passing_pkg.pass( :s, :n )
PL/SQL procedure successfully completed.
N
----------
55
S
-------------------------------------------------------------------------------
test Hello World
our is there an option to pass references from PL/SQL to JAVA rather then values?
August 1, 2004 - 6am Central time zone
Reviewer: j.
(at least declaring a PL/SQL-parameter of type varchar2 as in/out didn't help to retrieve a value
of JAVA-type String, but caused PLS-00235)
Followup August 1, 2004 - 11am Central time zone:
you say you have Expert one on one Oracle -- I encourage you to read the chapter on java external
routines.
if only i would have looked at page 850 ...
August 1, 2004 - 2pm Central time zone
Reviewer: j.
(i would have found the answer there)
thank you for your patience.
RMI from Java Stored Procedure
December 6, 2004 - 5pm Central time zone
Reviewer: A Reader
Hi Tom,
I heard that we cannot call RMI from the stored procedures in Oracle 9.2. Is this true?
Thanks!
Followup December 7, 2004 - 9am Central time zone:
yes, I believe that protocol was removed from the database server along with EJB's directly in the
database.
Java Stored Procedure vs. PL/SQL
May 12, 2006 - 1am Central time zone
Reviewer: Shuchi from India
Tom,
There are some math/string manipulation functions available in Java, which are not pre-defined in
PL/SQL. It reduces development effort if we simply build a PL/SQL wrapper for the Java code and
call it from PL/SQL.
I doubt though whether this would be the best approach to follow? Are there any drawbacks to
calling Java functions for such operations from PL/SQL? Please advise.
An example to calculate GCD of two numbers:
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "BigInt" AS
2 import java.math.BigInteger;
3 public class BigInt
4 {
5 public static String gcd(String s1, String s2)
6 {
7 BigInteger b1 = new BigInteger(s1);
8 BigInteger b2 = new BigInteger(s2);
9 return b1.gcd(b2).toString();
10 }
11 };
12 /
Java created.
SQL> CREATE OR REPLACE FUNCTION
2 fn_gcd(s1 IN VARCHAR2, s2 IN VARCHAR2) RETURN VARCHAR2
3 AS LANGUAGE JAVA
4 NAME 'BigInt.gcd(java.lang.String, java.lang.String) return java.lang.String';
5 /
Function created.
SQL>
SQL> SELECT fn_gcd(42, 35) from dual
2 /
FN_GCD(42,35)
--------------------------------------------------------------------------------
7
Followup May 12, 2006 - 9am Central time zone:
benchmark it - if it is "faster than fast enough...."
|