I'm converting some mysql scripts... what's the best oracular
idiom for mysql's "drop table if exists" command?

Many TIA!
Mark

--
Mark Harrison
Pixar Animation Studios

On Apr 21, 2:36*pm, m... (AT) pixar (DOT) com wrote:
There isn't one, really. Of course you're free to write PL/SQL to do
the 'dirty deed':

set serveroutput on size 1000000

declare
v_sqltxt varchar2(2000);
v_success number:=0;
v_tabname user_tables.table_name%type:=null;
p_tabname user_tables.table_name%type:='&1';
begin
select table_name
into v_tabname
from user_tables where table_name = upper(p_tabname);
if v_tabname is not null then
v_sqltxt:='drop table '||v_tabname;
execute immediate v_sqltxt;
end if;
exception
when no_data_found then
dbms_output.put_line('Table '||p_tabname||' does not
exist');
end;
/

Personally I don't mind seeing the error 'table or view does not
exist' as it's not usually a script killer.


David Fitzjarrell

On Apr 21, 4:08*pm, ddf <orat... (AT) msn (DOT) com> wrote:
First of all you should consider if you need to be dropping and
creating tables as part of the process under conversion. Perhaps the
tables in question should be replaced with Oracle temporary tables
which are defined once and then only exist within a session once
populated by an insert for use in a later select.

Otherwise, just code the drop and ignore errors OR perhaps remove the
drop and creates and replace the create with a truncate. The tables
will just be cleaned out prior to each use.

The temporary table method is best when multiple user sessions might
want to run concurrently as otherwise to use a common table you would
need to single thread the user sessions. Also only the owner or
especially privileged users can truncate a table natively so to use
truncate on non-owned tables you can write a procedure under the table
owner that performs the truncate command via 'execute immediate' (PL/
SQL statement).

HTH -- Mark D Powell --