This is probably a FAQ, but I haven't been able to find any good
information about this. Perhaps that means the answer is that I can't
do it, but I'll give it a try anyway.

In writing db setup scripts, it's convenient to add a "drop table"
statement before "create table" statements. This works fine if the
table already existed. If the table didn't exist, the script fails.

Is it practical/feasible to write Oracle-specific SQL that I can pass
through JDBC that will only drop the table if it exists, and ignore it
if it doesn't exist? I remember a colleague at another job doing this
quite a while ago, but I can't remember how it was done.

On 27 Oct 2003 14:39:54 -0800, david.karr@wamu.net (David M. Karr)
wrote:

>This is probably a FAQ, but I haven't been able to find any good
>information about this. Perhaps that means the answer is that I can't
>do it, but I'll give it a try anyway.
>
>In writing db setup scripts, it's convenient to add a "drop table"
>statement before "create table" statements. This works fine if the
>table already existed. If the table didn't exist, the script fails.

Not true. With the standard sql*plus error handling, the script will
continue.

>
>Is it practical/feasible to write Oracle-specific SQL that I can pass
>through JDBC that will only drop the table if it exists, and ignore it
>if it doesn't exist? I remember a colleague at another job doing this
>quite a while ago, but I can't remember how it was done.

I don't think anyone should even consider to run DDL through jdbc.
This is utterly unsafe. You also don't want to allow an arbitrary
end-user be capable to run installation like scripts.
You'll end up in a mess, if you are not in one already.

Use sql*plus for installation scripts, as this is an one-time
operation anyway, and don't even consider using jdbc.


--
Sybrand Bakker, Senior Oracle DBA

David M. Karr wrote:

>This is probably a FAQ, but I haven't been able to find any good
>information about this. Perhaps that means the answer is that I can't
>do it, but I'll give it a try anyway.
>
>In writing db setup scripts, it's convenient to add a "drop table"
>statement before "create table" statements. This works fine if the
>table already existed. If the table didn't exist, the script fails.
>
>Is it practical/feasible to write Oracle-specific SQL that I can pass
>through JDBC that will only drop the table if it exists, and ignore it
>if it doesn't exist? I remember a colleague at another job doing this
>quite a while ago, but I can't remember how it was done.
>
>
I agree with Sybrand. What you propose is a disaster waiting to strike.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

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.

"David M. Karr" <david.karr@wamu.net> wrote in message news:cafe491.0310271439.45ac1079@posting.google.co m...
> This is probably a FAQ, but I haven't been able to find any good
> information about this. Perhaps that means the answer is that I can't
> do it, but I'll give it a try anyway.
>
> In writing db setup scripts, it's convenient to add a "drop table"
> statement before "create table" statements. This works fine if the
> table already existed. If the table didn't exist, the script fails.
>
> Is it practical/feasible to write Oracle-specific SQL that I can pass
> through JDBC that will only drop the table if it exists, and ignore it
> if it doesn't exist? I remember a colleague at another job doing this
> quite a while ago, but I can't remember how it was done.

Hello David,

Assuming you want to use Java for this sort of things, you can enclose the
drop statement in the 'try ... catch' block:

Statement stmt = con.createStatement();
try {
stmt.executeUpdate("DROP TABLE " + table);
} catch (SQLException ex) {/* do a remedial action or just log an error
message */ }

The same can be done in a sqlplus scrip::

begin
execute immediate 'drop table t1';
exception
when others then
dbms_output.put_line('Table already exists');
end;

Rgds.

"David M. Karr" <david.karr@wamu.net> wrote in message
news:cafe491.0310271439.45ac1079@posting.google.co m...
> This is probably a FAQ, but I haven't been able to find any good
> information about this. Perhaps that means the answer is that I can't
> do it, but I'll give it a try anyway.
>
> In writing db setup scripts, it's convenient to add a "drop table"
> statement before "create table" statements. This works fine if the
> table already existed. If the table didn't exist, the script fails.
>
> Is it practical/feasible to write Oracle-specific SQL that I can pass
> through JDBC that will only drop the table if it exists, and ignore it
> if it doesn't exist? I remember a colleague at another job doing this
> quite a while ago, but I can't remember how it was done.

David,

Sorry, the error message in the PL/SQL block, of course, should say "The
table _does not_ exist".


Rgds

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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

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@x.washington.edu> wrote in message news:1067304888.881013@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/e...ad/oad_crs.asp
> http://www.outreach.washington.edu/e...oa/aoa_crs.asp
> damorgan@x.washington.edu
> (replace 'x' with a 'u' to reply)
>

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@x.washington.edu> wrote in message news:1067304888.881013@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/e...ad/oad_crs.asp
>>http://www.outreach.washington.edu/e...oa/aoa_crs.asp
>>damorgan@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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

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@x.washington.edu> wrote in message news:1067325267.598889@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@x.washington.edu> wrote in message news:1067304888.881013@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/e...ad/oad_crs.asp
> >>http://www.outreach.washington.edu/e...oa/aoa_crs.asp
> >>damorgan@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/e...ad/oad_crs.asp
> http://www.outreach.washington.edu/e...oa/aoa_crs.asp
> damorgan@x.washington.edu
> (replace 'x' with a 'u' to reply)
>