util

-- util.sql
SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- Error checker (silent unless errors exist)
CREATE OR REPLACE PROCEDURE CHECK_ERRORS(p_name VARCHAR2, p_type VARCHAR2) AS
    v_cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM user_errors
    WHERE name = UPPER(p_name)
      AND type = UPPER(p_type);

    IF v_cnt > 0 THEN
        FOR r IN (
            SELECT line, position, text
            FROM user_errors
            WHERE name = UPPER(p_name)
              AND type = UPPER(p_type)
            ORDER BY sequence
        ) LOOP
            DBMS_OUTPUT.PUT_LINE('Line ' || r.line || ': ' || r.text);
        END LOOP;

        RAISE_APPLICATION_ERROR(-20001, 'Compilation failed.');
    END IF;
END;
/
SHOW ERRORS PROCEDURE CHECK_ERRORS


--------------------------------------------------------------------------------
-- FUNCTION example
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION HELLO_FN RETURN VARCHAR2 AS
BEGIN
    RETURN 'Hello!';
END;
/
ALTER FUNCTION HELLO_FN COMPILE;
EXEC CHECK_ERRORS('HELLO_FN', 'FUNCTION');


--------------------------------------------------------------------------------
-- PROCEDURE example
--------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE DO_SOMETHING AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('OK');
END;
/
ALTER PROCEDURE DO_SOMETHING COMPILE;
EXEC CHECK_ERRORS('DO_SOMETHING', 'PROCEDURE');