Check if a table exists in an oracle sql database with c#

Question

This code:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='xxx'

The above code throws an exception:

ORA-00900: invalid SQL statement

What did I do wrong? The above code worked long ago I could swear.

Answer 1

Oracle does not support INFORMATION_SCHEMA, you need to use ALL_TABLES, see here

Answer 2

Assuming you want to check the schema you are currently connected to I would use user_tables :

SELECT table_name 
FROM USER_TABLES
WHERE table_name='xxx'

if you want to check the table is in in a different schema use all_tables don't forget to add the owner predicate as the table may exist is several schemas :

SELECT table_name 
FROM ALL_TABLES
WHERE table_name='xxx' AND owner='yourschemahere'

Answer 3

Have you migrated from another dbms?

AFAIK, Oracle does not support INFORMATION_SCHEMA (not even a subset of it) but you can retrieve a lot of metadata by querying the data dictionary.

Answer 4

SELECT
    TABLE_NAME
FROM
    ALL_TABLES
WHERE
    TABLE_NAME = 'YourTableName'

http://en.wikipedia.org/wiki/Oracle_metadata#Example_1:_finding_tables

Answer 5

On my most recent project requirements was to check if certain tables existed in an Oracle DB using C#.

Prerequisites:

Oracle .Net Assembly

App.Config file with connection string

I came up with this to meet this requirement.

private static void CheckIfOracleTableExists()
{
 try
   {
       string connectionString = ConfigurationManager.ConnectionStrings["dbConnectString"].ConnectionString;
       if (connectionString == null) throw new Exception();

       using (OracleConnection orclConn = new OracleConnection(connectionString))
       using (OracleCommand orclCmd = new OracleCommand())
           {
           orclConn.Open();
           orclCmd.Connection = orclConn;

           string commandText = String.Format("SELECT COUNT(*) FROM " + DbTestName);

           orclCmd.CommandText = commandText;
           orclCmd.CommandType = CommandType.Text;
           orclCmd.CommandTimeout = Convert.ToInt32(DbTimeout);

           try
           {
               orclCmd.ExecuteScalar();
               {
                   if (orclCmd.RowSize == 0) return;
                   TableExists = true;

                   orclConn.Close();
                   orclConn.Dispose();
                   orclCmd.Dispose();
               }
            }
            catch (OracleException oex)
            {
               if (oex.ToString().Contains("table or view does not exist"))
                  {
                     Console.WriteLine("\r\n\tTable not found.");
                  }
                  TableExists = false;
            }
            catch (OracleException oex)
            {
                Console.WriteLine("{0}", oex);
                TableExists = false;
            }
            catch (Exception ex)
            {
                if (ex.ToString().Contains("Object reference not set to an instance of an object"))

                Console.WriteLine("\r\n\t Invalid Connection String.");
            }
            TableExists = false;
           }
    }    
}//// / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / /

Answer 6

Actual code version of Kevin Burton's answer, with both the versions with and without schema:

    public Boolean TableExists(OracleConnection connection, String tableName)
    {
        return TableExists(connection, tableName, null)
    }

    public Boolean TableExists(OracleConnection connection, String tableName, String schema)
    {
        String sql;
        if (schema == null)
            sql = "SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME=:table";
        else
            sql = "SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME=:table AND OWNER=:schema";
        OracleCommand command = new OracleCommand(sql, connection)
        command.Parameters.AddWithValue("table", tableName);
        if (schema != null)
            command.Parameters.AddWithValue("schema", schema);
        using (DbDataReader reader = command.ExecuteReader())
            return reader.HasRows;
    }