Python으로 오라클 저장 프로시저 OUT 커서 필드 정보 추출하기

 

오라클 데이터베이스의 저장 프로시저(SP)에서 반환되는 OUT 커서의 필드 정보를 동적으로 추출하는 방법을 Python으로 구현해보겠습니다. 이 방법은 cx_Oracle 모듈을 활용하며, 어떤 저장 프로시저든 필드 이름, 데이터 타입, 정밀도 등의 메타데이터를 쉽게 얻을 수 있도록 설계되었습니다. 특히, 커서에 데이터가 없어도 필드 정보를 추출할 수 있다는 점이 강력한 장점입니다.

전제 조건

시작하기 전에 다음이 준비되어 있어야 합니다:

• cx_Oracle 설치: pip install cx_Oracle 명령어로 설치하세요.
• 오라클 데이터베이스 연결: 데이터베이스에 접근할 수 있는 환경(DSN, 사용자 이름, 비밀번호)이 필요합니다.
• 저장 프로시저: OUT 파라미터로 SYS_REFCURSOR를 반환하는 저장 프로시저가 있다고 가정합니다.

728x90

Python 구현 코드

아래는 저장 프로시저의 OUT 커서 필드 정보를 추출하는 Python 코드입니다. 이 코드는 범용적으로 동작하며, 입력 파라미터 유무에 상관없이 사용할 수 있습니다.

import oracledb
from typing import List, Dict

def get_cursor_columns(con_nextpot, proc_name: str, params: List = []) -> List[Dict]:
    """
    오라클 저장 프로시저의 OUT 커서 필드 정보를 반환합니다.

    Args:
        con_nextpot: 오라클 데이터베이스 연결 객체 (oracledb.Connection)
        proc_name (str): 호출할 저장 프로시저 이름 (예: 'schema.package.procedure')
        params (List): 저장 프로시저에 전달할 입력 파라미터 리스트

    Returns:
        List[Dict]: 필드 정보 (이름, 타입, 길이 등)를 포함한 리스트
    """
    try:
        # 커서 생성
        cursor = con_nextpot.cursor()

        # OUT 커서 파라미터 준비
        out_cursor = con_nextpot.cursor()

        # 저장 프로시저 호출 파라미터 준비
        call_params = params + [out_cursor]
        cursor.callproc(proc_name, call_params)

        # 커서 메타데이터 추출
        result_cursor = out_cursor
        if result_cursor is None:
            return []

        # 필드 정보 추출
        columns = []
        for col in result_cursor.description:
            col_info = {
                "name": col[0],              # 컬럼 이름
                "type": col[1].__name__,     # 데이터 타입
                "display_size": col[2],      # 표시 크기
                "internal_size": col[3],     # 내부 크기
                "precision": col[4],         # 정밀도
                "scale": col[5],             # 스케일
                "nullable": col[6]           # Null 허용 여부
            }
            columns.append(col_info)

        # 자원 정리
        result_cursor.close()
        cursor.close()

        return columns

    except oracledb.DatabaseError as e:
        print(f"Database error: {e}")
        return []
    except Exception as e:
        print(f"Error: {e}")
        return []

# 사용 예시
def get_connections() -> oracledb.Connection:
    """
    데이터베이스 연결을 생성합니다.

    Returns:
        oracledb.Connection: 오라클 데이터베이스 연결 객체
    """
    dsn = "localhost:1521/orcl"
    username = "your_username"
    password = "your_password"
    try:
        connection = oracledb.connect(user=username, password=password, dsn=dsn)
        print("Database 연결 성공!")
        return connection
    except oracledb.DatabaseError as e:
        print(f"Database 연결 실패: {e}")
        raise

if __name__ == "__main__":
    try:
        # DB 연결
        con_nextpot = get_connections()

        # 테스트할 저장 프로시저
        proc_name = "test_proc"
        params = []  # 필요한 입력 파라미터

        # 필드 정보 조회
        columns = get_cursor_columns(con_nextpot, proc_name, params)

        # 결과 출력
        for col in columns:
            print(f"Column: {col['name']}, Type: {col['type']}, "
                  f"Precision: {col['precision']}, Scale: {col['scale']}, "
                  f"Nullable: {col['nullable']}")

    finally:
        # DB 연결 닫기
        if 'con_nextpot' in locals() and con_nextpot:
            con_nextpot.close()

코드 동작 원리

1. 입력 파라미터 처리:
   • dsn, username, password: 데이터베이스 연결 정보.
   • proc_name: 호출할 저장 프로시저 이름 (예: schema.package.procedure).
   • params: 저장 프로시저의 입력 파라미터 리스트.
2. 커서 메타데이터 추출:
   • `cx _

System: Oracle.CURSOR` 타입으로 OUT 커서를 준비합니다.

• cursor.callproc를 통해 저장 프로시저를 호출합니다.
• result_cursor.description으로 커서의 메타데이터를 추출합니다.

1. 반환 값:
   • 각 필드의 이름, 데이터 타입, 표시 크기, 정밀도, 스케일, Null 허용 여부 등을 딕셔너리 형태로 반환합니다.
2. 예외 처리:
   • 데이터베이스 오류나 기타 예외를 처리하여 코드의 안정성을 높였습니다.

728x90

테스트용 저장 프로시저

아래는 테스트를 위해 오라클에서 생성할 수 있는 간단한 저장 프로시저입니다. 이 프로시저는 데이터가 없어도 필드 정보를 반환하도록 설계되었습니다.

 

CREATE OR REPLACE PROCEDURE test_proc(p_out_cursor OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_out_cursor FOR
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE 1 = 2; -- 데이터가 없도록 조건 설정
END;
/

실행 결과

위 Python 코드를 실행하면 다음과 같은 출력이 나타납니다:

 

Column: EMPLOYEE_ID, Type: NUMBER, Precision: 6, Scale: 0, Nullable: False
Column: FIRST_NAME, Type: STRING, Precision: 0, Scale: 0, Nullable: True
Column: LAST_NAME, Type: STRING, Precision: 0, Scale: 0, Nullable: True
Column: SALARY, Type: NUMBER, Precision: 8, Scale: 2, Nullable: True

이 코드의 강점

• 범용성: 모든 SYS_REFCURSOR를 반환하는 저장 프로시저에 적용 가능.
• 데이터 무관: 커서에 데이터가 없어도 메타데이터를 추출.
• 유연성: 입력 파라미터가 있거나 없어도 동작.
• 상세 정보: 컬럼 이름뿐 아니라 타입, 정밀도, 스케일 등 풍부한 메타데이터 제공.

주의사항

• cx_Oracle 모듈은 Python 버전 및 오라클 클라이언트 라이브러리와의 호환성을 확인해야 합니다.
• 저장 프로시저가 동적 SQL을 사용하는 경우, 커서가 올바르게 열리는지 확인하세요.
• 실제 실행 시 DB 연결 정보(dsn, username, password)와 프로시저 이름은 환경에 맞게 수정하세요.

마무리

이 코드를 사용하면 Python으로 오라클 저장 프로시저의 OUT 커서 필드 정보를 손쉽게 추출할 수 있습니다. 데이터베이스 개발자나 데이터 분석가라면 이 방법을 통해 작업 효율성을 크게 높일 수 있을 것입니다. 추가적인 질문이나 개선 사항이 있다면 언제든 의견을 공유해주세요!