Oracle Stored Procedure 샘플로 ora2pg 사용예

 

Oracle의 Stored Procedure를 PostgreSQL로 변환할 때, 실제 샘플 코드와 변환 결과를 단계별로 상세히 설명드리겠습니다.

---

Oracle Stored Procedure 샘플

아래는 Oracle에서 사용되는 간단한 Stored Procedure입니다.

Oracle 코드

CREATE OR REPLACE PROCEDURE calculate_bonus (
    p_employee_id IN NUMBER,
    p_bonus OUT NUMBER
) AS
    v_salary NUMBER;
BEGIN
    -- 사원의 급여 조회
    SELECT salary INTO v_salary 
    FROM employees 
    WHERE employee_id = p_employee_id;

    -- 보너스 계산 (급여의 10%)
    p_bonus := v_salary * 0.1;
END;
/

Oracle 코드 설명

1. 입력 및 출력 매개변수:
   • p_employee_id는 입력 매개변수 (사원의 ID).
   • p_bonus는 출력 매개변수 (계산된 보너스).
2. 로직:
   • employees 테이블에서 employee_id에 해당하는 급여를 가져옴.
   • 급여의 10%를 계산하여 p_bonus에 저장.

---

 

728x90

Oracle과 PostgreSQL 코드 비교

특징 Oracle PostgreSQL

프로시저 유형	PROCEDURE	FUNCTION
출력 매개변수	OUT 키워드 사용	RETURN 문 사용
변수 선언	DECLARE 구문	함수 내부에서 DECLARE 구문
예외 처리	EXCEPTION 블록 사용	BEGIN...EXCEPTION...END 사용
디버깅 메시지	DBMS_OUTPUT.PUT_LINE	RAISE NOTICE

---

Oracle의 Stored Procedure를 PostgreSQL로 변환하기 위해 ora2pg.conf 파일을 설정하는 방법을 구체적으로 설명드리겠습니다. 여기서는 제공하신 Oracle의 calculate_bonus 프로시저를 변환 대상으로 설정합니다.

---

1. ora2pg.conf 구성

Ora2Pg는 변환 작업의 설정을 ora2pg.conf 파일에서 정의합니다. 아래는 calculate_bonus 프로시저를 변환하기 위한 설정 파일 예시입니다.

# 1. Oracle 데이터베이스 연결 정보
ORACLE_DSN     dbi:Oracle:host=your_oracle_host;sid=your_sid;port=1521
ORACLE_USER    your_oracle_user
ORACLE_PWD     your_oracle_password

# 2. PostgreSQL 연결 정보 (변환 후 적용을 위해)
PG_DSN         dbi:Pg:host=your_pg_host;port=5432;dbname=your_pg_database
PG_USER        your_pg_user
PG_PWD         your_pg_password

# 3. 변환 대상 유형 설정 (Stored Procedures)
TYPE           PROCEDURE

# 4. 출력 디렉토리 설정
OUTPUT         /path/to/output_directory

# 5. 특정 스키마 및 프로시저 필터링
SCHEMA         your_schema_name      # 변환할 스키마 이름
INCLUDE_PROC   calculate_bonus       # 변환할 프로시저 이름

# 6. PLSQL 변환 활성화
PLSQL_PGSQL    1                     # PL/SQL을 PL/pgSQL로 변환 활성화

# 7. 데이터 타입 변환 설정 (Oracle 데이터 타입을 PostgreSQL에 매핑)
DATA_TYPE      number="NUMERIC"      # NUMBER → NUMERIC 변환
DATA_TYPE      varchar2="TEXT"       # VARCHAR2 → TEXT 변환
DATA_TYPE      date="TIMESTAMP"      # DATE → TIMESTAMP 변환

# 8. 디버그 및 로깅
DEBUG          1                     # 디버깅 활성화
LOGFILE        /path/to/ora2pg.log   # 로그 파일 경로

---

2. 주요 설정 설명

(1) Oracle 및 PostgreSQL 연결 정보

• ORACLE_DSN: Oracle DB의 연결 문자열입니다. host, sid, port 정보를 입력합니다.
• PG_DSN: 변환 후 PostgreSQL에 연결하기 위한 정보입니다. 변환만 할 경우 설정하지 않아도 됩니다.

(2) 변환 대상 설정

• TYPE: PROCEDURE로 설정하여 Oracle의 Stored Procedure만 변환합니다.
• INCLUDE_PROC: 변환하려는 특정 프로시저를 지정합니다. 여러 개의 프로시저를 변환하려면 쉼표로 구분합니다.

(3) 데이터 타입 매핑

Oracle의 데이터 타입을 PostgreSQL의 대응 타입으로 변환하도록 설정합니다. 예를 들어:

• NUMBER → NUMERIC
• VARCHAR2 → TEXT
• DATE → TIMESTAMP

(4) PLSQL 변환 활성화

• PLSQL_PGSQL: 1로 설정하면 Oracle PL/SQL 코드가 PostgreSQL PL/pgSQL로 자동 변환됩니다.

(5) 출력 디렉토리

변환된 결과(SQL 스크립트)를 저장할 디렉토리를 지정합니다.

(6) 디버그 및 로깅

• 변환 과정에서 로그를 생성하여 오류를 추적합니다.

---

3. 변환 실행

설정이 완료되면 아래 명령어를 실행합니다.

ora2pg -c /path/to/ora2pg.conf -t PROCEDURE

---

4. 변환 결과 예시

Ora2Pg는 Oracle 프로시저를 PostgreSQL 함수로 변환합니다. 변환 결과는 지정한 OUTPUT 디렉토리에 저장됩니다.

변환된 PostgreSQL 코드 예시

