moving_data

What are moving data methods in oracle and how it works?

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.                             

sqlldr1

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.                                                                                              

sqlldr2
sqlldr7

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.

sqlldr5
sqlldr6

And all the data is loaded from Traffic_Violations.csv

Author Profile

Tejas
Tejas
Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j
Like
Like Love Haha Wow Sad Angry
Summary
What are moving data methods in oracle and how it works?
Article Name
What are moving data methods in oracle and how it works?
Description
How SQL * Loader is used and What are External Tables? What are Limitations of external tables?
Author

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.