I have a Java stored procedure which returns an array (oracle.sql.ARRAY) which maps to a nested table type in PL/SQL.
It works fine for a relatively small number of elements, but for larger number, results in 'ORA-03113: end-of-file on communication channel' and ORA-07445: exception encountered: core dump [kgmealo()+32] [SIGSEGV] [Address not mapped to object] [20] [] [] in the alert log.
Here's my code:
1. Create a named type:
create type TEST_TABLE_TYPE as table of varchar2(10);
/
2. Create a Java class / method to return an array (and use loadjava to load it into the database):
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class ArrayTest {
public static oracle.sql.ARRAY getArray (int aNum) throws SQLException {
OracleDriver ora = new OracleDriver();
Connection conn = ora.defaultConnection();
String [] stringArray = new String[aNum];
/* Create an array of Strings with aNum items */
for(int i=0;i<aNum;i++)
stringArray[i] = "Item " + Integer.toString(i);
ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor( "TEST_TABLE_TYPE", conn );
ARRAY testArray = new ARRAY(arraydesc, conn, stringArray);
return testArray;
}
}
3. Create at PL/SQL function spec:
create or replace function get_test_array(in_num_items IN number) return TEST_TABLE_TYPE
as LANGUAGE JAVA
NAME 'ArrayTest.getArray(int) return oracle.sql.ARRAY';
/
4. Create a test script:
declare
myTab TEST_TABLE_TYPE;
begin
-- Prompt the user for a number of items
myTab := get_test_array(&aNumber);
for i in myTab.FIRST..myTab.LAST loop
dbms_output.put_line(i || ' ' || myTab(i));
end loop;
end;
/
When running the test script in my environment (8.1.7 on Solaris 8), the code works as expected for 124 elements. When run for a larger number, I get the errors mentioned above. Incidentally, I have also tried the same code using a VARRAY type instead of a TABLE and experience the same errors.
Am I doing something wrong?
Any help would be greatly appreciated.