===============================================================================
IMPLIED DECIMAL PLACES
===============================================================================
1. How do you load an
implied decimal place into the database?
Example:
=======
Let us take an example of a bank account, the data contains a
transaction
type, an account number, and a dollar amount. The last column is a
numeric value with an implied decimal place (100.73, 75.25, and
20.00):
DEPOSIT 10015
10073
DEPOSIT 10020
7525
WITHDRAWAL 10015
2000
We want to load the data into the following table:
SQL> CREATE TABLE
register
2 (tx_type CHAR(15),
3 acct NUMBER,
4
amt NUMBER);
The control file would look like:
LOAD DATA
INFILE 'month.dat'
INTO TABLE register
(tx_type POSITION(1:10),
acct POSITION(13:17),
amt POSITION(20:24) ":amt/100"
)
After we run SQL*Loader, our table data looks like:
SQL> SELECT * FROM
register;
TX_TYPE ACCT AMT
---------------
---------- ----------
DEPOSIT 10015 100.73
DEPOSIT 10020 75.25
WITHDRAWAL 10015 20
How this works:
==============
SQL*Loader simply builds an insert statement which looks like:
INSERT INTO register
(tx_type,acct,amt)
VALUES (:data1,
:data2, ":amt/100");
So, for every AMT loaded, we divide it by 100.
See chapter 6 of the Utilities Guide "Applying SQL Operators
to Fields".
Restrictions:
============
This method cannot be used with DIRECT PATH Loader.
===============================================================================
LOADING DATABASE SEQUENCES
===============================================================================
2. How do you load a
database sequence into a table?
Example-I:
=========
In the first example, all of the fields are located in the
datafile based
on position, which makes this easier. Another example below covers data that
is comma delimited.
We want to load the data into the following table:
SQL> CREATE TABLE
load_db_seq_positional
2 (seq_number NUMBER,
3 data1 NUMBER,
4 data2 CHAR(15) );
We will use the following sequence:
SQL> CREATE SEQUENCE
db_seq
2 START WITH 1
3 INCREMENT BY 1;
The control file would look like:
LOAD DATA
INFILE *
INTO TABLE
load_db_seq_positional
(seq_number
"db_seq.nextval"
data1 POSITION(1:5),
data2 POSITION(6:15),
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
After we run SQL*Loader, our table data looks like:
SQL> SELECT * FROM
load_db_seq_positional;
SEQ_NUMBER DATA1 DATA2
---------- ----------
---------------
1 11111 AAAAAAAAAA
2 22222 BBBBBBBBBB
Example-II:
==========
In this example, the data fields are comma delimited. The key here
is that
since fields are delimited, SQL*Loader will expect to find values
for the
field SEQ_NUMBER in the data file.
Since such entries do not exist, what
we must do is to put the SEQ_NUMBER field as the last field in the
control
file, and then use the TRAILING NULLCOLS clause to indicate
to Loader that
on some lines (in this case all), there may be "trailing
columns" which are
null, or non-existent.
Here is the similar create table statetement, we will use the same
sequence:
SQL> CREATE TABLE
load_db_seq_delimited
2 (seq_number NUMBER,
3 data1 NUMBER,
4 data2 CHAR(15));
The control file would look like:
LOAD DATA
INFILE *
INTO TABLE
load_db_seq_delimited
FIELDS TERMINATED BY
","
TRAILING NULLCOLS
(data1,
data2,
seq_number
"db_seq.nextval"
)
BEGINDATA
11111,AAAAAAAAAA
22222,BBBBBBBBBB
After we run SQL*Loader, our table data looks like:
SQL> SELECT * FROM
load_db_seq_delimited;
SEQ_NUMBER DATA1 DATA2
---------- ----------
---------------
3 11111 AAAAAAAAAA
4 22222 BBBBBBBBBB
How this works:
==============
SQL*Loader simply builds an insert statement which looks like:
INSERT INTO
load_db_seq_delimited (data1,data2,seq_number)
VALUES (:data1,
:data2, "db_seq.nextval");
See chapter 6 of the Utilities Guide "Applying SQL Operators
to Fields".
Restrictions:
============
Both of these methods cannot be used with DIRECT PATH Loader.
===============================================================================
LOADING
USERNAME OF USER RUNNING SQL*LOADER
===============================================================================
3. How do you load the
username of the user running the SQL*Loader session?
Example-I:
=========
In this example, all of the fields are located in the datafile
based
on position, which makes this easier. Another example below, which is
slightly more difficult, covers data that is comma delimited. Both
methods
take advantage of the "USER" pseudo-variable. If you prefer to use the
Oracle User ID number, you could use "UID" instead.
We want to load the data into the following table:
SQL> CREATE TABLE
load_user_positional
2 (username CHAR(30),
3 data1 NUMBER,
4 data2 CHAR(15) );
The control file would look like:
LOAD DATA
INFILE *
INTO TABLE
load_user_positional
(username
"USER"
data1 POSITION(1:5),
data2 POSITION(6:15),
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
If we run SQL*Loader as scott:
$ SQLLDR scott/tiger load_user_d.ctl
Our table data looks like:
SQL> SELECT * FROM
load_user_positional;
USERNAME DATA1 DATA2
-------------- ----------
---------------
SCOTT 11111 AAAAAAAAAA
SCOTT 22222 BBBBBBBBBB
Example-II:
==========
In this example, the data fields are comma delimited. The key here
is that
since fields are delimited, SQL*Loader will expect to find values
for the
field USERNAME in the data file.
Since such entries do not exist, then we
must put the USERNAME field as the last field in the control file,
and then
use the TRAILING NULLCOLS clause to indicate to SQL*Loader that on
some lines
(in this case all), there may be "trailing columns"
which are null, or
non-existent.
Here is the similar create statetement:
SQL> CREATE TABLE
load_user_delimited
2 (username CHAR(30),
3 data1 NUMBER,
4 data2 CHAR(15) );
The control file would look like:
LOAD DATA
INFILE *
INTO TABLE
load_user_delimited
FIELDS TERMINATED BY
","
TRAILING NULLCOLS
(data1,
data2,
username
"USER"
)
BEGINDATA
11111,AAAAAAAAAA
22222,BBBBBBBBBB
If we run SQL*Loader as jack:
$ SQLLDR jack/jack load_user_d.ctl
Our table data looks like:
SQL> SELECT * FROM
load_user_delimited;
USERNAME DATA1 DATA2
-------------- ----------
---------------
JACK 11111 AAAAAAAAAA
JACK 22222 BBBBBBBBBB
How this works:
==============
SQL*Loader simply builds an insert statement which looks like:
INSERT INTO
load_user_delimited (data1,data2,seq_number)
VALUES (:data1,
:data2, "user");
See chapter 6 of the Utilities Guide "Applying SQL Operators
to Fields".
Restrictions:
============
Both of these methods cannot be used with DIRECT PATH Loader.