I have a java object
Abhijit, March 17, 2003 - 6:11 am UTC
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,
March 17, 2003 - 9:21 am UTC
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
Dan White, December 10, 2003 - 9:56 am UTC
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)
December 10, 2003 - 3:41 pm UTC
not in 9ir2 and before, not really.
Can all java files be converted to stores procedures? limitations any?
A reader, July 07, 2004 - 8:53 am UTC
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
July 07, 2004 - 9:02 am UTC
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?
j., July 30, 2004 - 9:09 am UTC
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?
July 30, 2004 - 4:57 pm UTC
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 ...
j., July 30, 2004 - 5:48 pm UTC
... 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?
July 30, 2004 - 6:20 pm UTC
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 ...
j., July 31, 2004 - 9:01 am UTC
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?
July 31, 2004 - 12:17 pm UTC
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 ...
j., August 01, 2004 - 6:19 am UTC
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?
August 01, 2004 - 10:59 am UTC
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?
j., August 01, 2004 - 6:57 am UTC
(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)
August 01, 2004 - 11:02 am UTC
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 ...
j., August 01, 2004 - 2:08 pm UTC
(i would have found the answer there)
thank you for your patience.
RMI from Java Stored Procedure
A Reader, December 06, 2004 - 5:48 pm UTC
Hi Tom,
I heard that we cannot call RMI from the stored procedures in Oracle 9.2. Is this true?
Thanks!
December 07, 2004 - 9:51 am UTC
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
Shuchi, May 12, 2006 - 1:02 am UTC
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
May 12, 2006 - 9:26 am UTC
benchmark it - if it is "faster than fast enough...."
How to debug a java stored procedure
A reader, April 29, 2019 - 7:12 pm UTC
Hi,
I need to debug and see the output of some functions like, logWriter.write();
or System.out.println();
or see the stackTrace.
How and where I could see those outputs?
How can I debug the java stored procedure?
Thanks
April 30, 2019 - 2:30 am UTC
In SQLPlus you can do:
set serveroutput on
exec dbms_java.set_output(nn)