Python과 oracledb로 SQL 파일의 Oracle 저장 프로시저 실행하기

 

오라클 데이터베이스에서 저장 프로시저를 관리하다 보면, 특정 디렉토리 내의 SQL 파일을 자동으로 실행해야 할 때가 있습니다. 이 블로그에서는 oracledb 라이브러리와 Python을 사용해, 특정 경로와 그 하위 디렉토리를 순회하며 CREATE OR REPLACE PROCEDURE로 시작하는 SQL 파일을 실행하는 방법을 소개합니다. 이 코드는 기존의 oracle_procedure_util.py 모듈을 기반으로 작성되었으며, 디렉토리 순회와 SQL 실행 로직을 추가했습니다.

1. 코드 개요

이 Python 스크립트는 다음 기능을 수행합니다:

• 디렉토리 순회: 지정된 경로와 하위 디렉토리를 탐색해 .sql 확장자를 가진 파일을 찾습니다.
• SQL 파일 필터링: 파일 내용이 CREATE OR REPLACE PROCEDURE로 시작하는지 확인합니다.
• 저장 프로시저 실행: oracledb를 사용해 SQL 파일의 저장 프로시저를 오라클 데이터베이스에 실행합니다.
• 에러 처리: 실행 중 발생하는 오류를 로깅하고, 성공 여부를 출력합니다.

 

728x90

 

이 코드는 oracle_procedure_util.py에서 사용된 데이터베이스 연결 로직(connection_module.get_connections)을 재사용하며, 디렉토리 순회에는 pathlib 모듈을 활용합니다.

2. 구현 코드

아래는 특정 경로를 순회하며 저장 프로시저 SQL 파일을 실행하는 Python 코드입니다.

Python 코드: execute_procedure_sql_files.py

import oracledb
from pathlib import Path
from connection_module import get_connections  # 기존 모듈에서 DB 연결 가져오기
import logging

# 로깅 설정
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    filename='sql_execution.log'
)
logger = logging.getLogger(__name__)

def is_procedure_sql_file(file_path: Path) -> bool:
    """
    SQL 파일이 CREATE OR REPLACE PROCEDURE로 시작하는지 확인
    """
    try:
        with file_path.open('r', encoding='utf-8') as file:
            # 파일의 첫 줄 읽기
            first_line = file.readline().strip().upper()
            return first_line.startswith('CREATE OR REPLACE PROCEDURE')
    except UnicodeDecodeError:
        logger.error(f"파일 인코딩 오류: {file_path}")
        return False
    except Exception as e:
        logger.error(f"파일 읽기 오류: {file_path}, 에러: {e}")
        return False

def execute_sql_file(con, file_path: Path):
    """
    SQL 파일을 읽고 오라클 데이터베이스에서 실행
    """
    try:
        cursor = con.cursor()
        with file_path.open('r', encoding='utf-8') as file:
            sql_content = file.read()
        
        # SQL 실행
        cursor.execute(sql_content)
        con.commit()
        logger.info(f"성공적으로 실행됨: {file_path}")
        print(f"성공: {file_path}")
        
        cursor.close()
    except oracledb.DatabaseError as e:
        logger.error(f"데이터베이스 오류: {file_path}, 에러: {e}")
        print(f"실패: {file_path}, 에러: {e}")
    except Exception as e:
        logger.error(f"실행 오류: {file_path}, 에러: {e}")
        print(f"실패: {file_path}, 에러: {e}")

def process_sql_files(directory: str, con):
    """
    지정된 디렉토리와 하위 디렉토리를 순회하며 SQL 파일 실행
    """
    try:
        # Path 객체 생성
        dir_path = Path(directory)
        if not dir_path.exists():
            logger.error(f"디렉토리가 존재하지 않음: {directory}")
            print(f"오류: 디렉토리가 존재하지 않음: {directory}")
            return

        # .sql 파일 순회
        sql_files = dir_path.rglob("*.sql")
        for sql_file in sql_files:
            if is_procedure_sql_file(sql_file):
                logger.info(f"처리 중: {sql_file}")
                print(f"처리 중: {sql_file}")
                execute_sql_file(con, sql_file)
            else:
                logger.info(f"건너뜀 (저장 프로시저 아님): {sql_file}")
                print(f"건너뜀: {sql_file}")

    except Exception as e:
        logger.error(f"디렉토리 처리 오류: {e}")
        print(f"오류: 디렉토리 처리 중 에러 발생: {e}")

def main():
    """
    메인 함수: DB 연결 및 디렉토리 처리
    """
    conApp, conHos, conNextpot = None, None, None
    try:
        # DB 연결
        conApp, conHos, conNextpot = get_connections()
        
        # 실행할 디렉토리 경로
        target_directory = "/path/to/sql/files"  # 실제 경로로 변경
        
        # SQL 파일 처리
        process_sql_files(target_directory, conNextpot)
        
    except Exception as e:
        logger.error(f"메인 실행 오류: {e}")
        print(f"오류: {e}")
    finally:
        # 연결 종료
        if conNextpot:
            conNextpot.close()
        if conHos:
            conHos.close()
        if conApp:
            conApp.close()

