On Wed, 2007-11-07 at 22:15 +0000, Mark Lyman wrote:> Is there a way to get a table in a certain schema? The Oracle database I am
> using has a table by the same name in two different schemas. This creates
> problems in sqlUpdate because to sqlUpdate there are duplicate columns. The
> following is part of the output of sqlColumns:
>
> sqlColumns(eids, "TEST_ARTCL_INST")[,1:4]
> TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME
> 1 EIDS TEST_ARTCL_INST CHANNEL_ID
> 2 EIDS TEST_ARTCL_INST ARTICLE_TEST_ID
> 3 EIDS TEST_ARTCL_INST CHANNEL_OLD_ID
> 4 EIDS TEST_ARTCL_INST FREQ_FM_CNT
> 5 EIDS TEST_ARTCL_INST RANGE_MAX_CNT
> 6 EIDS TEST_ARTCL_INST RANGE_MIN_CNT
> 7 EIDS TEST_ARTCL_INST TYPE_GAGE_ID
> 8 EIDS TEST_ARTCL_INST DRAWING_TYPE_ID
> 9 EIDS TEST_ARTCL_INST DRAWING_ID
> 10 EIDS TEST_ARTCL_INST RATE_SPECIFIED_CNT
> 11 EIDS TEST_ARTCL_INST ACCURACY_RQRD_CNT
> 12 EIDS TEST_ARTCL_INST UNIT_MSR_ID
> 13 EIDS_APP TEST_ARTCL_INST CHANNEL_ID
> 14 EIDS_APP TEST_ARTCL_INST ARTICLE_TEST_ID
> 15 EIDS_APP TEST_ARTCL_INST CHANNEL_OLD_ID
> 16 EIDS_APP TEST_ARTCL_INST FREQ_FM_CNT
> 17 EIDS_APP TEST_ARTCL_INST RANGE_MAX_CNT
> 18 EIDS_APP TEST_ARTCL_INST RANGE_MIN_CNT
> 19 EIDS_APP TEST_ARTCL_INST TYPE_GAGE_ID
> 20 EIDS_APP TEST_ARTCL_INST DRAWING_TYPE_ID
> 21 EIDS_APP TEST_ARTCL_INST DRAWING_ID
> 22 EIDS_APP TEST_ARTCL_INST RATE_SPECIFIED_CNT
> 23 EIDS_APP TEST_ARTCL_INST ACCURACY_RQRD_CNT
> 24 EIDS_APP TEST_ARTCL_INST UNIT_MSR_ID
>
> Mark Lyman
Typically, with a schema in Oracle, you use:
schema.object
syntax. So something like (in SQL):
select * from EIDS.TEST_ARTCL_INST;
would be different than:
select * from EIDS_APP.TEST_ARTCL_INST;
So in RODBC, prefix any occurrence of a table name with 'SchemaName.' as
may be appropriate. The same syntax is used for views.
The nuance is that in Oracle, all users typically have a schema that is
their UserID. When you login to Oracle and just use the table name, your
current UserID schema prefix is 'implied'.
However, if you want to access other objects within schema created by
other users, you need to explicitly use the schema prefix. You of course
also need appropriate access privileges for other schema that you have
not created.
HTH,
Marc Schwartz