forked from darold/ora2pg
-
Notifications
You must be signed in to change notification settings - Fork 0
/
changelog
6019 lines (5542 loc) · 312 KB
/
changelog
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
2021 11 15 - v23.0
This release fix several issues reported since past five months and
adds some new major features and improvements.
* Add new option --blob_to_lo that can be used to export BLOB as large
objects. It can only be used with action SHOW_COLUMN, TABLE and INSERT.
When used with TABLE action, the BLOB column will be translated into oid
PostgreSQL data type. When used with the INSERT export action BLOB data
will be store as large object in the pg_largeobjects table and the oid
referencing this large object will be stored in the main table instead
of a bytea.
It is not possible to use oid with COPY because this feature use function
lo_from_bytea() that stores the large object in the external table and
returns the oid.
This feature works with or without the use of oracle_fdw to import the
data and option -J can be used to improve the speed of the INSERT import
provide that there is a numeric unique key on the table.
Thanks to rodiq for the feature request.
* Add command line option -W | --where clause to set the WHERE clauses to
apply to the Oracle query to retrieve data. It can be used multiple time.
It will override the WHERE configuration directive if there is a global
WHERE clause or the same table WHERE clause definition. Otherwise the
clause will be appended.
* Add data validation feature consisting in comparing data retrieved from a
foreign table pointing to the source Oracle table and a local PostgreSQL
table resulting from the data export. By default Ora2Pg will extract 10000
rows from both side, you can change this value using DATA_VALIDATION_ROWS.
When it is set to zero all rows of the tables will be compared.
Data validation requires that the table has a primary key or unique index
and that the key columns is not a LOB.
Due to differences in sort behavior between Oracle and PostgreSQL, if the
collation of unique key columns in PostgreSQL is not 'C', the sort order of
is different compared to Oracle. In this case the data validation will fail.
Ora2Pg will stop comparing two tables after 10 errors, result is dumped to
an output file named data_validation.log.
* Add DATA_VALIDATION_ORDERING configuration directive enabled by default.
Order of rows between both sides are different once the data have been
modified. In this case data must be ordered using a primary key or a
unique index, that mean that a table without such object can not be
compared. If the validation is done just after data import in mode single
process and without any data modification the validation can be done on all
tables without any ordering.
* Add DATA_VALIDATION_ERROR to stop validating data from a table after a
certain amount of row mismatch. Default is to stop after 10 rows
validation errors.
* Allow multiprocess for TEST_DATA action to validate data import. Use -P
or PARALLEL_TABLES to set the number of parallel tables checked. Output
is now done to a file named data_validation.log saved in the current
directory.
* Add replacement of UTL_RAW.CAST_TO_RAW with encode().
* Add rewrite of XMLTYPE() with xmlparse(DOCUMENT convert_from(..., 'utf-8')).
* Add VARCHAR_TO_TEXT configuration directive. By default VARCHAR2 without
size constraint are tranlated into text PG data type. If you want to use
varchar instead, disable this directive.
* Add detection of XML function for migration assessment cost.
* Add DBMS_RANDOM to the list of Oraclism handled by Orafce.
* Add support to mysql_fdw foreign data wrapper to export data
PostgreSQL tables. Thanks to Yoni Sade for the feature request.
* Allow to transform all NUMBER(*,scale) to an other data type by a
redefinition like NUMBER(*\,2):decimal in the DATA_TYPE configuration
directive. Thanks to Florent Jardin for the patch.
* Add information on how to use SSL encrypted connection to documentation.
* Add TEST_COUNT action to just report the row count diff between Oracle and
Backward compatibility changes:
- Add FORCE_PLSQL_ENCODING configuration directive. By default Ora2Pg
encode all functions code to ut8, this sometime can generate double
encoding. To change this behavior, disable this configuration directive.
Thanks to rynerisraid and lee-jongbeom for the report.
- Change behavior regarding RAW columns. Now RAW(16) and RAW(32) columns or
RAW columns with "SYS_GUID()" as default value are now automatically
translated into uuid. Data will be automatically migrated as PostgreSQL
uuid data type provided by the "uuid-ossp" extension. To recover the old
behavior to export data as bytea whatever is the precision, the following
must be set with DATA_TYPE configuration: RAW(16):bytea,RAW(32):bytea
Complete list of changes:
- Fix USE_LOB_LOCATOR handling.
- Fix data validation using oracle_fdw where zero after decimal is not strip
unlike with PG.
- Apply MODIFY_STRUCT redefinition to test actions
- Fix PG filter when DATA_VALIDATION_ORDERING is disabled
- Apply RAW to uuid transformation for data validation
- Apply boolean transformation for data validation
- Do not export data for virtual column for PG >= 13.
- Fix wrong replacement function with name including a regexp_* function in
his name. Thanks to Rui Pereira for the report.
- Remove comments in the from clause before rewrite outer join (+), the entire
FROM clause will be rewritten and we don't know where to restore.
- Fix export of columns information for data verification.
- Fix TEST_VIEW for row count returned by views to exclude views created in
extensions.
- Fix comment on procedures
- Fix translation of MySQL type UNSIGNED
- Fix test count of indexes for MySQL database.
- Fix test MySQL sequence count.
- Do not display error messages when user and db is first checked in the
import_all.sh script
- Fix ordering of check constraints
- Fix mysql table scan when table name is using reserved word. Thanks to
Stanley Sung for the report.
- Fix double BOTH keyword in TRIM function. Thanks to Rui Pereira for the
report.
- Fix aliases placed in a wrong way. Thanks to Rui Pereira for the report.
- Fix parsing of procedure broken on keyword RETURN. Thanks to Pavithra
Jayasankar.
- Fix case where default partition is taken as a value. Thanks to Karsten
Lenz for the report.
- Fix conversion of NUMBER without precision in PL/SQL code to respect
settings PG_NUMERIC_TYPE, PG_INTEGER_TYPE and DEFAULT_NUMERIC. Fix
translation of INTEGER/BINARY_INTEGER that was wrongly exported as
numeric. Thanks to Philippe Beaudoin for the report.
- Documentation fix. Thanks to mperdikeas for the patch.
- Fix case where SQL%ROWCOUNT was not replaced by GET DIAGNOSTIC. Thanks to
Awdotia Romanowna for the report.
- Fix quote of unique constraints name. Thanks to Veka for the report.
- Fix looking at package function metadata when there is a huge amount of
package.
- Fix error when trying to remove temporary files.
- Fix wrong translation of a call to a procedure with PRAGMA AUTONOMOUS
TRANSACTION through dblink. Thanks to Rui Pereira for the report.
- Remove schema name in front of index name. Thanks to Menelaos Perdikeas
for the report.
- Fix virtual column generated from an other column of the table (supported
in PG 12). Thanks to Veka for the report.
- Fix case of columns names in boolean transformation when oracle_fdw is used
to export data. Thanks to veka for the report.
- Remove extra parenthesis with sub query and TABLE function. Thanks to Rui
Pereira for the report.
- Fix WHERE clause not removed in ROWNUM replacement. Thanks to Rui Pereira
for the report.
- Exclude extensions tables from table test count. Thanks to Yoni Sade for the
report.
- Fix pg_attribute column adsrc removed in PG 12. Thank to Thorsten Hochreuter
for the patch.
- Fix unwanted aliases after row_number() over(). Thanks to Rui Pereira for
the report.
- Fix several spelling issues. Thanks to Florian Eckert for the patch.
- Fix wrong condition to import constraints in import_all.sh. Thanks to
Thorsten Hochreuter for the report.
- Fix BITMAP_AS_GIN detection. Thanks to Nishanth Bejgam for the patch.
- Fix parsing of views from file and add PASSWORD, KEY and REF to the list
of reserved keywords.
- Fix replacement of CURSOR ... IS when there is comment after IS.
- Fix comment in auto generated file global_variables.conf
- Fix XML data export that was transformed by the call to function
extract(/).getClobVal(), it is now replaced by a direct call to
getClobVal().
- Improve COPY FREEZE data export when FILE_PER_TABLE is enabled, the
transactions are now managed per individual file and not following
the main file. Thanks to Yoni Sade for the report.
- Fix addition to UNLOGGED keyword on foreign table when exporting
data using oracle_fdw. Thanks to Veka for the report.
- Fix FK error when using TRUNCATE before data export with oracle_fdw.
- Fix export of user defined type. Actually type definitions are extracted
from ALL_SOURCE which contain the original CREATE TYPE and eventually
all the ALTER TYPE commands. Previously those type as considered as not
supported by Ora2Pg.
- replace date(n) by timestamp.
- Always remove the fqdn SYS schema before functions call.
- Add report of GTT in SHOW_TABLE action.
- Fix empty partition values for Oracle 9i.
- Add creation of schema in user defined type export when EXPORT_SCHEMA is
enabled to fix an error when the schema has not already been created.
- Fix some wordings and exclude from export user defined type starting
with SYS_PLSQL_ found in a 9i export. It looks that they are internal
to PL/SQL code.
- Exclude DBMS_SQL from the DBMS count in migration assessment when
USE_ORAFCE is enabled.
- Handle case where indexes name include the schema at create time
- Fix PL/SQL numeric datatype conversion
2021 07 02 - v22.1
This is a maintenance release to extend the feature of data export through the
oracle_fdw PostgreSQL extension to migration that use the public schema and
do not preserve case.
There is also some other fixes:
- Fix compile_schema() call that breaks valid function based indexes by
adding compile_all => FALSE to DBMS_UTILITY.compile_schema().
Thanks to Pawel Fengler for the patch.
- Force foreign table for data export as readonly to avoid accidental
write if import schema is not cleaned.
- Fix data export to file not possible since last changes for oracle_fdw
export. Thanks to Niels Jespersen for the report.
2021 06 26 - v22.0
This release fix several issues reported since past three months and
adds some new features and improvements. I must thanks MigOps Inc who
hire me to drive Oracle to PostgreSQL migrations and to develop Ora2Pg.
It's been a long time that I was looking for such a company and it is
an amazing gift for the 20 years of Ora2Pg. All improvements and new
new features developed during my work at MigOps will be available in
the public GitHub repository, here are the new ones.
- Add export of data using oracle_fdw when FDW_SERVER is set and export
type is COPY or INSERT. Multi-process using -P or -J is fully supported
but option -j is useless in this case. Boolean transformation of some
columns or data type is also supported. Actually, expect that it works
just like data migration without oracle_fdw. This can improve the data
migration speed from 30 to 40% especially for BLOB export.
- Improve export performances with huge number of objects by avoiding join
between Oracle catalog tables.
- Set a maximum of assessment score for tables, indexes, sequences,
partitions, global temporary table and synonym following the number of
objects.
- Add detection of XML functions to the assessment cost.
- Allow to change the assessment cost unit value in the export_all.sh script
when ora2pg is used with options --init_project and --cost_unit_value.
- Remove pragma restrict_references from P/PSQL code, it is useless.
- Add the oracle schema to search_path in SQL files generated and improve
the migration assessment when USE_ORAFCE is enabled.
- Apply ALLOW and EXCLUDED filtered stored procedures at package extraction
level. Previous this patch there was no way to not export some package
functions or to exclude them from assessment.
- Add new tests to check sequences last values and number of identity columns
in both side.
- Apply ALLOW/EXCLUDE without object to table object by default in TEST
action.
New configuration directives:
- Add ORACLE_FDW_TRANSFORM configuration directive to apply a transformation
to a column when exporting data. Value must be a semicolon separated list of
TABLE[COLUMN_NAME, <replace code in SELECT target list>]
For example to replace string 'Oracle' by 'PostgreSQL' in a varchar2 column
use the following.
ERROR_LOG[DBMS_TYPE:regexp_replace("DBMS_TYPE",'Oracle','PostgreSQL')]
Thanks to MigOps for the patch.
- Add DROP_IF_EXISTS configuration directive to add a statement
"DROP <OBJECT> IF EXISTS" before creating the object. Can be
useful in an iterative work. Default is disabled. Thanks to
dherzhau for the feature request.
Backward compatibility:
There is a backward compatibility issue with old configuration files
where FDW_SERVER is set by default. This directive was not used when
exporting data, this is not the case anymore as it instruct Ora2Pg to
use the given foreign server to use oracle_fdw to migrate the data.
Here is the full list of changes and acknowledgements:
- Fix replacement of TO_CLOB() function, now it is just removed and the
parenthesis are kept. Thanks to Rui Pereira for the report.
- Fix incorrect detection of cursor on dynamic query. Thanks to Rui
Pereira for the report.
- Fix quoting column names with spaces and dots. Dots are replaced by
underscore. Thanks to Veka for the report.
- Fix one case where DEFINED_PKEY with PRESERVE_CASE was not handled
correctly. Thanks to Veka for the report.
- Fix quoting of reserved keywords in CREATE INDEX columns names. Thanks
to Veka for the report.
- Fix column name starting with number not quoted in COMMENT. Thanks to
Veka for the report.
- Fix addition of PERFORM on call to stored procedures not prefixed by
the package name. Thanks to Rui Pereira fo the report.
- Fix search of ora2pg_conf.dist under Windows instead of ora2pg.conf.dist
when --init_project is used. Thanks to Julien Monticolo for the report.
- Fix translation from file of check constraint when created on same column,
only the last one was exported. Also shortened the prefix for constraint
naming, ora2pg_ckey becomes o2pc, ora2pg_ukey is now o2pu and ora2pg_fkey
is renamed into o2pf. Thanks to anvithaprabhu8 for the report.
- Replace wildcard precision * for numeric by 38.
- Fix incomplete listagg() conversion. Thanks to avandras for the report.
- Fix potential problem in last_day conversion when USE_ORAFCE is off and
a number is added or subtracted to the last day. Thanks to atlterry for
the report.
- Do not apply utf8 conversion of comments to input files.
- Fix termination of last writer process when parallel and quiet mode are
used together. Thanks to David Harper for the patch.
- Remove precision in number of digit in timestamp microseconds when
setting NLS_TIMESTAMP_FORMAT at session startup. Now use:
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'
instead of '.FF6'. This was possibly the cause of sporadic error:
ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtFetch)
Default is precision 6 so it may not change anything. Thanks to
Abhijeet Bonde for the report.
- Fix call to deprecated MySQL spatial function when version is after 5.7.6.
Thanks to naveenjul29 for the report.
- Fix false positive detection for nested table with MySQL export.
- Fix sequence export read from file.
- Add PG_VERSION to the documentation. Thanks to xinjirufen for the report.
- Fix error report when preparing query to ALL_IND_COLUMNS IC. Thanks to
ganeshakorde for the report.
- ora2pg_scanner: Fix detection of service_name in DSN.
- Fix error raise_application_error() with named parameters.
- Fix replacement of out parameters in triggers.
- Change sequence export result storage to hash instead of array.
- Fix package export when there is a comment between PACKAGE BODY and the
name of the package.
- Fix Perl error "malformed utf-8 character in substitution" when there is
character not in utf8 in the comment or constants.
- Fix detection of MySQL FUNCTION vs PROCEDURE for version >= 5.5. Thanks
to naveenjul29 for the report.
- Exclude nested tables from the export as it is not supported and it
always generate an error. A warning is raised.
- Fix column case in check constraints when PRESERVE_CASE is enabled.
- Fix search_path in direct PG data export when PG_SCHEMA is set.
- Fix -Infinity insert for direct PG data export.
- Fix drop indexes when PRESERVE_CASE is enabled.
- Remove potential double affectation for function with out parameter.
- Create immutable to_char function when used in an index.
- Replace dmake by gmake on Windows installation instruction. Thanks to
Julien Monticolo for the report.
- Fix MySQL version conditions. Thanks to Christoph Berg for the report.
- Fix HASH partitioning for duplicate WITH clause.
- Fix tests when no schema are set to compare all objects in all schemes.
Thanks to gp4git and dlc75 for the report.
- Apply ALLOW/EXCLUDE without object to table object by default in TEST
action. Thanks to Yony Sade for the feature request.
- Add DROP_IF_EXISTS configuration directive. Thanks to dherzhau for the
feature request.
- Fix regression in removing %ROWTYPE from function parameters. Thanks to
Eric Delanoe for the report.
- Fix not adding default values to parameters when this is an OUT parameter.
Thanks to Eric Delanoe for the report.
- Fix ALL_DIRECTORIES call be using table name relative to USER_GRANTS.
Thanks to Yoni Sade for the report.
- Change all remaining call to static ALL_* tables to a call relative to
USER_GRANTS. Thanks to Yoni Sade for the report.
- Fix export or partitioned table with unsupported partitioning type
like PARTITION BY REFERENCE. The table is created without partition
and a warning it fired as well as the following message in the output
file as a comment: -- Unsupported partition type, please check
2021 04 01 - v21.1
This release fix several issues reported since past six months and
as usual adds some new features and improvements.
* Now that Orafce 3.15.0 has a definition for the REGEXP_* function,
makes the translation optional to USE_ORAFCE directive.
* Add set application name in connection to Oracle/MySql/PostgreSQL.
* Add translation of REGEXP_COUNT() and change assessment cost.
* Rewrite the way REGEXP_LIKE() is translated into regexp_match to
support modifiers. This rewrite also fix default behavior between
Oracle and PostgreSQL.
* Replace DBMS_LOB.GETLENGTH() by PostgreSQL octet_length() function.
* Add types correspondences for VARCHAR2 and NVARCHAR2 in DATA_TYPE
configuration directive.
* Add autodetection and support of geometry type, srid and dimension
for ArcGis geometries.
* Add conversion of default value in function parameters.
* Add -u | --unit option to ora2pg_scanner to be able to set the
migration cost unit value globally.
* Replace DBMS_LOB.SUBSTR() by SUBSTR()
* Remove TO_CLOB() it is useless, manual cast could be necessary.
* Replace IS JSON validation clause in CHECK constraints by
(CASE WHEN $1::json IS NULL THEN true ELSE true END)
When the code is invalid an error is fired.
* DISTINCT and UNIQUE are synonym on Oracle
Backward compatibility changes:
- Force a column to be bigint if this is an identity column. Thanks
to MigOps.com for the patch.
- Fix EMPTY_LOB_NULL, enable/disable was inverted, keeping default
to enabled. Take care that in old ora2pg.conf it is disabled so it
will break backward compatibility with old configuration.
- Replace NO_LOB_LOCATOR with USE_LOB_LOCATOR and NO_BLOB_EXPORT
with ENABLE_BLOB_EXPORT to avoid confusion with double negative
variable. Backward compatibility is preserved with a warning.
- SRID for SDO_GEOMETRY export is now taken from the value not forced
from the metadata table.
Here is the full list of changes and acknowledgements:
- Take Geometry SRID from the data and fallback to SRID defined in
metadata when not found. Thanks to Sebastian Albert for the report.
- Fix case where Ora2Pg temporary substitution of '' by placeholder
was not restored. Thanks to MigOps.com for the patch.
- Fix identity column export on unsupported Oracle 18c options.
Thanks to MigOps.com for the patch.
- Fix export of columns indexes created with single quote.
Thanks to MigOps.com for the patch.
- Fix replacement of keyword PROCEDURE by FUNCTION in constraints
constants definition. Thanks to marie-joechahine for the report.
- Replace IS JSON validation clause in CHECK constraints. Thanks to
marie-joechahine for the report and MigOps.com for the patch.
- Add support to ON OVERFLOW clause in LISTAGG replacement.
Thanks to MigOps.com for the patch.
- Fix incorrect handling of HAVING+GROUP BY rewriting.
Thanks to MigOps.com for the patch.
- Add replacement of TO_NCHAR by a cast to varchar. Thanks to
MigOps.com for the patch.
- Fix replacement of NOTFOUND when there is extra space or new line
in the WHEN EXIT clause. Thanks to MigOps.com for the patch.
- Fix a regression in NO_VIEW_ORDERING, it was not taken in account
anymore. Thanks to RonJojn2 for the report.
- Replace DATA_TYPE with DTD_IDENTIFIER in MySQL catalog queries for
version prior 5.5.0. Thanks to zejeanmi for the report.
- Fix import script to import sequences before tables. Thanks to
MigOps.com for the patch.
- Fix detail report of custom type in migration assessment. Thanks
to MigOps.com for the patch.
- Fix duplicate schema prefixed to SYNONYM. Thanks to dlc75 for the
reports.
- Replace NO_LOB_LOCATOR with USE_LOB_LOCATOR and NO_BLOB_EXPORT with
ENABLE_BLOB_EXPORT to avoid confusion with double negative variable.
Thanks to Rob Johnson for the report.
- Fix some missing replacements of NVL and rewrite !=-1 into != -1.
Thanks to MigOps.com for the patch.
- Fix ROWNUM followed by + or - operator and when no aliases are
provided. Thanks to MigOps.com for the patch.
- Add DBSFWUSER to the list of user/schema exclusion. Thanks to
MigOps.com for the patch.
- Fix regexp to not append subquery aliases on JOIN clause. Thanks
to Rui Pereira for the report.
- Handle PRESERVE_CASE and EXPORT_SCHEMA in sequence name. Thanks
to marie-joechahine for the report.
- Add CREATE SCHEMA statement to sequence export when EXPORT_SCHEMA
is enabled. Thanks to marie-joechahine for the report.
- Fix duplicate index name on subpartition. Thanks to Philippe
Beaudoin for the report.
- Exclude sequences used for IDENTITY column (ISEQ$$_). Thanks to
marie-joechahine for the report.
- Fix parsing from file of CREATE SEQUENCE. Thanks to Rui Pereira
for the report.
- In export_all.sh script use the database owner provided if it is a
superuser instead of postgres user. Thanks to jjune235 for the
feature request.
- Fix parsing of triggers when there is a CASE inside the code.
Thanks to Rui Pereira for the report.
- Add set application name in connection to Oracle/MySql/PostgreSQL.
Thanks to Yoni Sade for the patch.
- Fix double column alias when replacing ROWNUM. Thanks to Rui
Pereira for the report.
- Add translation of the REGEXP_COUNT function and change assessment
cost.
- Rewrite the way REGEXP_LIKE is translated into regexp_match to
support modifiers. This rewrite also fix default behavior between
Oracle and PostgreSQL. Thanks to otterrisk for the report.
- Add IS JSON to assessment. Thanks to marie-joe Chahine for the
report.
- Fix multi-columns RANGE partitioning. Thanks to Philippe Beaudoin
for the report.
- Improve reordering columns. Sort by fieldsize first, if same size
then it sorts by original position. Thanks to Sebastien Caunes for
the patch.
- Append partition's column to the primary key of the table as it
must be part of the PK on PostgreSQL. Thanks to xinjirufen for the
report.
- Fix partition export where PRESERVE_CASE was applied to Oracle
side. Thanks to schleb1309 for the report.
- Fix trigger export with column restriction. Thanks to Sebastien
Caunes for the report.
- Update installation information.
- Fix table reordering following data type. Thanks to Sebastien
Caunes for the patch.
- Fix incorrect variable name corresponding to DATA_EXPORT_ORDER
making this directive inefficient. Thanks to Ron Johnson for the
report.
- Fix translation of check constraint when read from file
- Fix EMPTY_LOB_NULL, enable/disable as inverted, keep default to
enabled. Take care that in old ora2pg.conf it is disabled so it
will break backward compatibility with old configuration.
- Fix false positive detection of input filename is the same as
output file.
- Rename variables SCHEMA_ONLY, DATA_ONLY and CONSTRAINTS_ONLY in
script import_all.sh to conform to their real use. Thanks to
Sebastien Caunes for the report.
- Fix comment detection breaking the package header parsing and
global variable detection.
- Fix ROWNUM detection for replacement by LIMIT
- Fix escaping of psql command in configuration file comment and
set default value for PG_VERSION to 12.
- Replace precision by exactness in documentation. Thanks to
Sebastien Caunes for the report.
- Prevent reducing DATA_LIMIT when NO_BLOB_EXPORT is enabled.
Thanks to Thomas Reiss for the report.
- Fix geometry type detection.
- Add autodetection of geometry type, srid and dimension for
ArcGis geometries. Thanks to changmao01 for the feature request.
- Fix call to ST_GeomFromText when no SRID is found.
- Fix case where OVERRIDE SYSTEM VALUE clause could be added if PG
version is < 10. Thanks to changmao01 for the report.
- Fix unwanted call to internal GEOM library for ArcGis geometries.
Thanks to changmao01 for the report.
- Exclude schema SDE (ArGis) from export. Thanks to changmao01 for
the report.
- prevent looking twice to same custom data type definition.
- Fix previous patch to catch SDO_GEOMETRY on lowercase regexp.
- Limit detection of geometry data type to SDO_GEOMETRY.
- Fix column name replacement in view definition. Thanks to Amit
Sanghvi for the report.
- Fix REPLACE_COLS parsing to allow space in column name. Thanks
to Amit Sanghvi for the report.
- Fix translation from file of triggers with WHEN clause. Thanks
to Rui Pereira for the report.
- Fix column name kept lowercase in the MOD() clause when -J is
used. Thanks to Code-UV for the report.
- Keep case of PG_SCHEMA definition when used in TEST action.
- Fix data export for columns with custom data type. Thanks to
Aymen Zaiter for the report.
- Fix missing bracket with || operator in CREATE INDEX. Thanks to
traxverlis for the report.
- Fix export of single row unique function base index. Example:
CREATE UNIQUE INDEX single_row_idx ON single_row ((1));
Thanks to unrandom123 for the report.
- Update documentation about schemas used in TEST action.
- Disable materialized view export with MySQL export it is not
supported. Thanks to naveenjul29 for the report.
- Fix table alias detection in Oracle (+) join rewrite.
- Fix an infinite loop in Oracle (+) join rewrite when there is no
table aliases and the table is prefixed by its schema. Thanks to
Olivier Picavet for the report.
- Fix MODIFY_STRUCT when column name need to be escaped. Thanks to
helmichamsi10 for the report.
- Fix empty PARTITION BY () clause. Thanks to Aymen Zaiter.
- Fix export of global variable from package description when there
is no package body. Thanks to naveenjul29 for the report.
- Add package description export when dumping package source,
previously only the package body was dump. This will allow to
check global variables export.
- Whilst working on the Reproducible Builds effort (https//reproducible-builds.org/)
it appears that ora2pg could not be built reproducibly. Thanks to
Chris Lamb for the patch.
- Fix case of NUMBER(*,10) declaration. Oracle has a precision of 1 to 38
for numeric. Even if PostgreSQL allow a precision of 1000 use 38 to
replace junk parameter. Thanks to xinjirufen for the report.
- Add conversion of default value in function parameters, like syssdate
rewriting for example. Thanks to unrandom123 for the report.
- Fix a regression in data encoding when exporting data introduced in
commit fa8e9de. Thanks to gp4git for the report.
- Add debug information about the environment variables used before
connecting to Oracle.
- Fix case of duplicate between unique index and unique constraint with
multiple columns. Thanks to gp4git.
2020 10 12 - v21.0
This release fix several issues reported since last release and adds
several new features and improvements.
* Add clause OVERRIDING SYSTEM VALUE to INSERT statements when the
table has an IDENTITY column.
* Considerably increase the speed to generate the report about the
migration assessment, especially for database with huge number of
objects.
* Reduce time passed in the progress bar. Following the number of
database objects we were spending too much time in refreshing the
progress bar.
* Add number of identity columns in migration assessment report.
* Make assessment details report initially hidden using HTML5 tags
<details><summary>
* Improve speed of BLOB/CLOB data export. Oracle recommends reading
from and writing to a LOB in batches using a multiple of the LOB
chunk size. This chunk size defaults to 8k (8192). Recent tests
show that the best performances can be reach with higher value
like 512K or 4Mb.
* Add progress bar when --oracle_speed is use in single process mode.
* Automatically activate USER_GRANTS when the connection user has no DBA
privilege. A warning is displayed.
* Complete port to Windows by using the Windows separator on stdout
redirection into a file at ora2pg command line call and improve
ora2pg_scanner port on Windows OS.
* Add rewrite of MySQL JOIN with WHERE clause instead of ON.
* Add MGDSYS (Oracle E-Business Suite) and APEX_040000 to the list
of schemas excluded from the export.
* Supply credentials interactively when a password is not defined in
the configuration file. Need the installation of a new Perl module
Term::ReadKey.
* Add supports oracle connections "as sysdba" with username "/" and
an empty password to connect to a local oracle instance.
* Add translation of PRIVATE TEMPORARY TABLE from Oracle 18c into
PostgreSQL basic temporary table, only the default behavior for
on commit change.
New command line options:
* Add new command line option to ora2pg_scanner: -b | --binpath DIR
to set the full path to directory where the ora2pg binary stays.
Might be useful only on Windows OS.
* Add -r | --relative command line option and PSQL_RELATIVE_PATH
configuration directive. By default Ora2Pg use \i psql command to
execute generated SQL files if you want to use a relative path
following the script execution file enabling this option will use
\ir. See psql help for more information.
New configuration directives:
* NO_VIEW_ORDERING:
By default Ora2Pg try to order views to avoid error at import time
with nested views. With a huge number of views this can take a very
long time, you can bypass this ordering by enabling this directive.
* NO_FUNCTION_METADATA
Force Ora2Pg to not look for function declaration. Note that this
will prevent Ora2Pg to rewrite function replacement call if needed.
Do not enable it unless looking forward at function breaks other
export.
* LOB_CHUNK_SIZE
See explanation in the new features and improvement list.
* ALTERNATIVE_QUOTING_REGEXP
To support the Alternative Quoting Mechanism ('Q' or 'q') for String
Literals set the regexp with the text capture to use to extract the
text part. For example with a variable declared as
c_sample VARCHAR2(100 CHAR) := q'{This doesn't work.}';
the regexp to use must be:
ALTERNATIVE_QUOTING_REGEXP q'{(.*)}'
ora2pg will use the $$ delimiter, with the example the result will
be:
c_sample varchar(100) := $$This doesn't work.$$;
The value of this configuration directive can be a list of regexp
separated by a semi colon. The capture part (between parenthesis) is
mandatory in each regexp if you want to restore the string constant.
Backward compatibility changes:
- Default for NO_LOB_LOCATOR is now 1 to benefit from the LOB_CHUNK_SIZE
performances gain.
- Enable schema compilation (COMPILE_SCHEMA set to 1) by default to
speed up DDL extraction.
- Change the behavior of Ora2Pg with the parameters that follows a
parameter with a default value. Ora2Pg used to change the order of the
parameter's function to put all parameters with a default value at end
of the list which need a function call rewrite. This have been abandoned
now any parameter without default value after a parameter with a default
value will be appended DEFAULT NULL.
Here is the full list of changes and acknowledgements:
- Fix unwanted references to PK/UK when DROP_INDEXES is enabled.
- Fix comparison between function name in TEST report.
- Fix duplicates on retrieving partitions information.
- Improve SHOW_TABLE report about partitioned tables information.
- Drop code about removing DEFAULT NULL in functions parameters. Thanks to
chaluvadi286 for the report.
- Fix two other case where materialized view can be listed in the table list.
- Fix case where materialized view can be listed in the table list. Thanks
to Thomas Reiss for the report.
- Fix %ROWTYPE removing to be restricted to REF CURSOR. Thanks to
jagmohankaintura-tl for the report.
- Fix PG functions count when comparing Oracle functions count in TEST action.
Remove useless -l option to import_all.sh auto generated script.
- Fix PRESERVE_CASE on schema name for functions extracted from a package.
- Fix search_path adding public default schema.
- Apply PRESERVE_CASE to partition by involved columns.
- Add IF EXIXTS to create schema to avoid error when import_all.sh is run
several time.
- Fix sort order of comment on columns for tables and views.
- Fix warning about data export from nonexistent table resulting of index
lookup on nested table.
- Fix infinite loop in global variables package extraction. Thanks to Thomas
Reiss for the report.
- Fix global variables and packages export when comments are present in the
package description.
- Add information about XML_PRETTY size limit to 4000
- Fix column name in indexes when PRESERVE_CASE is enabled. Thanks
to Julien traxverlis for the report.
- Fix Top 10 of largest tables sort order. Thanks to Tom Vanzieleghem
for the patch.
- Fix duplicates between indexes and constraints. Thanks to sdpdb and
Jon Betts for the report.
- Fix SYSDATE replacement and possible infinite loop in SYSDATE parsing.
Thanks to pbidault for the report.
- Fix export of Oracle TEXT indexes with USE_UNACCENT disabled. Thanks to
Eric Delanoe for the report.
- Add new configuration directive ALTERNATIVE_QUOTING_REGEXP to support
the Alternative Quoting Mechanism ('Q' or 'q') for String Literals.
Thanks to just-doit for the report.
- Fix OF clause missing in update triggers. Thanks to just-doit for
the report.
- Fix IS NULL translation in WHERE clause of UPDATE statement. Thanks
to Eric Delanoe for the report.
- Remove DDL export of LOG indexes on materialized views.
- Fix unexpected materialized view listed in table export. Thanks to
jagmohankaintura-tl for the report.
- Fix default values with single quote in create table DDL. Thanks to
justdoit for the report.
- Fix double quote in CREATE TRIGGER code and applying of preserve case
on column name.
- Supply credentials interactively when a password is not defined in
configuration file. Thanks to rpeiremans for the patch.
- Add supports oracle connections "as sysdba" with username "/" and
an empty password to connect to a local oracle instance. Thanks to
rpeiremans for the patch.
- Fix documentation about materialized view export.
- Fix export order of comments on columns.
- Fix export of views comments when no schema is used for export and
export schema is activated.
- Fix cast in replacement with TO_NUMBER and TO_CHAR in indexes. Thanks
to Kiran for the report.
- Add MGDSYS (Oracle E-Business Suite) to the list of schemas excluded
from the export. Thanks to naveenjul29 for the report.
- Add more information about PG_DSN use. Thanks to Pepan7 for the report.
- Update copyright year.
- Fix regression where "SET client_encoding TO ..." was missing data file
header. Thanks to Emmanuel Gaultier for the report.
- Fix EDITABLE vs EDITIONABLE parsing. Thanks to Naveen Kumar for the report.
- Fix typos in documentation. Thanks to swallow-life, ChrisYuan, Edward Betts,
Jack Caperon and cavpollo for the patches.
- Add OVERRIDING SYSTEM VALUE to INSERT statement when the table has an
IDENTITY column. Thanks to Robin Windey for the report
- Remove empty parenthesis of identity column options
- Limit sequence/identity column value to bigint max
- Add an example of DBD::Oracle DSN with 18c.
- Fix parsing of identity column from file. Thanks to deepakp555 for the
report.
- Fix quoting of identifier when PRESERVE_CASE is enable and no particular
schema is specified. Thanks to mkgrgis for the report.
- Move setting of search_path before truncate table. Thanks to Michael Vitale
for the report.
- Add explanation about TEST and SIZE migration assessment values.
- Mark XMLTYPE as having LOB locator.
- Fix XMLTYPE columns that are exported as lob locator. Thanks to Tamas for
the report.
- Fix a problem of data export throughput that was slowing down all along
the export when multiprocess for output was not used. Ora2Pg was forking
a process for each chunk of data (see DATA_LIMIT) which is useless when
write output is done on a single process (-j 1) and slow down the export.
Thanks to markhooper99 and Tamas for reporting, testing and finding the
source of the issue.
- Fix progress bar in multiprocess mode, update was not displayed at each
chunk of data processed.
- Add internal debug information for progress bar.
- Add debug information for SHOW_REPORT
- Fix a long pending issue with custom data type export. Thanks to
jhollandsworth for the patch.
- Fix LOB data export with value changed to NULL when the CLOB value was 0.
Thanks to jhollandsworth for the report.
- Fix escape format issue with COPY and bytea. Thanks to Christoph Noel and
dwbrock62 for the report.
- Add LD_LIBRARY_PATH and PATH prerequisite to run ora2pg.
- Fix use of the HIGH_VALUE column in partition listing with Oracle 9i. Thanks
to Francisco Puga for the report.
- Update the table row count logic to incorporate the PostgreSQL table FQN as
established through the set_pg_relation_name routine. Thanks to Jacob
Roberts for the patch.
- Add the PostgreSQL FQN when printing the results in the TEST function. Thanks
to Jacob Roberts for the patch.
- Do not look forward function with the SHOW_* action
- Fix BLOB export where \x was escaped. Thanks to Christophe Noel for the
report.
- Update Ora2Pg.pm to fix symbol in column name in create index statement.
Thanks to kpoluektov for the patch.
- Fix package function extraction when there is a start of comment (/*) in
a constant string. Thanks to Tiago Anastacio for the report.
- Fix type detection in package declaration. Thanks to Tiago Anastacio for
the report.
- Avoid displaying error ORA-22831 when exporting LOB. This error can
appears when LOB chunk size is different from default 8192. The error
has no incidence on the export so we can just ignore it. This patch
also use DBD::Oracle ora_lob_chunk_size() method to gather chunk the
chunk size of the LOB, fallback to 8192 if not available. Thanks to
joedbadmin for the report.
- Disable direct report of Oracle errors, all error should be handled at
Ora2Pg level.
- Fix MySQL data export with allow/exclude objects. Thanks to Manuel Pavy for
the report.
- Fix exclude/allow object feature in MySQL export that was not working since
release 19.0. Thanks to Manuel Pavy for the report.
- Add rewrite of MySQL JOIN with WHERE clause instead of ON. Thanks to Marc
Rechte for the report.
- Fix issue with custom type when multiprocess is used.
- Fix progress bar on final total estimated data in multiprocess mode.
- Fix ORACLE_HOME path in README.md. Thanks to Lubos Cisar for the patch.
- Fix missing replacement with PERFORM in CASE ... WHEN statements. Thanks to
Eric Delanoe for the report.
- Fix duplicate ora2pg command in iteration.
- Improve ora2pg_scanner port on Windows OS. Thanks to Marie Contencin for the
report.
- Add perl call to all ora2pg commands when the scanner is executed on
Windows system as the shebang is not recognized. Thanks to Marie Contencin
for the report.
- Fix several issue with compressed output. Thanks to Bach Nga for the report.
- Fix translation of CURSOR IS SELECT with a comment before the SELECT.
Thanks to Izaak van Niekerk for the report.
- Fix export of procedures as PostgreSQL procedures with version 11.
- Add APEX_040000 to the schemas exclusion list. Thanks to Don Seiler for the
report.
- Fix possible unquoted default values. Thanks to Marc Rechte for the report.
- Fix MySQL SET TRANSACTION clause when TRANSACTION is set to readonly or
readwrite this is not supported so fall back in READ COMMITTED isolation
level in this case. Thanks to Marc Rechte for the report.
- Fix export of functions, column DATA_TYPE does not exists in table
INFORMATION_SCHEMA.ROUTINES before MySQL 5.5.0. Replace it with column
DTD_IDENTIFIER for prior version. Thanks to Marc Rechte for the report.
- Fix double quote in CREATE TRIGGER code and applying of preserve case on
column name.
2019 01 18 - v20.0
This release fix several issues reported during the last three months
and adds several new features and improvement. The change of major
version is related to backward compatibility break with the removed of
most PG_SUPPORTS_* configuration directives and their replacement with
the new PG_VERSION directive.
New features and configuration directives in this release:
* Add PG_VERSION configuration directive to set the PostgreSQL major
version number of the target database. Ex: 9.6 or 10. Default is
current major version at time of a new release. This replace the
old PG_SUPPORTS_* configuration directives.
* Removed all PG_SUPPORTS_* configuration directives minus
PG_SUPPORTS_SUBSTR that is related to Redshift engine.
* Export of BFILE as bytea is now done through a PL/SQL function to
extract the content of a BFILE and generate a bytea data suitable
for insert or copy into PostgreSQL.
* Foreign keys that reference a partitioned table are no more
exported.
* Show table name on Oracle side during export using at connection
time: DBMS_APPLICATION_INFO.SET_ACTION(table_name);
* When the date format is ISO and the value is a constant the call
to to_date() is removed and only the constant is preserved. For
example: to_date(' 2013-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS')
is replaced by a simple call to: ' 2013-04-01 00:00:00'.
This rewrite is limited to PARTITION export type when directive
PG_SUPPORTS_PARTITION is enabled.
* Add DATA_EXPORT_ORDER configuration directive. By default data
export order will be done by sorting on table name. If you have
huge tables at end of alphabetic order and are using multiprocess,
it can be better to set the sort order on size so that multiple
small tables can be processed before the largest tables finish.
In this case set this directive to size. Possible values are name
and size. Note that export type SHOW_TABLE and SHOW_COLUMN will
use this sort order too, not only COPY or INSERT export type.
* Add NO_BLOB_EXPORT configuration directive. Exporting BLOB could
take time and you may want to export all data except the BLOB
columns. In this case enable this directive and the BLOB columns
will not be included into data export. The BLOB column must not
have a NOT NULL constraint. Thanks to Ilya Vladimirovich for the
* Add PREFIX_SUB_PARTITION to enable/disable sub-partitioning table
prefixing in case of the partition names are a part of the sub-
partition names.
* Add special replacement for case of epoch syntax in Oracle:
(sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy'))*24*60*60
is replaced by the PostgreSQL equivalent:
(extract(epoch from now()))
Here is the full list of changes and acknowledgements:
- Export indexes and constraints on partitioned table with pg >= 11.
- Fix incorrect replacement of NLS_SORT in indexes.
- Bring back DISABLE_UNLOGGED feature. Thanks to Jean-Christophe
Arnu for the patch
- Fix CREATE SCHEMA statement that was not written to dump file.
- Fix DBMS_APPLICATION_INFO.set_action() call, old Oracle version
do not support named parameters.
- Fix duplicate index name on partition. Thanks to buragaddapavan
for the report.
- Add support to new configuration directive PG_VERSION to control
the behavior of Ora2Pg following PostgreSQL version.
- Fix error in creation of default partition with PostgreSQL 10.
Thanks to buragaddapavan for the report.
- Fix missing export of single MAXVALUE partition, this will produce
the following range partition: ... FOR VALUES FROM (MINVALUE) TO
(MAXVALUE) Previous behavior was to not export partition as it is
better to not partition the table at all. However it is declared
in Oracle so it is better to export it to see what can be done.
Thanks to buragaddapavan for the report.
- Do not export foreign keys that reference a partitioned table.
Remove NOT VALID on foreign keys defined on a partitioned
table if present. Thanks to Denis Oleynikov for the report.
- Fix export of BFILE as bytea. Ora2Pg now use a PL/SQL function to
extract the content of a BFILE and generate a bytea data suitable
for insert or copy into PostgreSQL. Thanks to RickyTR for the
report.
- Add TIMEZONE_REGION and TIMEZONE_ABBR to migration assessment, no
direct equivalent in PostgreSQL. Remove NSLSORT not used in
migration assessment. Thanks to buragaddapavan for the report.
- Fix output of multiple export type specified in TYPE directive.
- Rewrite and renaming of _get_sql_data() function into
_get_sql_statements().
- Limit CURSOR weight in migration assessment to REF CURSOR only,
other case are all covered. REF CURSOR might need a review to see
if they need to be replaced with a SET OF RECORD.
- Fix replacement of EMPTY_CLOB() or EMPTY_BLOB() with empty string
when EMPTY_LOB_NULL is disabled and NULL when it is enabled.
- Prefix output file with the export type in multiple export type
mode, ex: sequence_output.sql or table_output.sql. Thanks to
buragaddapavan for the report.
- Fix export of data from an Oracle nested table. Thanks to rejo
oommen for the report.
- Removed cast to timestamp from partition range. Thanks to
buragaddapavan and rejo-oommen for the report.
- Fix partition default syntax. Thanks to rejo-oommen for the
report.
- Apply missing SYSUSERS schemas exclusion on columns and partition
listing. Thanks to rejo-oommen for the report.
- Add warning about parameter order change in output file.
- Show table name on Oracle side during export using at connection
time: DBMS_APPLICATION_INFO.SET_ACTION(table_name);
Thanks to Denis Oleynikov for the feature request.
- Report change in ORA_RESERVED_WORDS into documentation.
- Add references in the keyword list of ORA_RESERVED_WORDS.
- Fix the missing white space in some lines while creating
import_all.sh file. Thanks to Fabiano for the patch.
- Fix translation of infinity value for float. Thanks to Damien
Trecu for the report.
- Fix default value in timestamp column definition when a timezone
is given. Thanks to buragaddapavan for the report.
- Fix missing export of index and constraint in a partitioned
table when DISABLE_PARTITION is enabled. Thanks to Denis Oleynikov
for the report.
- Prevent PARTITION BY when DISABLE_PARTITION is enabled. Thanks to
Denis Oleynikov for the report.
- Add DATA_EXPORT_ORDER configuration directive. By default data
export order will be done by sorting on table name. If you have
huge tables at end of alphabetic order and are using multiprocess,
it can be better to set the sort order on size so that multiple
small tables can be processed before the largest tables finish.
In this case set this directive to size. Possible values are name
and size. Note that export type SHOW_TABLE and SHOW_COLUMN will
use this sort order too, not only COPY or INSERT export type.
Thanks to Guy Browne for the feature request.
- Fix remove leading ':' on Oracle variable taking care of regex
character class. Thanks to jselbach for the report.
- Add NO_BLOB_EXPORT configuration directive. Exporting BLOB could
take time and you may want to export all data except the BLOB
columns. In this case enable this directive and the BLOB columns
will not be included into data export. The BLOB column must not
have a NOT NULL constraint. Thanks to Ilya Vladimirovich for the
feature request.
- Fix incorrect rewrote of the first custom type in a row. Thanks
to Francesco Loreti for the patch.
- Remove double quote in type definition en set type name in lower
case when PRESERVE_CASE is disabled.
- Add PREFIX_SUB_PARTITION to enable/disable sub-partitioning table
prefixing in case of the partition names are a part of the sub-
partition names.
- Fix epoch replacement case in CREATE TABLE statements.
- Apply epoch replacement to default value in table declaration.
- Add special replacement for case of epoch syntax in Oracle:
(sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy'))*24*60*60
is replaced by the PostgreSQL equivalent:
(extract(epoch from now()))
Thanks to rejo-oommen for the feature request.
- A few typos in --help sections. Thanks to Christophe Courtois
for the report.
- Fix export of primary key on partition table. Thanks to chmanu
for the patch.
- Fix malformed user defined type export. Thanks to Francesco Loreti
for the report.
2018 09 27 - v19.1
This release fix several issues reported during the last month and
add support to PostgreSQL 11 HASH partitioning.
It also adds some new features and configuration directives:
* Add export of default partition and default sub partition.
* Add export of HASH partition type.
* Add support of stored procedure object.
* Add replacement of NLSORT in indexes or queries. For example:
CREATE INDEX test_idx ON emp
(NLSSORT(emp_name, 'NLS_SORT=GERMAN'));
is translated into
CREATE INDEX test_idx ON emp
((emp_name collate "german"));
The collation still need to be adapted, here probably "de_DE".
NLSSORT() in ORDER BY clause are also translated.
* Prevent duplicate index with primary key on partition to be
exported.
* PostgreSQL native partitioning does not allow direct import of
data into already attached partitions. We now force direct import
into main table but we keep Oracle export of data from individual
This release also adds two new command line options:
--oracle_speed: use to know at which speed Oracle is able to send
data. No data will be processed or written
--ora2pg_speed: use to know at which speed Ora2Pg is able to send
transformed data. Nothing will be written
Use it for debugging purpose. They are useful to see Oracle speed to
send data and at what speed Ora2Pg is processing the data without
reaching disk or direct import into PostgreSQL.
Two new configuration directive has been added:
* PG_SUPPORTS_PROCEDURE : PostgreSQL v11 adds support to stored
procedure objects. Disabled by default.
- PARALLEL_MIN_ROWS: set the minimum number of tuples in a table
before calling Oracle's parallel mode during data export.
Default to 100000 rows.
Note that PG_SUPPORTS_PARTITION and PG_SUPPORTS_IDENTITY are now
enabled by default to use PostgreSQL declarative partionning and
identity column instead of serial data type.