| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sqllldr, long datatype and carriage returns - an ugly combin
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_at_vodaf
one.co.uk> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_fatcity.com 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 <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). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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: mrichard_at_transurban.com.au 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).Received on Thu Dec 12 2002 - 15:29:00 CST
 |
 |