The different between TABLE_NAME vs "TABLE_NAME" in Oracle?

Question

I'm a very new Oracle SQL user and I got a problem.

If I create a table with code such as

create table TBL_NAME...

after that I can get data by

select * from TBL_NAME;

However, when I create table with Navicat (just click on button new table), then I have to add "" to table name to access my table, such as:

select * from "TBL_NAME";

So, is there have other type of table? And if I use Navicat to create table, what type of it?

Answer 1

When you specify the name of a table in Oracle without double quotes then Oracle converts that table name to uppercase. But if you specify the name of the table within double-quotes then Oracle will respect the lower-case letters that you may have.

So, as in your example the name of your table is already all upper-case, then there is no difference in specifying or not the double-quotes.

But for example if you create a table like this:

CREATE TABLE "my_table" ....

Then you cannot access it like this:

SELECT * FROM my_table;

as Oracle will convert that select to this:

SELECT * FROM MY_TABLE;

And there is no such table in your system.

In your case with Navicat, it just needs you to specify the name of the table as-is, but don't worry, just put the double-quotes and stick to all upper-case names and you will be fine.