Thursday, September 27, 2012

A Simple SQL*Loader Case Study

A Simple SQL*Loader Case Study



My table had some contraints so before loading
I wanted to disable those:

 begin
 
for cur in (select owner, constraint_name , table_name 
from all_constraints
where owner = 'SCOTT' and
TABLE_NAME = 'ASSETMANAGER_TEST1') loop
execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" DISABLE ';
end loop;
end;


 The datafile:


 oracle@inventory_server/home/oracle> cat PowerTestData.data
 NULL,R25,P770,E8B 8233,Power,616DDP,NULL,N,NULL,NULL,20,20
 NULL,R25,POWER 5, P750,55A 9113,Power,6FA6FG,NULL,N,NULL,NULL,20,5
 NULL,R25,IO DRAWER,D20 73144,Power,63633B,NULL,N,NULL,NULL,20,6
 NULL,R25,IO DRAWER,D20 73144,Power,63632B,NULL,N,NULL,NULL,25,24
 NULL,R25,HMC,CR4 7978,Power,99H4397,NULL,N,NULL,NULL,11,36
 NULL,R25,HMC,CR4 7042,Power,689C5B,NULL,N,NULL,NULL,20,18
 NULL,R25,HMC,CR4 7310,Power,61B3CB,NULL,N,NULL,NULL,20,4
 NULL,R25,P780,E8B 8233,Power,626B2P,NULL,N,NULL,NULL,5,4
 NULL,R25,TAPE DRIVE,NULL,Power,,NULL,N,NULL,NULL,20,24
 NULL,R23,POWER 6, 570,MMA 9117,Power,1059EF2,NULL,N,NULL,NULL,34,36
 NULL,R23,P 780,NULL,Power,,NULL,N,NULL,NULL,12,18
 NULL,R23,IO DRAWER,D20 7311,Power,101495C,NULL,N,NULL,NULL,8,4
 NULL,R23,IO DRAWER,D20 7311,Power,101496C,NULL,N,NULL,NULL,6,4
 NULL,R20,POWER 5,595 9119,Power,836587,NULL,N,NULL,NULL,6,60


 The Control file:
 oracle@inventory_server/home/oracle> cat sqlloader_control_file.ctl
 LOAD DATA
   INFILE PowerTestData.data
    INTO TABLE  ASSETMANAGER_test1
   FIELDS TERMINATED BY ","
 TRAILING NULLCOLS
  (INV_NO,RACKNO,ITEMDESC,MODEL,TYPE,SNO,COMMENTS,GARS_Y_N,IN_DATE,OUT_DATE,CORES,MEMORY)
  
  The command:
  
  sqlldr userid=scott/tiger control=sqlloader_control_file.ctl log=loader_log.log




No comments:

Post a Comment