RE: Loading data from Flat files

From: Abhishek Gurung <abhishek.gurung_at_hotmail.com>
Date: Thu, 22 Dec 2011 15:13:29 +0530
Message-ID: <BLU159-W4141A1DD32A2F10EE3BFECEAAA0_at_phx.gbl>

Hi Subodh

Thanks a lot for you link It was really help full.

Regards
Abhishek Gurung

Date: Thu, 22 Dec 2011 11:52:02 +0530
Subject: Re: Loading data from Flat files From: deshpande.subodh_at_gmail.com
To: abhishek.gurung_at_hotmail.com
CC: oracle-l_at_freelists.org

hi,
I think following links can help you in overcoming the issue of linesize limit https://forums.oracle.com/forums/thread.jspa?threadID!56053 http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_10.shtmlhttp://www.oracle-base.com/articles/9i/GeneratingCSVFiles.php

thanks....subodhOn 21 December 2011 22:31, Abhishek Gurung <abhishek.gurung_at_hotmail.com> wrote:

Hi

Thanks a lot Subodh for your links.
UTL_FILE.FOPEN (

   location     IN VARCHAR2,
   filename     IN VARCHAR2,
   open_mode    IN VARCHAR2,

   max_linesize IN BINARY_INTEGER)
  RETURN file_type;The fopen has max_linesize parameter whose limit is between 1 to 32767 that is why I was asking whether we can use it for files containing lines more than this limit.

Regards
Abhishek Gurung

> Date: Wed, 21 Dec 2011 17:22:39 +0530
> Subject: Re: Loading data from Flat files
> From: deshpande.subodh_at_gmail.com

> To: abhishek.gurung_at_hotmail.com
> CC: oracle-l_at_freelists.org

>
> I think it all depends..I have not found such limitations
>
> is it a one time exercise or a daily or periodic
> requirement..depending upon that you need to choose the tool..
>

> following links may give you more info...
>
> https://forums.oracle.com/forums/thread.jspa?threadID!80669

> http://www.orafaq.com/forum/t/58333/0/
> http://www.dbforums.com/oracle/949550-when-use-sql-loader-when-utl_file-package-extract-file.html

> http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch10.htm
>
> thanks..subodh
> On 21/12/2011, Abhishek Gurung <abhishek.gurung_at_hotmail.com> wrote:

> > Hi
> >
> > 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?
> >
> > Regards
> > Abhishek Gurung
> >
> >
> > Date: Tue, 20 Dec 2011 07:04:00 -0800
> > From: jobmiller_at_yahoo.com

> > Subject: Re: Loading data from Flat files
> > To: abhishek.gurung_at_hotmail.com
> >
> > 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..
> > Job
> > From: Subodh Deshpande <deshpande.subodh_at_gmail.com>

> > To: abhishek.gurung_at_hotmail.com
> > Cc: Oracle Freelist <oracle-l_at_freelists.org>

> > 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..
> >
> > thanks..subodh
> >
> > On 20/12/2011, Abhishek Gurung <abhishek.gurung_at_hotmail.com> 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
> >>
> >> --
                                                                                              

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 22 2011 - 03:43:29 CST