CREATE OR REPLACE FUNCTION calculate_bonus (
    p_employee_id NUMERIC
) RETURNS NUMERIC AS $$
DECLARE
    v_salary NUMERIC;
    p_bonus NUMERIC;
BEGIN
    -- 사원의 급여 조회
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    -- 보너스 계산 (급여의 10%)
    p_bonus := v_salary * 0.1;

    -- 결과 반환
    RETURN p_bonus;
END;
$$ LANGUAGE plpgsql;

 

---

5. 주요 고려사항

1. 커스터마이징 필요
   Ora2Pg는 대부분의 PL/SQL 문법을 변환하지만, 복잡한 프로시저(예: 커서, 동적 SQL)는 추가 수동 수정이 필요할 수 있습니다.
2. 테스트
   변환된 PostgreSQL 함수가 제대로 작동하는지 테스트해야 합니다.
3. 데이터 타입 변환 확인
   Oracle의 NUMBER 타입은 PostgreSQL의 NUMERIC 또는 DOUBLE PRECISION으로 변환되며, 원하는 정밀도에 맞게 수정해야 할 수 있습니다.

---

 

728x90

1. ora2pg.conf 설정 상세화 및 추가 옵션

• 데이터 타입 변환:
  • DATE 데이터 타입: Oracle의 DATE는 PostgreSQL의 TIMESTAMP WITH TIME ZONE으로 변환하는 것이 일반적입니다. 시간대 정보를 고려해야 할 경우에는 이를 명시적으로 설정해야 합니다.
  • CLOB, BLOB: 대용량 텍스트 또는 바이너리 데이터는 PostgreSQL의 TEXT 또는 BYTEA로 변환합니다.
  • NUMBER 정밀도: Oracle의 NUMBER는 PostgreSQL의 NUMERIC으로 변환될 때 정밀도를 명시적으로 설정해야 할 수 있습니다.
• 인덱스, 트리거 변환:
  • ora2pg는 인덱스와 트리거도 변환할 수 있습니다. 해당 설정을 추가하여 더욱 완벽한 마이그레이션을 수행할 수 있습니다.
• 시퀀스 변환:
  • 시퀀스 역시 변환 가능하며, 시퀀스의 시작 값, 증가값 등을 설정할 수 있습니다.
• 패키지 변환:
  • 간단한 패키지는 변환이 가능하지만, 복잡한 패키지는 수동으로 변환해야 할 수 있습니다.
• 커스텀 함수:
  • 사용자 정의 함수는 PL/pgSQL로 변환되며, 함수 내부 로직을 검토하고 필요한 수정을 해야 할 수 있습니다.
• 기타 옵션:
  • SKIP_OBJECTS: 특정 객체를 변환에서 제외할 수 있습니다.
  • VERBOSE: 더 자세한 로그를 출력합니다.
  • LOG_SQL: 생성된 SQL 문장을 로그 파일에 기록합니다.

2. Oracle과 PostgreSQL의 차이점 고려

• PL/SQL과 PL/pgSQL의 차이:
  • PL/SQL과 PL/pgSQL은 문법과 기능에 차이가 있습니다. 특히 커서, 트랜잭션 처리, 예외 처리 부분에서 차이가 크므로 주의해야 합니다.
• 데이터베이스 객체:
  • Oracle과 PostgreSQL은 지원하는 데이터베이스 객체와 기능에 차이가 있습니다. 예를 들어, Oracle의 materialized view는 PostgreSQL에서는 materialized view라는 동일한 이름으로 존재하지 않을 수 있습니다.
• SQL 함수:
  • 각 데이터베이스가 제공하는 SQL 함수는 다를 수 있습니다. Oracle의 특정 함수를 PostgreSQL의 다른 함수로 대체해야 할 수 있습니다.

3. 변환 후 테스트 및 최적화

• 단위 테스트: 변환된 함수나 프로시저에 대한 단위 테스트를 수행하여 정확성을 검증해야 합니다.
• 성능 튜닝: PostgreSQL에서 최적의 성능을 위해 인덱스를 재생성하거나 쿼리를 최적화해야 할 수 있습니다.
• 데이터 유형: 변환된 데이터 타입이 원본 데이터와 일치하는지 확인해야 합니다.

4. 복잡한 프로시저 변환 시 추가 고려 사항

• 동적 SQL: 동적 SQL은 PL/pgSQL에서 다르게 처리해야 합니다.
• 커서: Oracle의 커서는 PostgreSQL의 커서와 다르게 사용됩니다.
• 패키지 의존성: 패키지 간의 의존성을 해결해야 합니다.
• 트리거: 트리거의 종류와 기능에 따라 변환 방법이 달라집니다.

5. 마이그레이션 전략

• 단계별 마이그레이션: 전체 데이터를 한 번에 마이그레이션하기보다는, 테이블이나 스키마 단위로 나누어 단계적으로 마이그레이션하는 것이 좋습니다.
• 데이터 검증: 마이그레이션 후 데이터의 무결성을 확인해야 합니다.
• 롤백 계획: 문제 발생 시 롤백할 수 있는 계획을 수립해야 합니다.

6. 도구 활용

• SQL Developer Data Modeler: Oracle 데이터베이스 모델을 시각적으로 확인하고, PostgreSQL로 변환하는 데 도움이 됩니다.
• Liquibase: 데이터베이스 변경 사항을 관리하고 마이그레이션하는 데 사용할 수 있습니다.

결론

Oracle에서 PostgreSQL로 마이그레이션하는 것은 단순한 작업이 아닙니다. ora2pg를 활용하면 많은 부분을 자동화할 수 있지만, 데이터베이스 구조와 기능의 차이를 이해하고, 각 프로젝트에 맞는 맞춤형 설정과 테스트가 필요합니다.