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

Niall -

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?

With a cursor loop like this, if Guy simply eliminates the declaration "i number" and let PL/SQL implicitly define the output record, I suspect that all will be well.

Steven Feuerstein has a simple example of this on page 211 of his book "Oracle PL/SQL Programming". Please note that I'm looking at the early version of the book.

Bye,
TG

Niall Litchfield wrote:

> Your code works as shown for me on 9.2
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Guy" <ni4ni_at_hotmail.com> wrote in message
> news:d2eb532b.0212190158.1858d489_at_posting.google.com...
> > Hello
> >
> >
> > I wrote a small PL/SQL procedure that should switch all referential
> > constraints enabled/disabled.
> > It seems very simple and it compiles just fine. But when I try to run
> > it it says "Invalid Character" in line 15 (I marked the line in the
> > code - the one with the execute immediate).
> > I cant see anything wrong in this line.
> > Can anyone see whats the problem here ?
> >
> > Thanks
> >
> > Guy
> >
> >
> >
> > 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
> > str:='alter table '||i.table_name||' '||inner_action||' constraint
> > '||i.constraint_name;
> > dbms_output.put_line(str);
> > -- this is the problematic line
> > -- | |
> > -- v v
> > EXECUTE IMMEDIATE str;
> > end loop;
> > end;
> > /
Received on Thu Dec 19 2002 - 10:46:43 CST