Have you checked the CONTINUEIF parameter of sqlldr. Since a CR will signal a new record to sqlldr you simply add a record with special character like '!!!!!' after the field during the extract and use:
CONTINUEIF next (1:5) not equal '!!!!!' If you write the multi line field as the last on the field list then the '!!!!!' will be taken as the first field for the next record. If the first field in your list is numeric then the line with '!!!!!' will go to the bad file. If it is character you can combine this with the WHEN clause you can discard the lines that begin with '!!!!'. I did not made a test, just from searching the manual. Yechiel Adar Mehish ----- Original Message ----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, December 12, 2002 1:54 PM > Thanks for that Peter and Yechiel, whilst both posts were useful I don't > think they really answered my questions. > I know I could replace the CR characters but that involves a vast amount of > pre-processing (these are very large tables and there are hundreds of them > and they are in constant use). It may work on a varchar2 field but I am not > sure about long (easy enough to test I know). I know there is also an issue > with PL/SQL having a 32K limit on an variable so I could not assign it > within a pl/sql loop. > > There is also an issue of data integrity, this is part of a database > migration and we need to do it in the fastest manner possible (30 minute > downtime would be nice). The combination of moving o/s, Oracle versions and > having a lot of longs reduces our options quite significantly. > We have looked at a 3rd party ETL tool which does what we want and moves the > data very very quickly (it does not write to disk and therefore does not > need to perform any file i/o processing). However it stops dead when it hits > a CR (it uses sqlldr as it's main means of loading data). > Changing data would not endear us to the users and gain any confidence in > the process > > Thanks anyway > > John > > -----Original Message----- > Sent: 12 December 2002 10:40 > To: Multiple recipients of list ORACLE-L > combinatio > > > You can use > select translate(field1||chr(126)||field2||chr(126),chr(015),' ') > in the program that creates the sequential file. > this will replace every chr(015) with blank. > > Yechiel Adar > Mehish > ----- Original Message ----- > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Thursday, December 12, 2002 11:23 AM > > > > 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). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Yechiel Adar > 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.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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar 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).
