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

From: Hallas John <John.Hallas_at_btcellnet.net>
Date: Mon, 25 Feb 2002 01:03:19 -0800
Message-ID: <F001.004173FF.20020225010319@fatcity.com>

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-----
Sent: 23 February 2002 07:33
To: Multiple recipients of list ORACLE-L

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: Hallas John
  INET: John.Hallas_at_btcellnet.net

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

Received on Mon Feb 25 2002 - 03:03:19 CST