SQL Loader - Oracle 12C - Express Mode
I got introduced to the new capabilities of
SQL Loader in Oracle 12C today. One such feature is the express mode that is available in 12C and in this post let us experiment this new approach with a basic example. To get this example working, you would require a working version of Oracle 12C available at your end. Some theory to start with, that explains what all this express mode is about;Express Mode - Basic Definition
Express mode as the name suggests simplifies the SQL loader operation by offering a mode of loading, that requires no control files. All it warrants is a data file with the name which is same as the name of the table that you are loading. For this basic example, it would be enough to know this much (we will see some advanced usage in subsequent posts). Let us see how this mode works now.Working Example - Express Mode
We will create a test table in HR schema, which we will use for this post. The DDL of the table is shown below:
PDB1@ORCL> create table test
2 ( text1 varchar2(10),
3 counter number)
4 /
Table created.
Next, you will have to create a simple dat file the contents of which we will load into this table via SQL Loader. As we have two columns you have to specify the column values in the same order in the file. The separator between two columns in the file should be a comma. A sample file is shown below:
[oracle@localhost sqlldrtest]$ more test.dat
time,1
test,2
trim,3
twin,4
That is all you need. You are ready to use SQL Loader to load this data into the table - through the express mode. Run the command as shown below;[oracle@localhost sqlldrtest]$ sqlldr hr table=test
Note that we are specifying the schema where the load has to happen, followed by the table name. You have to keep the test.dat file in the same directory where you are triggering the load. When you enter this command, Oracle will prompt you for the password for hr schema. Enter the password and viola - you have completed the load. A sample output that comes on the screen is provided below:
[oracle@localhost sqlldrtest]$ sqlldr hr table=test
Password:
SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jul 4 07:42:41 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: TEST
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table TEST:
4 Rows successfully loaded.
Check the log files:
test.log
test_%p.log_xt
for more information about the load.
[oracle@localhost sqlldrtest]$
Let us discuss more on each of these techniques in the upcoming posts. For now, a snapshot of the log files created are provided below.
SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jul 4 07:42:41 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: TEST
Data File: test.dat
Bad File: test_%p.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
Table TEST, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TEXT1 FIRST * , CHARACTER
COUNTER NEXT * , CHARACTER
Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'test'
APPEND
INTO TABLE TEST
FIELDS TERMINATED BY ","
(
TEXT1,
COUNTER
)
End of generated control file for possible reuse.
enable parallel DML: ALTER SESSION ENABLE PARALLEL DML
creating external table "SYS_SQLLDR_X_EXT_TEST"
CREATE TABLE "SYS_SQLLDR_X_EXT_TEST"
(
"TEXT1" VARCHAR2(10),
"COUNTER" NUMBER
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test_%p.bad'
LOGFILE 'test_%p.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"TEXT1" CHAR(255),
"COUNTER" CHAR(255)
)
)
location
(
'test.dat'
)
)REJECT LIMIT UNLIMITED
executing INSERT statement to load database table TEST
INSERT /*+ append parallel(auto) */ INTO TEST
(
TEXT1,
COUNTER
)
SELECT
"TEXT1",
"COUNTER"
FROM "SYS_SQLLDR_X_EXT_TEST"
dropping external table "SYS_SQLLDR_X_EXT_TEST"
Table TEST:
4 Rows successfully loaded.
Run began on Fri Jul 04 07:42:41 2014
Run ended on Fri Jul 04 07:42:48 2014
Elapsed time was: 00:00:07.10
CPU time was: 00:00:00.05
If you see the log files, you can find that SQL Loader has actually generated a control file for you for possible reuse. It also specifies that express mode is used for loading and the actual load is done via the concept of external tables. (more on this later).
Another log file is created on the load, contents of which is provided below:
LOG file opened at 07/04/14 07:22:28
KUP-05004: Warning: Intra source concurrency disabled because parallel select was not requested.
Field Definitions for table SYS_SQLLDR_X_EXT_TEST
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields
Fields in Data Source:
TEXT1 CHAR (255)
Terminated by ","
Trim whitespace from left and right
COUNTER CHAR (255)
Terminated by ","
Trim whitespace from left and right
This file specifies the field mapping that was followed, the separator and action taken on whitespace columns. When you query the actual table after the load, you can find that the records are successfully inserted. This is shown below:
PDB1@ORCL> select * from test
2 /
TEXT1 COUNTER
---------- ----------
time 1
test 2
trim 3
twin 4
That completes a basic introduction to express mode in SQL Loader - Oracle 12C. Hope this was useful - and in the next post we can deep dive into using some additional options in this new load technique. We will also learn more on this feature and explore some additional possibilities.
No comments:
Post a Comment