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,
Member discussion: