Hi,
I'd need help to use correctly the ORABULK stage.
My problem is how to manage the access with ORACLEBULK to the DB. I need put in the Properties the schema owner as oracleuser or I could use another oracle user who has the grant wo insert to the table I want loasd ?
Many regards
LP
Oracle Bulk
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
DON'T
The ORABULK stage has been around since version 1.0, and is quite inefficient. Prefer the OCI stage with Load mode, or a Sequential File stage to write the DAT file.
The ORABULK stage does not have capability for automatically starting sqlldr, which means you have to create a sqlldr command in an after-stage or after-job subroutine. It's for that reason that the ORABULK stage was written as an active stage type rather than as a passive stage type, which it really should be.
The ORABULK stage has been around since version 1.0, and is quite inefficient. Prefer the OCI stage with Load mode, or a Sequential File stage to write the DAT file.
The ORABULK stage does not have capability for automatically starting sqlldr, which means you have to create a sqlldr command in an after-stage or after-job subroutine. It's for that reason that the ORABULK stage was written as an active stage type rather than as a passive stage type, which it really should be.
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.
-
lucianopolinari
- Participant
- Posts: 11
- Joined: Wed Apr 23, 2003 2:54 am
I've tried to use ORA OCI Load but I've the same probelm. Usually I use Oracle OCI 9 stage with an oracle user who's not the owner of the table where I'm loading and the job finishes OK ! instead if I use the ORa BULK stage with the same oracle user the job finishes NO !. I need to use an oracle user owner of the table I'm loading....
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
rleishman
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
If you're still having problems, could you post the error message into your next reply.
Try connecting to the userid/password using a SQL client like TOAD or SQL*Plus and issue the command:
INSERT INTO table_name SELECT * FROM table_name WHERE 1=0;
One of three things will (ok, should) happen:
- ORA-00942: table or view does not exist : You should create a synonym to point to the table in the other schema.
- ORA-01031: insufficient privileges - Connect to the other schema and grant INSERT privs.
- It works. Now you're in real trouble. I cannot explain why OCI Load would fail, but Orabulk could fail if you specified TRUNCATE in the sqlldr control file. Despite what the manual says, I've found DELETE privs are insufficient to do a truncate.
Try connecting to the userid/password using a SQL client like TOAD or SQL*Plus and issue the command:
INSERT INTO table_name SELECT * FROM table_name WHERE 1=0;
One of three things will (ok, should) happen:
- ORA-00942: table or view does not exist : You should create a synonym to point to the table in the other schema.
- ORA-01031: insufficient privileges - Connect to the other schema and grant INSERT privs.
- It works. Now you're in real trouble. I cannot explain why OCI Load would fail, but Orabulk could fail if you specified TRUNCATE in the sqlldr control file. Despite what the manual says, I've found DELETE privs are insufficient to do a truncate.
Ross Leishman