What is SQL Loader
SQL*Loader is a utility to load data from text file to Oracle data base. SQL Loader reads data from plain text file and apply instructions from control file and load data in specified table or tables.
How to use SQL Loader
Sample SQL*Loader control file
OPTIONS (SKIP=1) LOAD DATA APPEND INTO TABLE xxap_invoice_interface FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( invoice_number, vendor_name, vendor_site_code, invoice_amount, invoice_date DATE "DD-MON-YYYY", invoice_description , line_number , line_amount , process_flag CONSTANT "N" )
Sample Data file
INVOICE_NUMBER,VENDOR_NAME,VENDOR_SITE_CODE,INVOICE_AMOUNT,INVOICE_DATE,DESCRIPTION,LINE_NUMBER,LINE_AMOUNT TEST7311,"CDS, Inc",PITTSBURGH,200,25-NOV-2016,This Invoice is created for test,1,100 TEST7311,"CDS, Inc",PITTSBURGH,200,25-NOV-2016,This Invoice is created for test,2,100 TEST7312,"CDS, Inc",PITTSBURGH,200,25-NOV-2016,This Invoice is created for test,1,100 TEST7312,,,200,25-NOV-2016,This Invoice is created for test,2,100 TEST7313,"CDS, Inc",,100,25-NOV-0016,This Invoice is created for test,1,100
Explanation of Control File
Line1 Skipping one row from the data file usually this is column header, which need not to be loaded in table. Line 2 LOAD Data append will add the data in the table, other option is replaced which will delete the data and load the new data. Line3 INTO TABLE XXAP_INVOICE_INTERFACE will specify the table, where data needs to be loaded. Line 4 Line 4 specified that all the data fields are terminated by comma and enclosed in double quotes. It is a good practice to include data in double quotes, it will load data in same column even if data consist comma ',' which is a field separator. Line 5 onwards After this it have list of data fields, which are loaded in the table sequentially. Considerations Date format should be applied because often this does not match with the data base session default date format, it will help in loading dates without any errors. Constants can be specified in control file and need not to be a part of data file. SQL*Loader command assuming that both control file and data file in the same directory where the command is being issued otherwise fully qualified name is required in sqlldr command parameter.
sqlldr userid=<user id>/<password> control=invoice_load.ctl data=data_file.csv
More information on oracle
More on SQL Loader