Here I will first load data using Sql * Loader utility and then by using external tables.
Sql * Loader loads data from external files into tables of an oracle database.It has powerful data parsing engine that puts little limitation on format of data in data file. Input data files: Sql * Loader reads data from one or more files that are specified in control file. A particular data can be in a fixed data format ,variable record format or streamed record format. The record file format is specified using INFILE parameter.If no record format is specified then default is stream record format. Here I loaded data from Traffic_Violations.csv. Location of file is specified in control file.
Control Files: A control file is a text file written in language that Sql * Loader understands.Control file indicates Sql * Loader where to find data,how to parse and interpret, where to insert . Control files has three sections
Global options such as input data file name and records to be skipped INFILE: specifies location where the data is to be loaded. Data to be loaded. Below is syntax to create a control file.Here I specified all the columns that I want to load from Traffic_Violations.csv. Make sure that You have a ready definition(table created) for Traffic_Violations in database. Then I saved us_trafice_violation_mst.ctl. I also created a blank file us_trafice_violation_mst.log which is loaded when the batch command is executed.
The batch command is as follows:
sqlldr username/password control=control filepath log= log filepath
The second section consists of INTO blocks which contains information about table. Third section is optional and if present it contains input data. When Sql * Loader begins executiom it loads log files.Log files contains detailed summary of load,including description of errors occurred during load.
And all the data is loaded from Traffic_Violations.csv