oracle11g - CREATE TABLE statement in Oracle with the existence check

Question

This question is inspired by this.

As stated, I don't want a solution from PL/SQL. I want a 1 or 2 SQL statements that will check for table existence and if its not exist - create it.

Such statement(s) will be plugged into C++ application (not a script) and so I want a plain SQL solution. If such solution is not exist (please say so), I'd like to have a simple string I can plug into C++ code and use either SQLExecute() or a native Oracle client API to execute such a string.

Trying to google for a solution I am getting a results that can be used either in the shell script or a stored procedure. As I explain here and in the previous question - my situation is completely different - I work in C++ and want an appropriate solution.

question from:https://stackoverflow.com/questions/65876334/create-table-statement-in-oracle-with-the-existence-check

Answer 1

There is no single SQL statement that will create a table only if it does not exist in Oracle 11g.

It is not obvious to me why you're objecting to a PL/SQL based solution. If you're using raw ODBC calls in C++, you can pass a PL/SQL block to SQLPrepare just as you would pass a plain SQL statement. Given that PL/SQL blocks work almost exactly like a pure SQL statement, it would be unusual to categorically reject a PL/SQL based solution.

If you are going to categorically reject PL/SQL, you can certainly take the logic from any of the PL/SQL based solutions and implement that in a couple of SQL statement executed from your application. For example, you can query dba_| all_| user_tables (depending on your privileges, whether you are creating tables in other schemas, etc.) to determine whether the table exists and then conditionally execute your DDL

select owner, table_name
  from dba_tables
 where owner = <<schema that will own the table>
   and table_name = <<name of the table>>

If that returns no rows you can then execute your DDL.

Of course, you can also just execute your DDL statement and catch the ORA-00955 name is already used by an existing object error in C++.