SQL*Loader - what happened?

Monica Roman

unread,

Apr 7, 2003, 11:56:02 AM4/7/03

to

Hello,
I am brand new to Oracle...took some classes but none taught me how to
use SQL*Loader but I've been reading everything that I can including
many (MANY) postings here and was able to understand what to do. So I
ran my .ctl file and it seemed that it worked, except that when I
Select From in SQL*Plus nothing shows. Please help? I am using Oracle
Personal Server 9i Release 9.2.0.2.0

Here is what I am working with:

I issued this command:
sqlldr scott/tiger control=c:\docs\ctlfile log=file_oralog
data=c:\docs\file.txt

ctlfile.ctl looks like this:

Load Data
INFILE 'C:\DOCS\file.txt'
APPEND INTO TABLE tablex
FIELDS TERMINATED BY ','
(TBLDATE, TBLPRIORITY, TBLCODE, TBLTEXT)

the file.txt looks like this:

2003-04-07 08:59:15,Priority6.Save,44.55.66.00,%NUMBER1: lots of text:
lots of more text: and more text "text"
2003-04-07 08:59:16,Priority5.Run,88.99.76.9,%NUMBER7: lots of text:
lots of more text: and more text "text"/ and a little more text
2003-04-07 08:59:17,Priority4.Delete,77.79.43.05,%NUMBER2: lots of
text: lots of more text: and more text22

etc.

The command prompt message said:

"commit point reached - logical record count 64"

It put 64 lines in a .bad file and ignored more than 1000 lines in the
.txt file.

Where did I go wrong?

Thank you much for your help!!

Monica Roman

nobody

unread,

Apr 7, 2003, 6:24:00 PM4/7/03

to

look at the .log file it will tell you why the records were rejected.

It probably stopped at 50, as this is the default errors I believe.

"Monica Roman" <monic...@yahoo.com> wrote in message
news:9eb77af5.03040...@posting.google.com...

Joel Garry

unread,

Apr 7, 2003, 7:34:35 PM4/7/03

to

monic...@yahoo.com (Monica Roman) wrote in message news:<9eb77af5.03040...@posting.google.com>...

> Hello,
> I am brand new to Oracle...took some classes but none taught me how to
> use SQL*Loader but I've been reading everything that I can including
> many (MANY) postings here and was able to understand what to do. So I
> ran my .ctl file and it seemed that it worked, except that when I
> Select From in SQL*Plus nothing shows. Please help? I am using Oracle
> Personal Server 9i Release 9.2.0.2.0

Look for a .log file that says what all is wrong. It will say whether
you have bad data errors or all the constraints failed. If you don't
have a .log file, specify one when you run the command.

jg
--
@home.com is bogus.
0 Rows not loaded because all WHEN clauses were failed.

Monica Roman

unread,

Apr 8, 2003, 9:11:49 AM4/8/03

to

>
> Look for a .log file that says what all is wrong. It will say whether
> you have bad data errors or all the constraints failed. If you don't
> have a .log file, specify one when you run the command.
>
> jg

Hi,
I looked at the .log and it says that:

Record 1: Rejected - Error on table TABLEX, column TBLDATE.
ORA-01861: literal does not match format string

When I created the table, I said: CREATE TABLE Tablex (
TblDate date, ...etc);

The log file said:

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
TBLDATE FIRST * , CHARACTER
TBLPRIORITY NEXT * , CHARACTER
etc.

So I think it is trying to bring in the date as character. How do I
make it load as a date in this format? 2003-04-07 08:59:15

Thanks so much!

Monica Roman

unread,

Apr 8, 2003, 10:17:20 AM4/8/03

to

Following-up on the message I sent earlier.
I realized that I need to format the date field, so I loaded:

Load data
...
Fields terminated by ','
(TBLDATE "DD-MM-YYYY HH24:MI:SS",
TBLPRIORITY... etc.)

But I got an error: SQL*Loader-291: Invalid bind variable MI in SQL
string for column TBLDATE.

I couldn't find references but one, unanswered, on the group. How is
this resolved?

Thanks!

Monica

Eric Manseau

unread,

Apr 8, 2003, 12:54:07 PM4/8/03

to

Hi Monica

This is an example from a control file that I actually use.

START_DATE date « DD-MM-YYYY HH24:MI:SS » ,

REMARKS_TXT char(2000),

Good Luck!

"Monica Roman" <monic...@yahoo.com> wrote in message
news:9eb77af5.03040...@posting.google.com...
> >

Eric Manseau

unread,

Apr 8, 2003, 12:55:59 PM4/8/03

to

The cut and past changed the " format to «

START_DATE date "DD-MM-YYYY HH24:MI:SS"

"Eric Manseau" <eman...@cae.com> wrote in message
news:b6uueb$5eb$1...@dns3.cae.ca...

TurkBear

unread,

Apr 8, 2003, 3:58:16 PM4/8/03

to

monic...@yahoo.com (Monica Roman) wrote:

Try to specify that date in your .ctl files like this:

TBLDATE DATE 'dd-mm-yyyy hh24:MI:SS'

(Note single quotes )

With a date field, you should also handle NULLS with a NULLIF statement ...

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

stef

unread,

Apr 8, 2003, 5:43:48 PM4/8/03

to

monic...@yahoo.com (Monica Roman) wrote in message news:<9eb77af5.03040...@posting.google.com>...

Hello,

I recently used this control file, may be you can use it as well.

LOAD DATA
INFILE 'out.EXTRBPO.1.key.txt'
BADFILE 'loader.bad.extrbpo.key.txt'
DISCARDFILE 'loader.discard.extrbpo.key.txt'

APPEND INTO TABLE KEYSGR1
FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"'

(startsearchdate DATE 'yyyy/mm/dd',
applid CHAR(20),
fileid CHAR(5),
keyid CHAR(2),
searchvalue CHAR(30),
pageid CHAR(8))

Monica Roman

unread,

Apr 9, 2003, 8:21:23 AM4/9/03

to

Thank you so much everyone, I really appreciated the step by step
direction and I tried all the suggestions and finally got all the data
loaded using

...TBLDATE TIMESTAMP "YYYY-MM-DD HH24:MI:SS", TBL....

I learned about the timestamp in the Utilities reference book.
(If someone else has the same problem in the future.)

I now have to load this kind of data every hour forever and ever from
.txt files that are named according to the date and time. So I'll have
to write some more code. I'll go do some more research and I'm sure
I'll be back!

Thanks!!!!!!