| How to read a list returned by Java in PL/SQL? [message #301809] |
Thu, 21 February 2008 17:19  |
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 #301835 is a reply to message #301821] |
Thu, 21 February 2008 23:41  |
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 #301846 is a reply to message #301809] |
Fri, 22 February 2008 00:11 |
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 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
