Re: Table not found with jdbc sql statement

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 30 Oct 2003 13:11:03 -0000
Message-ID: <3fa10de7$0$9465$ed9e5944@reading.news.pipex.net>

"Thomas Kellerer" <spam_eater_at_gmx.net> wrote in message news:bnquup$14stg2$1_at_ID-13919.news.uni-berlin.de...
> Simon Bieri schrieb:
>
> > Hi
> >
> > I have a big problem. I created some tables in a db with a standard sql
> > script (script is below).
> > Now I try to select the table DbTableOne with a normal java script via
> > oracles jdbc thin driver (I tried with both classes111.zip as well
> > classes12.zip). When I enter the table name without double quotes
> > (DbTableOne), I can connect to it. When I enter the table name with the
> > double quotes ("DbTableOne"), I receive the error message, that there's
> > no such table.
> >
> > What's wrong? With SQL 99 standard this should not be a problem?
> >
> In contrary:
>
> The SQL standard says that as soon as you put an object in double quotes,
the
> database has to handle that case sensitive. When you create a table in
Oracle
> the table name is stored in uppercase, thus your mixed case name is not
found.
> btw: SQL*Plus should react in the same way.

As I read it the OP ran the create table script with quotes in sqlplus. As a result he got a mixed case table name.
eg

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Oct 30 13:07:18 2003

Connected to:
Oracle9i Release 9.2.0.3.0 - Production
JServer Release 9.2.0.3.0 - Production

SQL> CREATE TABLE "DbTableOne"
2 (

  3     "DateTime"           VARCHAR2(20)             NOT NULL,
  4     "Id"                 VARCHAR2(8)                  NULL,
  5     "Status"             VARCHAR2(2)                  NULL,
  6     "ErrorDescr"         VARCHAR2(300)                NULL,
  7     "MessageTextA"       VARCHAR2(2000)               NULL,
  8     "MessageTextB"       VARCHAR2(2000)               NULL,
  9     "MessageTextC"       VARCHAR2(2000)               NULL,
 10     "MessageTextD"       VARCHAR2(2000)               NULL,
 11     "MessageTextE"       VARCHAR2(2000)               NULL

12 )
13 ;

Table created.

SQL> select table_name from user_tables where table_name != upper(table_name);

TABLE_NAME

DbTableOne

SQL> I don't have a working java ide at the moment to test out what is happening, and in any case we haven't seen the java code that tries to select from the table.

>
> From my point of view using double quotes for table names is a bad habit
and
> should be avoided at all.

This I entirely agree with.

-- 
Niall Litchfield
Oracle DBA
Audit Commission Uk

Received on Thu Oct 30 2003 - 07:11:03 CST