RE: Can't access a table I just created
- From: "Daniel Fink" <daniel.fink@xxxxxxxxxxxxxx>
- To: orasnita@xxxxxxxxx
- Date: Wed, 7 Jul 2010 13:33:54 -0500
Try this
SELECT * FROM "tb";
I'd wager (and I am not a betting man) that the statement will succeed (though
likely no rows will be returned).
When you issued the command to create the table, you put the tablename in
double quotes, so the table name in the data dictionary is in lower case. Why
does this matter? Because Oracle is by nature case insensitive when it comes to
object names. However, it will store the metadata in the data dictionary as
UPPERCASE. The Oracle parser knows this and treats these UPPERCASE object names
as if they were case insensitive.
With one exception (which you have created), the following statements are
equivalent...
SELECT * FROM tb;
SeLeCt * FrOm Tb;
select * from TB;
SELECT * FROM "TB";
HOWEVER, when you put the object name in double quotes, you are telling Oracle to be case
sensitive. In this case the table name is, and can only be referenced as "tb".
SELECT * FROM "tb";
is not the same as any of the statements listed above...
In order to do anything to the table (insert, update, delete, modify, drop, etc), you
have to reference the table as "tb".
I know that many tools (Oracle's included) like to output DDL with quotes
around the object names. They do this because there *might* be objects with
lower or MiXeD case names. IMHO, that is fine for tools (they have to be
prepared for this contingency), but not for humans...
To get rid of the table, use
DROP TABLE "tb";
Then recreate the table with
CREATE TABLE tb (
id number NOT NULL,
symbol varchar2(20) NOT NULL,
PRIMARY KEY (id)
);
Regards,
Daniel Fink
------- Original Message -------
On 7/7/2010 6:19 PM Octavian Rasnita wrote:
Hi,
Our sysadmin recently installed Oracle and he created the user "teddy" for me.
I have created a table, but I can't select from it and I can't drop it either.
Here is what I tried after logging with the username "teddy" in sqlplus:
SQL> CREATE TABLE "tb" (
"id" number NOT NULL,
"symbol" varchar2(20) NOT NULL,
PRIMARY KEY ("id")
);
3 4 5
Table created.
SQL> SQL> drop table tb;
drop table tb
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table TB;
drop table TB
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table teddy.tb;
drop table teddy.tb
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from tb;
select count(*) from tb
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from teddy.tb;
select count(*) from teddy.tb
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
After so many tries, I think something's wrong. I have created 2 more other
tables, but I can't access them either.
Our sysadmin said that he can access that table if he connects to the database
with my username using a GUI program, SQL Developer if I remember well, and
that he was also able to add data to that table.
Do you have any idea what could be wrong? (Oracle newbie here)
Thanks.
Octavian
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
