NUMBER(25) are converting to numeric(25)

[lingeshpes]

Hi

NUMBER(*) are converting to NUMERIC, need to convert it to BIGINT.
Below are the configurations am using

Oracle:

CREATE TABLE RACT_RISK_MAPPING 
    ( 
     QUESTION_ID NUMBER (25)  NOT NULL , 
     RISK_ID NUMBER (25)  NOT NULL 
    ) 
;

Postgres:

CREATE TABLE ract_risk_mapping (
	question_id numeric(25) NOT NULL,
	risk_id numeric(25) NOT NULL
) ;

ora2pg.conf

ORACLE_HOME	C:\oraclexe\app\oracle\product\11.2.0\server
ORACLE_DSN	dbi:Oracle:host=192.168.112.11;sid=orcl;port=1521
ORACLE_USER	MT_PREVAL_S2B6
ORACLE_PWD	MT_PREVAL_S2B6
USER_GRANTS	1
DEBUG	0
EXPORT_SCHEMA 0
SCHEMA	MT_PREVAL_S2B6
CREATE_SCHEMA 
COMPILE_SCHEMA 0
EXPORT_INVALID	0
TYPE	TABLE
DATA_TYPE DATE:TIMESTAMP, LONG:TEXT, CLOB:TEXT, BLOB:BYTEA, NUMBER(1):BOOLEAN,NUMBER(*):BIGINT
BOOLEAN_VALUES '0':'1' TRUE:FALSE
ALLOW 
LONGTRUNKOK 0
DATA_LIMIT    20000
PG_DSN                 
PG_USER                  
PG_PWD        
OUTPUT  TENANT_SCHEMA.SQL           
PRESERVE_CASE         0
BZIP2
GEN_USER_PWD         0
FKEY_DEFERRABLE     1
DEFER_FKEY    1
DROP_FKEY     1
DROP_INDEXES           0
PG_NUMERIC_TYPE    0
PG_INTEGER_TYPE     0
DEFAULT_NUMERIC bigint
KEEP_PKEY_NAMES 0
DISABLE_TRIGGERS USER
NOESCAPE       0
DISABLE_SEQUENCE   1
PLSQL_PGSQL 1
ORA_RESERVED_WORDS       audit,comment
FILE_PER_CONSTRAINT          0
FILE_PER_INDEX                     0
FILE_PER_TABLE         0
TRANSACTION committed
PG_SUPPORTS_WHEN                        1
PG_SUPPORTS_INSTEADOF    1
FILE_PER_FUNCTION  0
TRUNCATE_TABLE      0
FORCE_OWNER          0
STANDARD_CONFORMING_STRINGS 0
JOBS                1
ORACLE_COPIES         1
PARALLEL_TABLES      1
ALLOW_CODE_BREAK            1
XML_PRETTY   0
DISABLE_COMMENT         0
USE_RESERVED_WORDS        0
PKEY_IN_CREATE                    0
NULL_EQUAL_EMPTY 1
EXTERNAL_TO_FDW               1
ESTIMATE_COST                     0
COST_UNIT_VALUE                 5
DUMP_AS_HTML                    0
STOP_ON_ERROR	0
TOP_MAX                                10
ALLOW_PARTITION                PARTNAME
USE_TABLESPACE                   0
PG_SUPPORTS_MVIEW          1
REORDERING_COLUMNS       0
SYNCHRONOUS_COMMIT      0
PG_SUPPORTS_CHECKOPTION           0
AUTODETECT_SPATIAL_TYPE 1
CONVERT_SRID                       1
DEFAULT_SRID                        4326
GEOMETRY_EXTRACT_TYPE  WKT
PREFIX_PARTITION     0
LOG_ON_ERROR	1
PG_SUPPORTS_IFEXISTS         1
WITH_OID                   0
MODIFY_TYPE 

[darold]

Hi,

First you need to set PG_INTEGER_TYPE to 1, but converting NUMBER(25) to bigint will not be automatic. bigint are used with data type from NUMBER(10) to NUMBER(19), for NUMBER(25) you need to replace your current value of DATA_TYPE with:

DATA_TYPE     NUMBER(25):bigint

You also have to set:

REPLACE_AS_BOOLEAN       NUMBER:1

Others values in your current DATA_TYPE are default so they don't have to be set.

[lingeshpes]

Thanks darold.

1. can we convert NUMBER(*):bigint ?
2. I have used DATA_TYPE number(1):boolean
   to convert all number(1) oracle field to boolean in postgres.

Is it fine?

REPLACE_AS_BOOLEAN NUMBER:1 will this do the same thing?

[darold]

Yes, do not use DATA_TYPE number(1):boolean when you want to convert number(1) into boolean. The REPLACE_AS_BOOLEAN directive is here for that.

About DATA_TYPE value NUMBEtR(*):bigint it was not supported, commit 3e3f74b adds this feature.