?
Solved

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

Posted on 2009-04-06
6
Medium Priority
?
2,902 Views
Last Modified: 2013-12-18
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

0
Comment
Question by:mservidio
  • 3
  • 2
6 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 24084260
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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24084272
0
 

Author Comment

by:mservidio
ID: 24086881
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
mservidio earned 0 total points
ID: 24087203
oops, I've found my issue. The connection string that I used previously, was different from the connection string that I specified in my using clause. Thus, I granted the permissions to the incorrect user.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24089789
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.
0
 

Expert Comment

by:hydroIT
ID: 24257574
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month8 days, 9 hours left to enroll

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question