Re: Table and Field Name Capitalization

Scott Krull wrote:

> Hello,
>
> We're working on migrating a database from SQL Server 2000 to Oracle
> 9i, and are having a problem with Oracle automatically convering field
> and table names to all upper-case letters.
>
> Specifically, this problem occurs when we access the Oracle database
> with ASP, then format the results using XML/XSL. The problem is that
> XML looks for the field names we used in SQL Server (eg, LoginName)
> but finds only all-uppercase versions (LOGINNAME).
>
> There are two solutions that I have found thus far:
> -Covert all XML tags to all uppercase. I would prefer to stay away
> from this, simply due to the amount of conversion necessary.
> -Use quotes around table and field names when creating tables in
> Oracle. However, it seems that I then need to use quotes every time I
> reference the table. In other words, if I use the following command:
>
> CREATE TABLE "TSystemAccount"
> (
> "Pkey" INT PRIMARY KEY,
> "LoginName" VARCHAR2(8),
> "Passwd" VARCHAR2(8)
> );
>
> Then I need to put quotes around the field and table names every time
> I query them. This would require a lot of quote escaping in the ASP
> -- again, something I would prefer to avoid because of the amount of
> conversion required.
>
> Is there another solution possible? Ideally, I would like a way to
> "turn off" Oracle and/or ODBC's automatic capitalization so that it
> would simply accept the capitalization given (like SQL Server
> apparently does). Any suggestions would be appreciated.
>
> Thanks,
> Scott

The problem you are having is that you didn't read the Oracle concepts and architecture documents and are in the process of making one of the biggest mistakes possible. Unless your goal is to waste time and money producing trash that will either be thrown away or run slightly slower than a stack of 3x5 cards ... STOP!

Good.

Now go read the appropriate manuals and if you can find a copy, new or used, of Tom Kyte's book "Expert one-on-one Oracle" read that too. Especially chapters 1 through 3.

Every attempt to treat Oracle like SQL Server results in a disaster because while both are RDBMS's they are very very different. And so far you have demonstrated all of the prerequisites for failure.

BTW: The names should be all upper case. Leave them that way!

Daniel Morgan Received on Mon Apr 07 2003 - 17:10:59 CDT