CODEWITHSUNDEEP
oracle-databaseexceptionprocedure
superigno
superigno

Reputation: 1024

Oracle Doesn't Catch Exception

I have a procedure in Oracle, I encountered an error but it doesn't change the value of the p_out_msg, why is that so?? The error goes to the cursor instead.

Code:

PROCEDURE get_proj(p_date    IN VARCHAR2,
                   p_out_cur OUT sys_refcursor,
                   p_out_msg OUT VARCHAR2)
IS 
BEGIN
  OPEN p_out_cur FOR 
    SELECT *
      FROM table t
     WHERE TO_DATE(p_date, 'DD-MM-YYYY HH24:MI:SS') = t.date

  p_out_msg := 'SUCCESS';

EXCEPTION
   WHEN OTHERS THEN
     p_out_msg := SUBSTR('An error was encountered: '||SQLERRM, 1, 250);

END get_proj;

Say I entered an invalid date:

Output:

p_out_cur: ORA-01858: a non-numeric character was found where a numeric was expected

p_out_msg: SUCCESS

Here is the anonymous block as requested by @brenners1302:

DECLARE
  P_DATE VARCHAR2(200);
  P_OUT_CUR sys_refcursor;
  P_OUT_MSG VARCHAR2(200);
BEGIN
  P_DATE := '1111';

  PACKAGE_TEST.GET_PROJ(
    P_DATE => P_DATE,
    P_OUT_CUR => P_OUT_CUR,
    P_OUT_MSG => P_OUT_MSG
  );

  DBMS_OUTPUT.PUT_LINE(P_OUT_MSG); --SUCCESS

END;

Upvotes: 0

Views: 243

Answers (1)

diziaq
diziaq

Reputation: 7795

When you're opening a cursor with OPEN p_out_cur FOR the query SELECT * FROM ... is not being executed at the moment, but just associated with the cursor variable. So an exception may appear when you begin to FETCH from p_out_cur, because this is when the associated query starts its execution.

The simplest way to see it is change your procedure this way.

PROCEDURE get_proj(p_date    IN VARCHAR2,
                   p_out_cur OUT sys_refcursor,
                   p_out_msg OUT VARCHAR2)
IS 
 l_rec myTable%ROWTYPE;
BEGIN
       OPEN p_out_cur FOR 
        SELECT *
          FROM myTable t
         WHERE TO_DATE(p_date, 'DD-MM-YYYY HH24:MI:SS') = t.date

      -- at this point we can get an exception
      FETCH p_out_cur INTO l_rec;

      p_out_msg := 'SUCCESS';

EXCEPTION
   WHEN OTHERS THEN
       p_out_msg := SUBSTR('An error was encountered: '||SQLERRM, 1, 250);    
END get_proj;

So yes, it means that when you open a ref cursor and pass it to 'outer world', the one who tries to fetch it can get an exception. You may reduce the number of such cases with checking parameters before passing them to the functions used in SQL associated with cursors. For example,

PROCEDURE get_proj(p_date    IN VARCHAR2,
               p_out_cur OUT sys_refcursor,
               p_out_msg OUT VARCHAR2)
IS 
 l_normal_date DATE;
BEGIN
       -- if p_date has wrong format we better find it out
       -- before using it in a query
       l_normal_date := TO_DATE(p_date, 'DD-MM-YYYY HH24:MI:SS');

       OPEN p_out_cur FOR 
        SELECT *
          FROM myTable t
         WHERE l_normal_date = t.date

      p_out_msg := 'SUCCESS';

EXCEPTION
   WHEN OTHERS THEN
       p_out_msg := SUBSTR('An error was encountered: '||SQLERRM, 1, 250);    
END get_proj;

Upvotes: 3

Related Questions