Oracle_SQLLDR

Super simple load a clob from csv

-- ***********************************************************

--   Oracle SQL Loader Control file

--   Author.....: Michael Dockery

--   Type.......: Truncate

--   BINDSIZE=max_row_len * rows      (dflt=256,000) (max=20,971,520)

-- ***********************************************************

OPTIONS (ROWS=5000,BINDSIZE=8000000)

Load Data TRUNCATE into TABLE INDOTDWGIS.NHS_CLOB

  fields terminated by "," optionally enclosed by '"' trailing nullcols    

    (name, rte_id,

                geo_wkt_Clob CHAR(1000000),

                geo_wkt_Clob_len)

Simple template

@SET APP_NAME=%~n0
@echo  ******************************************************************
@echo  **  %0 is running 
@echo  **  Author: Michael Dockery 
@echo  **  Purpose: Load ETL Process Flows Details into %APP_NAME%
@echo  ***********************************************************
@echo  **  Loading %APP_NAME%.csv 
SqlLdr userid=myuser/mypwd@mytnsalias ^
  control=%APP_NAME%.ctl ^
     DATA=%APP_NAME%.csv ^
      BAD=%APP_NAME%.bad ^
      LOG=%APP_NAME%.log 
@echo  ***********************************************************
@echo  **  Job Complete %date% %time% >> %APP_NAME%.log

@echo  ***********************************************************

-- ***********************************************************

--   Oracle SQL Loader Control file

--   Author.....: Michael Dockery

--   Type.......: Truncate 

--   BINDSIZE=max_row_len * rows      (dflt=256,000) (max=20,971,520) 

-- ***********************************************************

OPTIONS (SKIP=1,ROWS=5000,BINDSIZE=8000000) 

Load Data TRUNCATE into TABLE DWSTAGE.STG_PS_PO_BUYER

   fields terminated by "," optionally enclosed by '"' trailing nullcols

  (PO_NO,Buyer_ID,Descr,Email)

  

t:\IRD>SqlLdr control=SiteMaster.ctl BAD=SiteMaster.bad LOG=SiteMaster.log userid=user/pwd@mysid

  SQL*Loader: Release 11.2.0.3.0 - Production on Mon Mar 14 15:17:54 2016
  specified value for readsize(1048576) less than bindsize(8000000)
  SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table myschema.SITEMASTER
  ORA-02266: unique/primary keys in table referenced by enabled foreign keys

so since constraints are in place, use DELETE instead of TRUNCATE (which is slower and logged)

Fast direct load

direct load is much faster than conventional
 C:\app\temp\ShapeFiles\SQLLDR>SqlLdr userid=dwstage/dwstage@INDOTDW  DIRECT=TRUE   control=FED_CLOB.ctl      DATA=FED_CLOB.csv       BAD=FED_CLOB.bad       LOG=FED_CLOB.log

or as ctl file option:
  OPTIONS (DIRECT=Y)

sqlldr using direct path load bypasses constraints:

OPTIONS (DIRECT=Y)

  conventional path SQL*Loader method, INSERT's use integrity constraints and insert triggers 
        direct path SQL*Loader method  disables ALL triggers and SOME integrity constraints (CHECK & Referential/FOREIGN KEY)
The constraints that remain in force are: NOT NULL, UNIQUE, PRIMARY KEY 

https://docs.oracle.com/cd/B10501_01/server.920/a96652/ch09.htm#1008082

Sample ctl file

-- ***********************************************************

--   Oracle SQL Loader Control file: SiteMaster.ctl

--   Method.....: truncates insert, 

--     populates date columns with sysdate

--     direct load bypasses fk's, 

--   direct loaded needs this run first $ORACLE_HOME/rdbms/admin/catldr.sql.sql 

--

--   Mod_Date...: 20Jan2016

--   Author.....: Michael Dockery

--   BINDSIZE=max_row_len * rows (dflt=256,000) (max=20,971,520) 

-- ***********************************************************

OPTIONS (ROWS=5000,BINDSIZE=8000000,DIRECT=TRUE) 

Load Data InFile 'SiteMaster.csv'

   TRUNCATE

   into TABLE mySchema.SiteMaster 

   fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS  

   (Site_ID, 

    Site_notes,  

    Date_Activated "SYSDATE" , 

    Date_Inactivated "SYSDATE")