Re: best "drop table if exists" idiom?

On Apr 21, 4:08 pm, ddf <orat...@xxxxxxx> wrote:
On Apr 21, 2:36 pm, m...@xxxxxxxxx wrote:

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

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

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 --
.

Relevant Pages

  • Re: how to shrink the .LDF ? -- spanish subtitled
    ... DBCC SHRINKFILE is not a truncate command. ... Commmand to truncate the Log is BACKUP LOG command. ...
    (microsoft.public.sqlserver.setup)
  • Re: [Info-Ingres] commit question
    ... The next block has been turned into a rollback ... hence the next command executed is the rollback and not the commit. ... The situation we had was that the "modify to truncate" command succeeded ...
    (comp.databases.ingres)
  • RE: Trucate Command
    ... you can not do any kind of recovery using truncate table command. ... into syntax will look as follows. ... To transfer rows back to the original table from temp table you can use same INSERT INTO....SELECT syntax. ...
    (microsoft.public.sqlserver.mseq)
  • SUMMARY: Question on why ps sometimes truncates the process name and puts it in brackets
    ... Why does the ps output sometimes truncate the process name and put it in ... Thanks to Dr. Blinn for the following thorough answer: ... command, especially if there are a lot of processes on the system). ... because there is a temporary shortage of the in-memory buffer size ...
    (Tru64-UNIX-Managers)
  • Re: One or Multiple command objects in .NET to MS SQL
    ... you will not see much of performance impact in a case if you create ... > In my web application I am using one single command object to do stuff ... for all user sessions. ...
    (microsoft.public.data.ado)
Loading