Re: Can't run "execute immediate" in PL/SQL

On Thu, 19 Dec 2002 09:46:43 -0700, Thomas Gaines <Thomas.Gaines_at_noaa.gov> wrote:

>>"Guy" <ni4ni_at_hotmail.com> wrote in message
>>
>>> CREATE OR REPLACE procedure alter_constraints (action varchar2)
>>> is
>>> cursor cons is select constraint_name,table_name from
>>> user_constraints where constraint_type='R';
>>> str varchar2(150);
>>> i number;
>>> inner_action varchar2(50);
>>> begin
>>> inner_action:=action;
>>> for i in cons loop

<snip>
>>
>Niall Litchfield wrote:
>
>> Your code works as shown for me on 9.2
>
>I'm surprised that your testing was successful. My experience with cursor
>loops tells
>me that the declaration "i number;" is incorrect. Normally, the variable
>just after the "for" in "for i in cons loop" must correspond to the
>stuff being selected by the cursor. For more than one item, this
>variable is obviously a record. Am I in the wrong here?

 A cursor for loop implicitly declares the loop variable to be of the cursor record type; in this case it masks the declaration in the outer block. You could think of a cursor for loop such as:

for i in c_cursor
loop

    statements;
end loop;

 ... as really being:

declare

    i c_cursor%ROWTYPE;
begin

    for i in c_cursor

        statements;
    end loop;
end;

-- 
Andy Hassall (andy@andyh.org) icq(5747695) http://www.andyh.org
http://www.andyhsoftware.co.uk/space | disk usage analysis tool