-- 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');