Posts Tagged ‘SQLLDR’

Oracle SQL*Loader 4 – Load data using SQLLDR command

Now we have two files and one table named STUDENT_DATA

The Data file         : std_data.dat
The Control file     : std_control.ctl

If you didn't have the two files then read this post –> Oracle SQL*Loader 2 – Data and Control File

Place the data and control file inside the directory "D:\A5518\Zzz\SQL Loader". Open command prompt and navigate to "D:\A5518\Zzz\SQL Loader" directory then enter the following command.

The command used to initiate this load needs to invoke SQL*Loader and point it to the control file describing the data. In this case, the input file name is not provided in the control file, that name needs to be passed in on the command line as well. The following sqlldr command will do the job:

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

or

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

Example

D:\A5518\Zzz\SQL Loader>sqlldr jijo/kjose CONTROL=std_control DATA=std_data LOG=std_log DISCARD=std_discard BAD=std_bad

SQL*Loader: Release 11.2.0.2.0 – Production on Mon Aug 5 17:05:14 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached – logical record count 9
Commit point reached – logical record count 10

D:\A5518\Zzz\SQL Loader>

Read more »

Oracle SQL*Loader 3 – SQLLDR parameters

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 »

Oracle SQL*Loader 2 – Data and Control File

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 »