Delete rows only if the table exists

Question

I need to run a delete statement on my database if a table was previously created.

The problem is - I can't just run the delete statement, since the product is not on every client's productive environments - therefore, they don't have the table where I want to run the delete statement, and would end up with an error 00942. 00000 - "table or view does not exist".

An example:

I would like to run something like this:

IF EXISTS (TABLE TB_FIELD)
    DELETE FROM TB_FIELD WHERE ID = '213';

If there isn't a generic statement, I would like one that would run for Oracle databases

Are you using SAP Hana or Oracle? Those are two **very** different database systems. — , Nov 12 '18 at 13:23
Possible duplicate of [Oracle: If Table Exists](https://stackoverflow.com/questions/1799128/oracle-if-table-exists) — kgzdev, Nov 12 '18 at 13:27
I'm using both, depending on the client, @a_horse_with_no_name — Leonardo Alves Machado, Nov 12 '18 at 13:29

score 5 · Accepted Answer · answered Nov 12 '18 at 13:39

Here's one for Oracle. This assumes the current user owns the table. If you're updating someone else's table you'll need to swap out user_tables with dba_tables.

declare
    table_name_l    user_tables.table_name%type;
begin
    select table_name into table_name_l from user_tables where table_name = 'TB_FIELD';
    -- if we didn't raise an exception the table must exist 
    execute immediate 'delete from tb_field where id = :1' using '213';

    exception 
        when no_data_found then 
            -- do nothing - table doesn't exist
            null;
end;

score 2 · Answer 2 · answered Nov 13 '18 at 02:22

Simplest way is to catch and ignore the "table not found" exception:

declare
  l_id number := 12345;
begin
  execute immediate 'delete tb_field where id=:1' using l_id;
exception
  when others then
    if sqlcode != -942 /*table or view does not exist*/ then
      raise;
    end if;
end;
/

Delete rows only if the table exists

2 Answers2