I believe the "copy" command has a 32k limit on longs. steve
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, December 15, 2002 1:48 PM > For that large amount of data SQL*Plus copy > should not take any longer than dumping to > a flat file, ftp large file and sqlldr. > > SQL*Plus copy command will handle the longs. > Plus you will not need additional disk storage > to hold the intermediate files. > > You will need to have a SQL*net connection > between the two databases though. > > Babette Turner-Underwood > > -----Original Message----- > [EMAIL PROTECTED] > Sent: Thursday, December 12, 2002 6:54 AM > To: Multiple recipients of list ORACLE-L > combin > > > 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: Babette Turner-Underwood > 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: Steve Perry 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).
