See How to Java, Pl/Sql Cursor
Create the Java sourceCREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED ALWORKSHOP."myOracle/TestStaticsInOracleServer" as
package myOracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Vector;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class TestStaticsInOracleServer {
private static Vector v = new Vector();
public static void addLineToVector(String line) {
v.add(line);
}
public static int getVectorSize() {
return v.size();
}
/**
* @param structureTypeName For example ALWORKSHOP.TABLEOFVARCHAR2
*/
public static oracle.sql.ARRAY getElementsAsArray(String structureTypeName)
throws SQLException {
Connection c = DriverManager.getConnection("jdbc:default:connection");
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor(
structureTypeName, c);
String[] list = new String[v.size()];
{ /* Create the list */
int i = 0;
for(Enumeration e = v.elements(); e.hasMoreElements();) {
String s = (String)e.nextElement();
list[i++] = s;
}
}
ARRAY result = new ARRAY(descriptor, c, list);
return result;
}
}
|
Create package headCREATE OR REPLACE PACKAGE myjavautils AUTHID CURRENT_USER AS -- TEST -- procedure addLineToVector(line in varchar2); function getVectorSize return number; function getElementsAsArray(structureTypeName in varchar2) return TABLEOFVARCHAR2; END myjavautils; / Create package body CREATE OR REPLACE PACKAGE BODY myjavautils AS -- TEST -- procedure addLineToVector(line in varchar2) as LANGUAGE JAVA NAME 'myOracle.TestStaticsInOracleServer.addLineToVector(java.lang.String)'; function getVectorSize return number as LANGUAGE JAVA NAME 'myOracle.TestStaticsInOracleServer.getVectorSize() return int'; function getElementsAsArray(structureTypeName in varchar2) return TABLEOFVARCHAR2 as LANGUAGE JAVA NAME 'myOracle.TestStaticsInOracleServer.getElementsAsArray(java.lang.String) return oracle.sql.ARRAY'; END myjavautils; / |
Test
create or replace type TABLEOFVARCHAR2 as table of varchar2(255);
declare
l TABLEOFVARCHAR2;
i integer;
begin
myjavautils.addLineToVector('monazzo');
dbms_output.put_line(myjavautils.getVectorSize);
l := myjavautils.getElementsAsArray('ALWORKSHOP.TABLEOFVARCHAR2');
FOR i IN l.FIRST .. l.LAST
loop
dbms_output.put_line(l(i));
end loop;
end; |