Home » RDBMS Server » Server Utilities » SQL loader question
| SQL loader question [message #457043] |
Thu, 20 May 2010 20:40  |
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!!! 
|
|
|
|
| Re: SQL loader question [message #457044 is a reply to message #457043] |
Thu, 20 May 2010 20:47  |
 |
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 |
 |
Barbara Boehmer
Messages: 9102
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 #457453 is a reply to message #457423] |
Mon, 24 May 2010 09:18 |
|
Barbara Boehmer
Messages: 9102
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.
|
|
|
|
Goto Forum:
Current Time: Sat Jan 11 02:21:55 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
