将java存储过程中的String[]转换为ORA_MINING_VARCHAR2_NT

Question

是否可以将存储过程中的String[]转换为"ORA_MINING_VARCHAR2_NT“作为返回值？VARRAY不是一个选项(我有可变数量的元素)

我总是得到一个错误：

ORA-00932: inconsistent datatypes: expected a return value that is an instance of a user defined Java class convertible to an Oracle type got an object that could not be converted

在此代码中：

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED Parser AS
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class Parser {

    public static oracle.sql.ARRAY parseToArray(String str, String delim) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:default:connection");
        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("ORA_MINING_VARCHAR2_NT", conn);
        ARRAY result = new ARRAY(descriptor, conn, str.split(delim));
        return result;
    }
}
/
CREATE OR REPLACE FUNCTION
PARSETOARRAY (str IN VARCHAR2, delim IN VARCHAR2) 
RETURN ORA_MINING_VARCHAR2_NT AS
LANGUAGE JAVA NAME 'Parser.parseToArray (java.lang.String, java.lang.String) return oracle.sql.ARRAY';
/
DECLARE
v_array ORA_MINING_VARCHAR2_NT;
BEGIN
  FOR testing IN (SELECT record FROM interfacelog) LOOP
    v_array := PARSETOARRAY(testing.record, '|'); 
    -- do smth
  END LOOP;
END;

谢谢!

为我糟糕的英语感到抱歉

Answer 1

我已经解决了。它与其他一些类一起工作得很好：

create or replace type stringArray is table of varchar2(4000);
/
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED Parser AS
import java.sql.Connection;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleConnection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Array;

public class Parser {

    public static Array parseToArray(String str, String delim) throws SQLException {
        OracleDriver ora = new oracle.jdbc.OracleDriver();
        Connection conn = ora.defaultConnection();
        OracleConnection oraConn = (OracleConnection)conn;
        Array arr = oraConn.createARRAY("STRINGARRAY", str.split(delim));
        return arr;
    }
}
/
CREATE OR REPLACE FUNCTION
PARSETOARRAY (str IN VARCHAR2, delim IN VARCHAR2) 
RETURN STRINGARRAY AS
LANGUAGE JAVA NAME 'Parser.parseToArray (java.lang.String, java.lang.String) return java.sql.Array';
/
DECLARE
v_array STRINGARRAY;
BEGIN
  FOR testing IN (SELECT record FROM interfacelog) LOOP
    v_array := PARSETOARRAY(testing.record, '|'); 
  END LOOP;
END;

Answer 2

始终为作为参数传递给createDescriptor的类型指定架构名称。