If you run the query “CREATE TABLE some_name “, it will throw an exception jdbc

ORA-00955: name is already used by an existing object

provided that in the base of the table with the name some_name . You can catch this exception specifically.

try {
  if (stmt.execute ( "CREATE TABLE scn_test (value NUMBER)")) {
    System.out.println ( "Table scn_test is successfully created.");
  }
} Catch (SQLSyntaxErrorException e) {
  if (e.getErrorCode ()! = 955) {
    throw e;
  }
}

Is this right? Is there a right approach?

Answer 1, Authority 100%

It depends on what action to take if the table already exists. If these actions are within the database that sense and realize client eksepshennom – not if the action is to take a client or database is used exclusively as the business logic of the data warehouse it is best to check on the client, but I do not think it’s a case of Orakla – use it as a simply store – idiocy.

But in any case, to check whether or not the table can be for example such a request:

select count (*) from ALL_TABLES where table_name = 'table_name'

In the case of Oracle will probably be more correct to write the code on the pl / sql that will catch eksepshen Orakla on the table already.

p.s. In Orakle really no construction “if table not exists”, apparently to promote object-oriented approach.

P.S.S. although if your java code is integrated into the database (he is part of the base), such an approach correct.

Answer 2, Authority 100%

TL; DR Normal tables and many other objects in the database are permanent, that is created once and is no longer deleted and recreated, the more the client code.

Perhaps this is why Oracle does not hurry to implement such features as: a predicate IF NOT EXISTS or static DDL in PL / SQL. Quote @ConnorMcDonald at Ask Tom :

DDL is a rare event in Oracle (unlike some other databases). If you need to run lots of DDL from PL / SQL, I’d be interested in seeing the justification for that.

In exceptional cases, you can create a procedure, for example:

create or replace procedure createTableIfNotExists (clauses varchar2) is
  alreadyExists exception;
  pragma exception_init (alreadyExists, -955);
Begin.
  execute immediate 'create table' || clauses;
exception when alreadyExists then null;
end createTableIfNotExists;
/
SQL & gt; exec createTableIfNotExists ( 'testtab (id number, memo varchar (32))')
PL / SQL Procedure SuccessFully Completed.
SQL & gt; desc testtab
Name Null Type
---- ---- ------------
ID NUMBER
MEMO VARCHAR2 (32)

Do not forget that the call DDL will produce an implicit commit of the current transaction, if any.

Answer 3

This is a problem specific for Oracle. In Oracle, there is no predicate IF NOT EXISTS allows for example to give MySQL CREATE TABLE & lt; NAME & gt; IF NOT EXISTS

The options solutions mass of catching eksepshna when trying to create an existing table to a PL / SQL inserts.
SELECT COUNT (*) is really not the best way (I would even say the worst way)

overview of the various workarounds discussed here