Config option to override the NLS_TIMESTAMP_FORMAT or such encoding parameters used by Ora2pg

[priyanshi-yb]

Hi @darold, I was wondering if there is a way to override all these default encoding parameters (mainly NLS_TIMESTAMP_FORMAT)

[centos@ip-10-9-14-120 yb-voyager]$ ora2pg -t SHOW_ENCODING -c <conf_file>
Current encoding settings that will be used by Ora2Pg:
        Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8
        Oracle NLS_NCHAR AL32UTF8
        Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF
        Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
        PostgreSQL CLIENT_ENCODING UTF8
        Perl output encoding ''
Showing current Oracle encoding and possible PostgreSQL client encoding:
        Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8
        Oracle NLS_NCHAR AL32UTF8
        Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6
        Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
        PostgreSQL CLIENT_ENCODING UTF8

I was trying an example where my source has DD-MON-RR HH.MI.SSXFF AM but data is still getting dumped in YYYY-MM-DD HH24.MI.SS.FF , see below - example -

SQL> select * from test_format;

   ID T                                  
_____ __________________________________ 
    1 01-MAY-24 05.01.22.213763000 PM    
    2 01-MAY-24 05.01.34.286288000 PM    

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS
  2* ;

PARAMETER                  VALUE                           
__________________________ _______________________________ 
NLS_RDBMS_VERSION          19.0.0.0.0                      
NLS_NCHAR_CONV_EXCP        FALSE                           
NLS_LENGTH_SEMANTICS       BYTE                            
NLS_COMP                   BINARY                          
NLS_DUAL_CURRENCY          $                               
NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR    
NLS_TIME_TZ_FORMAT         HH.MI.SSXFF AM TZR              
NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AM        
NLS_TIME_FORMAT            HH.MI.SSXFF AM                  
NLS_SORT                   BINARY                          
NLS_DATE_LANGUAGE          AMERICAN                        
NLS_DATE_FORMAT            DD-MON-RR                       
NLS_CALENDAR               GREGORIAN                       
NLS_NUMERIC_CHARACTERS     .,                              
NLS_NCHAR_CHARACTERSET     AL16UTF16                       
NLS_CHARACTERSET           AL32UTF8                        
NLS_ISO_CURRENCY           AMERICA                         
NLS_CURRENCY               $                               
NLS_TERRITORY              AMERICA                         
NLS_LANGUAGE               AMERICAN                        

20 rows selected. 

SQL>

Data dumped by ora2pg -

COPY test_format (id,t) FROM STDIN;
1	2024-05-01 17:01:22.213763
2	2024-05-01 17:01:34.286288
\.