Skip to content

Display a Function's Errors in Oracle

June 30th, 2017 - SoftwareTutorial(1 min)

TL;DR: Use the power of DBMS_OUTPUT.PUT_LINE, Luke!

I’ve built a function along the lines of:

CREATE OR REPLACE FUNCTION HAS_PANEL_FLAG_OK(
  P_USER_ID    in USERS.USER_ID%TYPE,
  P_PANEL_ID   in PANELS.PANEL_ID%TYPE,
)
RETURN integer
IS
  lv_count NUMBER;
BEGIN
  SELECT count(*) into lv_count
    FROM USERS
    WHERE USERS.PANEL_ID = P_PANEL_ID and USERS.USER_ID = P_USER_ID;
  return lv_count;
EXCEPTION
  when OTHERS then
    return 0;
END;

When using the function, I got some 0 results and I wanted to know why. The solution I got was using DBMS_OUTPUT.PUT_LINE:

CREATE OR REPLACE FUNCTION HAS_PANEL_FLAG_OK(
  P_USER_ID    in USERS.USER_ID%TYPE,
  P_PANEL_ID   in PANELS.PANEL_ID%TYPE,
)
RETURN integer
IS
  lv_count NUMBER;

  -- Error variables
  v_code  NUMBER;
  v_errm  VARCHAR2(64);
BEGIN
  -- The normal function code
EXCEPTION
  when OTHERS then
    v_code := SQLCODE;
    v_errm := SUBSTR(SQLERRM, 1, 64);
    DBMS_OUTPUT.PUT_LINE (v_code || ' ' || v_errm || '(U: ' || P_USER_ID || ', P:' || P_PANEL_ID || ')');
    return 0;
end;

Now, all you have to do is enable DBMS_OUTPUT in your environment and you’ll see the exceptions.

NOTE: This is useful while building and tuning the function. IMHO one should disable the PUT_LINE statements in production.

HTH,

Share on
Reddit
Linked in
Whatsapp

A little experiment: