Load data from Oracle to Teradata with correct character set

Tools & Utilities
Enthusiast

Load data from Oracle to Teradata with correct character set

Hello guys, I am new here in this forum and I would appreciate your help.

I had a problem to load some data from an Oracle database to Teradata using Data Direct tool. All data were loaded correctly to target, but some special brazilian characters (like "Ç") came to Teradata as a "?" character.

I tryed to put "USING CHARACTER SET UTF8" in the beginning of the script file, I tryed to put "-e UTF8" in the command line. Nothing works as I expect.

I Looking foward to put some information at OPERATOR TYPE ODBC to specify which character set the driver will use, but I didn't find this parameter.

Does anyone can help me?

Thanks.

/* SCRIPT FILE */

USING CHARACTER SET UTF8

DEFINE JOB "ODILoadaa1a1e33-521c-4b7b-95c8-c829ecee6368" 

DESCRIPTION 'ODI step name: prCARGA_TPT2 ODI step #:12 from session #aa1a1e33-521c-4b7b-95c8-c829ecee6368'

(

DEFINE SCHEMA Oracle_DataSource_Schema DESCRIPTION 'Source table for PESSOA_JURIDICA'

  (

  OID_PESSOA NUMBER(22,0),OID_PES_MATRIZ NUMBER(22,0),NOM_RAZAO_SOCIAL VARCHAR(115),NOM_FANTASIA VARCHAR(55),DAT_CONSTITUICAO VARCHAR(75),OID_NAT_JURIDICA NUMBER(22,0),QTD_FILIAL NUMBER(5,0),QTD_CLIENTE NUMBER(6,0),NUM_REGISTRO VARCHAR(16),DAT_REGISTRO VARCHAR(75),DAT_ALT_CONTRATUAL VARCHAR(75),FLG_MATRIZ VARCHAR(1),OID_CRZ_CAL_SOCIAL NUMBER(22,0),OID_NAT_EMPRESA NUMBER(22,0),OID_CAT_BACEN NUMBER(22,0),TPO_REGIME_TRIBUTARIO NUMBER(1,0),FLG_SUBESTAB_PODER VARCHAR(1),OID_TPO_COOPERATIVA NUMBER(22,0),VLR_FATURAMENTO NUMBER(14,2),FLG_AUDITORIA VARCHAR(1),FLG_AREA_ATUACAO VARCHAR(1),NUM_ARQUIVO VARCHAR(16),DAT_ARQUIVO VARCHAR(75),COD_USU_ATUALIZACAO NUMBER(5,0),DAT_ATUALIZACAO VARCHAR(75),NUM_CNPJ VARCHAR(14),QTD_FUNCIONARIO NUMBER(7,0),ANO_FATURAMENTO NUMBER(4,0)

  );

  DEFINE OPERATOR ODBC_Operator

  DESCRIPTION 'TPT ODBC Operator'

  TYPE ODBC

  SCHEMA Oracle_DataSource_Schema

  ATTRIBUTES

  (

  VARCHAR  PrivateLogName   = @ODBCPrivateLogName,

  VARCHAR  DSNName             = @DSNName,

  VARCHAR  UserName             = @ODBCUserName,

  VARCHAR  UserPassword       = @ODBCPassword,

  VARCHAR  SelectStmt             = @SelectStmt

  );

  APPLY

(

'INSERT INTO A_STG_SICREDI_OWNER_T.PESSOA_JURIDICA

(

  OID_PESSOA                                                  ,OID_PES_MATRIZ                                              ,NOM_RAZAO_SOCIAL                                            ,NOM_FANTASIA                                                ,DAT_CONSTITUICAO                                            ,OID_NAT_JURIDICA                                            ,QTD_FILIAL                                                  ,QTD_CLIENTE                                                 ,NUM_REGISTRO                                                ,DAT_REGISTRO                                                ,DAT_ALT_CONTRATUAL                                          ,FLG_MATRIZ                                                  ,OID_CRZ_CAL_SOCIAL                                          ,OID_NAT_EMPRESA                                             ,OID_CAT_BACEN                                               ,TPO_REGIME_TRIBUTARIO                                       ,FLG_SUBESTAB_PODER                                          ,OID_TPO_COOPERATIVA                                         ,VLR_FATURAMENTO                                             ,FLG_AUDITORIA                                               ,FLG_AREA_ATUACAO                                            ,NUM_ARQUIVO                                                 ,DAT_ARQUIVO                                                 ,COD_USU_ATUALIZACAO                                         ,DAT_ATUALIZACAO                                             ,NUM_CNPJ                                                    ,QTD_FUNCIONARIO                                             ,ANO_FATURAMENTO                                             ,TPO_DML                                                     ,DAT_ATUALIZACAO_ORIGEM                                      ,NUM_SCN_TRANSACAO                                           ,NUM_IDF_REGISTRO                                            

)

VALUES 

(

  :OID_PESSOA                    ,:OID_PES_MATRIZ                ,:NOM_RAZAO_SOCIAL              ,:NOM_FANTASIA                  ,:DAT_CONSTITUICAO               (TIMESTAMP(0), FORMAT ''YYYYMMDDHHMISS''),:OID_NAT_JURIDICA              ,:QTD_FILIAL                    ,:QTD_CLIENTE                   ,:NUM_REGISTRO                  ,:DAT_REGISTRO                   (TIMESTAMP(0), FORMAT ''YYYYMMDDHHMISS''),:DAT_ALT_CONTRATUAL             (TIMESTAMP(0), FORMAT ''YYYYMMDDHHMISS''),:FLG_MATRIZ                    ,:OID_CRZ_CAL_SOCIAL            ,:OID_NAT_EMPRESA               ,:OID_CAT_BACEN                 ,:TPO_REGIME_TRIBUTARIO         ,:FLG_SUBESTAB_PODER            ,:OID_TPO_COOPERATIVA           ,:VLR_FATURAMENTO               ,:FLG_AUDITORIA                 ,:FLG_AREA_ATUACAO              ,:NUM_ARQUIVO                   ,:DAT_ARQUIVO                    (TIMESTAMP(0), FORMAT ''YYYYMMDDHHMISS''),:COD_USU_ATUALIZACAO           ,:DAT_ATUALIZACAO                (TIMESTAMP(0), FORMAT ''YYYYMMDDHHMISS''),:NUM_CNPJ                      ,:QTD_FUNCIONARIO               ,:ANO_FATURAMENTO               ,''I'',timestamp ''2014-01-01 00:00:00'',   0,   0

);'

)

TO OPERATOR 

(

$LOAD [@LoadInstances]

)

SELECT 

  OID_PESSOA,OID_PES_MATRIZ,NOM_RAZAO_SOCIAL,NOM_FANTASIA,DAT_CONSTITUICAO,OID_NAT_JURIDICA,QTD_FILIAL,QTD_CLIENTE,NUM_REGISTRO,DAT_REGISTRO,DAT_ALT_CONTRATUAL,FLG_MATRIZ,OID_CRZ_CAL_SOCIAL,OID_NAT_EMPRESA,OID_CAT_BACEN,TPO_REGIME_TRIBUTARIO,FLG_SUBESTAB_PODER,OID_TPO_COOPERATIVA,VLR_FATURAMENTO,FLG_AUDITORIA,FLG_AREA_ATUACAO,NUM_ARQUIVO,DAT_ARQUIVO,COD_USU_ATUALIZACAO,DAT_ATUALIZACAO,NUM_CNPJ,QTD_FUNCIONARIO,ANO_FATURAMENTO

FROM OPERATOR(ODBC_Operator [@ODBCInstances]); 

);

