I think that you are on to something there Don.
I just tried accessing a table from our Oracle server, which I do know
exists, but for which I do not have access permissions.
Using the following query in the Oracle Instant Client:
select table_name from all_tables;
I can get a list of all tables on the server, which includes a table
called INCOMPATIBLE_USER_AGENTS, for which I do not have access
permissions.
When attempting to query the table in the Instant Client I get:
SQL> select * from INCOMPATIBLE_USER_AGENTS;
select * from INCOMPATIBLE_USER_AGENTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
When running the same query from R using RODBC I get:
> sqlQuery(db, "select * from INCOMPATIBLE_USER_AGENTS")
[1] "[RODBC] ERROR: Could not SQLExecDirect"
[2] "42S02 942 [Oracle][ODBC][Ora]ORA-00942: table or view does not exist\n"
So it looks like permission issues may be possible here. Eric,
definitely confirm with your SysAdmins that you have appropriate
permissions.
HTH,
Marc
On Wed, 2007-07-18 at 07:43 -0700, Don MacQueen wrote:
> I believe I have seen that error message from
> Oracle when I tried to query a table for which I
> did not have "select" privileges (and when I knew
> for certain that the table existed). Ask your
> database administrator about the table, and make
> sure that you do have that privilege.
>
> What I am uncertain about is whether Oracle, when
> asked to list tables, returns a list that
> includes tables for which the user does not have
> select privileges.
>
> -Don
>
> At 9:24 AM +0200 7/17/07, [EMAIL PROTECTED] wrote:
> >essai <- odbcConnect("ORESTE_prod",
> >uid="osis_r", pwd="12miss15" ,case="oracle")
> >
> >> sqlTables(essai)$ORESTE
> >
> >...
> >
> >1315 <NA> ORESTE S_PROFESSIONS_OLD TABLE
> ><NA>
> >1316 <NA> ORESTE S_PROVENANCES TABLE
> ><NA>
> >1317 <NA> ORESTE S_SEXES TABLE
> ><NA>
> >1318 <NA> ORESTE S_SOUS_CLASSES TABLE
> ><NA>
> >1319 <NA> ORESTE S_TYP_COLLEGES TABLE
> ><NA>
> >1320 <NA> ORESTE S_TYP_ENSEIGNEMENT TABLE
> ><NA>
> >
> >...
> >
> >> sqlQuery(essai, "select * from S_TYP_COLLEGES")
> >[1] "[RODBC] ERROR: Could not SQLExecDirect"
> >[2] "42S02 942 [Oracle][ODBC][Ora]ORA-00942: Table ou vue inexistante\n"
> >
> >I have also tried the
> >essai2 <- odbcDriverConnect(connection="essai2")
> >But with no succes.
> >
> >
> >
> >On Lun Juil 16 15:32 , Prof Brian Ripley <[EMAIL PROTECTED]> sent:
> >
> >>The problem could be quoting, if Oracle is not standards-compliant.
> >>See the options in ?odbcConnect.
> >>
> >>If sqlQuery(essai, "select * from S_TYP_COLLEGES") works, this is likely
> >>to be the problem.
> >>
> >>On Mon, 16 Jul 2007, [EMAIL PROTECTED] wrote:
> >>
> >>>
> >>>
> >>>> essai
> >>>> odbcGetInfo(essai)
> >>> DBMS_Name DBMS_Ver Driver_ODBC_Ver
> >>> "Oracle" "09.00.0121" "03.51"
> >>> Data_Source_Name Driver_Name Driver_Ver
> >>> "ORESTE_prod" "SQORA32.DLL" "09.00.0101"
> >>> ODBC_Ver Server_Name
> >>> "03.52.0000" "weba"
> >>>
> >>>
> >>>> sqlTables(essai)
> >>>
> >>> The result of this function is a liste of tables, one of them is called:
> >>> S_TYP_COLLEGES.
> >>>
> >>>
> >>>> sqlFetch(essai,"S_TYP_COLLEGES")
> >>> [1] "[RODBC] ERROR: Could not SQLExecDirect"
> >>> [2] "42S02 942 [Oracle][ODBC][Ora]ORA-00942: Table ou vue inexistante\n"
> >>>
> >>>> sqlFetch(essai, "S_TYP_COLLEGES", colnames=TRUE, rownames=FALSE)
> >>> [1] "[RODBC] ERROR: Could not SQLExecDirect"
> >>> [2] "42S02 942 [Oracle][ODBC][Ora]ORA-00942: Table ou vue inexistante\n"
> > >>
> > >>
> > >> What could be the problem here ?
> > >> Any help is welcome
> > >> Eric Röthlisberger, Neuchâtel
> > >>
______________________________________________
R-help@stat.math.ethz.ch 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.
