Re: sqlload question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 27 Oct 1998 15:05:16 GMT
Message-ID: <3636dfd9.4096310@192.86.155.100>

A copy of this was sent to Ajay Damani <damani_at_uts.cc.utexas.edu> (if that email address didn't require changing) On Mon, 26 Oct 1998 20:44:05 -0600, you wrote:

>
>Hi!
>
>I have the following syntax in my control file.
>
>LOAD DATA
>INFILE "mydatafile"
>DISCARDFILE "mydatafile.dis"
>DISCARDMAX 2000
>REPLACE
>INTO TABLE mytable FIELDS TERMINATED BY X'13' -- X'13' is ascii for TAB
>(
> field1 char,
> field2 char,
> field3 char
>)
>

No, x'13' is hex for 19. A tab is 9.

Try:

LOAD DATA
INFILE *
REPLACE
INTO TABLE mytable FIELDS TERMINATED BY X'09' (
  field1 char,
  field2 char,
  field3 char
)
begindata
abc 01 345 789 cba
def 1 56789 fed
ghi 0 9 ihg

and it will result in:

SQL> desc mytable;

 Name                            Null?    Type
 ------------------------------- -------- ----
 FIELD1                                   VARCHAR2(25)
 FIELD2                                   VARCHAR2(25)
 FIELD3                                   VARCHAR2(25)

SQL> select * from mytable;

FIELD1                    FIELD2                    FIELD3
------------------------- ------------------------- -------------------------
abc                       01                        345
def                       1                         56789
ghi                       0                         9

>begindata
>
>abc 01 345 789 cba
>def 1 56789 fed
>ghi 0 9 ihg
>
>
>where field1 is atmost 3 characters, field2 is always 10 characters
>(including most ascii characters i.e spaces,:;"'~!@#$%^|&*()_+ 0-9a-zA-Z)
>and field 3 can be 25 characters long. Now the problem is that since
>almost all ascii characters are allowed for field2, my separator is a TAB
>(which I am guaranteed will never appear in field2 or any field for that
>matter).
>
>My question is this:
>
> How can I write the instruction for the control file so that sqlload
> will do the right thing i.e put 'abc' into field1, '01 345 789' into
> field2, and 'cba' into field3 oy mytable?
>
>I have tried using PRESERVE BLANKS, TRAILING NULLCOLS but with no luck.
>The error that I get is the following:
>
> Record 1: Rejected - Error on table MYTABLE, column FIELD1.
> Column not found before end of logical record (use TRAILING NULLCOLS)
> ...
>
>If I use the following,
>
>LOAD DATA
>INFILE "mydatafile"
>DISCARDFILE "mydatafile.dis"
>DISCARDMAX 2000
>REPLACE
>PRESERVE BLANKS
>INTO TABLE mytable FIELDS TERMINATED BY X'13' TRAILING NULLCOLS
>(
>
>... rest is the same..
>
>)
>
>Record 1: Rejected - Error on table MYTABLE.
>ORA-01401: inserted value too large for column
>
>Problem with using FIELDS TERMINATED BY WHITESPACE is that sqlload does
>not distinguish between a single space, a tab character etc. Regardless,
>since field2 contains a space, this will fail.
>
>Any suggestions are appreciated. Thanks
>
>
>-Ajay
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Oct 27 1998 - 09:05:16 CST