Database Reference
In-Depth Information
Now that we know each and every record is 80 bytes long, we are ready to load it using the control file listed
earlier with the
FIX 80
clause. When we do so, we can see the following:
EODA@ORA12CR1> select '"' || comments || '"' comments from dept;
COMMENTS
-------------------------------------------------------------------------------
"This is the Sales
Office in Virginia "
"This is the Accounting
Office in Virginia "
"This is the Consulting
Office in Virginia "
"This is the Finance
Office in Virginia "
You might need to
trim
this data, since the trailing whitespace is preserved. You can do that in the control file,
using the
TRIM
built-in SQL function.
A word of caution to those of you lucky enough to work on both Windows and UNIX/Linux: the end-of-line
marker is different on these platforms. On UNIX/Linux, it is simply
\n
(
CHR(10)
in SQL). On Windows/DOS, it is
\r\n
(
CHR(13)||CHR(10)
in SQL). In general, if you use the
FIX
approach, make sure to
create and load
the file on a
homogenous platform (UNIX/Linux and UNIX/Linux, or Windows and Windows).
Use the VAR Attribute
Another method of loading data with embedded newline characters is to use the
VAR
attribute. When using this
format, each record will begin with some fixed number of bytes that represent the total length of the incoming record.
Using this format, we can load variable-length records that contain embedded newlines, but only if we have a
record length field
at the beginning of
each and every
record. So, suppose we use a control file such as the following:
LOAD DATA
INFILE demo.dat "var 3"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
COMMENTS
)
