Re: Trouble coverting a Procedure into a "STORED" procedure
From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 06 Feb 2004 18:37:33 -0800
Message-ID: <1076121400.237621_at_yasure>
>>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.
Date: Fri, 06 Feb 2004 18:37:33 -0800
Message-ID: <1076121400.237621_at_yasure>
Andy Hassall wrote:
> 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; > END; > NULL; > END x; > / > > Just trying to clarify your statement 'remove DECLARE it does not belong in > any proc or function' as that's pretty absolute.
The comment 'remove DECLARE' was specific to the OP's example. Not to every possible usage.
-- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan_at_x.washington.edu (replace 'x' with a 'u' to reply)Received on Sat Feb 07 2004 - 03:37:33 CET