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

I think you are looking this stackoverflow.com/questions/7872688/… — Exhausted
– Exhausted, Commented Dec 28, 2014 at 14:00

Geek · Accepted Answer · 2016-08-25 13:50:16Z

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:

Create oracle datatype:

CREATE OR REPLACE TYPE tFilesList AS TABLE OF VARCHAR2(2000);
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;    
  }
}

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';
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;

Collectives™ on Stack Overflow

Mapping java type string[] to oracle type

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related