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
Oracle connector - bulk load fails if not the table owner
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Premium Member
- Posts: 87
- Joined: Sat Jul 14, 2007 2:24 pm
Re: Oracle connector - bulk load fails if not the table owne
hi
did anyone find an explanation to this problem ?
is it a know issue ?
did anyone find an explanation to this problem ?
is it a know issue ?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Some databases have a different privilege to permit bulk load.
Last edited by ray.wurlod on Mon Dec 03, 2012 10:13 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers