best way to extract the data from several tables into a file

Discussion in 'Java' started by mike hengins, Aug 17, 2005.

  1. mike hengins

    mike hengins Guest

    hi,
    I have to extract the data from several tables (Oracle is the database)
    and insert them into a file, in a specified format.

    Each column will be allocated a certain size (filled with empty spaces if
    size is too big), and I will assign a line for each record extracted from
    the tables. There wont be any delimiter.
    Example of such line:
    column1 colum2column2 column4 etc

    So i am looking for ways to do it in Java, of course.
    Thanks in advance for your suggestions and ideas.
     
    mike hengins, Aug 17, 2005
    #1
    1. Advertising

  2. mike hengins

    Malte Guest

    mike hengins wrote:
    > hi,
    > I have to extract the data from several tables (Oracle is the database)
    > and insert them into a file, in a specified format.
    >
    > Each column will be allocated a certain size (filled with empty spaces if
    > size is too big), and I will assign a line for each record extracted from
    > the tables. There wont be any delimiter.
    > Example of such line:
    > column1 colum2column2 column4 etc
    >
    > So i am looking for ways to do it in Java, of course.
    > Thanks in advance for your suggestions and ideas.
    >
    >


    Hmm, depending on the amount of data there are a couple of ways that
    come to mind:

    1. write a java or pl/sql stored procedure that formats the data and
    runs in the database, then use jdbc to get it.

    2. use BC4J or Toplink to get at the data easily, then use (Toplink's)
    methods in a wrapper class that formats the data.
     
    Malte, Aug 17, 2005
    #2
    1. Advertising

  3. mike hengins

    jan V Guest

    > I have to extract the data from several tables (Oracle is the database)
    > and insert them into a file, in a specified format.
    >
    > So i am looking for ways to do it in Java, of course.
    > Thanks in advance for your suggestions and ideas.


    Can you bypass Oracle and go straight to database's data store? For a static
    (non-live) database, that would be a super-efficient way... assuming you can
    get at the data in those files.. [this is called out of the box thinking,
    and as with all out of the box thinking, some ideas may not always be as
    practical as alternative ideas ;-) ]
     
    jan V, Aug 17, 2005
    #3
  4. mike hengins

    Joan Guest

    "jan V" <> wrote in message
    news:Q5OMe.171850$-ops.be...
    >> I have to extract the data from several tables (Oracle is the
    >> database)
    >> and insert them into a file, in a specified format.
    >>
    >> So i am looking for ways to do it in Java, of course.
    >> Thanks in advance for your suggestions and ideas.

    >
    > Can you bypass Oracle and go straight to database's data store?
    > For a static
    > (non-live) database, that would be a super-efficient way...
    > assuming you can
    > get at the data in those files.. [this is called out of the box
    > thinking,
    > and as with all out of the box thinking, some ideas may not
    > always be as
    > practical as alternative ideas ;-) ]
    >


    How about if you bypass the operating system too and write a
    program in binary.
     
    Joan, Aug 18, 2005
    #4
  5. mike hengins

    mike hengins Guest

    "Joan" <> wrote in message
    news:...
    >
    > "jan V" <> wrote in message
    > news:Q5OMe.171850$-ops.be...
    >>> I have to extract the data from several tables (Oracle is the database)
    >>> and insert them into a file, in a specified format.
    >>>
    >>> So i am looking for ways to do it in Java, of course.
    >>> Thanks in advance for your suggestions and ideas.

    >>
    >> Can you bypass Oracle and go straight to database's data store? For a
    >> static
    >> (non-live) database, that would be a super-efficient way... assuming you
    >> can
    >> get at the data in those files.. [this is called out of the box thinking,
    >> and as with all out of the box thinking, some ideas may not always be as
    >> practical as alternative ideas ;-) ]
    >>

    >
    > How about if you bypass the operating system too and write a program in
    > binary.


    i need more concrete ideas please. Looking for some real experience on best
    way to extract data from a database to
    a flat file. thanks
     
    mike hengins, Aug 18, 2005
    #5
  6. mike hengins

    jan V Guest

    > > [this is called out of the box thinking, and as with all out of the box
    thinking, some ideas may not
    > > always be as practical as alternative ideas ;-) ]

    >
    > How about if you bypass the operating system too and write a program in

    binary.

    A true engineer comes up with a whole spectrum of ideas *before* choosing
    which one to go for. Without creativity, you may not find a decent solution.

    But it's a well-understood thing in psychology that (narrow-minded) people
    very often ridicule people who come up with surprising suggestions or
    statements because this is an instinctive mental world model preservation
    reaction: the human brain simply does not like inputs which severely
    challenge its model of the world. If I say "The universe is made of cheese",
    virtually everyone will instinctive laugh *before* consciously and
    critically thinking about the statement. This is normal, since a decent
    mental model is key to the organism's short- and long-term survival. But
    engineering is the business of creating working solutions, and that requires
    suspending our instinctive mental conservatism in favour of letting the
    neurons go wild for a bit, in the hope we see solutions we'd otherwise never
    have found just trying to rely on what's already in our brain.

    For the record though, hundreds of video games written in the 80s and 90s
    *had to be* written by bypassing machine OSes. It was the only way to
    achieve what the marketplace expected of the games at the time.

    And last but not least, a few years ago I worked for a company where another
    team had this major database performance bottleneck (the database wasn't a
    live thing, though it was huge). They just couldn't get the performance they
    needed, so some guy (not me), came up with the idea of going behind the
    facade and hit the data files themselves (no idea if it was Oracle, I wasn't
    on that team)... and that solved their problem. Instead of struggling with a
    program that needed days to run to completion, they managed to write a
    solution that ran in a few hours, if I recall correctly.
     
    jan V, Aug 18, 2005
    #6
  7. Hi,

    mike hengins wrote:
    > i need more concrete ideas please.


    Yes! I'll try somthing without a cheesy universe... ;-)

    > Looking for some real experience on best
    > way to extract data from a database to
    > a flat file. thanks


    Look at the package java.sql (Statement, ResultSet, ...) to read the
    data from the DB.

    Use a StringBuffer (or better StringBuilder) to concatenate the fields
    to a line.

    Write the line to File using PrintWriter or BufferedWriter or something
    from the java.io-package.

    It's quite easy, indeed. If you tell us more about your problems, we can
    help you much better.

    Ciao,
    Ingo
     
    Ingo R. Homann, Aug 18, 2005
    #7
  8. jan V wrote:
    >>>[this is called out of the box thinking, and as with all out of the box

    >
    > thinking, some ideas may not
    >
    >>>always be as practical as alternative ideas ;-) ]

    >>
    >>How about if you bypass the operating system too and write a program in

    >
    > binary.
    >
    > A true engineer comes up with a whole spectrum of ideas *before* choosing
    > which one to go for. Without creativity, you may not find a decent solution.
    >
    > But it's a well-understood thing in psychology that (narrow-minded) people
    > very often ridicule people who come up with surprising suggestions or
    > statements because this is an instinctive mental world model preservation
    > reaction: the human brain simply does not like inputs which severely
    > challenge its model of the world. If I say "The universe is made of cheese",
    > virtually everyone will instinctive laugh *before* consciously and
    > critically thinking about the statement. This is normal, since a decent
    > mental model is key to the organism's short- and long-term survival. But
    > engineering is the business of creating working solutions, and that requires
    > suspending our instinctive mental conservatism in favour of letting the
    > neurons go wild for a bit, in the hope we see solutions we'd otherwise never
    > have found just trying to rely on what's already in our brain.
    >
    > For the record though, hundreds of video games written in the 80s and 90s
    > *had to be* written by bypassing machine OSes. It was the only way to
    > achieve what the marketplace expected of the games at the time.
    >
    > And last but not least, a few years ago I worked for a company where another
    > team had this major database performance bottleneck (the database wasn't a
    > live thing, though it was huge). They just couldn't get the performance they
    > needed, so some guy (not me), came up with the idea of going behind the
    > facade and hit the data files themselves (no idea if it was Oracle, I wasn't
    > on that team)... and that solved their problem. Instead of struggling with a
    > program that needed days to run to completion, they managed to write a
    > solution that ran in a few hours, if I recall correctly.
    >


    Such a solution has many potential negatives. For example, the data
    format for the database is unlikely to be standard. So the method may
    break upon upgrades. Obviously the method is not portable to other
    databases. The biggest potential negative is that the format is
    unlikely to be documented and you have no idea if you've gotten it right
    -- particularly for all database states and not just the state the DB is
    in when you test it.

    Another big potential drawback is the risk of getting data in an
    inconsistent state. E.g., you read the file for one table, then a big
    transaction is committed and you read the next table.

    If this is the solution to a problem, the question you really need to
    ask is if a database is the right for your application. This is not a
    solution to embark upon lightly.

    In this particular case, there does not seem to be enough of an issue to
    warrant such a radical non-standard solution. A solution using
    standard, portable techniques is available and the OP has not yet
    implemented it to see if it performs adequately.

    That said, I'm glad the technique worked out for your team. And point
    taken about thinking outside the box.

    Ray

    --
    XML is the programmer's duct tape.
     
    Raymond DeCampo, Aug 18, 2005
    #8
  9. mike hengins

    jan V Guest

    > Such a solution has many potential negatives.

    Of course. My colleagues were aware of that.

    > For example, the data format for the database is unlikely to be standard.


    Whatever it was, it wasn't XML ;-)

    > So the method may break upon upgrades.


    The program only had to be run once on the database to achieve some goal
    (totally forgot what that was though)

    > Another big potential drawback is the risk of getting data in an
    > inconsistent state. E.g., you read the file for one table, then a big
    > transaction is committed and you read the next table.


    That database wasn't live. It was static data, and if I recall correctly
    came on an obscene number of CD-Rs (this was before ubiquitous broadband -
    even today I think sending the database by courier might make sense).

    > In this particular case, there does not seem to be enough of an issue to
    > warrant such a radical non-standard solution. A solution using
    > standard, portable techniques is available and the OP has not yet
    > implemented it to see if it performs adequately.


    Totally agree.

    > That said, I'm glad the technique worked out for your team. And point
    > taken about thinking outside the box.


    I'm sure the NASA girl/guy who thought of simply pulling out the inter tile
    filler got some sniggers and eye-rolling looks when it first got suggested,
    but it appears the technique was probably the simplest working solution.
     
    jan V, Aug 18, 2005
    #9
  10. jan V wrote:
    >>Such a solution has many potential negatives.

    >
    >
    > Of course. My colleagues were aware of that.
    >
    >
    >> For example, the data format for the database is unlikely to be standard.

    >
    >
    > Whatever it was, it wasn't XML ;-)
    >
    >
    >> So the method may break upon upgrades.

    >
    >
    > The program only had to be run once on the database to achieve some goal
    > (totally forgot what that was though)
    >
    >
    >>Another big potential drawback is the risk of getting data in an
    >>inconsistent state. E.g., you read the file for one table, then a big
    >>transaction is committed and you read the next table.

    >
    >
    > That database wasn't live. It was static data, and if I recall correctly
    > came on an obscene number of CD-Rs (this was before ubiquitous broadband -
    > even today I think sending the database by courier might make sense).
    >


    My comments weren't designed to cast aspersions on the wisdom of the
    application of the solution in the real life case you mentioned. I
    figured that the particulars of that case were such that the solution
    was a good fit. Your comments above indicate as much.

    >
    >>In this particular case, there does not seem to be enough of an issue to
    >>warrant such a radical non-standard solution. A solution using
    >>standard, portable techniques is available and the OP has not yet
    >>implemented it to see if it performs adequately.

    >
    >
    > Totally agree.
    >
    >
    >>That said, I'm glad the technique worked out for your team. And point
    >>taken about thinking outside the box.

    >
    >
    > I'm sure the NASA girl/guy who thought of simply pulling out the inter tile
    > filler got some sniggers and eye-rolling looks when it first got suggested,
    > but it appears the technique was probably the simplest working solution.
    >


    If the portrayal of the NASA team in the Apollo 13 movie was at all real
    to life, and the culture hasn't changed, I'm sure that they considered
    every idea seriously. There's a great scene in that movie where the
    crew's life depends on the ground team coming up with a solution to an
    air filtration problem using only certain materials. Worth watching if
    you've never seen it.

    Ray

    --
    XML is the programmer's duct tape.
     
    Raymond DeCampo, Aug 18, 2005
    #10
  11. mike hengins

    Roedy Green Guest

    On Wed, 17 Aug 2005 18:04:53 -0500, "Joan" <> wrote
    or quoted :

    >How about if you bypass the operating system too and write a
    >program in binary.


    That was uncalled for. All he is saying is you can do this with an
    oracle utility or an oracle command line query program bypassing Java
    altogether. This would be by far the fastest solution for one-shot
    use.

    He is not suggesting writing a program to analyse the Oracle keyed
    database structure.
     
    Roedy Green, Aug 19, 2005
    #11
  12. mike hengins

    Joan Guest

    "Roedy Green" <> wrote in message
    news:...
    > On Wed, 17 Aug 2005 18:04:53 -0500, "Joan"
    > <> wrote
    > or quoted :
    >
    >>How about if you bypass the operating system too and write a
    >>program in binary.

    >
    > That was uncalled for. All he is saying is you can do this with
    > an
    > oracle utility or an oracle command line query program
    > bypassing Java
    > altogether. This would be by far the fastest solution for
    > one-shot
    > use.
    >
    > He is not suggesting writing a program to analyse the Oracle
    > keyed
    > database structure.


    BUT, the OP says "So i am looking for ways to do it in Java, of
    course."
    So that is not the oracle sql*plus utility then.
     
    Joan, Aug 19, 2005
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. nospawn
    Replies:
    3
    Views:
    472
    Alex Hunsley
    Apr 8, 2006
  2. Harry Zoroc
    Replies:
    1
    Views:
    963
    Gregory Vaughan
    Jul 12, 2004
  3. beethoven_forever
    Replies:
    0
    Views:
    361
    beethoven_forever
    Jul 15, 2005
  4. Victor \Zverok\ Shepelev

    RDOC: several related modules in several C files

    Victor \Zverok\ Shepelev, Mar 6, 2007, in forum: Ruby
    Replies:
    3
    Views:
    188
    Max Lapshin
    Mar 16, 2007
  5. Replies:
    6
    Views:
    175
    Luca Cerone
    Sep 28, 2013
Loading...

Share This Page