asked on

OracleBulkCopy error, ORA-00604: error occurred at recursive SQL level 1 & ORA-01031: insufficient privileges

Hi All - I am trying to use the new OracleBulkCopy class that is included with the new Oracle 11g ODAC tools. When I try and bulk load a datatable into an oracle table, I am receiving the following errors:

ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges

Current the user that i am opening the connection with has connect, resource, and dba priviledges on the database.

using (OracleBulkCopy bulkCopy = new OracleBulkCopy(conn_config, OracleBulkCopyOptions.Default))
        {
            bulkCopy.DestinationTableName = "WORKTABLES.TEMP_DEFINITION_" + dd_reportid.SelectedValue + "_" + _packageid;
            bulkCopy.WriteToServer(dt);
            bulkCopy.Close();
            dt.Clear();
            ds.Clear();
            dt.Dispose();
            ds.Dispose();
        }

Open in new window

schwertner

Your Oracle user should have privileges on the schema:
CREATE TABLE, INSERT and other.
If the origin of the data is a flat file in an Oracle directory the user has to have READ privilege from the directory.

schwertner

http://dotnetslackers.com/articles/ado_net/BulkOperationsUsingOracleDataProviderForNETODPNET.aspx

mservidio

ASKER

I've granted the username the following privileges below and the exception is still occurring. Keep in mind, this is using the new OracleBulkCopy class for .net which is part of the new 11g ODP tools. http://download.oracle.com/docs/html/E10927_01/OracleBulkCopyClass.htm#BIGCDJDD. There is no documentation on the Oracle site which explains the necessary permissions needed to perform a writertoserver().

CREATE ANY TABLE.
CREATE TABLE
INSERT ANY TABLE
UNLIMITED TABLESPACE

ASKER CERTIFIED SOLUTION

mservidio

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwertner

I had a similar problem yesterday. The best experiment is to try
this as sys user and check if it goes through.
NonSYS users have limited privileges and often it is unknown what exactly they need to run smootly.

hydroIT

Hey,

Has anyone figured out what permissions you need to perform the OracleBulkCopy?

I can run as "ADMIN" (ie the schema owner) my code, but get this error if I use my personal account, which does have ALTER permissions on the bulk INSERT target table?

Cheers

OracleBulkCopy error, ORA-00604: error occurred at recursive SQL level 1 &amp; ORA-01031: insufficient privileges

OracleBulkCopy error, ORA-00604: error occurred at recursive SQL level 1 & ORA-01031: insufficient privileges