Home » SQL & PL/SQL » SQL & PL/SQL » Error Handling
Error Handling [message #269939] Tue, 25 September 2007 03:42 Go to next message
dimis283
Messages: 38
Registered: July 2007
Location: greece
Member
I have this code

declare 
  cursor cur_pmg is
    SELECT * FROM DIMIS WHERE LNAME||FNAME NOT IN (SELECT LNAME||FNAME FROM PMG );--cursor gia PGAPOSBPERC
    
        begin
        begin
        open cur_pmg;
  loop
    fetch cur_pmg into v_asth1;
    v_PMGPATIENTS_COUNT:=cur_pmg%RowCount;
    exit when cur_pmg%notfound;
    --dbms_output.put_line('a: ' || rec_t.a || ', b: ' || rec_t.b);   
  SELECT  PPA_SEQ.NEXTVAL INTO    PPA_IDS FROM DUAL;  
    insert into PMGPATIENTS (ID,com_id,KODIKOS,LNAME,FATHER,SPOUSE,BIRTHDAY,ADDRESS,PHONE
,FAMILYSTATUS_FLAG,JOB,FNAME) 
   values (PPA_IDS,1,'-'||v_asth1.kodikos,v_asth1.LNAME,v_asth1.FATHER
,v_asth1.SPOUSE,v_asth1.BIRTHDAY,v_asth1.ADDRESS,v_asth1.PHONE
,v_asth1.FAMILYSTATUS_FLAG ,v_asth1.JOB,v_asth1.FNAME);  
   IF v_asth1.ASFALISTIKOS IS NOT NULL THEN
   SELECT cuh_id INTO  CODE_ID  FROM PMG WHERE ID=PPA_IDS;
  SELECT ID INTO ASFALIA FROM MSR WHERE COM_ID=1 AND DESCRIPTION=v_asth1.ASFALISTIKOS;   
 insert into MSRT (id,com_id,org_id,cuh_id,ppa_id)            VALUES  
   (COR_SEQ.NEXTVAL,1,ASFALIA,CODE_ID,PPA_IDS) ;
  END IF;
   commit;
   wn_Ok := wn_Ok + 1;
  end loop; 
   
  --close cur_aposv;
     exception
         when others then
           log();
      end;
     log();

   close cur_pmg;  
exception
   when others then
      if cur_pmg%IsOpen then
         close cur_pmg;
      end if;
      
      np$Err.Unhandled_Exception('Some');
  end ;

Generally I have a cursor and doing a loop with their records I make a insertion at another tables.
But when there is an exception stop the insertion (maybe the loop).
How can we do not stop the entire insertion for a single error?
Dimis
--edit: I make the code more readable

[Updated on: Tue, 25 September 2007 04:11]

Report message to a moderator

Re: Error Handling [message #269941 is a reply to message #269939] Tue, 25 September 2007 03:46 Go to previous messageGo to next message
dimis283
Messages: 38
Registered: July 2007
Location: greece
Member
I make a
begin
insert....
exception
when ....
end
There I want to catch the exceptions.
I wish It will be good.
Dimis
Re: Error Handling [message #269942 is a reply to message #269939] Tue, 25 September 2007 03:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can write the exception handler inside the loop.

By
Vamsi
Re: Error Handling [message #269949 is a reply to message #269939] Tue, 25 September 2007 03:54 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad that you didn't follow the following guideline: "Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button."

Regards
Michel

Previous Topic: Execute dbms_scheduler.enable from variable
Next Topic: default behaviour of timestamp
Goto Forum:
  


Current Time: Fri Dec 09 19:39:54 CST 2016

Total time taken to generate the page: 0.09700 seconds