Home » RDBMS Server » Server Utilities » SQL loader question
SQL loader question [message #457043] Thu, 20 May 2010 20:40 Go to next message
jofem
Messages: 2
Registered: May 2010
Junior Member
Hi, I am newbie here, thanks for help!

I have the sql format original data (also can be opened in txt), like:
(76,'abusefilter'),(8029,'abusefilter'),(11061,'abusefilter'),(12013,'abusefilter'),(16980,'abusefilter'),

I created the control file below:

LOAD DATA
INFILE 'c:\oracle\user_groups.txt'
BADFILE 'c:\oracle\emp.bad'
DISCARDFILE 'c:\oracle\emp.dsc'
APPEND INTO TABLE userstatus
(
userid CHAR TERMINATED BY "(" ENCLOSED BY ',',
usergroup CHAR TERMINATED BY "'" ENCLOSED BY ')'
)


and the result of running sql loader is this:

SQL*Loader: Release 11.1.0.6.0 - Production on Wed May 19 21:20:37 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


then, when I checked the table, there is nothing inserted. Help me please!!! Shocked



Re: SQL loader question [message #457044 is a reply to message #457043] Thu, 20 May 2010 20:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Simply put you have posted NOTHING meaningful.

Please realize that we don't have your tables & we don't have your data.
We don't know what you have.
We don't know what you do.
We don't know what you see.
We can't reproduce what you did.
It is really, really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

I believe you have a problem, but I have no advice other than we need MORE details.

why is "userid" defined as CHAR in control file when it appears to be a NUMBER?

post content of 'c:\oracle\emp.bad' file.

include log=capture.log on sqlldr command line & post contents of it

[Updated on: Thu, 20 May 2010 21:14]

Report message to a moderator

Re: SQL loader question [message #457047 is a reply to message #457043] Thu, 20 May 2010 21:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
There are four things that you need to deal with: your record terminator, your enclosure characters, your field terminators, and trimming any extra characters that cannot be identified as enclosure or terminator characters.

Your record terminator is "(," and you can identify that using str.

Your userid is terminated by "," but it is not enclosed by two characters. There is only a leading "(" that can be removed using the ltrim function.

Your usrename is enclosed within single quotes and terminated by the same characters as your record.

Please see the demonstration below, in which I used a directory on my system.

-- c:\oracle11g\user_groups.txt:
(76,'abusefilter'),(8029,'abusefilter'),(11061,'abusefilter'),(12013,'abusefilter'),(16980,'abusefilter'),


-- test.ctl:
LOAD DATA
INFILE 'c:\oracle11g\user_groups.txt' "STR'),'"
BADFILE 'c:\oracle11g\emp.bad'
DISCARDFILE 'c:\oracle11g\emp.dsc'
APPEND INTO TABLE userstatus
(
userid CHAR TERMINATED BY "," "LTRIM (:userid, '(')",
usergroup CHAR TERMINATED BY ")," ENCLOSED BY "'"
)

-- table, load, and results:
SCOTT@orcl_11g> CREATE TABLE userstatus
  2    (userid	   NUMBER,
  3  	usergroup  VARCHAR2 (15))
  4  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> SELECT * FROM userstatus
  2  /

    USERID USERGROUP
---------- ---------------
        76 abusefilter
      8029 abusefilter
     11061 abusefilter
     12013 abusefilter
     16980 abusefilter

5 rows selected.
SCOTT@orcl_11g>





Re: SQL loader question [message #457423 is a reply to message #457047] Mon, 24 May 2010 06:41 Go to previous messageGo to next message
jofem
Messages: 2
Registered: May 2010
Junior Member
Thanks, Barbara,

I have followed your suggestion, it seems work!! Smile

However, if I don't know how many cases in the original file, How can I check if sql loader insert all of them? Thanks.

Re: SQL loader question [message #457453 is a reply to message #457423] Mon, 24 May 2010 09:18 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
jofem wrote on Mon, 24 May 2010 04:41

... if I don't know how many cases in the original file, How can I check if sql loader insert all of them?


Check your logfile and badfile.
Previous Topic: Kill Session Software
Next Topic: IMPDP full tablespace
Goto Forum:
  


Current Time: Sun Sep 19 22:20:45 CDT 2021