Home » Developer & Programmer » JDeveloper, Java & XML » How to read a list returned by Java in PL/SQL?
How to read a list returned by Java in PL/SQL? [message #301809] Thu, 21 February 2008 17:19 Go to next message
doublezz
Messages: 3
Registered: February 2008
Junior Member
Hi All,

I think I really need some help since I have stuck on this problem for the whole day and anyone who could help will be really appreciated.

I am currently having my PL/SQL call a java method to get a list of String, I created the Java code in PL/SQL as the following:

create or replace java source named "Generator" as
public class Generator {
public static java.util.List getNumberList(java.lang.String rangeStart, java.lang.String rangeEnd) throws java.lang.Exception {
...
}

The java class is compiled and created in PL/SQL successfully.

The I need to have my PL/SQL function to call this Java code, I use a cursor to read the List:
create or replace package pkg_list
as
type pkg_no is ref cursor;
end pkg_list;

create or replace function generateNo (start_number VARCHAR2, end_number VARCHAR2) return pkg_list.pkg_no
as language java name
'Generator.getNumberList(java.lang.String,java.lang.String) return java.util.List';

The function is created without problem.

Then I start to call the function in my procedure:

CURSOR myList is (select generateNo (no_start, no_end) from dual);
BEGIN
FOR infringement_no IN myList LOOP
...
END LOOP;
END;

The procedure just gives me an error message:
PLS-00989: Cursor Variable in record, object, or collection is not supported by this release

Can anyone please give me a light to resolve this problem? Or at least some suggestion that I can read the Java List in PL/SQL?

By the way, I cannot upgrade the database.

Thank you.
Regards,
Zhan
Re: How to read a list returned by Java in PL/SQL? [message #301820 is a reply to message #301809] Thu, 21 February 2008 20:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> Or at least some suggestion that I can read the Java List
What exactly is a "Java List"?
Re: How to read a list returned by Java in PL/SQL? [message #301821 is a reply to message #301820] Thu, 21 February 2008 20:03 Go to previous messageGo to next message
doublezz
Messages: 3
Registered: February 2008
Junior Member
java.util.List
Re: How to read a list returned by Java in PL/SQL? [message #301835 is a reply to message #301821] Thu, 21 February 2008 23:41 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
I am not positive, but I am pretty sure you can't do it. It's not a perfect analogy, but we pass "lists" throughout our application from the java layer to the DB. We cast the list in java to a DB object type.

But I'm curious though as to why you are using java to generate a list. Is it really so complicated you can't just use simple PL/SQL or SQL?
Re: How to read a list returned by Java in PL/SQL? [message #301844 is a reply to message #301809] Fri, 22 February 2008 00:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If what you are searching for is a row or number generator, search for "row generator" here.

Regards
Michel
Re: How to read a list returned by Java in PL/SQL? [message #301846 is a reply to message #301809] Fri, 22 February 2008 00:11 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
doublezz wrote on Fri, 22 February 2008 00:19

Then I start to call the function in my procedure:

CURSOR myList is (select generateNo (no_start, no_end) from dual);
BEGIN
FOR infringement_no IN myList LOOP
...
END LOOP;
END;

The procedure just gives me an error message:
PLS-00989: Cursor Variable in record, object, or collection is not supported by this release

Which line throws that error?

Quote:

By the way, I cannot upgrade the database.

What version are you on now?
Re: How to read a list returned by Java in PL/SQL? [message #301851 is a reply to message #301809] Fri, 22 February 2008 00:20 Go to previous message
doublezz
Messages: 3
Registered: February 2008
Junior Member
Hi All,
Thanks for your replies, I have resolve the problem after another 8 hours' investigation.

The solution is to change the java class to output a java.lang.String array instead of a java.util.List
For example:
public static void getList(java.lang.String rangeStart, java.lang.String rangeEnd,oracle.sql.ARRAY[] vReturnArray) {
...
String[] vTestArray = YOUR STRING ARRAY;
java.sql.Connection conn = new oracle.jdbc.driver.OracleDriver().defaultConnection();
oracle.sql.ArrayDescriptor desc=oracle.sql.ArrayDescriptor.createDescriptor("SIMPLESTRINGARRAYTYPE",conn);
vReturnArray[0] = new oracle.sql.ARRAY(desc,conn,vTestArray);
}

then define a procedure to receive the array:
CREATE OR REPLACE TYPE SimpleStringArrayType AS TABLE OF VARCHAR2(12);
create or replace PROCEDURE ObtainStringArrayFromJava(start_number VARCHAR2, end_number VARCHAR2, vReturnArray OUT SimpleStringArrayType) AS
LANGUAGE JAVA NAME 'MyClass.getList(java.lang.String,java.lang.String,oracle.sql.ARRAY[])';

[Updated on: Fri, 22 February 2008 00:21]

Report message to a moderator

Previous Topic: Cursor FOR LOOP Issue
Next Topic: create and load a value from any xml file
Goto Forum:
  


Current Time: Fri Mar 29 02:33:36 CDT 2024