/* CONFIGURATION FILE */

/**********************************/

/* Values for ODBC operator */

/**********************************/

ODBCInstances            = 1,

ODBCPrivateLogName       = 'odbclog',

DSNName                  = 'DATABASE',

ODBCUserName             = 'USERNAME',

ODBCPassword             = 'PASS',

SelectStmt               = 'SELECT OID_PESSOA,OID_PES_MATRIZ, NOM_RAZAO_SOCIAL,NOM_FANTASIA,TO_CHAR (DAT_CONSTITUICAO, ''YYYYMMDDHH24MISS'') AS DAT_CONSTITUICAO,OID_NAT_JURIDICA,QTD_FILIAL,QTD_CLIENTE,NUM_REGISTRO,TO_CHAR (DAT_REGISTRO, ''YYYYMMDDHH24MISS'') AS DAT_REGISTRO,TO_CHAR (DAT_ALT_CONTRATUAL, ''YYYYMMDDHH24MISS'') AS DAT_ALT_CONTRATUAL,FLG_MATRIZ,OID_CRZ_CAL_SOCIAL,OID_NAT_EMPRESA,OID_CAT_BACEN,TPO_REGIME_TRIBUTARIO,FLG_SUBESTAB_PODER,OID_TPO_COOPERATIVA,VLR_FATURAMENTO,FLG_AUDITORIA,FLG_AREA_ATUACAO,NUM_ARQUIVO,TO_CHAR (DAT_ARQUIVO, ''YYYYMMDDHH24MISS'') AS DAT_ARQUIVO,COD_USU_ATUALIZACAO,TO_CHAR (DAT_ATUALIZACAO, ''YYYYMMDDHH24MISS'') AS DAT_ATUALIZACAO,NUM_CNPJ,QTD_FUNCIONARIO,ANO_FATURAMENTO FROM SICREDI_OWNER.PESSOA_JURIDICA;'

