0

I am trying to access a database. 

myConn <-odbcDriverConnect("Driver={Oracle in OraClient11g_home1};Dbq=IIESP;Uid=user;Pwd=pass;")
table_list <- sqlTables(myConn)
res <- sqlFetch(myConn, "MV_LIFELAPSE", max = 10)
res

We find that res is a character vector of length 2.

[1] "42S02 942 [Oracle][ODBC][Ora]ORA-00942: table or view does not exist\n"

[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM \"MV_LIFELAPSE\"'"

The issue I am having is that table_list contains thousands of rows. MV_LIFELAPSE is an entry in the TABLE_NAME column of table_list. I am not entirely certain of the "Driver" I am just using the code from this answer, Failure to connect to odbc database in R. It seems strange that I can view all of the available tables but am unable to retrieve results from the table.

I have accessed the data in PowerPivot using the same user and pass so I do not think it is an issue of access.

R Yoda
  • 8,358
  • 2
  • 50
  • 87
sendHelpPlease
  • 45
  • 6
  • 1
    Looks like your connection was opened successful but the table name is wrong (or not fully qualified). Try to add the user/schema name... – R Yoda Nov 26 '18 at 18:33
  • What would this involve? The table name is correct. I do not see a place to put arguments for schema in sqlFetch. – sendHelpPlease Nov 26 '18 at 18:41
  • 1
    Try to find out the schema name your table belongs to, e. g. via (`select * from all_objects where object_name='MV_LIFELAPSE';`). The schema name is called "owner" AFAIK. Then qualify your table name in the select, e. g. `select * from schema.MV_LIFELAPSE` (where "schema" is the schema name you have found out). – R Yoda Nov 26 '18 at 19:17
  • It works! Thank you. – sendHelpPlease Nov 26 '18 at 19:37
  • Great to hear that. Please drop at least a comment here what was the reason (how did you solve the problem) to help other readers. THX :-) – R Yoda Nov 26 '18 at 19:45

0 Answers0