Re: problems loading multilingual data using SQL*Loader with External Tables ...

Hello Frank,

"Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message
> Giving the actual reason for rejection, perhaps *with*
> error codes would mke your fingertips wear, wouldn't it?
>

Yep I am looking exactly for this.

> You specify the log file with the definition of the external
> table, and it's not the alert log file.
> ...
> You have NOLOGFILE...
>

True thanks for pointint out ...

I changed the definition of my external table to the following: ...
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE MY_EXT_DIR:'EXT_NLM_ENTITY.BAD'
LOGFILE MY_EXT_DIR:'EXT_NLM_ENTITY.LOG'
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
...

I get now log files but they look pretty useless, I get one of this sample bellow per each record rejected, no error message, no hint, nothing ...

LOG file opened at 04/28/05 16:46:13

Field Definitions for table EXT_NLM_ENTITY   Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform   Rows with all null fields are accepted

  Fields in Data Source:

    UPDATE_FLAG                     CHAR (255)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    EXT_ENTID                       CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    EXT_INDID                       CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    NAME_LAST                       CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    NAME_FIRST                      CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    TITLE                           CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    PREFIX                          CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    SUFFIX                          CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    ENTRY_TYPE                      CHAR (255)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    POSITIONS                       CHAR (800)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    CATEGORY                        CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    DATE_OF_BIRTH                   CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    PLACE_OF_BIRTH                  CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    COUNTRY                         CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    SANCTIONS_PROGRAM               CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    EXPIRATION_DATE                 CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    PICTURE                         CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    DECEASED_DATE                   CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    WEB_LINK                        CHAR (2000)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    VESSEL_CALL_SIGN                CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    VESSEL_TYPE                     CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    VESSEL_TONNAGE                  CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    VESSEL_TONNAGE_GROSS            CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    VESSEL_FLAG                     CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    VESSEL_OWNER                    CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    REMARKS                         CHAR (4000)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    LANGUAGE_ISO_CODE               CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    ENTERED_DATE                    CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    ENTERED_BY                      CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    LAST_UPDATE                     CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    LAST_UPDATE_BY                  CHAR (500)
      Terminated by "|"
      Trim whitespace same as SQL Loader

********************************

> And.. out of curiousity: does the BADFILE work the way
> you defined it?
>

Yes I get all bad records ... unless I specify LOGFILE then I do not get .BAD files any longer. Probably this is what is happening to you as well? when LOGFILE is specified then SQL*Loader does not generate .BAD files, really bizarre behavior ...

I tried changing the NLS_LANG to AMERICAN_AMERICA.UTF8 but does not work either ... this whole multilanguage is very very frustrating.

Thanks for your help,

Best Regards,
Giovanni Received on Thu Apr 28 2005 - 09:56:44 CDT