Is any possible way to get warning message through by oracle

Question

I am working in developing oracle data base tool and I want display a warning message when executing a 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;
/

The above function will execute successfully, but it won't throw any error message even though its status would be invalid.

How would I get the warning message thrown out in Java?

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 03 '12 at 12:19
To be honest. If you just add the semi-colon after `dual` then this'll compile fine. Why not just ensure that everything is coded correctly and test that this is so? — Ben, May 03 '12 at 12:27
Function status is invalid means there must be some issue. your code must populate `SQLException` with info. — Subhrajyoti Majumder, May 03 '12 at 12:01

score 2 · Answer 1 · answered May 03 '12 at 18:55

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

score 0 · Answer 2 · answered May 03 '12 at 12:03

0

Why don't you just check if the object is invalid "before" running it ? Something like this will help.

select *
from 
   dba_objects 
where 
   status != 'VALID'
order by
   owner,
   object_type
;

answered May 03 '12 at 12:03

BigMike

6,683
1
23
24

1

It's [_never_ wise to write `select *`](http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select). – Ben May 03 '12 at 12:07
1

@Ben: of course, but that was just an example. if you look at the where condition you'll clearly see it doesn't filter on obj name. – BigMike May 03 '12 at 12:12

Is any possible way to get warning message through by oracle

2 Answers2