Oracle Procedure - JDBC 배열(ARRAY 타입) 호출

출처 : 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