Load Variable Fields Flat File in Oracle Table

  • Updated
  • Posted in Programming
  • 4 mins read

In certainly one of my venture, we bought a requirement to load knowledge from a various fields flat file into oracle desk. The full necessities are talked about under:


  • Daily we are going to get a comma delimited flat file which incorporates the month-to-month sensible gross sales data of merchandise.
  • The knowledge in the flat file is in denormalized construction.
  • The variety of months in the flat file might differ from day after day.
  • The header of the flat file incorporates the fields.

Let say at this time the construction of the flat file would possibly look as


The subsequent day the flat file construction would possibly differ in the variety of months. However the product area would be the all the time be there in the primary area of the flat file. The pattern flat file construction in the subsequent day seems to be as


Now the issue is to load this flat file into the oracle desk. The very first thing is designing the goal desk. We designed a normalized goal desk and the construction of the desk seems to be as

Table Name: Product_Sales
Product, Month, Sales
A, Jan2012,100
A, Feb2012,200
B, Jan2012,500
B, Feb2012,300
C, Jan2012,300
C, Feb2012,200
C, Mar2012,500
D, Jan2012,100
D, Feb2012,300
D, Mar2012,700

Anyhow we designed the goal desk. Now comes the true drawback. How to determine the variety of fields in the flat file and easy methods to load the denormalized flat file into the normalized desk?

We created new process to deal with this drawback. Here i’m itemizing the sequence of steps in the process which we used to load the flat file knowledge into the oracle database.

Reading the Header data from the file:

  • Created the required variables. I’ll point out them as and when required.
  • We have used the utl_file bundle in oracle which is for studying the flat file.
  • The syntax for opening the file is
FileDeal with utl_file.file_type; --variable

FileDeal with:=utl_file.fopen(
file_location IN VARCHAR2,
file_name IN VARCHAR2,
open_mode IN VARCHAR2,

  • We have opened the file. Now we are going to learn the flat file header which is the primary line in the file. The syntax is
Header Varchar2(4000);

utl_file.get_line(FileDeal with,Header);
utl_file.fclose(FileDeal with);

  • The Header variable incorporates the header a part of the file which incorporates the fields in the file. The knowledge in the Header variable seems to be as
  • We have created an exterior desk through the use of this Header variable.

Creating the External Table:

  • As the Header variable incorporates the fields from the file, it’s simple to assemble the syntax for exterior desk creation.
  • Replace the comma in the Header variable with “varchar2(100),”. Then concatenate the variable with ” Varchar2(100)” on the finish. This step is proven in the under instance:
Header_With_datatypes:=Replace(Header,',',' varchar2(100),') || ' varchar2(100);

The knowledge in the Header_With_datatypes variable will look as

product varchar2(100), Jan2012 varchar2(100), Feb2012 Varchar2(30)

  • We have constructed the fields with knowledge varieties. Now now we have to assemble the construction of exterior desk utilizing the variable. This is present in the under instance:
Create desk external_stage_table
Organization exterior
Access parameters
skip 1
  • Use execute speedy to create the exterior desk.

Transposing the columns into rows:

  • Now now we have to transpose the columns in the flat file into rows after which load into the ultimate desk. We should transpose solely the month columns and never the product column. The steps concerned in transposing the columns are listed under:

insert into target_table
choose *
from external_stage_table
unpivot (gross sales for month in (Header))

  • Drop the exterior desk as soon as inserting the goal desk is finished.

I’ve supplied simply an summary of the steps that now we have used.

Leave a Reply