我有一個問題會從 Java 調用 plsql 過程。程序包如下：（模式job_runner和連接用戶/方案不同）：create or replace package test_package_for_sp as type some_record_type is record ( field_number number, field_varchar2 varchar2 (128), field_date date ); type some_table_type is table of some_record_type; procedure proc_table (p_card_bin in varchar2, p_date in date default null, p_out_table out some_table_type);}然后我嘗試使用 callableStatement 從 Java 調用它： final String typeTableList = "SOME_TABLE_TYPE"; CallableStatement cs = null; try (Connection con = dataSource.getConnection()) { con.setSchema("JOB_RUNNER"); cs = con.prepareCall("{call job_runner.test_package_for_sp.proc_table(?, ?, ?)}"); cs.setString(1, "54867321"); cs.setDate(2, Date.valueOf(ZonedDateTime.now().minusDays(200).toLocalDate())); cs.registerOutParameter(3, Types.ARRAY, typeTableList); cs.execute(); } finally { if (cs != null) cs.close(); }錯誤引發：java.sql.SQLException: invalid name pattern: <connection_scheme>.SOME_TABLE_TYPE at oracle.jdbc.oracore.OracleTypeADT.initMetadata11_2(OracleTypeADT.java:764) at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:479) at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:443)如果我將值typeTableLis從值更改SOME_TABLE_TYPE為完整路徑，并且包和方案JOB_RUNNER.TEST_PACKAGE_FOR_SP.SOME_TABLE_TYPE異常更改為：java.sql.SQLSyntaxErrorException: ORA-01948: identifier's name length (35) exceeds maximum (30)ORA-06512: at "SYS.DBMS_PICKLER", line 18ORA-06512: at "SYS.DBMS_PICKLER", line 58ORA-06512: at line 1 at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)有人知道如何從java調用這個過程嗎？
2 回答
月關寶盒
TA貢獻1772條經驗 獲得超5個贊
您可以將 JDBC 連接屬性設置"oracle.jdbc.createDescriptorUseCurrentSchemaForSchemaName"為"true"，然后將架構切換為"job_runner"( ALTER SESSION SET CURRENT_SCHEMA=job_runner) 并TEST_PACKAGE_FOR_SP.SOME_TABLE_TYP用于 typeTableList。
九州編程
TA貢獻1785條經驗 獲得超4個贊
方法之一：
package testproject;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.ARRAY;
import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ArrayDescriptor;
public class MainClass {
public MainClass() {
super();
}
public static void main(String[] args) {
MainClass mainClass = new MainClass();
Connection conn = null;
ArrayDescriptor des = null;
try {
Object[] obj1 = { 1, "2017-01-01 10:12:10", 200 };
Object[] obj2 = { 2, "2017-06-01 10:12:10", 600 };
Object[] obj3 = { 3, "2017-08-01 10:12:10", 990 };
conn =
DriverManager.getConnection("jdbc:oracle:thin:@<DB_HOST>",
"<user>", "<pass>");
try {
des = ArrayDescriptor.createDescriptor("AJ_TEST_OBJ_TBL", conn);
} catch (SQLException e) {
System.out.println("Arraydesc went wrong.");
System.out.println(e.getStackTrace());
}
ARRAY nArray =
new ARRAY(des, conn, new Object[] { obj1, obj2, obj3 });
OracleCallableStatement pstmt =
(OracleCallableStatement)conn.prepareCall("begin
aj_test_array_pck.print_tbl_parameters(?,?); end;");
pstmt.setArray(1, nArray);
pstmt.registerOutParameter(2, Types.VARCHAR);
pstmt.execute();
String status = pstmt.getString(2);
System.out.println("Status: " + status);
pstmt.close();
conn.close();
} catch (SQLException e) {
System.out.println("Oops with select");
System.out.println(e.getStackTrace());
} catch (ClassNotFoundException e) {
System.out.println("Oops with class");
}
}
}
其中“AJ_TEST_OBJ_TBL”是一個 SQL 類型的對象。
添加回答
舉報
0/150
提交
取消