Python에서 Oracle 저장 프로시저의 DBMS_OUTPUT.PUT_LINE 메시지 확인하기

오라클 데이터베이스의 저장 프로시저에서 DBMS_OUTPUT.PUT_LINE으로 출력된 디버깅 메시지를 Python에서 확인하는 방법을 소개합니다. 이 블로그에서는 oracle_procedure_util.py 모듈을 기반으로, DBMS_OUTPUT 메시지를 읽어오는 방법을 단계별로 설명하고, 실제 구현 코드를 제공합니다.

1. oracle_procedure_util.py 모듈 개요

oracle_procedure_util.py는 오라클 저장 프로시저를 호출하고, 결과를 처리하거나 테스트용 SQL 쿼리를 동적으로 생성하는 유틸리티 모듈입니다. 주요 기능은 다음과 같습니다:

• 저장 프로시저 호출: 오라클 저장 프로시저를 호출해 결과 데이터를 처리합니다.
• 랜덤 데이터 생성: 결과 데이터가 없을 경우, 커서 메타데이터를 기반으로 랜덤 데이터를 생성해 테스트 SQL 쿼리를 만듭니다.
• 클립보드 저장: 생성된 SQL 쿼리를 클립보드에 복사해 편리하게 활용할 수 있습니다.

이 모듈은 oracledb와 clipboard 라이브러리를 사용하며, 데이터베이스 연결은 connection_module.get_connections에서 가져옵니다.

 

2. DBMS_OUTPUT.PUT_LINE이란?

오라클 PL/SQL에서 DBMS_OUTPUT.PUT_LINE은 디버깅 메시지나 실행 상태를 출력하는 데 사용됩니다. 하지만 이 메시지는 기본적으로 클라이언트에서 명시적으로 활성화하지 않으면 표시되지 않습니다. Python에서 이를 읽으려면 DBMS_OUTPUT를 활성화하고, DBMS_OUTPUT.GET_LINE을 호출해 버퍼를 읽어야 합니다.

3. DBMS_OUTPUT 메시지 읽기 구현

Python에서 DBMS_OUTPUT.PUT_LINE 메시지를 읽으려면, 저장 프로시저 호출 후 DBMS_OUTPUT.GET_LINE을 사용해 메시지를 가져옵니다. 아래는 이를 반영한 수정된 call_oracleProcedure 함수입니다.

수정된 코드: call_oracleProcedure

 

 

728x90

 

 

 

 

 

 

 

def call_oracleProcedure(conNextpot, num_rows=10):
    try:
        # 커서 생성
        cursor = conNextpot.cursor()

        # DBMS_OUTPUT 활성화
        cursor.execute("BEGIN DBMS_OUTPUT.ENABLE(1000000); END;")  # 버퍼 최대 1,000,000

        # OUT SYS_REFCURSOR 준비
        ot_out_cursor = conNextpot.cursor()

        # 저장 프로시저 이름
        procedureName = "NEXTPOT.STP_SAL_020101_LST_01"

        # 저장 프로시저 파라미터
        params = {
            "io_ui_id": "UI001",
            "io_prcd_id": "PROC001",
            "in_user_id": "test_user",
            "in_ip": "192.168.0.1",
            "P_storecd": "*",
            "P_strbizhourdate": "*",
            "P_strsaletypecd": "*",
            "P_str_arg": "*",
        }

        # 저장 프로시저 호출 파라미터
        call_params = [
            cursor.var(str),                 # ot_status_code OUT VARCHAR2
            params.get("io_ui_id", None),    # io_ui_id IN OUT VARCHAR2
            params.get("io_prcd_id", None),  # io_prcd_id IN OUT VARCHAR2
            params["in_user_id"],            # in_user_id IN VARCHAR2
            params["in_ip"],                 # in_ip IN VARCHAR2
            cursor.var(str),                 # ot_respon_code OUT VARCHAR2
            cursor.var(str),                 # ot_res_msg OUT VARCHAR2
            params["P_storecd"],
            params["P_strbizhourdate"],
            params["P_strsaletypecd"],
            params["P_str_arg"],
            ot_out_cursor                    # ot_out_cursor OUT SYS_REFCURSOR
        ]

        # 저장 프로시저 호출
        cursor.callproc(procedureName, call_params)

        # DBMS_OUTPUT 메시지 가져오기
        dbms_output = []
        while True:
            line_var = cursor.var(str)
            status_var = cursor.var(int)
            cursor.callproc("DBMS_OUTPUT.GET_LINE", [line_var, status_var])
            if status_var.getvalue() != 0:
                break
            dbms_output.append(line_var.getvalue())

        # 커서 데이터 처리
        cursor_data = []
        query = None
        if ot_out_cursor.description:
            cursor_data = [row for row in ot_out_cursor]
            if not cursor_data:
                query = generate_sql_from_cursor_description(ot_out_cursor.description, 10)

        # 결과 반환
        results = {
            "ot_status_code": call_params[0].getvalue(),
            "io_ui_id": call_params[1],
            "io_prcd_id": call_params[2],
            "ot_respon_code": call_params[5].getvalue(),
            "ot_res_msg": call_params[6].getvalue(),
            "cursor_data": cursor_data,
            "query": query,
            "dbms_output": dbms_output,
        }

        # 커서 닫기
        ot_out_cursor.close()
        cursor.close()
        return results

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

