0

I am not able to write data to a table part of the different owner (Oracle data base) using dbwriteTable() even after I have INSERT Grants.

I have tried using the below option but, getting an error. When I tried checking the dbExistsTable(), it is returning true for the same table. Execution Steps followed:

Connecting to DB:

library(RJDBC)

drv <- JDBC("oracle.jdbc.OracleDriver", 
             classPath = "C:/oracle_64/product/11.2.0/client_2/jdbc/lib/ojdbc6.jar", " ") 

con <- dbConnect(drv, "jdbc:oracle:thin:@//hostname:1521/oracle_sid", 
                 "MASTER_OWNER", "PASSWORD" )

Write Dataframe into Oracle Table:

dbWriteTable(conn, "R_STG_INSERT", DF_NAME, row.names = FALSE, 
             overwrite = FALSE, append = TRUE, schema = "TEST_OWNER")

Note: The same dbWriteTable() is working, if it is with same Database Owner. I am able to use it as expected.

Expected: Load the data frame into Oracle table

Actual: Error Message

Error in .local(conn, statement, ...) : execute JDBC update query failed in dbSendUpdate (ORA-00942: table or view does not exist ) Calls: dbWriteTable ... dbWriteTable -> .local -> dbSendUpdate -> dbSendUpdate -> .local Execution halted

Parfait
  • 104,375
  • 17
  • 94
  • 125
Sravan
  • 1
  • 3
  • Thanks. I have updated my post as mentioned with steps. Can you please help on this. – Sravan Aug 13 '19 at 14:33
  • First, check the [privileges of owner on that schema](https://stackoverflow.com/q/14604576/1422451). Possibly, even though you are not creating/dropping table but appending data, `dbWriteTable` may need you to [grant the create table privilege](https://stackoverflow.com/questions/21412747/oracle-grant-create-table-in-another-schema) to non-schema owner. If so, consider adding a [RJDBC ticket to GitHub](https://github.com/s-u/RJDBC/issues) for your situation as you do have working code but user issue. – Parfait Aug 13 '19 at 14:54
  • Thanks for your response. I had provided the SELECT, INSERT, DELETE and UPDATE Grants to schema (to which the table is not a part of). Is there any other way to achieve without providing the Create table privileges. – Sravan Aug 13 '19 at 19:56
  • Again, if that privilege is required for an `INSERT` process, you should add a ticket to package authors on GitHub as that should not be the case and may be an API issue. Be sure to clearly state your problem and that it works for schema owner but not the non-schema owner with the four DML privileges. Curious can you re-produce with `ROracle` which falls under same DBI family as `RJDBC`? – Parfait Aug 13 '19 at 20:01
  • I can't use ROracle, due to limitation at client end. Thanks – Sravan Aug 13 '19 at 21:48

0 Answers0