PL/SQL Function / Procedure 실행 오류 확인 방법

오라클에서 PL/SQL FUNCTION 또는 PROCEDURE가 실행 중 오류를 발생시키는 경우, 해당 실행 오류를 확인하는 방법은 다음과 같습니다:

1. EXCEPTION 블록 사용 : 오라클 PL/SQL 블록에서 발생하는 실행 오류를 확인하려면 EXCEPTION 블록을 사용하여 오류 메시지를 캡처하고 출력하거나 로그에 기록할 수 있습니다.

DECLARE
    result NUMBER;
BEGIN
    -- 함수 호출
    result := my_function(10);

    -- 결과 출력
    DBMS_OUTPUT.PUT_LINE('Result: ' || result);
EXCEPTION
    WHEN OTHERS THEN
        -- 오류 메시지 출력
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

• SQLERRM: 마지막 실행 오류의 메시지를 반환.
• SQLCODE: 오류 번호를 반환.

2. DBMS_OUTPUT.PUT_LINE로 디버깅 : 함수 내부에 디버깅 메시지를 삽입하여 실행 흐름을 확인합니다.

CREATE OR REPLACE FUNCTION my_function(x IN NUMBER) RETURN NUMBER IS
    result NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Function started. Input: ' || x);

    -- 예제 로직
    IF x < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Input must be non-negative');
    END IF;

    result := x * 2;
    DBMS_OUTPUT.PUT_LINE('Function result: ' || result);

    RETURN result;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error in function: ' || SQLERRM);
        RAISE;
END;
/

이렇게 하면 함수 실행 중 어떤 단계에서 문제가 발생했는지 확인할 수 있습니다.

3. USER_ERRORS 뷰로 컴파일 오류 확인 : 컴파일 오류를 확인하려면 아래 쿼리를 실행합니다:

SELECT * FROM USER_ERRORS WHERE NAME = 'MY_FUNCTION';

• TEXT: 오류 메시지를 보여줍니다.
• LINE, POSITION: 오류가 발생한 위치를 제공합니다.

4. EXECUTE IMMEDIATE로 실행 오류 캡처 : 실행 중 오류가 발생한 SQL 문장을 동적으로 실행하면서 오류를 확인할 수 있습니다.

DECLARE
    stmt VARCHAR2(1000);
BEGIN
    stmt := 'BEGIN :result := my_function(-1); END;';
    EXECUTE IMMEDIATE stmt USING OUT :result;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Execution Error: ' || SQLERRM);
END;

5. TRACE 또는 V$DIAG_INFO 활용 : 추적 로그를 확인하여 오류의 상세한 내용을 분석할 수도 있습니다.

-- 추적 설정
ALTER SESSION SET SQL_TRACE = TRUE;

-- 추적 해제
ALTER SESSION SET SQL_TRACE = FALSE;

이후 생성된 트레이스 파일을 분석합니다.

6. SHOW ERRORS 명령 : SQL*Plus 또는 SQL Developer에서 오류를 확인할 때 사용합니다.

SHOW ERRORS FUNCTION my_function;

7. 오류 로그 테이블 사용 : 오류 정보를 데이터베이스 테이블에 저장하여 추적할 수 있습니다.

CREATE TABLE error_log (
    error_time  TIMESTAMP,
    error_code  NUMBER,
    error_msg   VARCHAR2(4000)
);

-- 함수 내에서 로그 기록
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO error_log (error_time, error_code, error_msg)
        VALUES (SYSTIMESTAMP, SQLCODE, SQLERRM);
        RAISE;
END;

위 방법 중 하나를 사용하여 실행 오류를 확인하고 문제를 해결할 수 있습니다. 디버깅 단계에서는 DBMS_OUTPUT.PUT_LINE을 적극 활용하는 것이 유용합니다.