New SQL*loader express mode you can load data from file by specifying only a table name with the following requirements:

1. Delimited data only
2. Column data types only character, numbers, and datetime.
3. Order is same as table column definition.
4. Express mode does not use a control file.

 

SQL*Loader express mode assumes the following

1. Data file – when non is specified, looks for file table_name.dat in current directory
2. Load Method – default is external tables.
3. Fields – use name, data type, and order of table definition. Records are delimited by comma, separated by new line, have no enclosures, and use left-right trim.
4. DOP – Degree of Parallelism is set to Auto.
5. Date Format – Uses NLS settings used.
6. Character Set – Uses NLS settings
7. Append mode – Appended to table if it already exists.
8. File Names – where %p is process ID of database slave process:
   1. Data File – table_name.dat
   2. SQL*Loader Log File – table_name.log
   3. Oracle DB log file – table_name_%p.log_xt
   4. Bad Files – table_name_%p.bad

 

 

EXAMPLE:

 

1. Logon to SQLPLUS as sysdba and grant the user scott create any directory and drop any directory

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 3 12:40:37 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL> grant create any directory to scott;

Grant succeeded.

SQL> grant drop any directory to scott;

Grant succeeded.

SQL>

 

2. Reconnect to SQLPLUS as the user scott and create the table test.

 

SQL> connect scott/password

Connected.

SQL> create table test(a char(5), b char(5));

Table created.

SQL>

 

3. Exit out of SQLPLUS and create a file test.dat with the following text.

 

a,a

b,b

c,c

 

4. Execute the command: sqlldr scott/password table=test

 

 

[oracle@linux2 ~]$ sqlldr scott/password table=test

SQL*Loader: Release 12.1.0.2.0 – Production on Tue Oct 3 12:36:18 2016

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

Express Mode Load, Table: TEST

Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table TEST:

  3 Rows successfully loaded.

 

Check the log files:

  test.log

  test_%p.log_xt

for more information about the load.

[oracle@linux2 ~]$

 

5. Logon to SQLPLUS as the user scott and perform select on table TEST to see the results of SQLLDR utility with Express Mode.

 

[oracle@linux2 ~]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 3 12:44:03 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 03 2016 12:41:50 -05:00

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL> select * from test;

A     B

—– —–

a     a

b     b

c     c

 

SQL>

 

 

6. This completes example of SQLLDR Express feature.

 

Larry Catt

OCP

Oracle provides the utility SQL*loader for loading data from non-Oracle databases.  SQL*Loader is very flexible and can read a large range of flat files.  SQL*Loader can perform the following functions:

 

1. Load data across network if files on different machine.
2. Load data from multiple files in same session.
3. Load data into multiple tables in same session.
4. Specify character set.
5. Selectively load data by values.
6. Manipulate data before loading.
7. Generate unique sequential keys.
8. Use OS to access data files.
9. Load data from disk, tape or named pipe.
10. Load arbitrarily complex object-related data.
11. Use secondary data files for loading LOBs and collections.
12. Use conventional, direct path or external table loads.

 

SQL*Loader can be used with or without control file and you may encounter the following files during use:

• Control – Defines data format and control behaviors.
• Data – One or more data files containing info to be loaded.
• Log – Logs performance and errors.
• Bad – Contains all records not loaded because of errors.
• Discard – Contains all records discarded by control file identified bypass.

 

SQL*Loader is invoked with the command SQLLDR.  You use parameters in the PARFILE or at the command line to control actions of SQLLDR.  If parameter appears in PARFILE and command line, command line is used.  Three sections of control files: PARFILE.

 

1. Session wide info: global options, input data file location, data to be loaded.
2. Contains INTO TABLE blocks (one or more)
3. Optional third section can contain input data.

 

SQL*Loader can use two methods for loading data: Conventional Path and Direct Load option.  In Conventional Path SQL*Loader creates INSERT statement for data to be loaded and passes it to SQL Parser.   In Direct Load Path SQL*Loader bypasses the SQL and parser and loads data straight into the target table.  Direct Path is much faster than Conventional path, but conventional path is more flexible.

There are a few restrictions of Direct Path method:

 

1. Cannot run concurrently with other transactions against a target table.
2. Triggers on table do not fire.
3. Data is written above high water mark, even if space is available below.
4. Clustered tables are not supported.
5. Foreign Key constraints are disabled during load.

 

 

Larry Catt

OCP