728x90
출처 : Passing Arrays between Java and Oracle Procedures
how to call procedure with out parameter as table type from a java class
Oracle procedure 'out' 'table of varchar2' type parameter
Oracle stored procedure using array as parameter for table insert
Jdbc array binding: character set encoding
Oracle Database 11g Release 2 JDBC Drivers
주의사항
1. Spring을 사용하는 경우 OracleConnection을 가지고 오기 위해서 <property name="accessToUnderlyingConnectionAllowed" value="true"/>
2. 인코딩 문제로 varchar2의 경우 orai18n.jar를 추가 해야 됨
oracle 배열 타입 생성
CREATE OR REPLACE TYPE ARRAY_V AS TABLE OF VARCHAR2(512);
oracle procedure
CREATE OR REPLACE PACKAGE XXX.PKG_TEST AS
PROCEDURE SP_TERMINAL_SELECT
(
IN_TERMINAL_MAC IN ARRAY_V,
OUT_TERMINAL_IP OUT ARRAY_V
);
END PKG_TEST;
/
CREATE OR REPLACE PACKAGE BODY XXX.PKG_TEST IS
PROCEDURE SP_TERMINAL_SELECT
(
IN_TERMINAL_MAC IN ARRAY_V,
OUT_TERMINAL_IP OUT ARRAY_V
) IS
BEGIN
-- need to initialize the collection or it will fail if it is used
OUT_TERMINAL_IP := ARRAY_V();
FOR i IN 1 .. IN_TERMINAL_MAC.COUNT LOOP
OUT_TERMINAL_IP.extend(1);
SELECT TERMINAL_IP INTO OUT_TERMINAL_IP(i)
FROM TERMINAL
WHERE TERMINAL_MAC = IN_TERMINAL_MAC(i);
END LOOP;
END SP_TERMINAL_SELECT;
END PKG_TEST;
/
java 소스
package test;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import org.apache.commons.dbcp.DelegatingConnection;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import test.util.ConnectionManager;
public class SelectTerminal {
public static ApplicationContext context;
public static void main(String[] args) throws Exception {
System.out.println("test");
String[] configLocation = new String[] { "file:WebContent/WEB-INF/test-servlet.xml" };
context = new ClassPathXmlApplicationContext(configLocation);
Connection conn = null;
try {
conn = ConnectionManager.getConnection();
//
Connection oraConn = ((DelegatingConnection)conn).getDelegate();
if ((oraConn instanceof DelegatingConnection) && !(oraConn instanceof oracle.jdbc.OracleConnection)) {
oraConn = ((DelegatingConnection)oraConn).getDelegate();
}
// Create descriptors for each Oracle collection type required
// CREATE OR REPLACE ARRAY_V AS TABLE OF VARCHAR2(512);
ArrayDescriptor oracleVarchar2Collection = ArrayDescriptor.createDescriptor("ARRAY_V", oraConn);
// JAVA arrays to hold the data.
String[] terminal_mac_array = { "08:xx:xx:xx:xx:xx", "00:xx:xx:xx:xx:xx" };
ARRAY ora_terminal_mac = new ARRAY(oracleVarchar2Collection, oraConn, terminal_mac_array);
show_array_info(ora_terminal_mac);
// Bind the input arrays.
OracleCallableStatement stmt =
(OracleCallableStatement) oraConn.prepareCall("{ call PKG_TEST.SP_TERMINAL_SELECT(?,?) }");
stmt.setArray(1, ora_terminal_mac);
stmt.registerOutParameter(2, OracleTypes.ARRAY, "ARRAY_V");
stmt.execute();
//
ARRAY ora_terminal_ip = stmt.getARRAY(2);
if (ora_terminal_ip!=null) {
String[] terminal_ip_array = (String[])ora_terminal_ip.getArray();
for (int i=0; i<terminal_ip_array.length; i++) {
System.out.println(i + " / terminal_ip = " + terminal_ip_array[i]);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn!=null) try { conn.close(); } catch (SQLException e) { }
}
}
private static void show_array_info(oracle.sql.ARRAY p_in) throws SQLException {
System.out.println("Array is of type " + p_in.getSQLTypeName());
System.out.println("Array is of type code " + p_in.getBaseType());
System.out.println("Array is of length " + p_in.length());
String[] values = (String[]) p_in.getArray();
for (int i = 0; i < p_in.length(); i++)
System.out.println("p_in[" + i + "] = " + values[i]);
}
}
결과
Array is of type XXX.ARRAY_V Array is of type code 12 Array is of length 2 p_in[0] = 08:xx:xx:xx:xx:xx p_in[1] = 00:xx:xx:xx:xx:xx 0 / terminal_ip = 192.168.0.2 1 / terminal_ip = 192.168.0.3
728x90
