Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
In java i could able to get warning by st.getWarnings() , but display in general java.sql.SQLWarning: Warning: execution completed with warning , can i able to query and get exact oracle . –  Mallika May 3 '12 at 12:20
add comment

closed as off topic by Jack Douglas May 4 '12 at 16:05

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 2 down vote accepted

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    
share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.