Stephen,
That is interesting... The DBA and Oracle programmers are really raising an issue on the difficulties of this extraction. We can use a ~ delimited file but were told to give "our" standards. To which now they are saying is out of time scope.
Thank you for this information since it gives us some help understanding their side of the issue.
A view would be the best but that is a REALLY bad idea... At least in their opinion.
R.
On Wed, 23 Mar 2005, Stephen Andert wrote:
Actually, exporting data from an Oracle database is one of the weak spots of Oracle. Oracle makes it easy to get data in, but hard to get it back out. There are ways to do it, but nothing easy that an Oracle DBA will have if they only read the Oracle docs. Spooling an output file can get tedius if there are a lot of columns in the table or many tables.
There are a number of freeware and open source solutions as well as third party solutions, but in most cases, a good DBA should be able to develop some dynamic scripts to get the data out in the format you need. Features like the use of LONG datatypes may throw a monkey wrench in the works.
Good luck. Stephen (Sr Oracle DBA by day - Jr MySQL DBA by night)
On Tue, 22 Mar 2005 10:48:53 -0500, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:Z X C V <[EMAIL PROTECTED]> wrote on 03/21/2005 08:32:24 PM:
Hello,
Are there any good rfcs or other documents that would assist in the discussion I'm having with our organization's Oracle programmers in regards the following:
Oracle DB -> MySQL
They recommend:
~ col~col ~col~ col'",~col
NOTE::I placed the '" in for a reason.
I am looking for:
"col","col","col","col\'\",","col"
Of course the "non-Oracle" system is being tasked as the problem in this
case for the "abnormal" data request.
Any help on this would be appreciated.
Rob.
Unfortunately I am not sure any document like you seek actually exists (at least not in any modern literature). There are many ways to move data from memory to media and the format of the data on the media is usually determined by a number of factors (type of data, type of media, is the data intended for human or machine use, etc.). So I am not sure there ARE any standards for database export formatting (I did a quick Google and didn't find any in the top 50 responses for the search: data export standard format CSV (see notes below))...
To be honest, the mysqlimport and LOAD DATA INFILE facilities should be flexible enough to read the tilde-delimited format that your Oracle guys want to give you. If not, you should be able to ask them to either create a tab-delimited or comma-delimited dump (both formats are so "ancient" as to be /de facto/ standards and should be something that Oracle can create without any difficulty). What you were asking for is a comma-delimited format but you need some extra escaping to make it more MySQL compatible. I think that the extra escaping is what's throwing a monkey-wrench into the export process.
One viable option is for you to write a quick converter that re-parses their tilde-delimited format into something properly escaped for MySQL. IF you write it correctly, your format converter should be able to process several megabytes of dumped data per second. Yes, it's a pain in the neck to take the extra conversion step but sometimes we DBA's have to assemble our own lemons before we get to make the lemonade.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Notes: a non-normative description of the CSV format: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm An example data export screen, a "custom delimiter" of a tilde would reproduce the format you are being offered: http://www.jinfonet.com/manualpro/userguide/expt_txt.htm Some export/import formats of various ecological data. Notice how many there are: http://gce-lter.marsci.uga.edu/lter/data/formats.htm A proposed format for "Thoughts" exchange: http://www.ms.lt/importexport.html
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
