2

I have a type abc_type and an array of type abc_table. I am trying to pass an object array to a java function.

create type abc_type authid definer as object
  ( file_name    varchar2(5000),
    file_size    number,
    file_paths   varchar2(4000)
    );
create type abc_table as table of abc_type;

create or replace and compile java source named "Hello" as
public class Hello
{
  public static String world(String str,Array str2)
  {
    return "Hello world - "+ str;
  }
}
/

CREATE OR REPLACE FUNCTION helloworld (
str_in in varchar2,
str2_in in abc_table
)
RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'Hello.world (java.lang.String,oracle.sql.Array) return java.lang.String';
/

declare 
str varchar2(200):='def';
zipfiles abc_table:=abc_table();
begin
zipfiles.extend(1);
zipfiles(1) := abc_type('aaa',22,'bbb');
dbms_output.put_line('test:'||helloworld('abc',zipfiles));
end;
/

Everything compiles fine, but I get the error ORA-29541: class .Hello could not be resolved. It works fine if I replace Array type with String/Varchar2.

Heading

jaekid
  • 31
  • 3

2 Answers2

1

Fixed with the following changes

Added the import statements

import oracle.sql.*; // I had added this earlier, but this alone didn't fix the issue.
import java.sql.*;

//With the above, I was able to pass the object, but I was not able to reference the object.

Added exception handling.

throws IOException,java.sql.SQLException to the function.

Final code ( Have a few other changes as well)

create or replace and compile java source named "Hello" as
package abc;

    import oracle.sql.*;
    import java.sql.*;
    import java.io.*;
    import java.util.ArrayList;
    import java.util.zip.*;
    import java.sql.Connection;

    public class Hello extends Object
    {
      public static void world(String str,String[] strout, oracle.sql.ARRAY arr) throws IOException,java.sql.SQLException
      {
          Datum[] ZipTyp = ((ARRAY)arr).getOracleArray();
          strout[0] = "Output - "+ str + ","+arr.getSQLTypeName()+ ","+ java.lang.reflect.Array.getLength(ZipTyp);
      }
    }
    /

    CREATE OR REPLACE PROCEDURE helloworld2 (
    str_in IN varchar2,
    str_out OUT varchar2,
    arr_in in abc_table
    )AS
    LANGUAGE JAVA 
    NAME 'abc.Hello.world (java.lang.String,java.lang.String[],oracle.sql.ARRAY)';
    /

    declare 
      str varchar2(200):='def';
      zipfilelist abc_table := abc_table();
      l_in v_table := v_table();
    begin
      zipfilelist.extend(2);
      zipfilelist(1) := abc_type('aaa1',11,'bbb1');
      zipfilelist(2) := abc_type('aaa2',22,'bbb2');
      helloworld2('abc',str,zipfilelist);
      dbms_output.put_line('test:'||str);
    end;
    /
jaekid
  • 31
  • 3
0

You should use import oracle.sql.ARRAY

create or replace  and COMPILE java source named "Hello" as
import oracle.sql.ARRAY;
public class Hello
{
  public static String world(String str,ARRAY str2)
  {
    return "Hello world - "+ str;
  }
}

I don't think that you can pass a composite type(nested table) to java function from PL/SQL. In your case you are passing abc_type which is of composite data type. As far as I know, nested table or array of one dimension or single data type can only be passed. I am not very sure about it, please do consider my thought.

Kindly check the following link for the same https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8908169959941

Dinesh V
  • 131
  • 4