Re: EXECUTE IMMEDIATE ON ORACLE 9i

Originally posted by Gabriel Gonzalez
> I don't think execute immediate works for DDL, you have to use
> the dynamic
> sql package...

Actually, it does. And Nickp's code works for me without error:

SQL> create table address (id number);

Table created.

SQL> desc address

 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                                       NUMBER

SQL> DECLARE
  2 NCOUNT NUMBER;
  3 drptbl varchar2 (255);
  4
  5 BEGIN
  6 drptbl := 'DROP TABLE ADDRESS';
  7 SELECT COUNT(*) into NCOUNT FROM USER_TABLES WHERE TABLE_NAME =   'ADDRESS';
  8
  9 IF NCOUNT = 1 THEN
 10 EXECUTE IMMEDIATE drptbl;
 11 END IF;
 12
 13 END;
 14 /

PL/SQL procedure successfully completed.

SQL> desc address
ERROR:
ORA-04043: object address does not exist

--
Posted via http://dbforums.com