Thank you for the reply.

With regard to not needing the exceptions: Actually, I'm relying on the
exceptions to cause the loop for that particular record to abort, then
continue with the next record.

I'll test the BEGIN / END within the loop and let everyone know how that
works out.

And the colon was a typo on my part. :-) My bad. But thank you for
catching it.

Jeff

-----Original Message-----
From: Chelur Jayadas
[mailto:oracledba-ezmlmshield-x12840288.[Email address protected]
Sent: Tuesday, January 08, 2008 3:40 PM
To: LazyDBA Discussion
Subject: RE: Exception Handling

Based on the code, you had provided there are two issues with that.
There is
no BEGIN after LOOP. There is a colon (:) and NOT semi-colon (;) after
the
END before END LOOP;

However,

The first three exceptions are raised by the procedure programmatically
and are then handled within the EXCEPTION section. Rather than RAISE
exceptions on certain conditions and then executing some code based on
that you can execute the code directly based on the conditions
themselves ...

CREATE OR REPLACE PROCEDURE xxxx.PROC_REORG_TABLE
AS
BAD_COL EXCEPTION;
NO_EXIST EXCEPTION;
NO_SPACE EXCEPTION;

BEGIN
FOR get_reorg_table_name IN .....
LOOP
BEGIN
IF V_IFEXISTS = 0 then
update xxxx.xxxx_reorg_tables
END IF;

IF V_BADEXISTS > 0 THEN
update xxxx.xxxx_reorg_tables
END IF;

IF V_SIZE > V_SPACE THEN
update xxxx.xxxx_reorg_tables
END IF;
EXCEPTION
WHEN OTHERS THEN
update xxxx.xxxx_reorg_tables
END;
END LOOP;
END;



-----Original Message-----
From: Cote Jeffrey
[mailto:oracledba-ezmlmshield-x38901874.[Email address protected]
Sent: Tuesday, January 08, 2008 3:24 PM
To: LazyDBA Discussion
Subject: Exception Handling

Hello, all.

I have a procedure in which I'm using user-defined exception handling.
Below is the code:

-------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE xxxx.PROC_REORG_TABLE
AS
BAD_COL EXCEPTION;
NO_EXIST EXCEPTION;
NO_SPACE EXCEPTION;

BEGIN
FOR get_reorg_table_name IN .....
LOOP
.....
IF V_IFEXISTS = 0 then
RAISE NO_EXIST;
END IF;
......
IF V_BADEXISTS > 0 THEN
RAISE BAD_COL;
END IF;
......
IF V_SIZE > V_SPACE THEN
RAISE NO_SPACE;
END IF;
..........
EXCEPTION
WHEN NO_EXIST THEN
update xxxx.xxxx_reorg_tables
........

WHEN NO_SPACE THEN
update xxxx.xxxx_reorg_tables
......

WHEN BAD_COL THEN
update xxxx.xxxx_reorg_tables
......

WHEN OTHERS THEN
update xxxx.xxxx_reorg_tables
.......
END:
END LOOP;
END;
-------------------------------------------------

The error I'm receiving is:

PLS-00103: Encountered the symbol "EXCEPTION"

Any help would be greatly appreciated.

Thanks in advance!

Jeff
PROTECTED COMMUNICATION (Privileged, Confidential, Copyrighted and/or
Proprietary)
This communication and any of its attachments may contain privileged or
confidential
information protected by legal rules or copyrighted or proprietary
materials of
Handleman Company and/or its affiliates protected by law. It is solely
for the use
of the intended recipient(s) named above. Any review, dissemination,
distribution,
forwarding, or copying of this communication or associated attachments,
or the taking of any action based on it, by someone other than the
intended recipient,
or the employee responsible for delivering this communication to the
intended recipient,
is prohibited. If you have received this communication in error reply to
the sender
via email, then permanently delete the original message and associated
attachments
and destroy any copies or printouts.
Thank you.


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




PROTECTED COMMUNICATION (Privileged, Confidential, Copyrighted and/or Proprietary)
This communication and any of its attachments may contain privileged or confidential
information protected by legal rules or copyrighted or proprietary materials of
Handleman Company and/or its affiliates protected by law. It is solely for the use
of the intended recipient(s) named above. Any review, dissemination, distribution,
forwarding, or copying of this communication or associated attachments,
or the taking of any action based on it, by someone other than the intended recipient,
or the employee responsible for delivering this communication to the intended recipient,
is prohibited. If you have received this communication in error reply to the sender
via email, then permanently delete the original message and associated attachments
and destroy any copies or printouts.
Thank you.

Oracle LazyDBA home page