EXCEPTION HANDLING in PL/SQL

[Muskaan]

Evidently I am a novice at this.

I have written the following procedure which gives compilation errors. The intention is to continue the cursor loop even when I come across tables that I do not have access to. I am sure it will be easy to tell for any one of you experts... help!

PROCEDURE
-------------------------
CREATE OR REPLACE PROCEDURE A1
AS
CURSOR c1 is
select distinct table_name from all_tab_columns where TABLE_NAME not in
(select table_name from all_tab_columns where data_type='DATE' ) and
TABLE_NAME not like '%$%' and OWNER='N1_DBA';
v_tname all_tab_columns.table_name%type;

FUNCTION countit(tn VARCHAR2)
RETURN NUMBER
IS
return_value NUMBER(10);
strsql VARCHAR2(255);
BEGIN
strsql := 'select count(1) from ' || tn;
EXECUTE IMMEDIATE strsql into return_value;
RETURN return_value;
END;

BEGIN
open c1;
loop
fetch c1 into v_tname;
exit when c1%notfound;
dbms_output.put_line(v_tname || ' '|| TO_CHAR(countit(v_tname)));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(v_tname || ' == could not count');
END;
end loop;
close c1;
END;

ERRORS:
------------------
Errors for PROCEDURE A1:

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

30/2 PLS-00103: Encountered the symbol &quot;END&quot; when expecting one of the
following:
begin function package pragma procedure form

 

[sem]

You have 1 BEGIN and 2 END clauses. You should place one more begin to separate your &quot;problem&quot; block from the other code:

loop
BEGIN

fetch c1 into v_tname;
exit when c1%notfound;
dbms_output.put_line(v_tname || ' '|| TO_CHAR(countit(v_tname)));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(v_tname || ' == could not count');
END;
end loop;
Regards, Dima

 

[R1972]

Exception is defined in improper place. try the following statement

.......................
........................
BEGIN
open c1;
loop
fetch c1 into v_tname;
exit when c1%notfound;
dbms_output.put_line(v_tname || ' '|| TO_CHAR (countit(v_tname)));
end loop;
close c1;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(v_tname || ' == could not count');
END;
END;


Regards
Raj

 

[Muskaan]

all: thanks for your responses. I put in another Begin and things work just the way I had wanted. Raj, your suggestion will also work, but I wanted the loop to continue to next iteration even after an exception, which will not be possible in your suggested code.