Home » SQL & PL/SQL » SQL & PL/SQL » Load Utility in DB2 - Alternative in Oracle? (Oracle 10.2.0.4)
Load Utility in DB2 - Alternative in Oracle? [message #376062] Mon, 15 December 2008 21:43 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I have used the load utility in db2 to load large amounts of data faster.

In addition to the above, it has the added flexibility that any records that get rejected in the process of loading are written into another table.

For e.g.

create table STOP_ACTION_ORDER_exception as select * from STOP_ACTION_ORDER where 1=2;

declare stop_action_order cursor for
SELECT 
   SEQ_STOP_ACTION_ORDER_ID.NEXTVAL,
   O.SHIPMENT_ID,
   S.STOP_SEQ,
   S.STOP_ACTION_SEQ,
   O.ORDER_ID,
   1
FROM ORDERS O
INNER JOIN STOP_ACTION S ON S.SHIPMENT_ID = O.SHIPMENT_ID;

load from stop_action_order of cursor 
INSERT INTO STOP_ACTION_ORDER
(
STOP_ACTION_ORDER_ID,
SHIPMENT_ID,         
STOP_SEQ,            
STOP_ACTION_SEQ,     
ORDER_ID,            
HIBERNATE_VERSION
)
for exception STOP_ACTION_ORDER_exception;

COMMIT;


The above queries load data as per the insert statement into STOP_ACTION_ORDER.

The records that fail to get inserted are moved into

STOP_ACTION_ORDER_EXCEPTION.

Is there some way the same can be done in Oracle?

Thanks,

Sharath
Re: Load Utility in DB2 - Alternative in Oracle? [message #376077 is a reply to message #376062] Mon, 15 December 2008 23:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read INSERT for top to bottom?

Regards
Michel
Re: Load Utility in DB2 - Alternative in Oracle? [message #376080 is a reply to message #376062] Mon, 15 December 2008 23:55 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Is there some way the same can be done in Oracle?

Probably yes - it looks similarly to the error logging clause of the INSERT statement, as described in http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BGBEIACB. Also see the enclosed example.

[Edit: too late...]

[Updated on: Mon, 15 December 2008 23:55]

Report message to a moderator

Previous Topic: Automatic Spool File Generation
Next Topic: NUMBER or NUMBER(12)
Goto Forum:
  


Current Time: Sun Dec 11 00:42:23 CST 2016

Total time taken to generate the page: 0.15282 seconds