RE: SQL Loader:How to load data with carriage return embedded

Hello Arul and John

1. From Oracle 8.1.6 (at least) physical record can be up to 64K so you would not need multiple records.
2. If you need to combine multiple records for one logical record read the utilities manual for sqlloader. There is a parameter continueif that is exactly what you need. Yechiel Adar, Mehish Computer Services adary_at_mehish.co.il

> -----Original Message-----
> From: Hallas John [SMTP:John.Hallas_at_btcellnet.net]
> Sent: Mon, February 25, 2002 11:03 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: SQL Loader:How to load data with carriage return
> embedded
>
> Arul,
> Interesting problem. We had a similar situation and used AWK instead after
> a bit of head-scratching.
> The following code worked for us
>
>
> BEGIN{
> FS="`"
> first=0
> }
> {
> if (substr($1,1,3)=="KB0"){ -- to identify each new
> record which starts with those letters
> if (first==1)
> printf("\n")
> printf("%s", $0)
> }
> else
> printf("<br>%s", $0)
> if (first==0)
> first=1
> }
> END{
> printf("\n")
> }
>
> I am still interested in a sqlloader resolution to this problem if anyone
> else has one
>
> John
>
> -----Original Message-----
> From: Arul kumar [mailto:rakumar_at_mahindrabt.com]
> Sent: 23 February 2002 07:33
> To: Multiple recipients of list ORACLE-L
> Subject: SQL Loader:How to load data with carriage return embedded
>
>
> Hi DBAs,
>
> I have a flat file with Varchar2 data spread across lines with
> carriage return..
>
> I have tried using the following but in vain.
> =================================
> LOAD DATA
> INFILE 'test.dat' "str X'0c0a'"
> INTO TABLE arul.test1
> FIELDS TERMINATED BY "~" optionally enclosed by '"'
> trailing nullcols
> (
> no ,
> descr
> )
> =================================
>
> where descr - Varchar2(4000) field..
>
> There is no error message(!)??
>
> Note - With the above spec. able to load only the first record with
> carriage returns embedded. Rest of the records are skipped as bad
> records(!) .
>
> Can anyone suggest the ways of loading it thru SQL Loader?
>
> Thank you.
>
> regards,
> Arul.
> *********************************************************
> Disclaimer
>
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
>
> *********************************************************
> Visit us at http://www.mahindrabt.com
>
>
>
>
>
> =========================================================
> This electronic message contains information from the mmO2 plc Group
> which may be privileged or confidential. The information is intended to be
>
> for the use of the individual(s) or entity named above. If you are not the
>
> intended recipient be aware that any disclosure, copying, distribution or
> use of the contents of this information is prohibited. If you have
> received
> this electronic message in error, please notify us by telephone or email
> (to the numbers or address above) immediately.
> =========================================================
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
  INET: adary_at_mehish.co.il

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).