CODEWITHSUNDEEP
oracle-databaseplsql
Howie
Howie

Reputation: 2778

Oracle's need for apostrophes

I am using Oracle 11g and Oracle SQL Developer 3.2.20.09. I am creating a procedure.

Why is it that I get an error PL/SQL: ORA-00942: table or view does not exist when I write the query like this:

select * from TableExample;

But if I write the query like this:

select * from "TableExample";

it works fine?

Is there a way to use the first option without errors? Note that the table was created using the same user that is executing the query above.

EDIT: I've also noticed that I have to use apostrophes for every field, EXCEPT the primary key ID

Upvotes: 0

Views: 492

Answers (3)

Frank Schmitt
Frank Schmitt

Reputation: 30775

The reason is that the table was created with the table name in double quotes. Usually, table and column names are case insensitive, but if you put double quotes around them, they become case sensitive UNLESS they are completely in upper case (insane, but true :-( ).

To find the correct table and column names, you can query the data dictionary:

select * from dba_tab_cols where upper(table_name) = upper('TableExample')

(if you don't have the necessary rights, use user_tab_cols instead of dba_tab_cols). For each table name and column name returned by this query, if it contains anything but uppercase characters or underscores, you'll have to quote it.

Upvotes: 2

diederikh
diederikh

Reputation: 25281

It depends on how you create the table. If the table was also created as "TableExample" (with the quotes) then the table name is case sensitive and you need to use quotes when selecting the table.

Upvotes: 1

Shepherdess
Shepherdess

Reputation: 651

Because 'table' is a reserved word so oracle will see it as a syntax error like for example:

select * from from ;

You can escape it with double quotes but you will have to remember to use them every time you query this table.

Upvotes: 1

Related Questions