Re: Problems loading data into the database using SQL Loader.
Date: Tue, 23 Jun 2009 12:38:12 -0700 (PDT)
Message-ID: <1a38865c-f4d4-410e-a0fb-fd8bf5c318a9_at_t2g2000yqt.googlegroups.com>
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 Received on Tue Jun 23 2009 - 14:38:12 CDT