Page 1 of 1
Oracle Bulk
Posted: Wed Sep 28, 2005 3:26 am
by lucianopolinari
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
Posted: Wed Sep 28, 2005 7:00 am
by chulett
Yes, you would need to use an Oracle user which has the permissions needed to bulk load into that table.
Posted: Wed Sep 28, 2005 4:46 pm
by ray.wurlod
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.
Posted: Thu Sep 29, 2005 4:20 am
by lucianopolinari
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....
Posted: Thu Sep 29, 2005 4:46 pm
by ray.wurlod
Specify an appropriate user ID and password when invoking sqlldr.
Posted: Thu Sep 29, 2005 5:28 pm
by rleishman
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.