Re: Help with export and import into Oracle
machiel.richards wrote: Good day guys [snip] . Each item in the text field is added in the field by entering the country name then pressing enter and then entering the next, etc . When exporting the data to a file (even when enclosing each field within quotes) it still writes the control characters causing each item to be read as a different line and thus the import into Oracle fails. Any idea on how we can resolve this as the process needs to be cronned to run on a weekly basis and thus we need to get this process resolved. You haven't described what process you're using to read the file for the Oracle import - all of Oracle's interfaces (oci, SQL, PL/SQL, load utilities like SQL*Loader and imp/impdp, external tables, etc.) can handle multi-line records like this. Given you're dumping to a file, it's mostly likely you're using SQL*Loader (i.e. sqlldr). The INFILE clause for the control file includes an os_file_proc_clause which let's you set the record delimiter, and override the default end of line behaviour. See http://www.orafaq.com/wiki/SQL*Loader_FAQ#How_does_one_load_records_with_multi-line_fields.3F for an example. If you're not using SQL*Loader, then more info would be required. Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Help with export and import into Oracle
It seems that one of the tables we need to export and import contains rows which is used for dropdown menus. This has the following effect: . Each item in the text field is added in the field by entering the country name then pressing enter and then entering the next, etc . When exporting the data to a file (even when enclosing each field within quotes) it still writes the control characters causing each item to be read as a different line and thus the import into Oracle fails. [JS] Leaving aside my opinion that this is a bad way to store options for a dropdown menu, I think you will need to use the REPLACE() function liberally. In fact, you might consider using CONCAT_WS() to cram all of your fields into one and then using REPLACE() on the result. I don't know how that will work with your data, but I've done it before when otherwise stumped. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with export and import into Oracle
Good day guys I previously requested information regarding the exporting of data which needs to be imported into oracle. We are however still struggling with the data though and maybe someone can give me some ideas... It seems that one of the tables we need to export and import contains rows which is used for dropdown menus. This has the following effect: . Each item in the text field is added in the field by entering the country name then pressing enter and then entering the next, etc . When exporting the data to a file (even when enclosing each field within quotes) it still writes the control characters causing each item to be read as a different line and thus the import into Oracle fails. Any idea on how we can resolve this as the process needs to be cronned to run on a weekly basis and thus we need to get this process resolved. Your assistance is appreciated. Regards Machiel