This discussion is archived
7 Replies Latest reply: Oct 12, 2012 9:58 AM by 962179 RSS

Error in dbWriteTable()

962179 Newbie
Currently Being Moderated
I have successfully installed ROracle.1.1-4 under R 2.15.0, Windows 32bit, my configuration is detailed here:

Re: ROracle 1.1-4 install failure for R  64 only: R version 2.15.0, Rtools215

I am able to connect to my Oracle database from within R and to successfully execute commands such as

dbListTables(con, schema="MYSCHEMA")
dbExistsTable(con, schema="MYSCHEMA", name="MYTABLE")
dbListFields(con, schema="MYSCHEMA", name="MYTABLE")
mydf <- dbReadTable(con, schema"MYSCHEMA", name="MYTABLE")

and so on. However, dbWriteTable() fails with this error message:

dbWriteTable(con, name="MYSCHEMA.MYTABLE", mydf)
Errore in .oci.GetQuery(con, stmt) :
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 49
ORA-20666: MYUSRNAME

Here "mydf" is a data.frame created with a dbReadTable(). An identical error message appears on different machines, with other data.frames and table names.
I would appreciate any help with this paralysing issue!

While I am at it, may I ask why has dbWriteTable() not got a "schema" parameter, whereas dbReadTable() and dbExistsTable() have?

