Home » SQL & PL/SQL » SQL & PL/SQL » To handle the exception and continue the process (Oracle 10g)
To handle the exception and continue the process [message #564466] Fri, 24 August 2012 02:54 Go to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Hi Team,

I am trying to execute the below package.
While executing i face a problem where when NO DATA FOUND the excpetion is handled and coming out of the loop.
but i want to to continue the loop after hadnling the exception.

Is there anyway i can modify the code

CREATE OR replace PACKAGE BODY pkg_purge_archive_check 
AS 
  PROCEDURE Purge_archive_tables_check (purgerows IN NUMBER) 
  IS 
    v_num_1          NUMBER(10); 
    v_num_2          NUMBER(10); 
    v_multiplier     NUMBER(10); 
    num_total_rows   NUMBER(10); 
    num_total_rows_1 NUMBER(10); 
    v_num_rows       VARCHAR2(10); 
    v_process        VARCHAR2(50); 
    n                DATE; 
    v_err_code       NUMBER; 
    v_err_msg        VARCHAR2(200); 
  BEGIN 
      v_num_1 := purgerows; 

      v_multiplier := 1; 

      SELECT Nvl((SELECT 'no' 
                  FROM   archive_data 
                  WHERE  category = 'ARCHIVE PKG PURGE' 
                         AND Trunc(start_time) = Trunc(SYSDATE) 
                         AND ROWNUM = 1), 'yes') 
      INTO   v_num_rows 
      FROM   dual; 

      IF v_num_rows = 'yes' THEN 
        v_process := 'ARCHIVE_PROCESS_1'; 
      ELSE 
        v_process := 'ARCHIVE_PROCESS_2'; 
      END IF; 

      FOR c1 IN (SELECT owner 
                        ||'.' 
                        ||table_name table_name 
                 FROM   all_tables 
                 WHERE  owner = 'ARCHIVE' 
                        AND num_rows > 0 
                 ORDER  BY table_name) LOOP 
dbms_output.Put_line(c1.table_name); 

EXECUTE IMMEDIATE 
'select ARCH_TS from '||c1.table_name||' where arch_ts < (sysdate-650) and rownum=1' INTO n; 

IF SQL%FOUND THEN 
  IF c1.table_name = 'ARCHIVE.SHIPMENT_STATUS_DMP' THEN 
    v_multiplier := 5; 
  ELSE 
    v_multiplier := 1; 
  END IF; 

  v_num_2 := v_num_1 * v_multiplier; 

  INSERT INTO archive_data 
              (id, 
               category, 
               insert_user, 
               start_time) 
  VALUES      (c1.table_name, 
               'ARCHIVE PKG PURGE', 
               v_process, 
               SYSDATE); 

  EXECUTE IMMEDIATE 
'delete from '||c1.table_name||' where arch_ts < (sysdate-650) and rownum <='||v_num_2; 

num_total_rows := SQL%rowcount; 

UPDATE archive_data 
SET    end_time = SYSDATE, 
       value1 = num_total_rows 
WHERE  id = c1.table_name 
       AND insert_user = v_process 
       AND category = 'ARCHIVE PKG PURGE'; 
END IF; 

COMMIT; 
END LOOP; 
EXCEPTION 
  WHEN no_data_found THEN 
             dbms_output.Put_line('NO DATA FOUND MANUAL'); WHEN OTHERS THEN 
             v_err_code := SQLCODE; 

             v_err_msg := Substr(SQLERRM, 1, 200); 

             dbms_output.Put_line('Error code: ' 
                                  ||v_err_code); 

             dbms_output.Put_line('Error message: ' 
                                  ||v_err_msg); 
END; 
END pkg_purge_archive_check; 


Please advise
Re: To handle the exception and continue the process [message #564468 is a reply to message #564466] Fri, 24 August 2012 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So push the exception hanlder close to the statement you want to trap the exception (and so inside the loop).

And REMOVE the STUPID WHEN OTHERS.

Regards
Michel
Re: To handle the exception and continue the process [message #564471 is a reply to message #564468] Fri, 24 August 2012 03:38 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
hi Michel,

Thanks for the reply .

Tried that way but getting the error as quoted below

the modified code is
create or replace PACKAGE BODY pkg_tesco_purge_archive_check
AS
  PROCEDURE Purge_archive_tables_check (purgerows IN NUMBER)
  IS
    v_num_1        NUMBER(10);
    v_num_2        NUMBER(10);
    v_multiplier   NUMBER(10);
    num_total_rows NUMBER(10);
 num_total_rows_1 NUMBER(10);
    v_num_rows     VARCHAR2(10);
    v_process      VARCHAR2(50);
    N             DATE;
	v_err_code NUMBER;
v_err_msg VARCHAR2(200);
  BEGIN
      v_num_1 := purgerows;
      v_multiplier := 1;
      
	  select nvl((SELECT 'no'
            FROM   tesco_archive_data
   where CATEGORY='ARCHIVE PKG PURGE' AND
   TRUNC(START_TIME)=TRUNC(SYSDATE) and
   rownum=1),'yes')INTO   v_num_rows from dual;
  
  IF v_num_rows = 'yes' THEN
        v_process := 'ARCHIVE_PROCESS_1';
      ELSE
        v_process := 'ARCHIVE_PROCESS_2';
      END IF;
  
  FOR c1 IN (SELECT owner
                        ||'.'
                        ||table_name table_name
                 FROM   all_tables
                 WHERE  owner = 'ARCHIVE'
                        AND num_rows > 0
                  ORDER  BY table_name) LOOP

				  dbms_output.Put_line(c1.table_name);
INSERT INTO tesco_archive_data
              (id,
               category,
               insert_user,
               start_time)
  VALUES      (c1.table_name,
               'SELECTION',
               v_process,
               SYSDATE);
			   
EXECUTE IMMEDIATE 'select ARCH_TS from '||c1.table_name||' where arch_ts < (sysdate-650) and rownum=1' INTO N;
IF SQL%FOUND THEN
  IF c1.table_name = 'ARCHIVE.SHIPMENT_STATUS_DMP' THEN
    v_multiplier := 5;
  ELSE
    v_multiplier := 1;
  END IF;
  v_num_2 := v_num_1 * v_multiplier;
  UPDATE tesco_archive_data
SET    end_time = SYSDATE
WHERE  id = c1.table_name
       AND insert_user = v_process
    and category='SELECTION';
INSERT INTO tesco_archive_data
              (id,
               category,
               insert_user,
               start_time)
  VALUES      (c1.table_name,
               'ARCHIVE PKG PURGE',
               v_process,
               SYSDATE);
EXECUTE IMMEDIATE
'delete from '||c1.table_name||' where arch_ts < (sysdate-650) and rownum <='||v_num_2;
num_total_rows := SQL%rowcount;
UPDATE tesco_archive_data
SET    end_time = SYSDATE,
       value1 = num_total_rows
WHERE  id = c1.table_name
       AND insert_user = v_process
    and category='ARCHIVE PKG PURGE';
END IF;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('NO DATA FOUND MANUAL');

COMMIT;
END LOOP;
END;
END pkg_tesco_purge_archive_check;


Quote:

Errors for PACKAGE BODY PKG_TESCO_PURGE_ARCHIVE_CHECK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
82/1 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
of the following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe

89/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
;

LINE/COL ERROR
-------- -----------------------------------------------------------------


Please advise
Re: To handle the exception and continue the process [message #564472 is a reply to message #564471] Fri, 24 August 2012 03:43 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
Do you understand which statement cause the NO_DATA_FOUND exception ?
Re: To handle the exception and continue the process [message #564473 is a reply to message #564472] Fri, 24 August 2012 03:51 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Hi,

If i am not wrong its this statement that will have NO_DATA_FOUND exception

Quote:

EXECUTE IMMEDIATE 'select ARCH_TS from '||c1.table_name||' where arch_ts < (sysdate-650) and rownum=1' INTO N;


Re: To handle the exception and continue the process [message #564476 is a reply to message #564473] Fri, 24 August 2012 04:01 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
You are not wrong.
Re: To handle the exception and continue the process [message #564478 is a reply to message #564473] Fri, 24 August 2012 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you correctly indent your code I'm sure you'll see the syntax error.
Your code is a mess.

Regards
Michel
Re: To handle the exception and continue the process [message #564484 is a reply to message #564478] Fri, 24 August 2012 05:06 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
I intended though couldnt spot that error. please help me
Re: To handle the exception and continue the process [message #564487 is a reply to message #564484] Fri, 24 August 2012 05:13 Go to previous messageGo to next message
flyboy
Messages: 1750
Registered: November 2006
Senior Member
Maybe, the most useful way for fixing that error would be acquainting yourself with correct PL/SQL syntax. Fortunately, Oracle already wrote a set of books describing it. They are available e.g. online on http://tahiti.oracle.com/

A quick look into PL/SQL User's Guide and Reference will reveal, that EXCEPTION word is not a command - it is just a part of anonymous block (DECLARE .. BEGIN .. END). So, you have to create one anonymous block inside that loop - EXCEPTION part will catch errors after corresponding BEGIN word.
Re: To handle the exception and continue the process [message #564491 is a reply to message #564484] Fri, 24 August 2012 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dhivyaenjoy wrote on Fri, 24 August 2012 12:06
I intended though couldnt spot that error. please help me


Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: To handle the exception and continue the process [message #564499 is a reply to message #564491] Fri, 24 August 2012 07:03 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
still cant find out.. if you had spotted out help
Re: To handle the exception and continue the process [message #564500 is a reply to message #564499] Fri, 24 August 2012 07:09 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need a BEGIN to go with each exception section.
Re: To handle the exception and continue the process [message #564501 is a reply to message #564500] Fri, 24 August 2012 07:17 Go to previous messageGo to next message
flyboy
Messages: 1750
Registered: November 2006
Senior Member
cookiemonster wrote on Fri, 24 August 2012 14:09
You need a BEGIN to go with each exception section.

And END, as I spotted in my post.

Unfortunately for him, original poster just ignores me. So, I will just try to make him start using his own brain. Quick questions: how shall Oracle determine:
- which statements the exception handler handles
- where the handling code ends
? Is anything like that present in your code? What about adding it?
Re: To handle the exception and continue the process [message #564503 is a reply to message #564499] Fri, 24 August 2012 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dhivyaenjoy wrote on Fri, 24 August 2012 14:03
still cant find out.. if you had spotted out help


Michel Cadot wrote on Fri, 24 August 2012 12:58
dhivyaenjoy wrote on Fri, 24 August 2012 12:06
I intended though couldnt spot that error. please help me


Use SQL*Plus and copy and paste your session.

Regards
Michel


Re: To handle the exception and continue the process [message #564509 is a reply to message #564503] Fri, 24 August 2012 09:06 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Hi All,

your post helped me a lot. was able to crack it . Thanks so much. Below is the corrected code
create or replace PACKAGE BODY pkg_purge_archive_check
AS
  PROCEDURE Purge_archive_tables_check (purgerows IN NUMBER)
  IS
    v_num_1        NUMBER(10);
    v_num_2        NUMBER(10);
    v_multiplier   NUMBER(10);
    num_total_rows NUMBER(10);
 num_total_rows_1 NUMBER(10);
    v_num_rows     VARCHAR2(10);
    v_process      VARCHAR2(50);

	v_err_code NUMBER;
v_err_msg VARCHAR2(200);
  BEGIN
      v_num_1 := purgerows;
      v_multiplier := 1;
      
	  select nvl((SELECT 'no'
            FROM   _archive_data
   where CATEGORY='ARCHIVE PKG PURGE' AND
   TRUNC(START_TIME)=TRUNC(SYSDATE) and
   rownum=1),'yes')INTO   v_num_rows from dual;
  
  IF v_num_rows = 'yes' THEN
        v_process := 'ARCHIVE_PROCESS_1';
      ELSE
        v_process := 'ARCHIVE_PROCESS_2';
      END IF;
 
  FOR c1 IN (SELECT owner
                        ||'.'
                        ||table_name table_name
                 FROM   all_tables
                 WHERE  owner = 'ARCHIVE'
                        AND num_rows > 0
                  ORDER  BY table_name) LOOP

				  dbms_output.Put_line(c1.table_name);

INSERT INTO _archive_data
              (id,
               category,
               insert_user,
               start_time)
  VALUES      (c1.table_name,
               'SELECTION',
               v_process,
               SYSDATE);
		DECLARE
		    N             DATE;
		BEGIN
				EXECUTE IMMEDIATE 'select ARCH_TS from '||c1.table_name||' where arch_ts < (sysdate-650) and rownum=1' INTO N;
					IF SQL%FOUND THEN
					IF c1.table_name = 'ARCHIVE.SHIPMENT_STATUS_DMP' THEN
						v_multiplier := 5;
					ELSE
						v_multiplier := 1;
					END IF;
  
					v_num_2 := v_num_1 * v_multiplier;
					
					UPDATE _archive_data
					SET    end_time = SYSDATE
					WHERE  id = c1.table_name
					AND insert_user = v_process
					and category='SELECTION';
					
					INSERT INTO _archive_data
					(	id,
						category,
						insert_user,
						start_time)
					  VALUES      
					  (c1.table_name,
						'ARCHIVE PKG PURGE',
						v_process,
					    SYSDATE);
						
					EXECUTE IMMEDIATE
					'delete from '||c1.table_name||' where arch_ts < (sysdate-650) and rownum <='||v_num_2;
					
					num_total_rows := SQL%rowcount;

					UPDATE _archive_data
					SET    end_time = SYSDATE,
						   value1 = num_total_rows
					WHERE  id = c1.table_name
						   AND insert_user = v_process
						and category='ARCHIVE PKG PURGE';
					
					END IF;
					COMMIT;
					
					EXCEPTION
						WHEN NO_DATA_FOUND THEN
							DBMS_OUTPUT.PUT_LINE('NO DATA FOUND MANUAL');
					END;
					

END LOOP;

END;
END pkg_purge_archive_check;
icon14.gif  Re: To handle the exception and continue the process [message #564510 is a reply to message #564501] Fri, 24 August 2012 09:15 Go to previous message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Hey flyboy,

Got your point cracked the code.. Thanks for your help ...
Previous Topic: Question about sys_guid
Next Topic: set forceplan on
Goto Forum:
  


Current Time: Fri Apr 18 05:36:52 CDT 2014

Total time taken to generate the page: 0.13254 seconds