Re: Store Procedure Questions

Toh Hong Giep wrote:
>
> I have a store procedure which made use of the 'SQL%FOUND' and
> 'SQL%NOTFOUND' attributes after a select statement.
>
> However this doesn't seemed to work as expected. When there is no
> data, the store procedure will terminate abnormally giving the error that
> no data is found!! I did not have an exception statement for the store
> procedure. My code goes something like this:
>
> SELECT .....
> If SQL%FOUND then
> .....
> End if;
>
> Doesn't this handle the "exception" when there's no data selected. And
> if I were to include an exception statement at the bottom of the
> procedure, will the program continue to execute from the point where the
> exception happened, after the exception has been handled???
>
> Thanks for the invaluable help and advise. Regards to all

If you have access to Oracle PL/SQL Programming by Steven Feuerstein published by O'Reilly and Associates, Inc. see page 172 for details.

The short answer is:

"The behavior of the %NOTFOUND attribute for UPDATE, DELETE or INSERT statements is the opposite of %FOUND. The situation for an implicit cursor is a bit different: when you use an implicit SELECT statement, never rely on the %FOUND and %NOTFOUND attributes.

When an implicit SELECT statement does not return any rows, PL/SQL immediately raises the NO_DATA_FOUND exception. When an implicit SELECT statment returns more than one row, PL/SQL immediately raises the TOO_MANY_ROWS exception. In either case, once the exception is raised, control shifts to the exception section of the PL/SQL block"

The answer to the question about continuing on may be found on page 253.

"When an exception is raised in a PL/SQL block, normal execution is halted and control is transferred to the exception section. "You can never go home again," and you can never return fto the execution section once an exception is raised in that block. In some cases, however, the ability to continue past exceptions is exactly the desired behavior."

page 255 gives you a workaround to the above:

BEGIN
        DELETE FROM table1;
EXCEPTION
        WHEN OTHERS THEN NULL;
END BEGIN
        DELETE FROM table2;
EXCEPTION
        WHEN OTHERS THEN NULL;
END If an exception is raised in the first block, control passes to the first block's exception section. After that control is passed to the second block. Your idea of putting an exception handler at the end of the procedure is a way around having your the server dump your procedure. If you have other code in the procedure that you what to execute after your select statement, you'd do well to wrap it in a block.

I hope that this helps. The book is quite good. Get it if you can.

-Allan Received on Tue Jun 10 1997 - 00:00:00 CDT