Re: EXECUTE IMMEDIATE IN ORACE 9i

Originally posted by Nickp
> HI,
> Can anyone help?
>
> I'm using SQL/PLUS from ORACLE 9i to test out some SQL.
> Here's the code:
>
> BEGIN
> FOR r IN (SELECT 1 FROM all_tables WHERE table_name = 'ADDRESS')
> LOOP
> EXECUTE IMMEDIATE 'DROP TABLE ADDRESS'
> END LOOP;
> END;
>
> But it returns the following error:
>
> EXECUTE IMMEDIATE 'DROP TABLE ADDRESS';
> *
> ERROR at line 4:
> ORA-06550: line 4, column 13:
> PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of
> the following:
> := . ( @ % ;
> The symbol ":= was inserted before "IMMEDIATE" to continue.
>
> I thought that the EXECUTE IMMEDIATE could handel a DDL Statement?
>
> Any help
>
> Cheers Nick

Apart from the missing semicolon on the EXECUTE IMMEDIATE line, it works:

SQL> BEGIN
  2 FOR r IN (SELECT 1 FROM all_tables WHERE table_name = 'ADDRESS')   3 LOOP
  4 EXECUTE IMMEDIATE 'DROP TABLE ADDRESS';   5 END LOOP;
  6* END;
SQL> / PL/SQL procedure successfully completed.

Are you sure you are using a 9i database?

--
Posted via http://dbforums.com