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

On Sep 15, 10:03 am, ken quirici <ken.quir..._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

Hi Ken,

Someone probably typed create table "table1" (c number); or whatever, or built the table by exporting it from a tool such a Microsoft Access into Oracle. This forces you to always quote the object name...

SQL> create table "table1"( c number);

Table created.

SQL> desc table1
ERROR:
ORA-04043: object table1 does not exist

SQL> desc "table1"

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 C                                                  NUMBER

SQL> drop table "table1";

Table dropped.

SQL> create table table1(c number);

Table created.

SQL> desc table1

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 C                                                  NUMBER

SQL> desc TABLE1
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 C                                                  NUMBER

SQL> HTH, Steve Received on Sat Sep 15 2007 - 09:50:28 CDT