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

Programming/Java 2013.04.11 15:24 Posted by 파란크리스마스

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

Tomcat / server.xml - Oracle Connection pool 설정

Programming/Java 2012.08.12 09:46 Posted by 파란크리스마스

Oracle Connection Pool

		<Resource auth="Container" 
              driverClassName="oracle.jdbc.driver.OracleDriver" 
              factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" 
              maxActive="100" 
              maxIdle="30" 
              maxWait="10000" 
              name="jdbc/testDS" 
              password="tiger" 
              type="javax.sql.DataSource" 
              url="jdbc:oracle:thin:@loclahost:1521:orcl" 
              username="scott"/>

Context 경로 추가

		<Context docBase="C:/workspace/images" path="images" reloadable="true"/>

URIEncoding

		<Connector port="8080" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="8443" URIEncoding="UTF-8"/>
신고

JDBC 연결

Programming/Java 2012.06.24 10:08 Posted by 파란크리스마스

MySQL JDBC 연결

import java.sql.Connection;
import java.sql.DriverManager;

public class Test {
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    try {
      Class.forName("org.gjt.mm.mysql.Driver"); 
      conn = DriverManager.getConnection("jdbc:mysql://localhost:5515/dbname?user=id&useUnicode=true&characterEncoding=UTF8", "id", "pw");
    } catch (Exception e) {
      e.printStackTrace();
      if (conn!=null) try { conn.close(); } catch (Exception e2) { }
    }
  }
}

Oracle thin

import java.sql.Connection;
import java.sql.DriverManager;

public class Test {
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver"); 
      conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
      
      System.out.println(conn);
      
    } catch (Exception e) {
      e.printStackTrace();
      if (conn!=null) try { conn.close(); } catch (Exception e2) { }
    }
  }
}

Oracle OCI

import java.sql.Connection;
import java.sql.DriverManager;

public class TestOCI {
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver"); 
      conn = DriverManager.getConnection("jdbc:oracle:oci:@xe", "scott", "tiger");
      
      System.out.println(conn);
      
    } catch (Exception e) {
      e.printStackTrace();
      if (conn!=null) try { conn.close(); } catch (Exception e2) { }
    }
  }
}
신고

오라클 계정 추가, 수정, 삭제, 8080포트 번경

Database 2011.10.21 23:43 Posted by 파란크리스마스

출처 : http://thlife.net/557
http://jonelove.tistory.com/entry/Oracle-XE-%EC%9B%B9-%ED%8F%AC%ED%8A%B8-8080-%EB%B3%80%EA%B2%BD%ED%95%98%EA%B8%B0

테이블 스페이스 생성

CREATE TABLESPACE user1_data
DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\USER1_DATA' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE 1024M;

오라클 계정 추가, 수정, 삭제

계정 추가

create user user01 identified by user0110
default tablespace user1_data temporary tablespace temp;

권한 부여

grant connect, resource to user01;
grant dba to user01;

계정 삭제

drop user user01 cascade;

암호 변경

alter user user01 identified by user0111;

관리자용 8080 포트 변경

현재 포트 확인

SELECT dbms_xdb.getHttpPort() FROM DUAL;

포트 변경

EXEC DBMS_XDB.SETHTTPPORT(9090);

백업

exp user01/user01@ubitech owner=user01 statistics=none file=user01.dmp

복원

출처 : http://www.rudaks.co.kr/bbs/view.jsp?seq=1965&bbs=db&PageNo=1

D:\>imp user01/user01 full=y fromuser=user01 touser=user01 file=user01.dmp

Export file created by EXPORT:V11.02.00 via conventional path
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)

D:\>imp user01/user01 fromuser=user01 touser=user01 file=user01.dmp




신고

Oracle - foreign key 생성

Database 2010.05.20 11:13 Posted by 파란크리스마스

CREATE TABLE ERD_EMP (
  EMP_ID         NUMBER            NOT NULL,
  EMP_NAME       VARCHAR2(256)         NULL,
  EMP_ADDRESS    VARCHAR2(256)         NULL,
  EMP_TEL        VARCHAR2(256)         NULL,
  DEPT_ID        NUMBER                NULL,
  CONSTRAINT PK_ERD_EMP PRIMARY KEY(EMP_ID)
);

CREATE TABLE ERD_DEPT (
  DEPT_ID      NUMBER            NOT NULL,
  DEPT_NAME    VARCHAR2(256)         NULL,
  CONSTRAINT PK_ERD_DEPT PRIMARY KEY(DEPT_ID)
);

ALTER TABLE ERD_EMP ADD
(
    CONSTRAINT FK_DEPTNO
    FOREIGN KEY ( DEPT_ID )
        REFERENCES ERD_DEPT ( DEPT_ID )
);


 

신고

Oracle Tip - 계정에 디버깅 권한 부여하기

Database 2010.04.20 00:22 Posted by 파란크리스마스


- 오류 메시지
ORA-01031: insufficient privileges
ORA-06512: at "SYS.PBSDE", line 78
ORA-06512: at "SYS.DBMS_DEBUG", line 224
ORA-06512: at line 2

debug connect session
debug any procedure

- 권한 부여
GRANT DEBUG CONNECT SESSION TO "SCOTT";
GRANT DEBUG ANY PROCEDURE TO "SCOTT";

신고

[Oracle] ORA-28000: the account is locked 해결방법

Database 2010.03.23 00:00 Posted by 파란크리스마스
- 출처

http://hyunayarn.tistory.com/9

- 오류 내용

- Lock 유저 확인 질의문

SELECT USERNAME,
        ACCOUNT_STATUS,
        TO_CHAR(LOCK_DATE,'YYYY.MM.DD HH24:MI') LOCK_DATE          
  FROM DBA_USERS
 WHERE USERNAME = 'SCOTT'

- 질의결과

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE      
------------------------------ -------------------------------- ----------------
SCOTT                          EXPIRED & LOCKED                 2010.03.22 23:45
1 row selected.

- Lock 해제

ALTER USER scott ACCOUNT UNLOCK;

- 확인1

SELECT USERNAME, ACCOUNT_STATUS
  FROM DBA_USERS
 WHERE USERNAME = 'SCOTT';

USERNAME                       ACCOUNT_STATUS                 
------------------------------ --------------------------------
SCOTT                          EXPIRED                        
1 row selected.

- 암호 설정

ALTER USER scott IDENTIFIED BY tiger;

- 확인2

SELECT USERNAME, ACCOUNT_STATUS
  FROM DBA_USERS
 WHERE USERNAME = 'SCOTT';

USERNAME                       ACCOUNT_STATUS                 
------------------------------ --------------------------------
SCOTT                          OPEN                           
1 row selected.
신고


 

티스토리 툴바