Database/Tibero

Tibero의 BLOB에서 Oracle의 BLOB으로 Migration하기

1Q74 2023. 2. 21. 11:22

얼마 전 오래된 시스템의 Tibero DB에서 Oracle DB로 데이터를 이전할 일이 있었다. 

보통의 컬럼들은 대부분 특별한 문제없이 이행했으나, 유독 BLOB타입이 말썽을 부려서 직접 코드를 작성해서 수동으로 이행했다.

1. Code

package migration;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.tmax.tibero.jdbc.TbDriver;
import oracle.jdbc.driver.OracleDriver;

public class MigrateTiberoToOracle {
	private static Connection tibero;
	private static Connection oracle;
	
	private static void setTiberoConnection() throws SQLException {
		DriverManager.registerDriver((Driver)new TbDriver());
		tibero =  DriverManager.getConnection("jdbc:tibero:thin:@1.1.1.1:1521:TBR", "username", "password");
	}
	
	private static void setOracleConnection() throws SQLException {
		DriverManager.registerDriver((Driver)new OracleDriver());
		oracle = DriverManager.getConnection("jdbc:oracle:thin:@2.2.2.2:1521:ORCL", "username", "password");
	}
	
	private static void openConnection() throws SQLException {
		setTiberoConnection();
		setOracleConnection();
	}
	
	private static void migrateUserFile() throws SQLException {
		Statement tbStmt = tibero.createStatement();
		ResultSet tbrs = tbStmt.executeQuery("SELECT id, content FROM user_file");

		PreparedStatement oStmt = oracle.prepareStatement("INSERT INTO user_file (id, content) VALUES(?, ?)");

		while(tbrs.next()) {
			int id = tbrs.getInt("id");
			System.out.println("[user_file] id = " + id);

			oStmt.setInt(1, id);
			oStmt.setBinaryStream(2, tbrs.getBinaryStream("content"));
			oStmt.executeUpdate();
		}

		tbrs.close();
		tbStmt.close();
		oStmt.close();
	}
	
	private static void closeConnection() throws SQLException {
		tibero.close();
		oracle.close();
	}
	
	public static void main(String[] args) throws SQLException, ClassNotFoundException {
		openConnection();
		migrateUserFile();
		closeConnection();
	}
}

2. File

MigrateTiberoToOracle.zip
0.00MB

3. JDBC