| To handle the exception and continue the process [message #564466] |
Fri, 24 August 2012 02:54  |
 |
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 #564471 is a reply to message #564468] |
Fri, 24 August 2012 03:38  |
|
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 #564487 is a reply to message #564484] |
Fri, 24 August 2012 05:13 |
flyboy
Messages: 1903
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 #564501 is a reply to message #564500] |
Fri, 24 August 2012 07:17 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
cookiemonster wrote on Fri, 24 August 2012 14:09You 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 #564509 is a reply to message #564503] |
Fri, 24 August 2012 09:06 |
|
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; |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
