Home » SQL & PL/SQL » SQL & PL/SQL » Continuing the process after an EXCEPTION (Oracle Database 10g Express Edition Release 10.2.0.1.0)
Continuing the process after an EXCEPTION [message #361377] Wed, 26 November 2008 02:48 Go to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi experts,

I have procedure (run by a job) that reads from a table (TABLE1) that executes the procedures (from the proc_name field of TABLE1) and query conditions etc.. If an error occured, exception will be implemented and an 'ERROR' message will flag the last_run_status field insted of 'DONE'..

My prob is that if i have 5 procedures stored in that table, and an error occured in procedure 3, procedures 4 and 5 will not be processed..

1 DONE
2 DONE
3 ERROR
4
5

instead of..

1 DONE
2 DONE
3 ERROR
4 DONE
5 DONE

Is there a way that if an exception occurs my process for the whole table will still continue?

Sample Code:
CREATE OR REPLACE PROCEDURE MyProc1 IS

 TYPE cv_mycursor IS REF CURSOR;
 cv_mycursor cv_cursor;
 v_genEndDate DATE;
 
 v_row TABLE1%ROWTYPE;

BEGIN

 open cv_cursor 'SELECT proc_name FROM TABLE1 order by process_order'
 loop
  fetch cv_cursor into v_row.procname;
  exit when cv_cursor%NOTFOUND;
 
  IF ...
   -- Process v_row.procname   
  
  ELSIF ...
   -- Process v_row.procname 
  
  ELSE
   -- Process v_row.procname
    
  END IF;
  
 end loop;
 
  dbms_output.put_line ('All processes for this day are done...');

 EXCEPTION WHEN OTHERS
 THEN  
  dbms_output.put_line ('* ERROR Process_Order: '||v_row.process_order||' Proc_Name:'||
v_row.proc_name||' Message: '||SQLERRM||' '||SQLCODE);

  SELECT SYSDATE INTO v_genEndDate FROM dual;
  UPDATE TABLE1
  set last_run_date = v_genEndDate,
      last_run_status = 'ERROR'
  where process_order=v_row.process_order AND proc_name=v_row.proc_name;
 COMMIT;

END;

So far im thinking of using GOTO as a work around? But is seems absurd that i use GOTO to continue the looping.. It is also not possible with this test code, forced exception error...

set serveroutput ON;

DECLARE
 str varchar2 (5 BYTE) := 'aa';
 str2 varchar2 (5 BYTE);
 
BEGIN

 dbms_output.put_line ('Start str: '||str);
 select TO_NUMBER(str) into str2 from dual;
 
 <<here>>
 dbms_output.put_line ('End str:'||str2);
 
 exception when others then
 dbms_output.put_line ('Error');
 goto here;
 
end;

ORA-06550: line 15, column 2:
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'HERE'
ORA-06550: line 15, column 2:
PL/SQL: Statement ignored

Im using Oracle 10g edition.. Hope ive explained my inquiry enough.. Smile

Thanks,
Wilbert

[Updated on: Wed, 26 November 2008 03:02] by Moderator

Report message to a moderator

Re: Continuing the process after an EXCEPTION [message #361380 is a reply to message #361377] Wed, 26 November 2008 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Enclose the statements you want to trap the exception in a PL/SQL block:
begin
  begin
    <something>
  exception
    when <deliberate or unfortunate exceptions>
  end;
  <<here>>
  <other things>
end;

And don't use WHEN OTHERS in this case.

Regards
Michel

[Updated on: Wed, 26 November 2008 03:03]

Report message to a moderator

Re: Continuing the process after an EXCEPTION [message #361383 is a reply to message #361377] Wed, 26 November 2008 03:05 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Oh yeah! Nice! Again, Big thanks to you Michel C.! Smile
Re: Continuing the process after an EXCEPTION [message #365074 is a reply to message #361377] Wed, 10 December 2008 19:47 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi again experts,

Done with the codes about a week ago and works quite well with the dynamic queries, im just wondering now, if I have coded it efficiently (in terms of my exception handling inquiry), if this is what Michel C. pointed out?

Though i included several exceptions, I still included the WHEN OTHERS exception, perhaps i need to include more specified exceptions..


CREATE OR REPLACE PROCEDURE MyProc1 IS

 TYPE cv_mycursor IS REF CURSOR;
 cv_mycursor cv_cursor;
 v_genEndDate DATE;
 ctr NUMBER :=0;
 
 v_row TABLE1%ROWTYPE;

BEGIN

 open cv_cursor 'SELECT proc_name FROM TABLE1 order by process_order'
 loop

 <<here3>>  

  fetch cv_cursor into v_row.procname;
  exit when cv_cursor%NOTFOUND;
 
  IF ...
   BEGIN 
    -- Process v_row.procname, increment ctr 
   EXCEPTION
    WHEN VALUE_ERROR then ...
     goto here1;
    WHEN INVALID_NUMBER then ...
     goto here1;
    WHEN OTHERS then ...
     goto here1;
   END;
  END IF;  

  <<here1>>
  IF ...
   BEGIN 
    -- Process v_row.procname, increment ctr  
   EXCEPTION
    WHEN VALUE_ERROR then ...
     goto here2;
    WHEN INVALID_NUMBER then ...
     goto here2;
    WHEN OTHERS then ...
     goto here2;
   END;
  END IF;  

  <<here2>>
  IF ...
   BEGIN 
    -- Process v_row.procname, increment ctr 
   EXCEPTION
    WHEN VALUE_ERROR then ...
     goto here3;
    WHEN INVALID_NUMBER then ...
     goto here3;
    WHEN OTHERS then ...
     goto here3;
   END;
  END IF;    
 end loop;
 
  dbms_output.put_line ('Records Processed for this day: '||ctr);
  dbms_output.put_line ('All processes for this day are done...');

 COMMIT;

END;



I also use SET SERVEROUTPUT ON always when running procedures or functions to display valuable end results for checking purposes..

I believe there's some way that it will be SET ON always? or some way to prompt/remind me of the end results other than displaying using dbms_output.put_line?

Any pointers or tips will do.. Would really like to learn alot from you experts, im just a newbie.. Smile

Thanks,
Wilbert
Re: Continuing the process after an EXCEPTION [message #365076 is a reply to message #361377] Wed, 10 December 2008 20:58 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
I believe there's some way that it will be SET ON always?

No, there is none; it is client's work and it is not possible to force it from server (where the stored procedure resides).
Quote:
or some way to prompt/remind me of the end results other than displaying using dbms_output.put_line?

Yes, function return value or OUT parameters are designed for doing this - pass the result(s) to the caller. You may also RAISE the exception after all the processing under required conditions.
Re: Continuing the process after an EXCEPTION [message #365112 is a reply to message #365074] Thu, 11 December 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I still included the WHEN OTHERS exception,

And you are wrong.
OTHERS is for logging and reraising not for skipping.
Only catch the exceptions you know you can have and what to do with.

Regards
Michel
Re: Continuing the process after an EXCEPTION [message #365649 is a reply to message #361377] Thu, 11 December 2008 18:21 Go to previous message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Thanks flyboy and Michel C.! Will keep those pointers in mind.. Smile
Previous Topic: SQL Problem
Next Topic: Query to find out weekly average of job execution time
Goto Forum:
  


Current Time: Sat Dec 10 07:00:56 CST 2016

Total time taken to generate the page: 0.11988 seconds