CODEWITHSUNDEEP
roracle11godbcr-dbi
Raja Saha
Raja Saha

Reputation: 509

"Select * from <table_name>" not working from Oracle 11g when tables are created from R using 'odbc', 'DBI' package

I have created some tables in 'Oracle Database 11g Express Edition' using two method:

Method-1: From R using odbc and DBI package

library(odbc)
db <- odbc::dbConnect(odbc::odbc(),
                'Oracle ODBC',
                uid = "raja",
                pwd = "test123")

And Create a table 'cars'.

DBI::dbWriteTable(conn = db, name = "cars", value = cars,
             append = T, row.names = F, overwrite = F)

Method-2: From sqlplus command prompt

create table my_table 
(id int not null,
 text varchar2(1000),
 primary key (id)
);

insert into my_table (id, text) values (1, 'This is some text.');

For verification I have run

SQL> select table_name from user_tables;
cars
mtcars
MY_TABLE

Clearly, it creates the tables.

Now the problem is: When try to view/read the tables success and error comes in following pattern.

From R:

Success:

> DBI::dbReadTable(db, "cars")

Error:

> DBI::dbReadTable(db, "my_table")
Error: <SQL> 'SELECT * FROM "my_table"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

Error:

> DBI::dbGetQuery(db, "select * from cars;")
Error: <SQL> 'select * from cars;'
  nanodbc/nanodbc.cpp:1587: 42S02: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

From sqlplus:

Success:

select * from my_table;

Error:

select * from cars;
ERROR at line 1:
    ORA-00942: table or view does not exist

Please help. Thanks.

Upvotes: 2

Views: 317

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

Your DBI call is using name = "cars", and you can see from the query of user_tables that the table is being created with a quoted-identifier name as that shows it as lowercase cars. You used an unquoted identifier for my_table, so that is uppercase in that query result.

As cars is a quoted identifier you have to quote it everywhere:

select * from "cars";

But that is a pain and no-one will thank you for using a quoted identifier (it's really better to avoid them completely unless for some reason you have to have a quoted name, based on the rules in the documentation linked to above); so it would be better to change your DBI call to use:

name = "CARS"

which will cause the table to be created with an unquoted identifier, meaning your original dbGetQuery will now work with the table name in any case - which is true any unquoted identifier (so that is why from my_table worked).

You will also need to change your dbReadTable call to also use the uppercase "CARS" and "MY_TABLE", from the errors you are seeing.

Upvotes: 2

Related Questions