[Q] sql loader problem while load record more than one line???
We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Q] sql loader problem while load record more than one line??
This is a hack but ... you might want to look into ftp-ing a file to Unix, run a 'tr' or 'sed' to get rid of the EOL character. Thanks, Nikhil -Original Message- Sent: Thursday, January 29, 2004 10:34 AM To: Multiple recipients of list ORACLE-L We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nikhil Khimani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] sql loader problem while load record more than one line???
So, why don't you use migration workbench, when it's available? On 01/29/2004 10:34:27 AM, dba1 mcc wrote: We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Q] sql loader problem while load record more than one line??
Thank you for answer. I did not said clearly. Most records are fine. ONly some records have this problem. re-transfer from PC to UNIX will not fix problem. --- Nikhil Khimani [EMAIL PROTECTED] wrote: This is a hack but ... you might want to look into ftp-ing a file to Unix, run a 'tr' or 'sed' to get rid of the EOL character. Thanks, Nikhil -Original Message- Sent: Thursday, January 29, 2004 10:34 AM To: Multiple recipients of list ORACLE-L We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nikhil Khimani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Q] sql loader problem while load record more than one line???
You may want to add CONTINUEIF clause and try the load again ... infile 'data.asc' CONTINUEIF NEXT (1) != '' into table test ... For complete reference, see this URL: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm#1005518 -- Thanks, Krishna ~~ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ~~ dba1 mcc wrote: We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
