Solved

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

Posted on 2009-04-06
6
2,612 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 47

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 47

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 47

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
comparing string in C# 3 34
Image(7) 1 37
ConsoleSql 1 21
Migrate Oracle Database from ASM to Non-ASM on a Windows server. 1 17
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now