Re: Any way to "conditionally" drop a table?

Read my message..

It is not too hard to figure out that you need to wrap the drop table clause inside an execute immediate clause.

HERE IS THE PROOF. SQL> select count(*) from user_tables where table_name = 'AA';

  COUNT(*)

         0

SQL> create table AA as select * from emp;

Table created.

SQL> select count(*) from user_tables where table_name = 'AA';

  COUNT(*)

         1

SQL> begin
  2 execute immediate 'drop table aa';   3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> select count(*) from user_tables where table_name = 'AA';

  COUNT(*)

         0

SQL> Scott Watson

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1067325267.598889_at_yasure...
> Scott Watson wrote:
>
> >Because that is what he needed to do. Surely, it is not too hard to figure out that you need to wrap the drop table
> >command inside an execute immediate clause.
> >
> >Besides I said execute something like this NOT execute this.
> >
> >I will try to be more precise in the future.
> >
> >Scott Watson.
> >
> >
> >"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1067304888.881013_at_yasure...
> >
> >
> >>Scott Watson wrote:
> >>
> >>
> >>
> >>>Try creating a callable statement and then execute something like the following.
> >>>
> >>>"begin drop table tablename cascade constraints; exceptions when others then null; end;"
> >>>
> >>>Hth
> >>>Scott Watson.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>Did you try this before you suggested it?
> >>
> >>And in answer to that rhetorical question ... why did you post it?
> >>
> >>--
> >>Daniel Morgan
> >>http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> >>http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> >>damorgan_at_x.washington.edu
> >>(replace 'x' with a 'u' to reply)
> >>
> >>
> I was hoping I wouldn't have to spell this out but since you didn't take
> the hint I will.
>
> Your syntax does not work in any version of Oracle from 6 through 10g.
> You can NOT
> perform DDL without DBMS_SQL or native dynamic SQL.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Tue Oct 28 2003 - 06:22:10 CST