Help, I can't drop a table which I created in my Oracle database using MS-Access.

The Oracle (tm) Users' Co-Operative FAQ

Help, I can't drop a table which I created in my Oracle database using MS-Access .

Normally Oracle stores most of the information it saves in the Oracle base tables (dictionary tables) in uppercase. When objects are imported from other database management tools the object names are usually stored as received. This means that lower case letters, spaces, and other characters not usually found in an Oracle object name may be stored. By default Oracle internally converts the object name in SQL statements to uppercase so when the rdbms attempts to perform DDL or DML against the imported object Oracle cannot find it. You can get around this problem by enclosing the object name in double quotes.

	> select table_name, owner from dba_tables
	  2  where table_name = 'i came from access';
	TABLE_NAME                     OWNER
	------------------------------ ------------------------------
	i came from access             MPOWEL01
	> insert into "i came from access" values ('one');
	1 row created.
	> select * from "i came from access";

	FLD1
	--------------------
	one
	> drop table "i came from access";

	Table dropped.

Note that when querying the dictionary query for exactly the object name you expect to find. If like most DBA's and developers you have scripts to inquire on Oracle objects like tables, indexes, constraints, etc... then there is a good change you have coded something like upper('&objname') into your script so do not forget this when trying to locate objects that were created via Microsoft tools. When referencing the object in an SQL statement I enclosed it in double quotes and Oracle understood what object I wanted to work with and found it.

It is advisable to inform the developers and customers using MS tools to use upper case in their DDL for object names. Better yet do not allow objects to be created from Access or other third party tools that suffer from this condition if you can.

Double quotes are also used to enclose Oracle reserved words that are used as column names in SQL statements.

Further reading: N/A