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