Re: sqllldr, long datatype and carriage returns - an ugly combin

I believe the "copy" command has a 32k limit on longs.

steve

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> 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-----
> John.Hallas_at_vodafone.co.uk
> 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 <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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Babette Turner-Underwood
> INET: babette_at_rogers.com
>
> 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: Steve Perry
  INET: sperry_at_sprynet.com

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