Re: double-quoting table-names in across-schema access

On Sat, 15 Sep 2007 07:03:11 -0700, ken quirici <ken.quirici_at_excite.com> wrote:

>Hi,
>
>I was in schema1 (not the actual name) in TOAD -Oracle 9i - and tried
>
>select count(*) from schema2.table1; (not the actual names)
>
>I got an 'invalid or unknown table' error, although, as you
>will see below, schema1 has read access to schema2.
>
>After various attempts at figuring this out, including running down
>refs to " in the Master Index for the Oracle 9i documentation set,
>I asked one of the dbas for help. He typed this into my session and
>it worked:
>
>select count(*) from schema2."table1";
>
>Anybody have any idea what principle is involved here?
>As I said it's not documented. Is it a TOAD issue, or maybe
>an issue accessing Oracle from a fat client like TOAD?
>Maybe the same 'fix' would also be needed from a C or
>C++ client? But that would have turned up a reference in
>the Master Index.
>
>Whatever the principle, it's got to be documented
>somewhere!
>
>Any help appreciated.
>
>Thanks.
>
>Ken Quirici

1. Databases are case-sensitive when it comes to object names.
2. Database objects by default are capitalized before execution\. So, querying schema2.table1 means to query SCHEMA2.TABLE1.
3. Double-quoting an object preserves its case, so querying schema2."table1" means to query SCHEMA2.table1.

Therefore, if the query does not work when unquoted, but does work when using quoted smallcaps, the TABLE was CREATEd with double-quotes, and must always be referred too like that.

B. Received on Mon Sep 17 2007 - 10:40:23 CDT