Re: Trouble coverting a Procedure into a "STORED" procedure

On Fri, 06 Feb 2004 17:44:22 -0800, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

>Andy Hassall wrote:
>
>> On Wed, 04 Feb 2004 23:12:51 -0800, Daniel Morgan <damorgan_at_x.washington.edu>
>> wrote:
>>
>>>remove DECLARE it does not belong in any proc or function
>>
>> A minor point with this statement: do you more specifically mean that DECLARE
>> doesn't belong in the top-level block of a stored procedure/function? (Which is
>> clearly true because it wouldn't compile otherwise).
>>
>> But if your stored procedure has nested blocks, then these blocks can have
>> DECLARE clauses; it can be good practice to have declarations close to usage in
>> various cases.
>>
>> For example, declaring exceptions in blocks around individual statements where
>> you want to catch a specific exception, handle it and continue as appropriate.
>
>What I mean is very simply put by trying these two examples:
>
>CREATE OR REPLACE PROCEDURE x IS
>
>i VARCHAR2(10);
>
>BEGIN
> NULL;
>END x;
>/
>
>CREATE OR REPLACE PROCEDURE x IS
>
>DECLARE
>
>i VARCHAR2(10);
>
>BEGIN
> NULL;
>END x;
>/
>
>One is valid syntax. The other is not.

 So that would be 'yes', then?

 I'm saying that DECLARE can be present in a procedure, e.g. extending the example above with a ridiculously trivial nested block:

CREATE OR REPLACE PROCEDURE x IS

    i VARCHAR2(10);
BEGIN
    DECLARE
        e exception;
    BEGIN
        raise e;
    EXCEPTION

	WHEN e THEN
            NULL;

 Just trying to clarify your statement 'remove DECLARE it does not belong in any proc or function' as that's pretty absolute.

-- 
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>