Mapping java type string[] to oracle type

Question

There's some java class, loaded to Oracle database (version 11g), and pl/sql function-wrapper:

create or replace function getDataFromJava( in_uListOfSms  in tStringArray )
return tStringArray    
as language java name 'JavaClass.getSms( java.lang.String[]) return java.lang.String[]';    

as you can see from example I try to match java datatype 'java.lang.String[]' to Oracle datatype:

create or replace type tStringArray as table of varchar2(4000);

and then when I run this code:

declare
  in_array  tStringArray := tStringArray();    
  out_array tStringArray := tStringArray();    
begin    

  in_array.EXTEND;    
  in_array(1) := '1';    
  in_array.EXTEND;    
  in_array(2) := '2';    
  in_array.EXTEND;    
  in_array(3) := '3';    

    out_array := getDataFromJava( in_array );    

      for i in 1..3    
      loop    
        dbms_output.put_line(out_array(i));    
      end loop;    
end;    

as a result I get error:

ORA-00932: inconsistent datatypes expected a java type at argument position 2 to which some Oracle value can be converted got something else   

Answer 1

Well, I've done this! The key mistake in my approach was impression that java datatype String[] should match with oracle dstatape based on "table of varchar2". That's wrong. If you have to do in/out parameters as array of strins/numbers/dates you should use oracle.sql.ARRAY datatype. Here small example of this approach:

1. Create oracle datatype:

   CREATE OR REPLACE TYPE tFilesList AS TABLE OF VARCHAR2(2000);

2. Create method which returns oracle.sql.ARRAY:

   create or replace and compile java source named "DirInfo" as

import java.io.File;    
import java.sql.*;    
import oracle.sql.*;    
import oracle.jdbc.*;    

public class DirInfo {    

  public static oracle.sql.ARRAY getFilesList (String dirName) throws java.sql.SQLException {    

      Connection orclConnection = new OracleDriver().defaultConnection();    

      ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor ("TFILESLIST", orclConnection);    

      File filesDir = new File (dirName);    

      String[] filesList = filesDir.list();    

      ARRAY dirArray = new ARRAY(arrayDesc, orclConnection, filesList);    

      return dirArray;    
  }
}

3. Create pl/sql function-wrapper:

   create or replace function getDirList (dirName in varchar2)
   return tFilesList
   as
   language java name 'DirInfo.getFilesList(java.lang.String) return
   oracle.sql.ARRAY';

4. Test:

 declare    
       myDdir tFilesList;    
    begin    
      myDdir := getDirList ('C:\Program Files');    
        for i in 1..myDdir.COUNT    
        loop    
          dbms_output.put_line(myDdir(i));    
        end loop;    
    end;