Is any possible way to get warning message through by oracle [closed]

Question

I am working in developing oracle data base tool, I want display warning message when executing function. For example:

CREATE OR REPLACE FUNCTION FN_OEDS_CONVERTDATE2(
V_DATE DATE) RETURN VARCHAR2
    IS
BEGIN
DECLARE
    V_RETVALUE VARCHAR2(15) :='';
    BEGIN


    SELECT
        CASE V_DATE
            WHEN NULL THEN ''
            ELSE TO_CHAR(V_DATE, 'DD-MM-YYYY')
        END INTO V_RETVALUE FROM DUAL

     RETURN V_RETVALUE;
    END;

END;
/

Above function will execute successfully, but it wont through any error message , but it status would be invalid, how to get the warning message through by out in java.

Answer 1

Compiler messages are not returned to the client through the getWarnings() on the statement or the connection.

Instead once you have identified the warning through Statement.getWarnings() you need to parse retrieve the compiler errors from the view ALL_ERRORS

Something like:

SELECT line, 
       position, 
       text, 
       name, 
       type 
FROM all_errors 
WHERE owner = user
AND type = 'FUNCTION'
AND name = 'FN_OEDS_CONVERTDATE2'
ORDER BY line, position