I cannot speak with experience of CRs in LONG fields, but they have certainly caused problems in varchar2 fields for us.
FWIW, here the simple SQL I use to both detect presence of ascii(10), and then clear them out: select COLUMN_NAME from TABLE_NAME where instr(COLUMN_NAME,chr(0)) > 0; update TABLE_NAME set COLUMN_NAME = replace(COLUMN_NAME,chr(0),null); In fact, this basic construct lies within more sophisticated nested scripts, which enables me to browse over all varchar2 columns in the database with a length greater than an arbitrary set value. Also used to hunt down ascii(13). (Damn users WILL use M'S Access to load data...). More if required... peter edinburgh > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: 12 December 2002 09:24 > To: Multiple recipients of list ORACLE-L > Subject: sqllldr, long datatype and carriage returns - an ugly > combinatio > > > Listers, > I have got a number of tables with long columns in them. The text is > free-format and contains carriage returns. Some long columns > may be empty > and others will have varying lengths of data (typical > call-centre operator > input scenario) there is no end of record marker that is > common between > columns. > I need to extract each record and load it via sqlldr into > another system. I > could extract using field1||chr(126)||field2||chr(126) etc > 126 being the ~ > character but that does not get around the chr(015) CR issue. > > I understand sqlldr has issues with CR and I have tried > various ways of > resolving this problem. I cannot pre-process the records and > remove the CR > character because of the volume of data and the lack of time involved. > > I have looked at the stream record format option in my > control file and I > fail to get this tro work as it seems to hang the session > every time I call > it. I also suspect that it defines a common end of record > marker and as I do > not have one I would have to massage the data which is not a > route I can go > down. PS This is Tru64 and 8.1.7 but I think it is a general > issue and not > specific to any version of Oracle or o/s > > Furthermore it appears that using stream record format > parameter reduces > performance which is a key requirement for us. > > The questions are > > 1) How have other people managed with carriage returns in > long columns > (I suspect a CR in a varchar2 field will be a problem as well)? > 2) Is the stream record format the way I want to go (I suspect not) > 3) Has anybody any better ideas > > TIA > > John > > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: <[EMAIL PROTECTED] > 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). > ********************************************************************* This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments. http://www.bgs.ac.uk ********************************************************************* -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter 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).
