Oracle SQL * Loader Features Analysis

Oracle SQL * Loader Features analysis Features: SQL * Loader loads data from external files into tables of an Oracle database. it has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. you can use SQL * Loader to do the following: ■ Load data into ss a network. this means that you can run the SQL * Loader client on a different system from the one that is running the SQL * L Oader server. ■ Load data from multiple datafiles during the same load session. ■ Load data into multiple tables during the same load session. ■ Specify the character set of the data. ■ Selectively load data (you can load records based on the records 'values ). ■ Manipulate the data before loading it, using SQL functions. ■ Generate unique sequential key values in specified columns. ■ Use the operating sy Stem's file system to access the datafiles. figure 6-1 SQL * Loader OverviewSQL * Loader ParametersSQL * Loader is invoked when you specify the sqlldr command and, optionally, parameters that establish session characteristics. in situations where you always use the same parameters for which the values seldom change, it can be more efficient to specify parameters using the following methods, rather than on The command line: ■ Parameters can be grouped together in a parameter file. you cocould then specify the name of the parameter file on the command line using the PARFILE parameter. ■ Certain parameters can also be specified within the SQL * Loader control file by using the OPTIONS clause. parameters specified on the command line override any parameter values specified in a parameter file or OPTIONS CIA Use. parameter: SQL * Loader ParametersSQL * Loader is invoked when you specify the sqlldr command and, optionally, parameters that establish session characteristics. in situations where you always use the same parameters for which the values seldom change, it can be more efficient to specify parameters using the following methods, rather than on the command line: ■ Parameters can be grouped together in a parame Ter file. you cocould then specify the name of the parameter file on the command line using the PARFILE parameter. ■ Certain parameters can also be specified within the SQL * Loader control file by using the OPTIONS clause. parameters specified on the command line override any parameter values specified in a parameter file or OPTIONS clause we use sqlldr to call SQL * Loader, parameters can be written in the form of commands or parameter files to call the control file: SQL * Load Er Control FileThe control file is a text file written in a language that SQL * Loader understands. the control file tells SQL * Loader where to find the data, how to parse and interpret the data, where to insert the data, and more. although not precisely defined, a control file can be said to have three sections. the first section contains sessio n-wide information, for example: ■ Global options such Bindsize, rows, records to skip, and so on ■ INFILE clses to specify where the input data is located ■ the Data to be loaded parameter file contains three major parts: 1. global items: Binding size, number of rows, number of records to be skipped, etc. 2. the INFILE clause specifies the path of the data file. the keywords CONSTANT and ZONE have special meaning to SQL * Loader and are therefore reserved keywords CONSTANT and ZONE are reserved words in SQL * Loader, we do not recommend using these reserved words in the table to be imported (such as fields and table names ). The following is an example: Write the control file: load datainfile 'example. dat '"fix 11" into table examplefields terminated by', 'optionally enclosed by '"' (col1, col2) example. dat: 001, cd, 0002, fghi, 00003, lmn, 1, "pqrs", 0005, uvwx, above is a Format of the control file: Fixed Record Format, the fixed length of each record is specified here. example 2: load datainfile 'example. dat '"var 3" into table examplefields terminated by', 'optionally enclosed by '"' (col1 char (5), col2 char (7) example. dat: 009 hello, cd, 010 world, im, 012my, name is, this is another Format of the control file: Variable Record Format, which specifies the maximum length of the field