RE: Loading data from Flat files

From: Abhishek Gurung <>
Date: Wed, 21 Dec 2011 13:17:47 +0530
Message-ID: <BLU159-W15DB3D9C4D779797687643EAA50_at_phx.gbl>


Thanks guys for your suggestions.
I have two more questions

  1. Can I use UTL_FILE.FOPEN () for larger files containing lines more than 32767 lines?
  2. Can I call SQL Loader to load csv files from stored procedure?

Abhishek Gurung

Date: Tue, 20 Dec 2011 07:04:00 -0800
Subject: Re: Loading data from Flat files To:

sounds like something someone with some perl dbi skills could accomplish rather quickly. perl would make tokenizing and parsing the file and generating the rather dynamic sql to do the load pretty easy. you could approach it many ways.. you could make one pass it at that did nothing but generate a file of insert/update statements that you could execute later after you validated it and determined when it was an insert vs. an update, or you could programmatically try everything as an insert, and when an insert fails because of pk violation, fall back to an update. depending on the variety of files, and the regularity of the ones you receive, you could attack it  many ways.
however, Perl is a simple way to process text files in creative ways. xml is a different story..

       From: Subodh Deshpande <>  To:
Cc: Oracle Freelist <>  Sent: Tuesday, December 20, 2011 5:17 AM  Subject: Re: Loading data from Flat files   

I think, first you should create staging tables and purify the data and do the checking etc.

then prepare a copy of the target tables and in this copy of tables update the columns with data in staging tables..check once again this is what you require and then finally you update your target tables with prebackup of target tables..


On 20/12/2011, Abhishek Gurung <> wrote:
> Hi
> Scenario:
> 1. I have to load data into oracle from multiple large flat flies and XML.
> 2. Each flat file contains data of multiple table.
> 3. Structure of flat file is like this
> system_variable TableName:Column1_Name="Value", Column2_Name="Value",
> Column3_Name="Value".......................ColumnN_Name="Value";
> 4. Only

 Changed/Added columns are available in file for that particular
> table.
> Question: What is the best method to load data from these files to Oracle
> tables?
> Regards
> Abhishek Gurung
> --

Love me or Hate me both are in my Favour.
Love me, I am in your Heart. Hate me, I am in your Mind.

Received on Wed Dec 21 2011 - 01:47:47 CST

Original text of this message