Oracle connector - bulk load fails if not the table owner

[galmeida]

Using the Oracle Connector Stage, I connect to a database using a DB user who is not the table owner (for security reasons, no application at my company establishes a connection as the schema that owns the tables).

A regular insert works. i.e. I have all the synonyms and grants set up correctly.

However, if I try to do a bulk load, I get a "table or view not found" error.

Bulk load when connected as the table owner works.

Has anyone encountered this? I assume there is no workaround except to connect as the table owner. My guess is that the stage reads some dictionary table.

What is the underlying technology of the bulk load? Is it using SQL Loader, or is it just an INSERT APPEND with NO LOGGING?

THANK YOU

[keshav0307]

yes. i was also having the similar issue. and found that oracle connecter stage does not work for synonyms.

[asaf_arbely]

hi
did anyone find an explanation to this problem ?
is it a know issue ?

[ray.wurlod]

Some databases have a different privilege to permit bulk load.

[chulett]

The database you would connect to using the Oracle Connector? Pretty sure that would be Oracle. (I kid)

I've asked my DBA if there are any specific grants needed to do DIRECT loads via sqlldr but he doesn't think there are any. Now you may need to drop and rebuild indexes as part of the process but lacking that privledge would throw a different error I presume. keshav0307 noted that it does not support synonyms so you'd need to ensure you specified the schema / owner in the stage. Not doing so would cause the issue reported.

asaf_arbely: why not let us know what issue you are having and if synonyms are involved at all?

[lstsaur]

Try to do the bulk load from the shell command line, using non-owner id, to see what you get. At least you will get better info. in the log.