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).
