On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, September 1, 2008

SQL*Loader - Fixed Length Record

Set Feedback Off Pagesize 0 Echo Off Null ' 0000'
--+--------------------------------------------
--| Set up to read in fixed length data.
--+--------------------------------------------

Prompt . 1 2 3 4
Prompt 1234567890123456789012345678901234567890
--+--------------------------------------------
--| Dump the data with pads to set exact lengths
--+--------------------------------------------
Spool Dumpfix.Txt

Select Empno,
Rpad(Ename,10),
Rpad(Job , 9),
Mgr,
Lpad(Sal ,7,0)
From Emp;

Spool Off

--+--------------------------------------------
--| Create an input table
--+--------------------------------------------

Drop Table Emp2;
Create Table Emp2
( Empno Number(4)
, Ename Varchar2(10)
, Job Varchar2(9)
, Mgr Number(4)
, Sal Number(7,2)
);

--+--------------------------------------------
--| Call the control file
--+--------------------------------------------

Host sqlldr scott/tiger control=dumpfix.ctl

--+--------------------------------------------
--| Prove the data made it into the new table
--+--------------------------------------------
Set Feedback On Pagesize 20 Echo On Null '.'

Select *
From Emp2;



The Control File:
Load Data
Infile 'Dumpfix.Txt'
Into Table Emp2
( Empno Position( 2: 5) Integer External
, Ename Position( 7:17) Char
, Job Position(18:28) Char
, Mgr Position(29:32) Integer External
, Sal Position(34:40) Integer External
)

No comments:

Post a Comment