Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: EXECUTE IMMEDIATE IN ORACE 9i

Re: EXECUTE IMMEDIATE IN ORACE 9i

From: andrewst <member14183_at_dbforums.com>
Date: Fri, 11 Apr 2003 14:47:45 +0000
Message-ID: <2756820.1050072465@dbforums.com>

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
Received on Fri Apr 11 2003 - 09:47:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US