Loading Oracle Tables from Fixed-Format Flat Files

[CraigJConrad]

A couple of weeks ago, I asked about extracting data from Oracle into fixed-format flat files. I received a very helpful reply from Mufasa (Dave) that gave a precise method for doing this. While I am now much smarter regarding SQL, my knowledge pales to those on this group, so please be gentle with me if I am asking stupid questions!

Now I have a similar issue with loading an Oracle database, where the input is coming from flat ascii files, with each field/record being of a fixed length. In other words, these are NOT CSV files. Let me offer a simple example of the layout of the flat file:

NAME Bytes 1 to 20
ADDRESS Bytes 21 to 50
SALARY Bytes 51 to 59

Therefore, an example of the file would be (assume that Tom makes $45,123.00 salary, and Mary makes $52,423.00):

000000000011111111112222222222333333333344444444445555555555
012345678901234567890123456789012345678901234567890123456789
TOM PETERS           56 MAPLE STREET               004512300
MARY HART            436 PINE STREET               005242300

Let's assume that I need to load this data into Oracle table named "Client_Table", with columns "Name", "Address" and "Salary", defined respectively as VARCHAR(20), VARCHAR(30), and NUMBER(9,2).

In z-OS DB2, there is a utility that performs this. The input would be something like:

  LOAD DATA RESUME YES LOG NO NOCOPYPEND DISCARDDN DISCARD INTO TABLE QHJL003.CLIENT_TAB
   (NAME       POSITION(01)  CHAR(20) ,
   ADDRESS     POSITION(21)  CHAR(20) NULLIF(21)=' ',
   SALARY      POSITION(51)  NUMBER(9,2))

How would I accomplish this in the Oracle world? Do I need to do some kind of import to a new table, treating each record as a row having a single CHAR, then use SQL scripts to select each one (subquery) and drive an UPDATE to the real table (use of substr to pick out the fields) from each?

 

[SantaMufasa]

Do I need to do some kind of import to a new table, treating each record as a row having a single CHAR, then use SQL scripts to select each one (subquery) and drive an UPDATE to the real table (use of substr to pick out the fields) from each?

Yes, you can do it that way.


But an easier way is to use Oracle's "SQL*Loader" utility, which uses syntax very similar to the DB2 example you offered, above. (BTW, since you started "numbering" with column "00", your first column is actually 21 characters long. But that's okay...SQL*Loader can handle it.)

Let's set the stage. (I modified a salary from "005242300" to "005242345" for the sake of illustration, to show how SQL*Loader handles decimal data):

Section 1 -- Your data:

D:\> TYPE craig.txt
TOM PETERS           56 MAPLE STREET               004512300
MARY HART            436 PINE STREET               005242345

SQL> desc client_table
 Name                    Null?    Type
 ----------------------- -------- ------------
 NAME                             VARCHAR2(20)
 ADDRESS                          VARCHAR2(30)
 SALARY                           NUMBER(9,2)

SQL> select * from client_table;

no rows selected

Section 2 -- Create a SQL*Loader control file, which I named "templdr.sql":

load data
infile "craig.txt" replace
into table client_table
(
 NAME    position( 1:21) CHAR
,ADDRESS position(22:51) CHAR
,SALARY  position(52:60) decimal external 
)

Section 3 -- Invocation of SQL*Loader:

D:>sqlldr userid=test/test control=templdr.sql log=craig.log bad=craig.bad

SQL*Loader: Release 9.2.0.4.0 - Production on Wed May 25 21:16:44 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 2

Section 4 -- Results of load:

SQL> select * from client_table;

NAME                 ADDRESS                            SALARY
-------------------- ------------------------------ ----------
TOM PETERS           56 MAPLE STREET                   4512300
MARY HART            436 PINE STREET                   5242345

2 rows selected.

Notice on the output display, that SALARY does not contain the proper decimal "pointage". SQL*Loader recognises and expects explicit decimals in decimal data. Rather than edit decimal points into your flat-file data, it is easier to simply post-process your data load with the remedial step using SQL:

SQL> update client_table set salary = salary/100;

2 rows updated.

SQL> select * from client_table;

NAME                 ADDRESS                            SALARY
-------------------- ------------------------------ ----------
TOM PETERS           56 MAPLE STREET                     45123
MARY HART            436 PINE STREET                  52423.45

2 rows selected.

SQL> commit;

Let us know if you have questions and if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CraigJConrad]

Dave,

You always come through! I've looked into this a bit, and I think I can avoid post-processing the data (to account for decimals in salary) by using something like:

load data
infile "craig.txt" replace
into table client_table
(
 NAME    position( 1:21) CHAR
,ADDRESS position(22:51) CHAR
,SALARY  position(52:60) ":salary/100" 
)

Does this make sense to you?

Craig

P.S. On a SELECT statement, we seem to not be able to create a result exceed about 4K per row in length. Is this a hard limit, installation option, etc? Any way around it?

 

[SantaMufasa]

Does this make sense to you?

Absolutely, Craig. I shuda thunk of that.

On a SELECT statement, we seem to not be able to create a result exceed about 4K per row in length.

It's not a row limitation...it is an expression limitation: 4000 character max per expression in SQL*Plus.

Can you post your precise SELECT statement so we can offer methods to resolve your issue?

There are a couple of methods to circumvent this problem, but I'd rather suggest in specific rather than in general to resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CraigJConrad]

Dave,

If that is the limitation, then I can reduce the SELECT string significantly by using a pseudonym (or whatever the proper name is) for the table name, as I specify the table name on EVERY column. Would this limit include white space (ie, if I have 10 spaces at the beginning of each continued line, to provide nicer formatting)?

Thanks, Craig

 

[SantaMufasa]

Craig,

Let's ensure that we are on the same page: in your earlier post you said, "we seem to not be able to create a result exceed about 4K per row in length"; then in your post just now you said, "I can reduce the SELECT string significantly by using a pseudonym..."

Is your 4000-character problem with source code max. or output max?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CraigJConrad]

Dave (really for the benefit of others that might read this):
Thanks to your assist on the phone, changing the "||" to "," between my column expressions, and adding "set colsep ''", I am past the 4K restriction.

Thanks!!!!!!