John,
I'm not sure what kind of timeframe you are looking at but you could
consider Move for Servers. I do not know if it handles this specific
problem but my experience with the product has been very good (certainly
handles long raws, etc) and their support is very forthcoming. I believe
the product is quite cheap and could also be useful to you in other areas.
We use it frequently to create relationally intact subsets of data for
testing and it's both quick and easy.
Regards,
Mark.
PS: No, I don't hold stock in Princeton Softech (the vendor), but perhaps
I should.
<John.Hallas@vodaf
one.co.uk> To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: sqllldr, long datatype
and carriage returns - an ugly
combin
12/12/2002 22:54
Please respond to
ORACLE-L
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).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Richard
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).
