FAQ
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

Search Discussions

  • Subodh Deshpande at Dec 20, 2011 at 10:17 am
    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 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


    --
    =============================================
    Love me or Hate me both are in my Favour.
    Love me, I am in your Heart. Hate me, I am in your Mind.
    =============================================
    --
    http://www.freelists.org/webpage/oracle-l
  • Abhishek Gurung at Dec 21, 2011 at 7:48 am
    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@yahoo.com
    Subject: Re: Loading data from Flat files
    To: abhishek.gurung@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@gmail.com>
    To: abhishek.gurung@hotmail.com
    Cc: Oracle Freelist <oracle-l@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 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


    --
    =============================================
    Love me or Hate me both are in my Favour.
    Love me, I am in your Heart. Hate me, I am in your Mind.
    =============================================
    --
    http://www.freelists.org/webpage/oracle-l





    --
    http://www.freelists.org/webpage/oracle-l
  • Abhishek Gurung at Dec 22, 2011 at 9:44 am
    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@gmail.com
    To: abhishek.gurung@hotmail.com
    CC: oracle-l@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 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@gmail.com
    To: abhishek.gurung@hotmail.com
    CC: oracle-l@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 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@yahoo.com
    Subject: Re: Loading data from Flat files
    To: abhishek.gurung@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@gmail.com>
    To: abhishek.gurung@hotmail.com
    Cc: Oracle Freelist <oracle-l@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 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
  • Wayne Smith at Dec 20, 2011 at 2:32 pm

    On Tue, Dec 20, 2011 at 4:47 AM, Abhishek Gurung < abhishek.gurung@hotmail.com> wrote, in part:
    1. I have to load data into oracle from multiple large flat flies ...
    I hate multiple large flies, flat or otherwise. They can be a ....
    What? Ah. Er. Oh! Never mind ... Subodh gives good advice! ;-)

    Cheers, Wayne

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 20, '11 at 9:50a
activeDec 22, '11 at 9:44a
posts5
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase