Uploading CSV in SQLLDR

Normal CSV Upload



Step 1: Before loading data you need two files control file and csv file.
Step 2: Change the following in the control file

•   Path of the csv file.
•   Table name.
•   Column names for which the data has to be inserted.

Step 3: Execute the command sqlldr control=c:\ctrl3, log=c:\tablename.log userid=username/password@connectionname
    


Blob and Date Column CSV Upload via SQLLDR
  
Step 1: Before loading data you need two files control file and csv file .

This is different from normal upload since it has date field to be loaded and XML_BLOB field that need to loaded.

Prerequisite: Char_Set_ID will be given
  
Step 2: Export the Char_Set  table (for the Char_Set_ID given) excluding the char_set_xml column as csv.

Step 3: Now export the char_set_xml column for each of the given Char_Set_ID as single dat file.
  
Step 4: Change the following in the csv file

•  In the XML_BLOB (char_Set_xml) column mention the path of the dat file XML_BLOB (char_Set_xml).
•  Also check for the date format of the date fields as mentioned in the control file.

Step 5: Change the following in the control file

•  Path of the csv file.
•  Table name.
•  Column names for which the data has to be inserted.
• Give the correct format of the date fields. For Example insert_date Date "MM/DD/YYYY HH24:MI:SS",
• XML_BLOB column has to be mentioned as below in the control file. For Example lob file filler char,CHAR_SET_XML LOBFILE(lob_file) TERMINATED BY EOF
  

Step 4: Execute the command sqlldr control=c:\ctrl, log=c:\tablename.log userid=suvin/welcome@connectionname

 
Control File Content


LOAD DATA

INFILE "c:\char_set.csv"
Append INTO TABLE CHAR_SET
FIELDS TERMINATED BY ','
(
CHAR_SET_ID,
CHAR_SET_ID,
CHAR_SET_NAME,
QUERY_TABLE_ID,
lob_file filler char,
CHAR_SET_XML LOBFILE(lob_file) TERMINATED BY EOF,
domain_name,
insert_user,
insert_date Date "MM/DD/YYYY HH24:MI:SS",
update_user,
update_date Date "MM/DD/YYYY HH24:MI:SS"
)

Sample

Microsoft Windows XP [Version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.


C:\Documents and Settings\;sqlldr userid=suvin/welcome@tmstest control=c:/CTRL4, log=tablename_04_NOV.log


SQL*Loader: Release 10.1.0.2.0 - Production on Wed Nov 3 17:13:44 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.


Commit point reached - logical record count 53


C:\Documents and Settings\sqlldr userid=suvin/welcome@tmsprod1 control=c:/CTRL4, log=tablename_04_NOV.log


SQL*Loader: Release 10.1.0.2.0 - Production on Wed Nov 3 17:16:01 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.


Commit point reached - logical record count 53


C:\Documents and Settings\;