Thank you very much and kind regards,
Renato
  • 1. Re: Error in dbWriteTable()
    Denis Mukhin Journeyer
    Currently Being Moderated
    Renato,

    The error you are seeing is related to your data. It seems that somewhere in your data you have value MYUSRNAME that ROracle is trying to convert to numeric value. I will need to know more about the data to understand why this is happening.

    I was able to do the same thing with the DEPT table from SCOTT.
    R> con  <- dbConnect(Oracle(), "scott", "tiger")
    R> dept <- dbReadTable(con, "DEPT")
    R> dbWriteTable(con, "MYSCHEMA.MYDEPT", dept)
    [1] TRUE
    R> dbListTables(con)
    [1] "MYSCHEMA.MYDEPT" "SALGRADE"        "BONUS"           "EMP"             "DEPT"           
    To answer your second question, dbWriteTable does not support writing into other schemas. Creating tables in other schemas requires CREATE ANY TABLE privileged that is rarely available to regular users. If you do want to write to other schemas you can write your own INSERT query with binds to accomplish this (similarly to the way it is done in the implementation of dbWriteTable which is purely written in R).

    Denis
  • 2. Re: Error in dbWriteTable()
    962179 Newbie
    Currently Being Moderated
    Hi Denis,

    thank you for your reply!

    The above error seems caused by dbWriteTable() attempting to write the table outside the schema "MYSCHEMA" which the user is enabled to access. The example R script

    library(ROracle)
    con <- dbConnect(Oracle(), username = "MYUSRNAME", password = "MYPASSWD", dbname="MYDWH")
    dbWriteTable(con,"MYTABLE",data.frame(x=1,y="a"))

    generates the error

    Errore in .oci.GetQuery(con, stmt) :
    ORA-00604: error occurred at recursive SQL level 1
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 49
    ORA-20666: MYUSRNAME

    Here "MYUSRNAME" is the user accessing the connection, rather than a portion of data.
    We just managed to get one step ahead by hand-setting the CURRENT_SCHEMA with an ad-hoc dbGetQuery() call, however a different error is now generated: the script

    library(ROracle)
    con <- dbConnect(Oracle(), username = "MYUSRNAME", password = "MYPASSWD", dbname="MYDWH")
    dbGetQuery(con,"ALTER SESSION SET CURRENT_SCHEMA = MYSCHEMA")
    dbWriteTable(con,"MYTABLE",data.frame(x=1,y="a"))

    generates the error

    Error in .oci.GetQuery(con, stmt, data = value) :
    ORA-01031: insufficient privileges

    By hand-debugging the library (namely, the oci.R script), we found out that the table "MYTABLE" is successfully created within .oci.WriteTable() by the call

    .oci.CreateTable(con, name, cnames, ctypes)     

    at line 441 of oci.R, however, the subsequent call

    .oci.GetQuery(con, stmt, data = value)
         
    at line 448, for which stmt is equal to

    insert into "MYTABLE" values (:1,:2)
         
    fails at line 248-249 of .oci.GetQuery() upon calling the C library with

    hdl <- .Call("rociResInit", drv@handle, con@handle, stmt, data,
    prefetch, bulk_read, lob_prefetch, PACKAGE = "ROracle")
                        
    (here stmt is the same as above). Since this error is not caught within a try() statement, .oci.GetQuery() stops at that point and execution returns within .oci.WriteTable(), where the newly created (empty) table is removed by the

    .oci.RemoveTable(con, name)
         
    at line 466. So it is the "insert" statement that does not go through.
    Any suggestions are more than welcome!
    Thanks again,
    Renato
  • 3. Re: Error in dbWriteTable()
    962179 Newbie
    Currently Being Moderated
    Forgot to mention: the instant client version is 11.2.0.3.0 (instantclient-basic-nt-11.2.0.3.0.zip, same for sdk, odbc) and we get the same errors from a Windows Server 2008 R2 Enterprise 64 bit, with R x64 2.15.1 with instant client version instantclient-basic-windows.x64-11.2.0.3.0.zip (same for sdk odbc), the same as in this post

    Unable to install ROracle_1.1-5 under Windows XP 32bit

    Renato
  • 4. Re: Error in dbWriteTable()
    Denis Mukhin Journeyer
    Currently Being Moderated
    Renato,

    This is a privilege problem. As far as I understand you granted CREATE ANY TABLE to MYUSRNAME making the CREATE TABLE statement succeeded. However, you don't have an INSERT privilege. To insert into a table in a different schema you either need an INSERT privilege on a table (which won't work in this case since you are creating a new table) or an INSERT ANY TABLE system privilege (however, regular users should not have this).

    I suggest that you try restructuring your use case to connect to the schema where you need to write data. You can read data from other schemas as long as you have a SELECT privileges on all tables that you need to read granted to the user that does the writes.

    Denis
  • 5. Re: Error in dbWriteTable()
    962179 Newbie
    Currently Being Moderated
    Hi Denis,

    you are right, it is indeed a privilege problem. In our setting, MYUSRNAME has a default schema, which is called again MYUSRNAME (what an unfortunate choice), which contains no objects and where MYUSRNAME has no CREATE privileges. A separate schema exists (called MYSCHEMA in the example above), which is populated by tables and where MYUSRNAME has CREATE and SELECT privileges. For this reason, we have to explicitly specify the schema MYSCHEMA in dbListTables() or dbListFields() calls (as I show in my original post), otherwise empty lists are returned. So issuing a top-level

    dbGetQuery(con,"ALTER SESSION SET CURRENT_SCHEMA = MYSCHEMA")

    statement will point to the right schema, to which subsequent dbWriteTable() and dbRemoveTable() calls will refer (note, however, that the "schema" parameter still has to be passed to dbListTables() or dbListFields() even after changing CURRENT_SCHEMA, which is a bit counterintuitive). This solves the error in my original post.

    Unfortunately, MYUSRNAME has no INSERT privileges in newly created tables within MYSCHEMA, as you correctly diagnosed: INSERT privileges must be granted explicitly after the CREATE TABLE occurring within .oci.WriteTable(). This causes the error in my second post.

    As a work-around, we have created our own version of .oci.WriteTableSchema() which contains a mandatory "schema" argument and where we have replaced the two

    .oci.CreateTable(con, name, cnames, ctypes)

    calls with two blocks like this one:

    .oci.CreateTable(con, name, cnames, ctypes)
    stmt <- sprintf("BEGIN CRRMG_ADMIN.OR_ROLE_AUTO.Grant_Role_Privs(\'%s\'); END;",schema) ## grants INSERT privileges to newly created table
    .oci.GetQuery(con,stmt)

    The function .oci.WriteTableSchema() has to be added to the oci.R file and the package reinstalled, because .oci.* functions are not exported by the library.

    We can now move forward on our own, although any further suggestions/hacks by yours would be more than welcome!

    Thank you very much for your support and kind regards,
    Renato

    Edited by: 959176 on 12-ott-2012 4.10
  • 6. Re: Error in dbWriteTable()
    Denis Mukhin Journeyer
    Currently Being Moderated
    Renato,

    May I ask what are you trying to achieve by using two schemas? From my point of view your solution looks somewhat unconventional. If I knew a bit more about your requirements I could have suggested a different solution.

    Denis
  • 7. Re: Error in dbWriteTable()
    962179 Newbie
    Currently Being Moderated
    Denis,

    let me give you some context. I work in a modelling group within a fairly large company and we are testing the useability of our Oracle database from within R. The DBA of the company created the application user MYUSRNAME for this purpose.

    The tables that the application user MYUSRNAME needs to access for our purposes are contained in a schema called MYSCHEMA in the example above. However, the default schema associated to the application user is not MYSCHEMA: indeed, it is a policy of our DBA that each application user logs onto her own schema, which happens to have the same name as the application user herself, thus MYUSRNAME, and on which said user has no CREATE privileges. As I gather from our DBA, this policy is unlikely to change so there we are, stuck with two different schemas.

    Regarding the "insufficient privilege" error, digging a little deeper I realized that there is a slight temporal delay between the creation of a new table in MYSCHEMA and the automatic granting of roles (and, consequently, INSERT privileges) to the application user. This is why the INSERT INTO generates the "insufficient privileges" error: it is just a matter of waiting a few moments after the CREATE and then the INSERT goes through without problems. By way of example, if I execute these statements

    rs <- dbSendQuery(con,"CREATE TABLE MYTABLE ( myvar varchar(1) )")
    dbHasCompleted(rs)
    dbGetQuery(con,"INSERT INTO MYTABLE (myvar) values(\'a\')")
    dbRemoveTable(con,"MYTABLE")

    as a block-paste into my R console, then an "insufficient privilege" error occurs: not enough time has elapsed between the CREATE and the INSERT. However, if I execute the same statements one-by-one (by pasting them separately), then the INSERT is successful. The BEGIN ... END block of my previous post enforces that the role granting has completed before attempting the INSERT.

    Any further suggestions/comments of yours are welcome and please do not hesitate to ask should you need more information.

    Once again thank you,
    Renato

    P.S.
    It is an unfortunate fact that my company's production DB is Oracle 10g (namely, "Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi"): this prevents us from trying the extremely interesting Oracle R Enterprise and R-ODM R package.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points