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

On Sep 15, 10:50 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> 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

Hi Steve,

Thanks for the reply. I don't think table1 was created with the name enclosed in "" since
I don't need the "" to refer to the table in its own schema. Also both schemas are test
and were copied from production.

Thanks again.

Ken Received on Sat Sep 15 2007 - 10:02:36 CDT