Toad World® Forums

Testing contents of SQLERRM

We received this request: “How can I check the contents of the exception message (in SQLERRM) for the presence of a specific string?”

Code Tester does not currently provide that ability directly (the Exception outcome type only looks at the error code). You can, however, get to where you need to go by creating a wrapper about the program being tested. Suppose, for example, that I am testing this function:

CREATE OR REPLACE FUNCTION betwnstr (string_in IN VARCHAR2,
start_in IN PLS_INTEGER,
end_in IN PLS_INTEGER)
RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
RAISE NO_DATA_FOUND;
RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;
/

I then create a procedure on top of it:

CREATE OR REPLACE PROCEDURE test_betwnstr (string_in IN VARCHAR2,
start_in IN PLS_INTEGER,
end_in IN PLS_INTEGER,
value_out OUT VARCHAR2,
error_out OUT VARCHAR2)
IS
BEGIN
value_out := betwnstr (string_in, start_in, end_in);
EXCEPTION
WHEN OTHERS
THEN
error_out := SQLERRM;
END;
/

I can then test the contents of error_out, using a “standard” string test (pick from equality, like, contains, etc.).

Hopefully we will add the ability to examine the error message directly in the future, but for now, at least there is a way to do it!