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 <ripley at stats.ox.ac.uk> 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, eric at net2000.ch 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-sig-DB mailing list -- R Special Interest Group
>> R-sig-DB at stat.math.ethz.ch
>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>
>
>--
>Brian D. Ripley, ripley at stats.ox.ac.uk
>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
Try the sqlQuery() syntax with a semi-colon at the end of it: sqlQuery(essai, "select * from S_TYP_COLLEGES;") Oracle requires the semi-colon at the end of the SQL statement. If that does not help, try these queries using the Oracle Instant Client command line application outside of R and see if your queries work there. If so, then we can likely isolate the problem to R. If not, then there is an ODBC/Oracle configuration issue. If you are unsure of how to use (or perhaps install) the Oracle Instant Client, check with one of your SysAdmins. BTW, unstated is the OS here, but I presume Windows, given the ODBC driver version and DLL noted previously. HTH, Marc Schwartz On Tue, 2007-07-17 at 09:24 +0200, eric at net2000.ch 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 <ripley at stats.ox.ac.uk> 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, eric at net2000.ch 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-sig-DB mailing list -- R Special Interest Group > >> R-sig-DB at stat.math.ethz.ch > >> https://stat.ethz.ch/mailman/listinfo/r-sig-db > >> > > > >-- > >Brian D. Ripley, ripley at stats.ox.ac.uk > >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 at 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.
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, eric at net2000.ch 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 <ripley at stats.ox.ac.uk> 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, eric at net2000.ch 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-sig-DB mailing list -- R Special Interest Group >>> R-sig-DB at stat.math.ethz.ch >>> https://stat.ethz.ch/mailman/listinfo/r-sig-db >>> >> >>-- >>Brian D. Ripley, ripley at stats.ox.ac.uk >>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 at 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.-- -------------------------------------- Don MacQueen Environmental Protection Department Lawrence Livermore National Laboratory Livermore, CA, USA 925-423-1062
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, eric at net2000.ch 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 <ripley at
stats.ox.ac.uk> 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, eric at net2000.ch 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
> > >>
Yes, it's a quesion of rights. My system administrator just confirme that. And I also try with a small Oracle client, and same as R, no permission to read tables. On R with RODBC I could list the tables but no permission to read or import tables. But I get confused because I was using the same Oracle user with an MS Access client, wich works perfectly even to importe table from Oracle DB. My system admin told me that this Oracle user is in fact a synomyme or duplicata from the real Oracle account. But why this very same account can import table via the MS Access client ? This remains mystery to me. As soon as may sysadmin return from vacations, I will clear that out. grettings et thanks to everyone. Eric R. On Mer Juil 18 17:50 , Marc Schwartz sent:>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, eric at net2000.ch wrote: >> >essai >> >uid="osis_r", pwd="12miss15" ,case="oracle") >> > >> >> sqlTables(essai)$ORESTE >> > >> >... >> > >> >1315 ORESTE S_PROFESSIONS_OLD TABLE >> >1316 ORESTE S_PROVENANCES TABLE >> >1317 ORESTE S_SEXES TABLE >> >1318 ORESTE S_SOUS_CLASSES TABLE >> >1319 ORESTE S_TYP_COLLEGES TABLE >> >1320 ORESTE S_TYP_ENSEIGNEMENT TABLE >> > >> >... >> > >> >> 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 >> >But with no succes. >> > >> > >> > >> >On Lun Juil 16 15:32 , Prof Brian Ripley ripley at stats.ox.ac.uk> 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, eric at net2000.ch 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 >> > >> >