/**********************************/

/* Values for LOAD operator */

/*********************************/

LoadInstances            = 1,

LoadPrivateLogName       = 'loadlog',

TargetTable              = 'A_STG_SICREDI_OWNER_T.PESSOA_JURIDICA',  

TargetTdpId              = 'TDHom_a_stgint_odi_run',

TargetUserName           = 'USER',

TargetUserPassword       = 'PASS',

LogTable                 = 'A_STG_SICREDI_OWNER_W.PESSOA_JURIDICA_LOG'

ErrorTable1              = 'A_STG_SICREDI_OWNER_W.PESSOA_JURIDICA_ET'

ErrorTable2              = 'A_STG_SICREDI_OWNER_W.PESSOA_JURIDICA_UV'

 /* DEFINITION OF SOURCE TABLE AT ORACLE */



CREATE TABLE "SICREDI_OWNER"."PESSOA_JURIDICA"

  (

    "OID_PESSOA"       NUMBER NOT NULL ENABLE,

    "OID_PES_MATRIZ"   NUMBER,

    "NOM_RAZAO_SOCIAL" VARCHAR2(115 BYTE) NOT NULL ENABLE,

    "NOM_FANTASIA"     VARCHAR2(55 BYTE),

    "DAT_CONSTITUICAO" DATE,

    "OID_NAT_JURIDICA" NUMBER,

    "QTD_FILIAL"       NUMBER(5,0),

    "QTD_CLIENTE"      NUMBER(6,0),

    "NUM_REGISTRO"     VARCHAR2(16 BYTE),

    "DAT_REGISTRO" DATE,

    "DAT_ALT_CONTRATUAL" DATE,

    "FLG_MATRIZ"            VARCHAR2(1 BYTE),

    "OID_CRZ_CAL_SOCIAL"    NUMBER,

    "OID_NAT_EMPRESA"       NUMBER,

    "OID_CAT_BACEN"         NUMBER,

    "TPO_REGIME_TRIBUTARIO" NUMBER(1,0),

    "FLG_SUBESTAB_PODER"    VARCHAR2(1 BYTE),

    "OID_TPO_COOPERATIVA"   NUMBER,

    "VLR_FATURAMENTO"       NUMBER(14,2),

    "FLG_AUDITORIA"         VARCHAR2(1 BYTE),

    "FLG_AREA_ATUACAO"      VARCHAR2(1 BYTE),

    "NUM_ARQUIVO"           VARCHAR2(16 BYTE),

    "DAT_ARQUIVO" DATE,

    "COD_USU_ATUALIZACAO" NUMBER(5,0) NOT NULL ENABLE,

    "DAT_ATUALIZACAO" DATE NOT NULL ENABLE,

    "NUM_CNPJ"        VARCHAR2(14 BYTE),

    "QTD_FUNCIONARIO" NUMBER(7,0),

    "ANO_FATURAMENTO" NUMBER(4,0)

  )

