[R] Dealing with schema in RODBC

[Marc Schwartz]

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