Jijo K Jose

Issuing the sqlldr command by itself results in a list of valid command-line parameters being displayed.

sqlldr <username>/<password> CONTROL=<control_file_name> DATA=<data_file_name> LOG=<log_file_name> DISCARD=<discard_file_name> BAD=<bad_file_name>

Example

sqlldr jijo/kjose CONTROL=std_control DATA=std_data LOG=std_log DISCARD=std_discard BAD=std_bad

Command-line parameters are usually keyword/value pairs, and may be any combination of the following:

USERID={username[/password][@net_service_name]|/}
CONTROL=control_file_name
LOG=path_file_name
BAD=path_file_name
DATA=path_file_name
DISCARD=path_file_name
DISCARDMAX=logical_record_count
SKIP=logical_record_count
SKIP_INDEX_MAINTENANCE={TRUE | FALSE}
SKIP_UNUSABLE_INDEXES={TRUE | FALSE}
LOAD=logical_record_count
ERRORS=insert_error_count
ROWS=rows_in_bind_array
BINDSIZE=bytes_in_bind_array
SILENT=[(]keyword[,keyword…][)]
DIRECT={TRUE | FALSE}
PARFILE=path_file_name
PARALLEL={TRUE | FALSE}
READSIZE=bytes_in_read_buffer
FILE=database_datafile_name

SQLLDR Command-Line Parameters

The SQL*Loader parameter descriptions are as follows:

USERID = {username[/password] [@net_service_name]|/}
    Specifies the username and password to use when connecting to the database. The net_service_name parameter optionally allows you to connect to a remote database. Use a forward-slash character ( / ) to connect to a local database using operating system authentication. On Unix systems, you may want to omit the password and allow SQL*Loader to prompt you for it. If you omit both the username and the password, SQL*Loader will prompt you for both.

CONTROL = control_ file_name
    Specifies the name, which may include the path, of the control file. The default extension is .ctl.

Read more »

The Data File – std_data.dat

The particular file used for this example contains the details of some students.

"1","Jijo","K","Jose","10","2010","March","92.82"
"2","Arun",,"Dev","3","2011","March","86.14"
"3","Deepak","T","Raj","5","2010","July","75.14"
"4","James","Tom","Mathew","8","2011","June","70"
"5","Sam","","Thomas","25","2010","July","89.52"
"6","Ajith","John","Mathew","1","2011","March","90.1475"
"7","Kamal","G","Sekar","17","2011","June","74"
"8","John","","James","12","2011","July","86.47"
"9","Thomas","","Paul","30","2009","March","90%"
"10","Praveen","Thomas","Jacob","22","2010","June","90.475"

As you can see, the data in the file is comma-delimited, and each field is enclosed within double quotes.
We used the following SQL statement to create the table into which all this data will be loaded:

CREATE TABLE STUDENT_DATA (
    id NUMBER(3),
    first_name VARCHAR2(10),
    last_name VARCHAR2(10),
    roll_number NUMBER(2),
    batch CHAR(10),
    mark NUMBER(5,2)
) TABLESPACE SYSAUX;

As you can see, not all fields in the data file are to be loaded into the table.

The Control File – std_control.ctl

The following control file will be used to load the data into the table.

LOAD DATA
   APPEND INTO TABLE STUDENT_DATA
   (

Read more »

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables.

The basis for almost everything you do with SQL*Loader is a file known as the control file. The SQL*Loader control file is a text file into which you place a description of the data to be loaded. You also use the control file to tell SQL*Loader which database tables and columns should receive the data that you are loading.

Once you have a data file to load and a control file describing the data contained in that data file, you are ready to begin the load process. You do this by invoking the SQL*Loader executable and pointing it to the control file that you have written. SQL*Loader reads the control file to get a description of the data to be loaded. Then it reads the input file and loads the input data into the database.

The SQL*Loader Control File

The SQL*Loader control file is the key to any load process. The control file provides the following information to SQL*Loader:

•     The name and location of the input data file

•     The format of the records in the input data file

•     The name of the table or tables to be loaded

•     The correspondence between the fields in the input record and the columns in the database tables being loaded

•     Selection criteria defining which records from the input file contain data to be inserted into the destination database tables.

•     The names and locations of the bad file and the discard file

Read more »