Help with export and import into Oracle

------=_NextPart_000_0032_01CA936A.CBD152F0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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








------=_NextPart_000_0032_01CA936A.CBD152F0--

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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.org