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

[Hallas John]

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:[EMAIL PROTECTED]]
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.
=========================================================