Home » SQL & PL/SQL » SQL & PL/SQL » stored proc > error doesn't fall in the exception block  () 1 Vote
stored proc > error doesn't fall in the exception block [message #188418] Fri, 18 August 2006 08:28 Go to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
in my exception block of my stored proc i have some specific actions, but it seems that when this error occurs: "ORA-01850: hour must be between 0 and 23", it doesn't falles in the WHEN OTHERS block.
is this normal?

PROCEDURE test_cursor_2 (
      p_recordset          OUT   cursor_type,
      return_code_out      OUT   VARCHAR2,
      return_message_out   OUT   VARCHAR2
   )
   IS


   BEGIN
      return_code_out := '00000';
      return_message_out := 'OK';

      OPEN p_recordset FOR
	  select col1, col2
          from table1 
          where SYSDATE > TO_DATE (busi_servicedate, 'DD-MM-YYYY HH24:MI:SS');
	  
   
   EXCEPTION
   		WHEN OTHERS THEN
         return_code_out := '43899';
         return_message_out := 'Unknown error.';
 
   END test_cursor_2;


it's not that the exceptions as a whole do not work in my stored proc, because i DO CAN catch the NO_DATA_FOUND exception when i add another exception for it.

WHY DOESN'T THE ERROR ABOVE FALL IN THE OTHERS BLOCK ????

[Updated on: Fri, 18 August 2006 08:32]

Report message to a moderator

Re: stored proc > error doesn't fall in the exception block [message #188423 is a reply to message #188418] Fri, 18 August 2006 08:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would be because the error you are getting does not happen until you perform a FETCH from that cursor that attempts to return a row with invalid data.

Thus the exception isn't being raised in that code at all, but instead it comes from wherever you do a FETCH from that RefCursor

This is just one more example (if such a thing were needed) of why you should always STORE DATES IN COLUMNS OF TYPE DATE

<RANT>
What is this madness that overtakes developers and designers, and makes them think
Quote:

Ooo. I need to store a date with a time component. Rather than putting it in a data type that will store just than, and validate it on entry as well, I'll put it in a free text string. That will be much safer, and there'll never be any problems with invalid data, or different NLS parameters causing different formats to appear, or not being able to use indexes on it

</RANT>
Re: stored proc > error doesn't fall in the exception block [message #188428 is a reply to message #188423] Fri, 18 August 2006 09:11 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
JRowbottom wrote on Fri, 18 August 2006 13:48

STORE DATES IN COLUMNS OF TYPE DATE

I know, you are right on this, but designers thought years ago that it would reduce a lot of overhead when passing dateformats to other applications.

JRowbottom wrote on Fri, 18 August 2006 13:48

it comes from wherever you do a FETCH from that RefCursor.

I don't completely understand this.
So you mean that the stored procedure correctly completes (without falling in exception), and all code after this "open p_recordset for..." is also completed with success ?


[Updated on: Fri, 18 August 2006 09:11]

Report message to a moderator

Re: stored proc > error doesn't fall in the exception block [message #188430 is a reply to message #188428] Fri, 18 August 2006 09:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's an example to show where the error happens:

CREATE TABLE test_data (col_1  VARCHAR2(10), date_col  VARCHAR2(30));

INSERT INTO test_data VALUES ('A','01-jan-2006 23:59:59');
INSERT INTO test_data VALUES ('A','01-jan-2006 25:59:59');

CREATE OR REPLACE PROCEDURE test_cursor AS
  TYPE c_ref  IS REF CURSOR;
  p_recordset  c_ref;
  p_1  VARCHAR2(30);
  p_2  VARCHAR2(30);  
BEGIN

  OPEN p_recordset FOR
    SELECT col_1, date_col
    FROM test_data 
    WHERE SYSDATE > TO_DATE (date_col, 'DD-mon-YYYY HH24:MI:SS');

  DBMS_OUTPUT.PUT_LINE('Cursor Opened');
  FETCH p_recordset INTO p_1,p_2; 

  DBMS_OUTPUT.PUT_LINE('First Fetch');
  FETCH p_recordset INTO p_1,p_2;

  DBMS_OUTPUT.PUT_LINE('Second Fetch');

  CLOSE p_recordset; 

EXCEPTION
  WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20001,'Error '||SQLERRM);

END test_cursor;
/
SQL> SET SERVEROUTPUT ON SIZE 10000
SQL> BEGIN
  2    test_cursor;
  3  END;
  4  /
Cursor Opened
First Fetch
BEGIN
*
ERROR at line 1:
ORA-20001: Error ORA-01850: hour must be between 0 and 23
ORA-06512: at "FPS.TEST_CURSOR", line 25
ORA-06512: at line 2


As you can see, the cursor is opened correctly (the first Dbms_output message is shown), and the first fetch happens (2nd message).
The exception is raised at the second FETCH statement.

So, in answer to your question, yes, the stored procedure that you posted will complete and the code after it will run until it tries to fetch an invalid row.
Re: stored proc > error doesn't fall in the exception block [message #188435 is a reply to message #188418] Fri, 18 August 2006 09:54 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
@JRowbottom:
so in my example above, it is my java program that is fetching in the cursor.
the cursor is executed towards the database (after the stored proc ends) as a corrupt select-statement ?
the only difference is that my java program falls in exception due to this corrupt statement, with the errorcode that oracle now returns with that corrupt select statement?
correct?
Re: stored proc > error doesn't fall in the exception block [message #188436 is a reply to message #188435] Fri, 18 August 2006 10:02 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes, the exception is being raised by your JAVA code. It is attempting to fetch a record from the cursor, and the database is raising that exception when it hits the bad data.
Previous Topic: Strange error messages when using inline views and full outer join
Next Topic: stored proc > cursor as output to java > max cursors exceeded > due to not closing in excep
Goto Forum:
  


Current Time: Sat Dec 03 17:54:11 CST 2016

Total time taken to generate the page: 0.12702 seconds