Home » SQL & PL/SQL » SQL & PL/SQL » Exception within the loop (PL/SQL (Oracle 9i))
Exception within the loop [message #313036] Thu, 10 April 2008 08:01 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi Friends,

I am handling Exceptions within the loop for no data found error. If that exception rasied in the loop the, its not terminating.. But its goint to next process.. How to terminate the exception within the loop?

Advance Thanks

Thanks
Manohar
Re: Exception within the loop [message #313037 is a reply to message #313036] Thu, 10 April 2008 08:05 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
My crystal ball is broken. So if you don't mind could you please post a code snippet.

Regards

Raj
Re: Exception within the loop [message #313040 is a reply to message #313037] Thu, 10 April 2008 08:14 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
My crystal ball is broken

I knew you were going to say that. Very Happy
Re: Exception within the loop [message #313041 is a reply to message #313036] Thu, 10 April 2008 08:16 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
LOOP
BEGIN
 UTL_FILE.get_line (pro_file, buffer_data);
BEGIN
  SELECT person_id
    INTO l_personid
    FROM per_people_f
    WHERE employee_number = l_empid
    AND SYSDATE BETWEEN effective_start_date 
	AND effective_end_date;
EXCEPTION
 WHEN NO_DATA_FOUND   THEN
     fnd_file.put_line (' NOT AVAILABLE');
 WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
      
  INSERT INTO spwr_hr.spwr_fica
              (empid, ytdfica,
                ytdsst
              )
  VALUES (l_personid, TO_NUMBER (l_ytdfica),
              TO_NUMBER (l_ytdsst)
         );
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        fnd_file.put_line (' NOT AVAILABLE');
   WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(SQLERRM);
 END;
END LOOP;
Re: Exception within the loop [message #313042 is a reply to message #313041] Thu, 10 April 2008 08:23 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Think about it,

BEGIN
  SELECT person_id
    INTO l_personid
    FROM per_people_f
    WHERE employee_number = l_empid
    AND SYSDATE BETWEEN effective_start_date 
	AND effective_end_date;
EXCEPTION
 WHEN NO_DATA_FOUND   THEN
     fnd_file.put_line (' NOT AVAILABLE');
 WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;


This section traps and handles the exception, a graceful exit occurs, control is passed to the calling environment, in this case, the enclosing block:

BEGIN
 UTL_FILE.get_line (pro_file, buffer_data);
BEGIN
  you handle (poorly) your exception here
END;
    control is returned to here.  
  INSERT INTO spwr_hr.spwr_fica
              (empid, ytdfica,
                ytdsst
              )
  VALUES (l_personid, TO_NUMBER (l_ytdfica),
              TO_NUMBER (l_ytdsst)
         );
EXCEPTION
   WHEN NO_DATA_FOUND THEN --This will never occur with your code
        fnd_file.put_line (' NOT AVAILABLE');
   WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(SQLERRM); --aaargh horrible code
                                    --just allow the error to raise
 END;


What exactly is it that you want to do with your code.
Previous Topic: Migrating testscript from MS SQLServer to Oracle
Next Topic: please, i need a help in procedure
Goto Forum:
  


Current Time: Sat Dec 10 06:57:31 CST 2016

Total time taken to generate the page: 0.07327 seconds