How to handle string array returned from Java function in PL/SQL...?

[slicendice]

Hi

I've got a function in Java that returns a string array but I'm not sure how to reference this in PL/SQL (I am a total beginner when it comes to Java!). The Java function is as follows:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "GetDirListArray" AS
    import java.io.*;
    import java.sql.*;

    public class GetDirListArray
    {
        public static String[] getList(String directory)
                           throws SQLException
        {
            File path = new File( directory );
            return (path.list());
        }
    }

What I want to do is use this function from within a PL/SQL program. I've created a type in Oracle as follows (to handle the string array returned by the Java function):

CREATE OR REPLACE TYPE typDirList IS TABLE OF VARCHAR2(200)

I've then tried to create an Oracle function for the Java function:

CREATE OR REPLACE FUNCTION get_dir_list_array(p_directory IN VARCHAR2) RETURN typDirList AS LANGUAGE JAVA
   NAME 'GetDirListArray.GetList( java.lang.String )';

But when I run this I get:

LINE/COL ERROR
-------- ------------------------------------------------------------------------------------------------------
2/4      PLS-00311: the declaration of "GetDirListArray.GetList( java.lang.String )" is incomplete or malformed
0/0      PL/SQL: Compilation unit analysis terminated

Can someone give me any pointers as to what I'm doing wrong? Or even if what I'm trying to do is possible!

Many thanks

 

[slicendice]

OK, I just realised that I forgot to include a return type for the Java function....doh!

Altered to:

CREATE OR REPLACE FUNCTION get_dir_list_array(p_directory IN VARCHAR2) RETURN typDirList AS LANGUAGE JAVA
   NAME 'GetDirListArray.GetList( java.lang.String ) return String[]';

Compiles OK now...surprisingly!

No idea if it will work though - that's the next task!