search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

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

Open priyanshi-yb opened 2 months ago

priyanshi-yb commented 2 months ago

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
\.
darold commented 2 months ago

Why do you want to change the timestamp format? PostgreSQL default format is YYYY-MM-DD HH24.MI.SS.FF so timestamp data must be exported in this format. If you want to see timestamp in another format you have to look at DataStyle PostgreSQL setting but it is not dependant of PostgreSQL "internal" storage.