Re: Problems loading data into the database using SQL Loader.
From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Tue, 23 Jun 2009 23:55:30 -0700 (PDT)
Message-ID: <914ccc85-9591-400a-8fb0-5611f0b24596_at_r10g2000yqa.googlegroups.com>
On Jun 23, 11:38 pm, trub3101 <trub3..._at_sky.com> wrote:
> On 23 June, 20:30, trub3101 <trub3..._at_sky.com> wrote:
>
>
>
> > On 23 June, 19:53, ddf <orat..._at_msn.com> wrote:
>
> > > On Jun 23, 1:38 pm, trub3101 <trub3..._at_sky.com> wrote:
>
> > > > On 23 June, 17:53, ddf <orat..._at_msn.com> wrote:
>
> > > > > On Jun 23, 11:42 am, trub3101 <trub3..._at_sky.com> wrote:
>
> > > > > > Hi all,
>
> > > > > > Database
> > > > > > Oracle 9.2.0.8
> > > > > > NLS_LANG=AMERICAN_AMERICA.UTF8
> > > > > > NLS_CHARACTERSET UTF8
> > > > > > NLS_NCHAR_CHARACTERSET AL16UTF16
>
> > > > > > Server
> > > > > > Red Hat Linux 2.6.9-67.ELsmp
> > > > > > LANG=en_US.UTF-8
>
> > > > > > I cannot figure this one out. When I try to populate a table using SQl
> > > > > > Loader I am getting corrupt characters in the table e.g. Alien³
> > > > > > appears in the table as Alien¿
>
> > > > > > I have even set the CHARACTERSET to UTF8 in the control file for good
> > > > > > measure!
>
> > > > > > Can anyone throw some light on this one?
>
> > > > > > Thanks in advance for your replies.
>
> > > > > > TB3101
>
> > > > > I'll take a guess that you're doing this from a client machine. What
> > > > > is the NLS_LANG on the machine where you're running the loader?
>
> > > > > David Fitzjarrell- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > Thanks for reply David,
>
> > > > I am actually logged onto the server and running the sql loader
> > > > process. The NLS_LANG is set to AMERICAN_AMERICA.UTF8
>
> > > > Cheers,
>
> > > > TB3101- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > We'll need to see your control file and data file.
>
> > > David Fitzjarrell- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi David,
>
> > Here is the control file:
>
> > LOAD DATA
> > CHARACTERSET UTF8
> > INFILE '/opt/ora/oracle/oradata/data/xxxx.dat' "str '\n'"
> > BADFILE '/opt/ora/oracle/oradata/xxxx.bad'
> > DISCARDFILE '/opt/ora/oracle/oradata/xxxx.dis'
> > TRUNCATE
> > INTO TABLE film_film
> > TRAILING NULLCOLS
>
> > (
> > START_TIME DATE "DD/MM/YYYY,HH24:MI:SS,"
> > TERMINATED BY '^',
> > CHANNEL_ID CHAR(300) TERMINATED BY '^',
> > REGION_CODE CHAR(10) TERMINATED BY '^',
> > TITLE CHAR(200) TERMINATED BY '^',
> > PERFORMERS CHAR(4000) TERMINATED BY '^',
> > DIRECTOR CHAR(150) TERMINATED BY '^',
> > FILM_RATING INTEGER EXTERNAL TERMINATED BY
> > '^',
> > YEAR CHAR(15) TERMINATED BY '^',
> > DESCRIPTION CHAR(4000) TERMINATED BY '^',
> > PREMIERE CHAR(1) TERMINATED BY '^',
> > TV_MOVIE CHAR(1) TERMINATED BY '^',
> > FILM_CERTIFICATE CHAR(10) TERMINATED BY '^',
> > WARNING CHAR(200) TERMINATED BY '^',
> > REVIEW_AUTHOR CHAR(50)
> > )
>
> > and here is the data file:
>
> > 30/06/2009 19:00:00^252^^Alien³^Warrant Officer Ripley*Sigourney
> > Weaver|Dillon*Charles S Dutton|Clemens*Charles Dance|Golic*Paul McGann|
> > Superintendent Andrews*Brian Glover|Aaron*Ralph Brown (2)|Morse*Danny
> > Webb|Rains*Christopher John Fields|Junior*Holt McCallany|Bishop
> > II*Lance Henriksen|Murphy*Christopher Fairbank|David *Pete
> > Postlethwaite|William*Clive Mantle^David Fincher^3^1992^Given that the
> > first two films stand up as sci-fi classics in their own right, Se7en
> > director David Fincher, in his feature film debut, had a virtually
> > impossible act to follow with this second sequel. He makes a
> > surprisingly good fist of it, developing the maternal themes of first
> > sequel Aliens and providing an exhilarating final showdown. Sigourney
> > Weaver returns as Ripley, who this time crash-lands on a prison colony
> > where another lethal alien is let loose. A familiar cast of Brits
> > (Charles Dance, Paul McGann, Brian Glover) provides the alien food
> > and, while it isn't in the same class as the first two films, this
> > provides a satisfactory entry in the series. Still, it would have been
> > interesting to see what second-choice director Vincent Ward (of The
> > Navigator: a Medieval Odyssey fame) would have made of it -
> > apparently, he was brought in when Renny Harlin left after script
> > disagreements, but was himself replaced when it emerged that his
> > version of the movie would be set in a monastery and the alien itself
> > wouldn't be appearing. ^N^N^18^Contains violence, swearing and nudity.
> > ^JF
>
> > Thanks again for your help,
>
> > TB3101- Hide quoted text -
>
> > - Show quoted text -
>
> Hi David,
>
> When I run 'cat -e' on the data file the output for the title is
> different!
>
> 30/06/2009 19:00:00^252^^AlienM-3^Warrant Officer Ripley*Sigourney
> Weaver|Dillon*Charles S Dutton|Clemens*Charles Dance|Golic*Paul McGann|
> Superintendent Andrews*Brian Glover|Aaron*Ralph Brown (2)|Morse*Danny
> Webb|Rains*Christopher John Fields|Junior*Holt McCallany|Bishop
> II*Lance Henriksen|Murphy*Christopher Fairbank|David *Pete
> Postlethwaite|William*Clive Mantle^David Fincher^3^1992^Given that the
> first two films stand up as sci-fi classics in their own right, Se7en
> director David Fincher, in his feature film debut, had a virtually
> impossible act to follow with this second sequel. He makes a
> surprisingly good fist of it, developing the maternal themes of first
> sequel Aliens and providing an exhilarating final showdown. Sigourney
> Weaver returns as Ripley, who this time crash-lands on a prison colony
> where another lethal alien is let loose. A familiar cast of Brits
> (Charles Dance, Paul McGann, Brian Glover) provides the alien food
> and, while it isn't in the same class as the first two films, this
> provides a satisfactory entry in the series. Still, it would have been
> interesting to see what second-choice director Vincent Ward (of The
> Navigator: a Medieval Odyssey fame) would have made of it -
> apparently, he was brought in when Renny Harlin left after script
> disagreements, but was himself replaced when it emerged that his
> version of the movie would be set in a monastery and the alien itself
> wouldn't be appearing. ^N^N^18^Contains violence, swearing and nudity.
> ^JF$
>
> Cheers,
>
> TB3101
Date: Tue, 23 Jun 2009 23:55:30 -0700 (PDT)
Message-ID: <914ccc85-9591-400a-8fb0-5611f0b24596_at_r10g2000yqa.googlegroups.com>
On Jun 23, 11:38 pm, trub3101 <trub3..._at_sky.com> wrote:
> On 23 June, 20:30, trub3101 <trub3..._at_sky.com> wrote:
>
>
>
> > On 23 June, 19:53, ddf <orat..._at_msn.com> wrote:
>
> > > On Jun 23, 1:38 pm, trub3101 <trub3..._at_sky.com> wrote:
>
> > > > On 23 June, 17:53, ddf <orat..._at_msn.com> wrote:
>
> > > > > On Jun 23, 11:42 am, trub3101 <trub3..._at_sky.com> wrote:
>
> > > > > > Hi all,
>
> > > > > > Database
> > > > > > Oracle 9.2.0.8
> > > > > > NLS_LANG=AMERICAN_AMERICA.UTF8
> > > > > > NLS_CHARACTERSET UTF8
> > > > > > NLS_NCHAR_CHARACTERSET AL16UTF16
>
> > > > > > Server
> > > > > > Red Hat Linux 2.6.9-67.ELsmp
> > > > > > LANG=en_US.UTF-8
>
> > > > > > I cannot figure this one out. When I try to populate a table using SQl
> > > > > > Loader I am getting corrupt characters in the table e.g. Alien³
> > > > > > appears in the table as Alien¿
>
> > > > > > I have even set the CHARACTERSET to UTF8 in the control file for good
> > > > > > measure!
>
> > > > > > Can anyone throw some light on this one?
>
> > > > > > Thanks in advance for your replies.
>
> > > > > > TB3101
>
> > > > > I'll take a guess that you're doing this from a client machine. What
> > > > > is the NLS_LANG on the machine where you're running the loader?
>
> > > > > David Fitzjarrell- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > Thanks for reply David,
>
> > > > I am actually logged onto the server and running the sql loader
> > > > process. The NLS_LANG is set to AMERICAN_AMERICA.UTF8
>
> > > > Cheers,
>
> > > > TB3101- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > We'll need to see your control file and data file.
>
> > > David Fitzjarrell- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi David,
>
> > Here is the control file:
>
> > LOAD DATA
> > CHARACTERSET UTF8
> > INFILE '/opt/ora/oracle/oradata/data/xxxx.dat' "str '\n'"
> > BADFILE '/opt/ora/oracle/oradata/xxxx.bad'
> > DISCARDFILE '/opt/ora/oracle/oradata/xxxx.dis'
> > TRUNCATE
> > INTO TABLE film_film
> > TRAILING NULLCOLS
>
> > (
> > START_TIME DATE "DD/MM/YYYY,HH24:MI:SS,"
> > TERMINATED BY '^',
> > CHANNEL_ID CHAR(300) TERMINATED BY '^',
> > REGION_CODE CHAR(10) TERMINATED BY '^',
> > TITLE CHAR(200) TERMINATED BY '^',
> > PERFORMERS CHAR(4000) TERMINATED BY '^',
> > DIRECTOR CHAR(150) TERMINATED BY '^',
> > FILM_RATING INTEGER EXTERNAL TERMINATED BY
> > '^',
> > YEAR CHAR(15) TERMINATED BY '^',
> > DESCRIPTION CHAR(4000) TERMINATED BY '^',
> > PREMIERE CHAR(1) TERMINATED BY '^',
> > TV_MOVIE CHAR(1) TERMINATED BY '^',
> > FILM_CERTIFICATE CHAR(10) TERMINATED BY '^',
> > WARNING CHAR(200) TERMINATED BY '^',
> > REVIEW_AUTHOR CHAR(50)
> > )
>
> > and here is the data file:
>
> > 30/06/2009 19:00:00^252^^Alien³^Warrant Officer Ripley*Sigourney
> > Weaver|Dillon*Charles S Dutton|Clemens*Charles Dance|Golic*Paul McGann|
> > Superintendent Andrews*Brian Glover|Aaron*Ralph Brown (2)|Morse*Danny
> > Webb|Rains*Christopher John Fields|Junior*Holt McCallany|Bishop
> > II*Lance Henriksen|Murphy*Christopher Fairbank|David *Pete
> > Postlethwaite|William*Clive Mantle^David Fincher^3^1992^Given that the
> > first two films stand up as sci-fi classics in their own right, Se7en
> > director David Fincher, in his feature film debut, had a virtually
> > impossible act to follow with this second sequel. He makes a
> > surprisingly good fist of it, developing the maternal themes of first
> > sequel Aliens and providing an exhilarating final showdown. Sigourney
> > Weaver returns as Ripley, who this time crash-lands on a prison colony
> > where another lethal alien is let loose. A familiar cast of Brits
> > (Charles Dance, Paul McGann, Brian Glover) provides the alien food
> > and, while it isn't in the same class as the first two films, this
> > provides a satisfactory entry in the series. Still, it would have been
> > interesting to see what second-choice director Vincent Ward (of The
> > Navigator: a Medieval Odyssey fame) would have made of it -
> > apparently, he was brought in when Renny Harlin left after script
> > disagreements, but was himself replaced when it emerged that his
> > version of the movie would be set in a monastery and the alien itself
> > wouldn't be appearing. ^N^N^18^Contains violence, swearing and nudity.
> > ^JF
>
> > Thanks again for your help,
>
> > TB3101- Hide quoted text -
>
> > - Show quoted text -
>
> Hi David,
>
> When I run 'cat -e' on the data file the output for the title is
> different!
>
> 30/06/2009 19:00:00^252^^AlienM-3^Warrant Officer Ripley*Sigourney
> Weaver|Dillon*Charles S Dutton|Clemens*Charles Dance|Golic*Paul McGann|
> Superintendent Andrews*Brian Glover|Aaron*Ralph Brown (2)|Morse*Danny
> Webb|Rains*Christopher John Fields|Junior*Holt McCallany|Bishop
> II*Lance Henriksen|Murphy*Christopher Fairbank|David *Pete
> Postlethwaite|William*Clive Mantle^David Fincher^3^1992^Given that the
> first two films stand up as sci-fi classics in their own right, Se7en
> director David Fincher, in his feature film debut, had a virtually
> impossible act to follow with this second sequel. He makes a
> surprisingly good fist of it, developing the maternal themes of first
> sequel Aliens and providing an exhilarating final showdown. Sigourney
> Weaver returns as Ripley, who this time crash-lands on a prison colony
> where another lethal alien is let loose. A familiar cast of Brits
> (Charles Dance, Paul McGann, Brian Glover) provides the alien food
> and, while it isn't in the same class as the first two films, this
> provides a satisfactory entry in the series. Still, it would have been
> interesting to see what second-choice director Vincent Ward (of The
> Navigator: a Medieval Odyssey fame) would have made of it -
> apparently, he was brought in when Renny Harlin left after script
> disagreements, but was himself replaced when it emerged that his
> version of the movie would be set in a monastery and the alien itself
> wouldn't be appearing. ^N^N^18^Contains violence, swearing and nudity.
> ^JF$
>
> Cheers,
>
> TB3101
Can you also post the SQL*loader log file? Do you see something like this at the very beginning of the log:
Character Set UTF8 specified for all input.
First primary datafile xxxx.dat has a
utf8 byte order mark in it.
I mean, you gotta make sure the file is really in UTF-8 so that SQL*Loader could process it properly.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Wed Jun 24 2009 - 01:55:30 CDT