주요 수정 포인트

1. DBMS_OUTPUT 활성화: 

   cursor.execute("BEGIN DBMS_OUTPUT.ENABLE(1000000); END;")

   저장 프로시저 호출 전에 버퍼를 활성화합니다. 버퍼 크기는 최대 1,000,000바이트로 설정했습니다.
2. DBMS_OUTPUT 메시지 읽기:

   dbms_output = []
   while True:
       line_var = cursor.var(str)
       status_var = cursor.var(int)
       cursor.callproc("DBMS_OUTPUT.GET_LINE", [line_var, status_var])
       if status_var.getvalue() != 0:
           break
       dbms_output.append(line_var.getvalue())

   DBMS_OUTPUT.GET_LINE을 호출해 메시지를 한 줄씩 읽고, 더 이상 메시지가 없으면 루프를 종료합니다.
3. 결과에 메시지 추가: dbms_output 리스트를 결과 딕셔너리에 추가해 반환합니다.

 

4. 결과 출력

메인 코드에서 dbms_output 메시지를 출력하도록 수정합니다:

if __name__ == "__main__":
    try:
        conApp, conHos, conNextpot = get_connections()
        results = call_oracleProcedure(conNextpot)

        if results:
            print(f"Status Code       : {results['ot_status_code']}")
            print(f"UI ID             : {results['io_ui_id']}")
            print(f"Process ID        : {results['io_prcd_id']}")
            print(f"Response Code     : {results['ot_respon_code']}")
            print(f"Response Message  : {results['ot_res_msg']}")
            print("Cursor Data       :")
            for row in results["cursor_data"]:
                print(row)
            print("DBMS Output Messages :")
            for msg in results['dbms_output']:
                print(msg)
            print(f"Generated SQL Query : {results['query']}")

            clipboard.copy(f"CLOSE ot_out_cursor; \nOPEN ot_out_cursor FOR \n{results['query']}\n;\n")

    finally:
        if "conNextpot" in locals() and conNextpot:
            conNextpot.close()

출력 예시

plaintext

 

Status Code       : 0
UI ID             : UI001
Process ID        : PROC001
Response Code     : 00
Response Message  : Success
Cursor Data       :
('Row1_col1', 'Row1_col2')
('Row2_col1', 'Row2_col2')
DBMS Output Messages :
Message 1 from DBMS_OUTPUT
Another debug message
Generated SQL Query :
SELECT ... FROM DUAL ...

5. 사용 시 주의사항

• 버퍼 크기: DBMS_OUTPUT.ENABLE의 버퍼 크기를 적절히 설정하세요. 메시지가 많을 경우 기본값(20,000바이트)으로는 부족할 수 있습니다.
• 예외 처리: 저장 프로시저 호출 실패나 연결 오류에 대비해 적절한 예외 처리가 필요합니다.
• 세션별 활성화: DBMS_OUTPUT은 세션마다 활성화해야 하므로, 연결마다 ENABLE을 호출하세요.

6. 결론

oracle_procedure_util.py를 활용해 DBMS_OUTPUT.PUT_LINE 메시지를 Python에서 읽는 방법을 알아보았습니다. 이 방법을 사용하면 저장 프로시저의 디버깅 메시지를 쉽게 확인할 수 있어, 개발과 테스트 과정이 한결 수월해집니다. 위 코드를 참고해 프로젝트에 적용해보세요!