On Fri, 9 Nov 2007, Mark Lyman wrote: > Thanks for your suggestion Marc. I saw that on some Oracle-related > web-sites, but something in the way RODBC functions verify the existance of > a table does not accept that naming structure. For example: > >> sqlColumns(eids, "EIDS.TEST_ARTCL_INST") > Error in sqlColumns(eids, "EIDS.TEST_ARTCL_INST") : > 'EIDS.TEST_ARTCL_INST': table not found on channel
Yes, and AFAICS it is not RODBC but Oracle's ODBC driver that is restricting you. Oracle-style schema are not supported that way: as Marc hinted, I believe you can set the schema and then use unqualified names. > > > On 11/7/07, Marc Schwartz <[EMAIL PROTECTED]> wrote: >> >> 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 >> >> >> > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > -- Brian D. Ripley, [EMAIL PROTECTED] Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