if __name__ == "__main__":
    main()

코드 설명

1. 로깅 설정:
   • logging 모듈을 사용해 실행 결과를 sql_execution.log 파일에 기록합니다.
   • 성공, 오류, 건너뛴 파일 등을 로깅해 추적 가능합니다.
2. is_procedure_sql_file:
   • .sql 파일의 첫 줄을 읽어 CREATE OR REPLACE PROCEDURE로 시작하는지 확인합니다.
   • 인코딩 오류나 파일 읽기 오류를 처리합니다.
3. execute_sql_file:
   • SQL 파일을 읽어 cursor.execute로 실행하고, 트랜잭션을 커밋합니다.
   • 데이터베이스 오류(oracledb.DatabaseError)와 기타 예외를 처리합니다.
4. process_sql_files:
   • pathlib.Path.rglob를 사용해 디렉토리와 하위 디렉토리의 .sql 파일을 순회합니다.
   • 저장 프로시저 파일만 실행하고, 나머지는 건너뜁니다.
5. main:
   • connection_module.get_connections로 데이터베이스 연결을 설정합니다.
   • 지정된 디렉토리(/path/to/sql/files)를 처리하고, 연결을 안전하게 종료합니다.

3. 사용 방법

1. 필요 패키지 설치:

   pip install oracledb

2. 디렉토리 준비:
   • 저장 프로시저 SQL 파일이 있는 디렉토리 경로를 target_directory 변수에 설정하세요.
   • 예: /home/user/sql_procedures
3. connection_module 설정:
   • oracle_procedure_util.py에서 사용된 get_connections 함수가 올바르게 설정되어 있어야 합니다.
   • 데이터베이스 연결 정보(사용자 이름, 비밀번호, DSN 등)를 확인하세요.
4. 코드 실행:

   python execute_procedure_sql_files.py

5. 결과 확인:
   • 콘솔에 성공/실패 메시지가 출력됩니다.
   • sql_execution.log 파일에 상세 로그가 기록됩니다.

728x90

 

4. 예시 SQL 파일

다음은 실행 대상이 되는 SQL 파일의 예시입니다:

procedure_example.sql:

CREATE OR REPLACE PROCEDURE STP_SAL_020101_LST_01 (
    ot_status_code OUT VARCHAR2,
    io_ui_id IN OUT VARCHAR2,
    io_prcd_id IN OUT VARCHAR2,
    in_user_id IN VARCHAR2,
    in_ip IN VARCHAR2,
    ot_respon_code OUT VARCHAR2,
    ot_res_msg OUT VARCHAR2,
    P_storecd IN VARCHAR2,
    P_strbizhourdate IN VARCHAR2,
    P_strsaletypecd IN VARCHAR2,
    P_str_arg IN VARCHAR2,
    ot_out_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Procedure STP_SAL_020101_LST_01 started');
    -- 프로시저 로직
    OPEN ot_out_cursor FOR
    SELECT 'Sample' AS col1, 123 AS col2 FROM DUAL;
    ot_status_code := '0';
    ot_respon_code := '00';
    ot_res_msg := 'Success';
    DBMS_OUTPUT.PUT_LINE('Procedure completed successfully');
END;
/

이 파일은 CREATE OR REPLACE PROCEDURE로 시작하므로 스크립트가 이를 감지해 실행합니다.

5. 실행 결과 예시

콘솔 출력:

처리 중: /path/to/sql/files/procedure_example.sql
성공: /path/to/sql/files/procedure_example.sql
건너뜀: /path/to/sql/files/other_script.sql

sql_execution.log:

2025-05-08 10:00:00,123 - INFO - 처리 중: /path/to/sql/files/procedure_example.sql
2025-05-08 10:00:00,125 - INFO - 성공적으로 실행됨: /path/to/sql/files/procedure_example.sql
2025-05-08 10:00:00,127 - INFO - 건너뜀 (저장 프로시저 아님): /path/to/sql/files/other_script.sql

6. 주의사항

• 파일 인코딩: SQL 파일은 UTF-8 인코딩을 사용해야 합니다. 다른 인코딩은 UnicodeDecodeError를 유발할 수 있습니다.
• 권한: 데이터베이스 사용자가 저장 프로시저를 생성/수정할 수 있는 권한이 필요합니다.
• SQL 구문 오류: SQL 파일에 문법 오류가 있으면 oracledb.DatabaseError가 발생하므로, 사전에 파일을 검증하세요.
• 디렉토리 경로: target_directory를 실제 경로로 변경해야 합니다.
• 로그 파일: sql_execution.log가 쓰기 가능한 디렉토리에 생성됩니다.

7. 결론

이 스크립트를 사용하면 디렉토리 내의 저장 프로시저 SQL 파일을 자동으로 실행할 수 있어, 대량의 프로시저 배포나 테스트가 훨씬 간편해집니다. oracledb와 pathlib를 활용한 이 코드는 확장 가능하며, 로깅 기능을 통해 실행 과정을 쉽게 추적할 수 있습니다. 프로젝트에 맞게 경로와 연결 정보를 설정해 바로 사용해보세요!