Manipulating Data and Loading via SQLLDR

Question

Can someone please help me. I need to load data from a .dat file into a Table.

.DAT file has Col1, Col2, Col3

Table have Col1, Col4

What I want is

 Table.Col4 = DAT File Col2*Col3

How can I achieve this via SQLLDR and CTL file. Note Columns in DAT File can be of Variable length.

eg DAT File: 


  110000002 , 1                                        , 7500                                       
  110000003 , 1                                        , 7500                                       
  110000008 , 1                                        , 7500                                       
  110000028 , 1                                        , 7500                                       
  110000054 , 1                                        , 7500                                       
  110000055 , 1                                        , 7500                                       
  110000082 , 1                                        , 7500                                       
  110000095 , 1                                        , 7500

Please edit your question and include an example of the data in the .dat file. Thanks. — Bob Jarvis - Слава Україні, Jun 29 '15 at 12:55
@BobJarvis, I have editted, please check if you can help me. — ShobhitSaxena, Jun 29 '15 at 12:58
See [here for an example](https://community.oracle.com/thread/558188?start=0&tstart=0) — OldProgrammer, Jun 29 '15 at 13:57

score 0 · Answer 1 · edited Jun 29 '15 at 15:45

0

I guess you could try this ctl file :

load data
infile 'my_file.dat'
truncate
into table my_table
fields terminated by ','
(
  COL1,
  COL2 boundfiller,
  COL3 boundfiller,
  COL4 expression ":COL2 * :COL3"
)

edited Jun 29 '15 at 15:45

Dimitri Mockelyn

1,535
2
11
17

answered Jun 29 '15 at 14:10

Pwin

3
2

score 0 · Answer 2 · answered Jun 29 '15 at 15:00

Hi Here is code you need to put in control file. it will work as col1 will get value from first string till ',' then second and third column value get saved into variable INPUTCOL2 and INPUTCOL3 and at the end we are combining these two values in col4 which is column of you table with multiplication.

LOAD DATA
INFILE 'mydata.dat'
INSERT
INTO TABLE MY_table
fields terminated by ','
trailing nullcols
(
 COL1,
 INPUTCOL2 BOUNDFILLER,
 INPUTCOL3 BOUNDFILLER,
 COL4 ":INPUTCOL2*:INPUTCOL3"
)

score 0 · Answer 3 · answered Jun 29 '15 at 21:35

Use of functions in a SQL*Loader control file

When using PLSQL functions in SQL*Loader, use quotations.

Here is column number four from the control file:

COL4 ":COL2*:COL3"

To see this used let us create a test table, MY_TABLE.

DDL for our test table, MY_TABLE.

CREATE TABLE SCOTT.MY_TABLE ( "COL1" NUMBER(9,0) NOT NULL ENABLE, "COL2" NUMBER(6,0), "COL3" NUMBER(6,0), "COL4" NUMBER(8,0) );
create a test control file with this approach. I included your sample data:

LOAD DATA INFILE * BADFILE 'MY_TABLE.bad' DISCARDFILE 'MY_TABLE.dsc' INSERT INTO TABLE MY_TABLE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' ' TRAILING NULLCOLS ( COL1, COL2, COL3, COL4 ":COL2*:COL3" ) BEGINDATA 110000002 , 1 , 7500 110000003 , 2 , 7500 110000008 , 1 , 7500 110000028 , 3 , 7500 110000054 , 1 , 7500 110000055 , 1 , 7500 110000082 , 4 , 7500 110000095 , 5 , 7500

Invoke sqlldr command referencing the control file.
spooled results:

SCOTT@tst>SELECT 2 * 3 FROM 4 my_table;

     COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
 110000002          1       7500       7500
 110000003          2       7500      15000
 110000008          1       7500       7500
 110000028          3       7500      22500
 110000054          1       7500       7500
 110000055          1       7500       7500
 110000082          4       7500      30000
 110000095          5       7500      37500

8 rows selected.

Manipulating Data and Loading via SQLLDR

3 Answers3