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

M

mike hengins

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.
 
M

Malte

mike said:
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.
 
J

jan V

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 ;-) ]
 
J

Joan

jan V said:
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.
 
M

mike hengins

Joan said:
jan V said:
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
 
J

jan V

[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.
 
I

Ingo R. Homann

Hi,

mike said:
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
 
R

Raymond DeCampo

jan said:
[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
 
J

jan V

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.
 
R

Raymond DeCampo

jan said:
Of course. My colleagues were aware of that.




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




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




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.
Totally agree.




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
 
R

Roedy Green

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.
 
J

Joan

Roedy Green said:
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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top