/* DEFINITION OF TERADATA TARGET TABLE */

CREATE MULTISET TABLE A_STG_SICREDI_OWNER_T.PESSOA_JURIDICA ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      OID_PESSOA NUMBER(22,0) NOT NULL,

      OID_PES_MATRIZ NUMBER(22,0),

      NOM_RAZAO_SOCIAL VARCHAR(115) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

      NOM_FANTASIA VARCHAR(55) CHARACTER SET UNICODE NOT CASESPECIFIC,

      DAT_CONSTITUICAO TIMESTAMP(0),

      OID_NAT_JURIDICA NUMBER(22,0),

      QTD_FILIAL NUMBER(5,0),

      QTD_CLIENTE NUMBER(6,0),

      NUM_REGISTRO VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC,

      DAT_REGISTRO TIMESTAMP(0),

      DAT_ALT_CONTRATUAL TIMESTAMP(0),

      FLG_MATRIZ VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,

      OID_CRZ_CAL_SOCIAL NUMBER(22,0),

      OID_NAT_EMPRESA NUMBER(22,0),

      OID_CAT_BACEN NUMBER(22,0),

      TPO_REGIME_TRIBUTARIO NUMBER(1,0),

      FLG_SUBESTAB_PODER VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,

      OID_TPO_COOPERATIVA NUMBER(22,0),

      VLR_FATURAMENTO NUMBER(14,2),

      FLG_AUDITORIA VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,

      FLG_AREA_ATUACAO VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,

      NUM_ARQUIVO VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC,

      DAT_ARQUIVO TIMESTAMP(0),

      COD_USU_ATUALIZACAO NUMBER(5,0) NOT NULL,

      DAT_ATUALIZACAO TIMESTAMP(0) NOT NULL,

      NUM_CNPJ VARCHAR(14) CHARACTER SET UNICODE NOT CASESPECIFIC,

      QTD_FUNCIONARIO NUMBER(7,0),

      ANO_FATURAMENTO NUMBER(4,0),

      TPO_DML CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

      DAT_ATUALIZACAO_ORIGEM TIMESTAMP(6) NOT NULL,

      NUM_SCN_TRANSACAO NUMBER(20,0),

      NUM_IDF_REGISTRO NUMBER(20,0) NOT NULL)

NO PRIMARY INDEX ;

7 REPLIES
Teradata Employee

Re: Load data from Oracle to Teradata with correct character set

The character set type to be used by the ODBC driver must be specified through the driver settings (odbc.ini file on Unix/Linux), not through the TPT script.

-- SteveF
Enthusiast

Re: Load data from Oracle to Teradata with correct character set

Steve,

Thanks for the response. But we are using Windows 7 64Bits operational system. At ODBC Configuration Screen in Windows I saw a lot of options but I don know what to do.

There is a option "Select Translator", but my list is empty. And there is another field called "Extended Options".

Thanks,

Paulo Gil

Teradata Employee

Re: Load data from Oracle to Teradata with correct character set

On Windows you will have to use the ODBC administrator.

I am not familiar with Windows settings.

I can ask around; the person in my group who might know is out until next week.

-- SteveF
Enthusiast

Re: Load data from Oracle to Teradata with correct character set

Thanks in advance. 

Enthusiast

Re: Load data from Oracle to Teradata with correct character set

Hi Steve, 

Could you talk to your colleague and asked my question?

Thanks.

Enthusiast

Re: Load data from Oracle to Teradata with correct character set

Hi,

There is a checkbox under the Advanced options tab, in the ODBC administrator Oracle Wire Protocol DSN settings called "Enable N-Char Support".

you should have this checked so as to enable the utf support.

Enthusiast

Re: Load data from Oracle to Teradata with correct character set

Thanks Vishnu, it works fine for me.