RE: sqllldr, long datatype and carriage returns - an ugly 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 <ORACLE-L_at_fatcity.com> 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: <John.Hallas_at_vodafone.co.uk
> INET: John.Hallas_at_vodafone.co.uk
>
> 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: ListGuru_at_fatcity.com (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: adar76_at_inter.net.il

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: ListGuru_at_fatcity.com (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: <John.Hallas_at_vodafone.co.uk
  INET: John.Hallas_at_vodafone.co.uk

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: ListGuru_at_fatcity.com (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).