forked from darold/ora2pg
-
Notifications
You must be signed in to change notification settings - Fork 0
/
changelog
6998 lines (6471 loc) · 366 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
2024 03 29 - v24.3
This release fix several issues reported since last release and adds some
new features and improvements.
* Add option control the "prefetch" used by oracle_fdw COPY/INSERT
Prior to this change Ora2Pg uses the default "prefetch" of oracle_fdw,
which at the time of writing is 50. Allowing this to be controlled by an
Ora2Pg configuration/option gives the option of increased performance at
the cost of some additional memory on the PostgreSQL side. Thanks to Martin
Nash for the patch.
* Modify the behavior of triggers export with EXPORT_INVALID. It used to
apply to ENABLED or DISABLED triggers instead of real VALID or INVALID
triggers. Export of INVALID triggers will be controlled by EXPORT_INVALID
like others objects like functions, packages, etc. This mean that disabled
triggers that are valid will be exported by default now, this was not
the case before. Thanks to dcgadmin for the feature request.
* Add new configuration directive PGTT_NOSUPERUSER. By default the pgtt
extension is loaded using the superuser privilege when EXPORT_GTT is
activated. Enabled it if you run the SQL scripts generated using a non
superuser user. It will use:
LOAD '$libdir/plugins/pgtt';
instead of default:
LOAD 'pgtt';
Thanks to Simon Martin for the feature request.
Here is the full list of changes and acknowledgements:
- Fix Inf replacement that must only be done with numeric datatype. Thanks to
gael-efluid for the report.
- Fix some replacement of OUTER JOIN (+). Thanks to Carens Kurniawan Wijaya
for the report.
- Fix schema filter with test function count. Thanks to dcgadmin for the
report.
- Fix TEST count objects when a table name is modified. Thanks to korolan
for the report.
- Fix issue with multi style comments. Thanks to newtglobal.com for the patch.
- Fix documentation about EXPORT_INVALID to precise that it also concern
disabled triggers. Thanks to dcgadmin for the report.
- Fix missing FOR EACH clause in trigger export after a regression introduced
by commit fb6b0ad. Thanks to Carens Kurniawan Wijaya for the report.
- Fix data export for table with a geometry column. Thanks to ruralqiu for the
report.
- Revert changes introduced by commit fc7008c, for some obscurs Oracle reasons
the DBA_SDO_GEOM_METADATA doesn't always exist.
Always uses ALL_SDO_GEOM_METADATA instead. Thanks to ruralqiu and
Pierre3939 for the report.
2024 03 07 - v24.2
This release fix several issues reported since last release and adds some
new features and improvements.
* Allow DATA_EXPORT_ORDER to take a filename at value to be able to
give a custom table order. The file must contain the ordered list
of the tables. One table per line in upper case for Oracle. Thanks
to DataCloudGaze for the feature request.
* Add progress bar when --oracle_speed is used to avoid waiting for
the whole data export ends.
* Add replacement of the BITAND function by the & operator
* Add option -f, --format to set the output format for the reports.
It can be html or json. Default to html. Thanks to mgole001 for the
feature request.
* Add automatic addition of the partition key to the primary key. Prefix
all columns with the alias in the query to get data from Oracle.
* Add information about MSSQL masked columns in SHOW_COLUMN.
* Add information about columnstore and compression on MSCSQL tables with
action SHOW_TABLE.
* Add new configuration directive PARTITION_BY_REFERENCE to defined
how to export Oracle partition by reference. Possible values are none,
duplicate or the number of hash partitions to create.
Value 'none' mean no translation and export of partition by reference
like before. Value 'duplicate' will duplicate the referenced column
in the partitioned table and apply the same partitioning from the
referenced table to the partitioned table. If the value is a number,
the table will be partitioned with the HASH method using the value
as the modulo. For example if you set it to 4 it will create 4 HASH
partitions.
Default is none to not export the partitions by reference definition.
Here is the full list of changes and acknowledgements:
- Fix default values in MSSQL function declaration and missing END keyword.
- Fix parsing of MSSQL function with a single query. Thanks to Saravanan Newt
for the report.
- Fix negative Oracle decimal when comparing data. Thanks to es99-24 for the
report.
- Fix typos in documentation. Thanks to Simon Martin for the report.
- Avoid doubling the NAME keyword in the XMLELEMENT() function.
- Remove clause "REFERENCING OLD AS OLD NEW AS NEW" that is useless and throw
an error in PostgreSQL
- Fix DSN for MSSQL in autogenerated configuration file using --init_project.
- Fix parsing of REFERENCING clause in trigger. Thanks to Carens Kurniawan
Wijaya for the report.
- Fix case where ALL_TAB_COLUMNS and ALL_SDO_GEOM_METADATA was used instead
of USER_TAB_COLUMNS. Thanks to rvanouter for the report.
- Fix double quoting in partition by reference where clause.
- Disallow setting of PARTITION_BY_REFERENCE to duplicate when FDW_SERVER
is set.
- Fix trigger export with duplicate FOR EACH clause when a REFERENCING clause
is present. Thanks to Carens Kurniawan Wijaya for the report.
- Fix typo in variable name in ora2pg_scanner
- Fix export of default partition for MySQL. Thanks to Priyanshi Gupta for
the report.
- Fix TEST_DATA action with issues on foreign server and import foreign schema
declaration. Thanks to Florent Jardin for the report.
- Fix export of NOT NULL constraint with column replacement. Thanks to Florent
Jardin for the report.
- Prevent reading file ora2pg_stdout_locker when it does not exist. Thanks to
Florent Jardin for the report.
- Fix translation of timestamp with precision < 6. Thanks to Andrei Briukhov
for the report.
- Fix json_arrayagg with returning clause, type was not translated.
- Fix data export for table partitioned by reference with duplicate method.
The query to extract data on Oracle side add the duplicated column and
perform the join with the referenced table using the FK definition.
- Fix partition by LIST export with a useless cast to text. Thanks to
Priyanshi Gupta for the report.
- Added closing curly bracket for object details. Thanks to andreas42 for
the patch.
- Added quotes around value of "human days cost" and "migration level".
Thanks to andreas42 for the patch.
- Format object type detail output as JSON array. Thanks to andreas42 for
the patch.
- Fix variable declaration in previous commit
- Fix MSSQL table export with nonexistent column auto_created in version
bellow 2017. Thanks to Florent Jardin for the report.
- Use ADD CONSTRAINT syntax to specify name of primary key. Thanks to Martin
Karlgren for the patch.
- Skip unwanted work on PG database when option --oracle_speed is enabled,
especially drop of constraints. Thanks to John Tian for the report.
- Remove trailing ); from primary key statements when reading from file.
Thanks to Martin Karlgren for the patch.
- Fix option in CREATE USER MAPPING for export of DBLINK.
- Fix translation of MSSQL floating point datatype to use float(n) notation.
- Exclude from assessment objects in Oracle bin.
- Add Oracle package HTP and HTF to migration assessment.
- Fix MSSQL foreign key export with multiple columns referenced.
- Fix MSSQL export of unique constraints that was merging columns of several
unique constraints for the same table into a single constraint.
- Fix case of MSSQL datetime default value 0 that must be converted to
'1900-01-01 00:00:00'
- Add rewrite of MSSQL getutcdate() function.
- Fix MSSQL index type and add compression + columnstore information
- Add clause IF NOT EXIST to all CREATE EXTENSION calls
- Fix MSSQL bit data migration through tds_fdw, it is already exported
as boolean by the fdw.
- Fix duplicated indexes in MSSQL export.
- Add export of MSSQL indexes with columns included (CREATE INDEX+INCLUDE).
2023 09 08 - v24.1
This release fix several issues reported since last release and adds some
new features and improvements.
- Replace "set feedback off" by "\set QUIET on;" and "set pagesize 0" with
"\pset pager off". Thanks to Martin Gerhardy for the suggestion.
- Always add package name into search_path of packages functions. Thanks to
janopha for the report.
- Allow to specify a password file to set PG_PWD at PostgreSQL database
connection. If the specified file exists on the system, Ora2Pg will read
the first line to get the password at each call of send_to_pgdb(). It can
be useful in some situation where the password change during data migration.
Thanks to Marius Hope for the feature request.
- Added option --dump_as_json and fixed some json output errors. Thanks to
Martin Gerhardy for the patch.
Here is the full list of changes and acknowledgements:
- Fix schema prefixing of type created in packages stored procedures.
Thanks to janopha for the report.
- Fix perl function get_schema_condition() to use quote_ident() in generated
SQL filters. Thanks to franxav06 for the report.
- Attempt to better test the not null constraint count in Oracle.
- Fix regression with PSQL_RELATIVE_PATH. Thanks to Ryan Taylor for the
report.
- Do not add the partition key to PK if DISABLE_PARTITION is enabled. Thanks
to pavel-moskotin-db for the report.
- Filter list of indexes on name instead of the GENERATED column because
we are missing the ones that have been created automatically by the
Automatic Indexing feature of Oracle 19c. Thanks to Franck Pachot for
the report.
- Fix incompleteness in GRANT action, add grant usage on schema to owner and
users. Thanks to elexus for the report.
- Apply missing --blob_to_lo to import_all.sh script.
- Fix regression in MySQL hash partitions export. Thanks to Shubham Dabriwala
for the report.
- Fix Oracle INTERVAL data export with negative value. Thanks to shubham-yb
for the report.
- Fix double count of not null constraints for TEST action. Thanks to Simon
Pane for the patch.
- Fix replacement of Oracle sys_refcursor in function return type.
- Fix replacement of SQL script setting from Oracle.
- Make scripts executable. Thanks to Martin Gerhardy for the patch.
- Exclude data pump SYS_EXPORT_SCHEMA_.* tables from Oracle export.
- ora2pg: use env based shebang for perl. Thanks to Martin Gerhardy for
the patch.
- Add unsupported clause message for PRAGMA AUTONOMOUS_TRANSACTION when it is
not rewritten by Ora2Pg. Thanks to Martin Gerhardy for the patch.
- Allow schema specific definitions of partitioning columns. Thanks to Martin
Gerhardy for the patch.
- Fixed invalid variable name in read_grant_from_file. Thanks to Martin
Gerhardy for the patch.
- Fix not double quoted column in alter set not null column statement.
Thanks to leonteq-reisg for the patch.
- Bug fixes and special treatment for Types with body. Thanks to Martin
Gerhardy for the patch.
- Avoid redundant definition of the version. Thanks to Martin Gerhardy for
the patch.
- Fix a MySQL exception when the column type is ENUM for version < 5.7.
Thanks to Code-UV for the report.
2023 07 05 - v24.0
This major release adds support to migration of SQL Server database to
PostgreSQL. It also fixes several issues reported since past height months
and adds some new features and improvements.
* Enable the use of ALLOW/EXCLUDE directive with SHOW_* reports and throw
a fatal error if global filters in ALLOW/EXCLUDE are set.
* Add replacement of DBMS_LOCK.SLEEP with pg_sleep
* Split estimate cost details per function/procedure/and package function.
* Add cmin, cmax, ctid to reserved keywords list.
* Add cost for presence of ADD CONSTRAINT in PLSQL code. It needs constraint
name stability.
* Add CLOB_AS_BLOB configuration directive to treat CLOB as BLOB when
exporting data. When enabled Ora2Pg will apply same behavior on CLOB
than BLOB with BLOB_LIMIT setting. This could be useful if you have
large CLOB data. Enabled by default. Thanks to Omar Mebarki for the patch.
* Allow COPY and TABLE type to use the NULLIF construct. Thanks to Luke Davies
for the patch.
* Add new SEQUENCE_VALUES export type to export DDL to set the last values
of sequences from current Oracle database last values like the following
statements: ALTER SEQUENCE departments_seq START WITH 290;
Thanks to sergey grinko for the feature request.
* Add replacement of Oracle variable : varname into PG :'varname'.
* Add SQL Server migration to Ora2Pg. Most of the SQL Server objects are
supported as well as data export. Translation of the TSQL stored
procedures to plpgsql is complicated because of the lack of statement
separator in TSQL but as usual Ora2Pg is doing is best to do as much
work as possible. Migration assessment is also possible with SQL Server
database. There is some dedicated configuration directives added to
ora2Pg.conf.
* Add support to MySQL PARTITION BY KEY() with a translation to HASH
partitioned table using the PK/UK definition of the table or the
columns specified in the KEY() clause. Thanks to Shubham Dabriwala
for the report.
* Make EXPORT_INVALID configuration directive works with TRIGGER export.
Until now disabled triggers were not exported, setting EXPORT_INVALID
to 1 will force the export of disabled triggers. Thanks to chetank-yb
for the report.
* Add support of MySQL generated default value on update. For example:
CREATE TABLE t1 (
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Ora2Pg will translate this syntax into a trigger on the table to force
the value of the column on an update event.
Thanks to heysky for the report.
* Add translation of ST_GEOMETRY data type to PostGis geometry datatype.
* Replace ROWNUM in target list with a "row_number() over ()" clause. Thanks
to Rui Pereira for the report.
New configuration directives:
* Add configuration directive ST_GEOMETRYTYPE_FUNCTION to be able to set the
function to use to extract the geometry type from a ST_Geometry column.
Default: ST_GeometryType, example it should be set to sde.ST_GeometryType
for ArcSDE. Thanks to Albert88373 for the report.
* Add four new configuration directive to be able to change or prefix the
functions used to extract information from ST_Geometry object and values.
- ST_SRID_FUNCTION: Oracle function to use to extract the srid from
ST_Geometry meta information. Default: ST_SRID, for example it should be
set to sde.st_srid for ArcSDE.
- ST_DIMENSION_FUNCTION: Oracle function to use to extract the dimension
from ST_Geometry meta information. Default: ST_DIMENSION, for example it
should be set to sde.st_dimention for ArcSDE.
- ST_ASBINARY_FUNCTION: Oracle function to used to convert an ST_Geometry
value into WKB format. Default: ST_ASBINARY, for example it should be set
to sde.st_asbinary for ArcSDE.
- ST_ASTEXT_FUNCTION: Oracle function to used to convert an ST_Geometry
value into WKT format. Default: ST_ASTEXT, for example it should be set
to sde.st_astext for ArcSDE.
Thanks to Albert88373 for the report.
* Add INSERT_ON_CONFLICT configuration directive. When enabled this instruct
Ora2Pg to add an ON CONFLICT DO NOTHING clause to all INSERT statements
generated for this type of data export. Thanks to Clemens Rieder for the
feature request.
Backward compatibility:
* Change the behavior of CASE_INSENSITIVE_SEARCH to allow the use of a
collation instead of the citext extension. To disable the feature the
value none can be used. If the migration is not MSSQL this feature is
disabled.
* Remove PREFIX_PARTITION configuration directive, it is now replaced by
the RENAME_PARTITION directive. Previous behavior was to construct the
partition name from the table name, the partition name and the sub
partition name if any. The problem is that we often reach the max length
for an object name and this leads to duplicate partition name. Now, when
RENAME_PARTITION is enabled the partition tables will be renamed
following rules:
<tablename>_part<pos>
where "pos" is the partition number. For subpartition this is:
<tablename>_part<pos>_subpart<pos>
If this is partition/subpartition default:
<tablename>_part_default
<tablename>_part<pos>_subpart_default
This change will break backward comaptibilty, if PREFIX_PARTITION is
still set, it will simply enable RENAME_PARTITION.
* Set START value to MINVALUE when a sequence is cycled and that the START
value is upper that MAXVALUE. Thanks to Shane Borden for the report.
Here is the full list of changes and acknowledgements:
- Fix MODIFY_STRUCT that was not working with MySQL. Thanks to Code-UV for
the report.
- Fix license string in Makefile.PL. Thanks to RodRaen for the report.
- Do not remove non alphanumeric character in index name. Thanks to gwidt
for the report.
- Reorder trigger event when the update of column is not the last one. Thanks
to tayalarun1 for the report.
- Fix export of MySQL function containing special characters and white spaces
in names. Thanks to Shubham Dabriwala for the report.
- Fix grant export for partitions. Thanks to elexus for the report.
- Add some other transformation for sqlplus/psql scripts.
- Remove comma as possible separator for values in DEFINED_PK, it was
preventing the use of a function with multiple parameters.
- Fix export of geometry tables when PG_SCHEMA is set.
- Add rewriting of some sqlplus settings to psql settings.
- Fix TABLESPACE export for partitioned tables. Thanks to elexus for the
report.
- Fix for Issue #1637. Thanks to Simon Pane for the patch.
- Fix typo in --init_project directories tree generation for sequences
values.
- Fix alias in view target list for function call without alias defined in
MySQL export. Thanks to Shubham Dabriwala for the report.
- Fix Mysql procedure export when a datatype with precision is used in
parameter list. Thanks to Shubham Dabriwala for the report.
- Fix collation on string default values. Thanks to Shubham Dabriwalafor
the report.
- Exclude recycle bin object from ALL_TAB_COLUMNS lookup. Thanks to Dave
Betterton for the report.
- Fix data types translation (TINYINT|SMALLINT|MEDIUMINT|INTEGER|BIGINT|INT)
for MySQL table export. Thanks to Shubham Dabriwala for the report.
- Do not export synonym destination table with table_owner when EXPORT_SCHEMA
is disabled. Thanks to Priyanshi Gupta for the report.
- Fully qualify calls to get_sequence_last_values() when PG_SCHEMA is set.
Thanks to Marius Hope for the report.
- Fix regression on exporting view as table when VIEW_AS_TABLE contains
regexp. Thanks to Neil Bower for the report.
- Fix missing execution of initial command statements at start of TEST_DATA
action and on both side, those applying to source and destination. Thanks
to Petter Jacobsen for the report.
- Fix script to get sequence last value with TEST action. Thanks to franxav06
for the patch.
- Prepend PERFORM before call to DBMS_OUTPUT.* when USE_ORAFCE is enabled.
- Disable USE_ORAFCE when export type is SHOW_REPORT.
- Extending the enhancement in Pull Request #1621 to the Oracle_FDW user
mapping. Thanks to Simon Pane for the patch.
- Changed prefix string to "DIFF:" in test report. Thanks to Simon Pane for
the patch.
- Fix cases where %ROWCOUNT was not correctly replaced. Thanks to Rui Pereira
for the report.
- Fix parsing of ORACLE_DSN when creating foreign server in COPY mode. Thanks
to Luke Davies for the report.
- Fix for Issue #1622, #1627. Thanks to Simon Pane for the patch.
- Fix index creation with DESC order in COPY action when DROP_INDEXES is
enabled. Thanks to Luke Davies for the report.
- Fix for Issue #1610, #1612, #1617 and #1381. Thanks to Simon Pane for the
patch.
- Fix typo in sqlnet.ora name (was sqlnet.or). Thanks to Martin Nash for the
patch.
- Fix data export, REPLACE_QUERY was not applied. Thanks to Bachev Constantin
for the report.
- Fix call to replace_sys_context().
- Fix timestamp(n) data type translation.
- Remove use of column GENERATION_EXPRESSION for MySQL version < 5.7.0. Thanks
to Hans Choi for the report.
- Fix conversion of DATE datatype to timestamp(0) instead of timestamp. Thanks
to Akhil Reddy for the report.
- Add NVARCHAR/NCHAR defaut convertion data types to DATA_TYPE configuration
directive in ora2pg.conf comments. Thanks to Akhil Reddy for the report.
- Rename method _get_partitions_type function into _get_partitions_list.
- Fix synonym export when no schema information is available.
- Fix support of REFERENCING clause in triggers.
- Fix partition output file renaming with new RENAME_PARTITION directive.
Thanks to Rahul Barigidad for the report.
- Fix export of the ROWNUM clause when there is a variable.
- Fix sprintf placeholders in geometry queries.
- Fix some others issues with row count report.
- Fix row count with destination schema and when the PostgreSQL table
doesn't exist.
Thanks to bizen-ya for the report.
- Fix tests comparison with the different settings of EXPORT_SCHEMA,
SCHEMA and PG_SCHEMA. Thanks to Marius Hope and bizen-ya for the
report.
- Fix St_AsText() call for MySQL data extraction.
- Add column count comparison for MySQL
- Export multi column partition by list as an expression with concat
operator. Multi column partition by list is not supported by PostgreSQL.
- Fix creation of non existant indexes on partition. Thanks to Shubham
Dabriwala for the report.
- Fix MySQL function export when there is no BEGIN clause. Thanks to
Shubham Dabriwala for the report.
- Fix MySQL export of unsigned numeric. Thanks to Shubham Dabriwala for
the report.
- Fix MySQL output with wrong synthax for JOIN without ON clause. Thanks
to Shubham Dabriwala for the report.
- Fix virtual column export. Thanks to Rafal Hollins for the report.
- Fix index creation on partition with no columns for MySQL export.
Thanks to Shubham Dabriwala for the report.
- Fix export of MySQL auto_increment when PG_INTEGER_TYPE is disabled.
Thanks to Shubham Dabriwala for the report.
- Fix MySQL subpartition export. Thanks to Sanyam Singhal for the report.
- Move any INTO clause in CONNECT BY query to the final SELECT on the
resulting CTE. Thanks to taptarap for the report.
- Fix translation of MySQL curtime() function in default values. Thanks
to Shubham Dabriwala for the report.
- Fix possible "Nested quantifiers in regex" error when exporting package
with package name containing regex special characters. Thanks to durandm70
for the report.
- Fix documentation about use of unique key for ORACLE_COPY.
- Fix extra comma at end of a CHECK contraint. Thanks to Shubham Dabriwala
for the report.
- Always add DROP TYPE statements with package export even if DROP_IF_EXISTS
is not enabled. Thanks to Rui Pereira for the report.
- Fix default value of simple dot in MySQL export. Thanks to Shubham
Dabriwala for the report.
- Fix regression in data type translation after fix on unsigned numeric type.
Thanks to Shubham Dabriwala for the report.
2022 10 08 - v23.2
This release fix several issues reported since past height months and
adds some new features and improvements.
* Add export of MySQL KEY and LINEAR KEY partitioning, translated as HASH
partitioning.
* Allow export of object with dollar sign is his name.
* Add export of CHECK constraints for MySQL >= 8.0.
* Add Functional/Expression indexes export from MYSQL.
* Add export of MySQL virtual column. Thanks to Shubham Dabriwala for the
* Remove scale and precision of a numeric if the scale is higher than the
precision. PostgreSQL does not support decimal/numeric attributes where
the scale is explicitly higher than the precision.
* Add command line option --drop_if_exists to add statement to drop objects
before creation if it exists. It corresponds to the DROP_IF_EXISTS
* Add option -C | --cdc_file to be able to change the name of the default file
used to store/read SCN per table during export. Default is TABLES_SCN.log in
the current directory. This is the file written by the --cdc_ready option.
* Add multiprocess to count rows in PostgreSQL tables (TEST_COUNT) using -P
command line option.
* Add support to PostgreSQL 14 procedure with out parameters.
* Set default PostgreSQL database target version to 14.
New configuration directives:
* Add configuration directive MVIEW_AS_TABLE and command line option
--mview_as_table to set which materialized view to export as table.
By default none. Value must be a list of materialized view name or
regexp separated by space or comma. If the object name is a materialized
view and the export type is TABLE, the view will be exported as a create
table statement. If export type is COPY or INSERT, the corresponding data
will be exported.
* Add configuration variable FDW_IMPORT_SCHEMA to rename the schema where
foreign tables for data migration will be created. If you use several
instances of ora2pg for data migration through the foreign data wrapper,
you might need to change the name of the schema for each instance.
Default: ora2pg_fdw_import
* Add TRANSFORM_VALUE configuration directive to apply an expression when
retrieving data from Oracle. For example:
TRANSFORM_VALUE CLOB_TABLE[CHARDATA:translate("CHARDATA", chr(0), ' ')]
to replace all Oracle char(0) in a string by a space character.
* Add EXCLUDE_COLUMNS configuration directive. Unlike MODIFY_STRUCT
that is used to redefine a table structure, this directive allow
to specify a list of columns per table that must be excluded from
the export. For example:
EXCLUDE_COLUMNS T1(nocol1,nocol2) T2(nocol1,nocol2)
* Add new configuration directive EXPORT_GTT to export Oracle Global Temporary
Table using syntax recognized by the pgtt extension. For more information see
https://github.com/darold/pgtt Default is to not export global temporary
table as they are not supported natively by PostgreSQL.
* Add new configuration option NO_EXCLUDED_TABLE. By default Ora2Pg exclude
from export some Oracle "garbage" tables that should never be part of an
export. This behavior generates a lot of REGEXP_LIKE expressions which are
slowing down the export when looking at tables. To disable this behavior
enable this directive, you will have to exclude or clean up later by
yourself the unwanted tables. The regexp used to exclude the table are
defined in the array @EXCLUDED_TABLES in lib/Ora2Pg.pm. Note this is behavior
is independent to the EXCLUDE configuration directive.
Backward compatibility:
* Force rewrite of all invalid date starting with zero year 0000 into 1970-01-01
when it is a default value and NULL for data. Old behavior was to only replace
0000-00-00 date.
* Until now there was a lot of untranslated call to TRUNC(date) because
Ora2Pg is unable to detect that the parameter is a date or a number.
The problem is that Oracle has TRUNC(number) too and Ora2Pg try to not
apply the transformation if there is a doubt. In most of the migration
have met very few TRUNC(number) so now all call to TRUNC()
will be converted to date_trunc(). There must be false positive rewrite
but this should be far less work than the actual situation.
Here is the full list of changes and acknowledgements:
- Fixed PostgreSQL "relation not found error" in _dump_fdw_table(), PostgreSQL
search_path was not being used. Thanks to James Schriever for the patch.
- Fix year and month quoting as reserved words when they are used as aliases.
Thanks to duursma for the report.
- Fix conversion of to_number(substr(...)) when PG substr() return empty
string where Oracle return NULL which make the conversion to numeric fail.
The fix using (nullif(substr(...), )::numeric) only concern TABLE export.
Thanks to Menelaos Perdikeas for the report.
- Add export of MySQL KEY and LINEAR KEY partitioning, now exported as HASH
partitioning. Thanks to Sanyam Singhal for the report.
- Remove $ sign from characters that require object name quoting.
- Fix export of objects with the $ sign in the name. Thanks to yano-rxa and
duursma for the report.
- Prevent translation of EXEC when used as alias. Thanks to Rui Pereira for
the report.
- Fix MySQL enum data type export with regression introduced by commit 24a476.
Thanks to Shivansh Gahlot for the report.
- Rename ORACLE_FDW_TRANSFORM to TRANSFORM_VALUE to apply in all case an
expression when retrieving data from Oracle. For example:
TRANSFORM_VALUE CLOB_TABLE[CHARDATA:translate("CHARDATA", chr(0), ' ')]
to replace all Oracle char(0) in a string by a space character.
- Fix add_month() translation failing with some use cases. Thanks to duursma
for the report.
- Add export of CHECK constraints for MySQL >= 8.0. Thanks to Rahul
Barigidad for the report.
- Fix MySQL unsigned numeric data type conversion. Thanks to Rahul Barigidad
for the report.
- Add Functional/Expression indexes fail while exporting from MYSQL. Thanks to
Shubham Dabriwala for the report.
- Fix export of descending indexes for MySQL. Thanks to Shubham Dabriwala for
the report.
- Force MySQL auto increment sequence to start at 1 when value is 0.
Thanks to Rahul Barigidad for the report.
- Fix conversion of MySQL decimal(p,s) to keep the original datatype
instead of use of real or double.
- Add export of MySQL virtual column. Thanks to Shubham Dabriwala for the
report.
- Fix export of MySQL function with return clause only. Thanks to Shubham
Dabriwala for the report.
- Remove scale and precision of a numeric if the scale is higher than the
precision. PostgreSQL does not support decimal/numeric attributes where
the scale is explicitly higher than the precision. Thanks to Rahul Barigidad
for the report.
- Fix export of comment for views. Thanks to gh-k-murata for the report.
- Add command line option --drop_if_exists to add statement to drop objects
before creation if tehy exists. It corresponds to the DROP_IF_EXISTS
configuration directive. Thanks to Yoni Sade for the feature request.
- Add option --mview_as_table to documentation.
- Add configuration directive MVIEW_AS_TABLE and command line option
--mview_as_table to set which materialized view to export as table.
By default none. Value must be a list of materialized view name or
regexp separated by space or comma. If the object name is a materialized
view and the export type is TABLE, the view will be exported as a create
table statement. If export type is COPY or INSERT, the corresponding data
will be exported.
- Disable EXPORT_GTT when export type is not TABLE. Thanks to gh-k-murata for
the report.
- Fix generated external servers wrongly placed in a schema. Thanks to duursma
for the report.
- Add configuration variable FDW_IMPORT_SCHEMA to rename the schema where
foreign tables for data migration will be created. If you use several
instances of ora2pg for data migration through the foreign data wrapper,
you might need to change the name of the schema for each instance. Default
to ora2pg_fdw_import. Thanks to James Schriever for the feature request.
- Fix wrong conversion of rownum clause when a subquery is used. Thanks to
Rui Pereira for the report.
- Escape comma and backslashes in BFILE data export. Thanks to duursma for
the patch.
- Fix possible infinite loop in Oracle outer join parsing. Thanks a lot to
yano-rxa for the report.
- Remove privileges default settings on views exported from SYNONYMs
- Add support for Rectangle geometry type. Thanks to duursma for the patch.
- Fix double replacement of IS NULL/IS NOT NULL when NULL_EQUAL_EMPTY is
enabled.
- Add CHECK not null only constraints to not null constraint count.
- Fix CHECK NOT NULL only constraints that was not exported by generating
NOT NULL constraints instead. They are exclude from the count of CHECK
constraint as suggested by Florent Jardin but a count difference persist
for NOT NULL constraints.
- Fix TYPE export when SCHEMA and PRESERVE_CASE have different values. Thanks
to Florent Jardin for the report.
- Fix custom exception replacement. Thanks to Rui Pereira for the report.
- Fix Collection and Polygon geometry INTERNAL export.
Thanks to duursma for the patch.
- Fix export of efile with parenthesis. Thanks to duursma for the report.
- Fix wrong column indices used in spatial index extraction. Thanks to duursma
for the report.
- Fix call of ST_GeomFromText() with WKT export. Thanks to duursma for the
report.
- Disable USE_LOB_LOCATOR with WKT geometry export type, ST_GeomFromText and
SDO_UTIL.TO_WKTGEOMETRY functions return a CLOB instead of a geometry.
Thanks to duursma for the report.
- Fix INTERNAL conversion uses the srid from the object instead of the
meta-data. Thanks to duursma for the report.
- Fix regression in data export when REPLACE_AS_BOOLEAN is set. Thanks to
Juri Berlanda for the report.
- Fix call to procedure using dblink. Thanks to Rui Pereira for the report.
- Keep untouched call to DBMS_OUTPUT functions if USE_ORAFCE is enabled.
Thanks to Sanyam Singhal for the report.
- Partial fix for MySQL subpartitioning export.
- Fix partitions export for MySQL. Thanks to Sanyam Singhal for the report.
- Fix generation of export_all.sh following the operating system.
- Add information of use of PARALLEL_TABLES with COPY, INSERT and TEST_DATA
actions. It is also useful with TEST, TEST_COUNT, and SHOW_TABLE if
--count_rows is used for real row count.
- Prevent calling real rows count twice with TEST action, and allow it for
the SHOW_TABLE action.
- Handle count errors when single process.
- Move row count wait for all child die to the right place
- Fix rewrite of nested replace() functions in CHECK constraint. Thanks to
Menelaos Perdikeas for the report.
- Fix call of procedures with out parameters when it is not declared in a
package. Thanks to taptarap for the report.
- Some minor code improvement. Thanks to Markus Elfring for the patch.
- Set encoding to read configuration file to utf8.
- Remove useless multiple semi-colon after END of a function.
- Fix conversion of regexp_replace() by always appending the 'g' modifier.
Thanks to Rui Pereira for the report.
- Fix synonym detection to avoid listing public synonym when no schema is
specified. Thanks to Dilan Salinda for the report.
- Fix regexp error with multi-line comment in default value declaration. Thanks
to taptarap for the report.
- Add missing sub-partition key in partitioned table primary key. Thanks to
downvoteit for the report.
- Replace all invalid date starting with zero year 0000- to 1970-01-01 when it
is a default value and NULL for data. Old behavior was to only replace
0000-00-00 date. Thanks to duursma for the report.
- Enclose \i path to data file beween quote to fix import of table with space
in their name.
- Add PARTITION to the list of reserved work and fix custom keywords list from
ORA_RESERVED_WORDS that was not applied. Thanks to markhooper99 for the
report.
- Add LOAD of pgtt extension before creating global temporary table with TABLE
:export. Thanks to duursma for the report.
- Fix case where package names should be lower cased. Thanks to Sergey Petrov
for the patch.
- Cover more case where ALTER ... OWNER TO should not be generated.
- Fix case where ALTER ... OWNER TO should not be generated when a view as
table definition was not exported.
- Fix sub-partition unique and primary keys that lacks columns part of the
partition key. Thanks to downvoteit for the report.
- Path for function_per_file are mixed case enabled now. Thanks to Sergey
Petrov for the patch.
- Fix AUTOINCREMENT script to set last value to sequences for serial an
identity column for PG version < 12. Thanks to Jaouad Bouras for the report.
- Fix detection of ENUM data type for MySQL
- Fix issue when exporting table with a geometry column. The search for
the SDO_GTYPE need a FROM clause with a FQDN table when the connection
user is not the same as the table schema. Thanks to Argo64 for the report.
- Rewrite numeric operation with ADD_MONTH(), LAST_DAY() and TRUNC() to use
interval. Thanks to duursma for the report.
- Fix rewrite of CONNECT BY in cursors and just after a BEGIN. Thanks to
taptarap for the report.
- Add partition keys to unique index on partitioned table. Thanks to
downvoteit for the report.
- Fix case where global variable are tested against NULL. Thanks to duursma
for the report.
- Fix remove of %ROWTYPE in function argument and returned data type. Add
regression test. Thanks to Eric Delanoe for the report.
- Fix case clause in autoincrement parameters. Thanks to jbouras for the
report.
- Fix typo in ORACLE_FDW_TRANSFORM example.
- Fix progress bar output in quiet mode. Thanks to Sanyam Singhal for the
report.
- Fix error Can't locate object method "gzclose" via package "IO::File".
Thanks to Sanyam Singhal for the report.
- Fix cases where translation of function with out parameter was not done
correctly.
- Fix translation of function with out parameter that returns a value. For
example the following Oracle function:
CREATE FUNCTION foo(a int, OUT b int)
RETURN boolean IS
BEGIN
b := a;
RETURN true;
END;
is now translated by adding an extra out parameter for the return value:
CREATE OR REPLACE FUNCTION foo (a integer, OUT b integer,
OUT extra_param boolean)
RETURNS record AS $body$
BEGIN
b := a;
extra_param := true;
RETURN;
END;
$body$ LANGUAGE PLPGSQL STABLE;
Thanks to Akhil Reddy for the report.
- Fix undefined database connection handle. Thanks to Alexander for the report
- Fix case preservation for row count in Oracle side with TEST_COUNT action..
Thanks to Veka for the report.
- Only generate the Powershell script when we are running on a Windows
operating system.
- Fix #1400 and generate PowerShell script "export_schema.ps1". Thanks to
moh-hassan for the report.
- Fix rewriting assignment of a global variable using SELECT INTO. Thanks to
duursma for the report.
- Fix partition export for MySQL. Thanks to Sanyam Singhal for the report.
- Apply WHERE clause to FDW data export.
- Fix useless ST geometry parsing. Thanks to jieguolove for the report.
- Replace backslash with slash in BFILE filename when destination data type is
text or efile.
- Fix RAW(16)/RAW(32) data export when MOFDIFY_TYPE is used on the column.
Thanks to Sergey Evseev for the report.
- Fix ST_SRID() call. Thanks to jieguolove for the report.
- Skip table data export when the table has no column defined. This was
generating a fatal error.
- Fix untranslated function returned data type when there was a comment just
after. The comment is removed. Thanks to taptarap for the report.
- Fix other fetching all-column-all-table properties for every table.
Thanks to Sergey Petrov for the report.
- Fix fetching all-column-all-table properties for every table. Thanks to
Sergey Petrov for the report.
- Remove any comments between RETURN and returned type to not break parsing.
Thanks to taptarap for the report.
- Fix global variables in the DECLARE section are not replaced if used with
a package name. Thanks to taptarap for the report.
- Remove renaming of dist configuration file under Windows OS. Thanks to
Julien Monticolo and ohamed Hassan for the report.
- Fix remaining data export query failure. Thanks to Sung Woo Chang for the
report.
- Fix data export, query to retrieve data was broken since change for GTT.
- Set function as VOLATILE when there is CALL in the body.
- Add support to PG14 procedure out parameters. Thanks to Rui Pereira for the
feature request.
- Fix missing parenthesis in index column expression with input file.
- Fix missing END keyword after embedded CASE clause in a package function.
Thanks to taptarap for the report.
- Fix conversion of dbms_lob.substr() where second and third parameters
must be inverted. Thanks to taptarap for the report.
- Fix an other case of wronf NOT NULL detection from input file.
- Fix detection of NOT NULL constraint in input file.
- Do not quit on error "Undefined subroutine &Ora2Pg::ReadLine", just
continue to be able to leverage an Oracle Wallet (SEPS) when no Oracle
user and password are provided. If you want to use the interactive mode
to type the username and password at command line you must install the
Perl package Term::ReadKey before. Thanks to Simon Pane for the report.
- Fix partitioning by LIST, only the first value of a list was exported.
Thanks to Sergey Grinko for the report.
- Fix quoting of DEFAULT NULL. Thanks to Veka for the report.
- Fix unwanted multiple CALL keywords. Thanks to taptarap for the report.
- Add assessment counter for FND_* packages.
- Fix LONG RAW export as bytea in COPY mode. Thanks to Helena Adiduyulmus for
the report.
- Add new configuration option NO_EXCLUDED_TABLE.
By default Ora2Pg exclude from export some Oracle "garbage" tables that
should never be part of an export. This behavior generates a lot of
REGEXP_LIKE expressions which are slowing down the export when looking at
tables. To disable this behavior enable this directive, you will have to
exclude or clean up later by yourself the unwanted tables. The regexp used
to exclude tables are defined in the array @EXCLUDED_TABLES in lib/Ora2Pg.pm
This behavior is independent to the EXCLUDE configuration directive. Thanks
to Peter Humaj for the feature request.
- Replace all remaining CURSORNAME%NOTFOUND with NOT FOUND
- Change translation to SYSDATE from LOCALTIMESTAMP to statement_timestamp()
in non PL/SQL code.
- Prevent append of SECURITY DEFINER when a procedure execute transaction
control statements (ex: COMMIT). When defined with this clause an error
is thrown. Thanks to Suman Michael for the report.
2022 02 10 - v23.1
This release fix several issues reported since past four months and
adds some new major features and improvements.
* Add use of greatest/least functions from new version of Orafce when
required to return NULL on NULL input like Oracle.
* ALLOW and EXCLUDE configuration values can now be read from a file.
Use -a filename or -e filename to specify the list of tables that need
to be filtered. This is useful if you have a lot of table to filter.
* Add possibility to use of System Change Number (SCN) for data export or
data validation by providing a specific SCN. It can be set at command
line using the -S or --scn option. You can give a specific SCN or if you
want to use the current SCN at first connection time set the value to
'current'. To use this last case the connection user must have the role
"SELECT ANY DICTIONARY" or "SELECT_CATALOG_ROLE", the current SCN is
looked at the v$database view.
Example of use:
ora2pg -c ora2pg.conf -t COPY --scn 16605281
This adds the following clause to the query used to retrieve data for example:
AS OF SCN 16605281
You can also use th --scn option to use the Oracle flashback capability by
specifying a timestamp expression instead of a SCN. For example:
ora2pg -c ora2pg.conf -t COPY --scn "TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')"
This will add the following clause to the query used to retrieve data:
AS OF TIMESTAMP TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')
or for example to only retrieve yesterday's data:
ora2pg -c ora2pg.conf -t COPY --scn "SYSDATE - 1"
* Add json output format to migration assessment. Thanks to Ted Yu for the patch.
* Add new TO_CHAR_NOTIMEZONE configuration directive to remove any timezone
information into the format part of the TO_CHAR() function. Disabled by default.
Thanks to Eric Delanoe for the report.
Note that the new default setting breaks backward compatibility, old behavior
was to always remove the timezone part.
* Add new configuration directive FORCE_IDENTITY_BIGINT. Usually identity
column must be bigint to correspond to an auto increment sequence so
Ora2Pg always force it to be a bigint. If, for any reason you want
Ora2Pg to respect the DATA_TYPE you have set for identity column then
disable this directive.
* Add command line option --lo_import. By default Ora2Pg imports Oracle BLOB
as bytea, the destination column is created
using the bytea data type. If you want to use large object instead of bytea,
just add the --blob_to_lo option to the ora2pg command. It will create the
destination column as data type Oid and will save the BLOB as a large object
using the lo_from_bytea() function. The Oid returned by the call to
lo_from_bytea() is inserted in the destination column instead of a bytea.
Because of the use of the function this option can only be used with actions
SHOW_COLUMN, TABLE and INSERT. Action COPY is not allowed.
If you want to use COPY or have huge size BLOB ( > 1GB) than can not be
imported using lo_from_bytea() you can add option --lo_import to the
ora2pg command. This will allow to import data in two passes:
1) Export data using COPY or INSERT will set the Oid destination column
for BLOB to value 0 and save the BLOB value into a dedicated file. It
will also create a Shell script to import the BLOB files into the
database using psql command \lo_import and to update the table Oid
column to the returned large object Oid. The script is named
lo_import-TABLENAME.sh
2) Execute all scripts lo_import-TABLENAME.sh after setting the
environment variables PGDATABASE and optionally PGHOST, PGPORT, PGUSER,
etc. if they do not correspond to the default values for libpq.
You might also execute manually a VACUUM FULL on the table to remove
the bloat created by the table update.
Limitation: the table must have a primary key, it is used to set the
WHERE clause to update the Oid column after the large object import.
Importing BLOB using this second method (--lo_import) is very slow so it
should be reserved to rows where the BLOB > 1GB for all other rows use
the option --blob_to_lo. To filter the rows you can use the WHERE
configuration directive in ora2pg.conf.
* Add command line option --cdc_ready to use current SCN per table when
exporting data and register them into a file named TABLES_SCN.log This
can be used for Change Data Capture (CDC) tools.
* Allow to export only invalid objects when EXPORT_INVALID is set to 2
* Disable per partition data export when a WHERE clause is define on the
partitioned table or that a global WHERE clause is defined.
Backward compatibility:
Ora2Pg used to removr any timezone information from the TO_CHAR() format
function. To recover this behavior set TO_CHAR_NOTIMEZONE to 1 in ora2pg.conf
Complete list of changes:
- Replace PERFORM by CALL when the stored procedure is a procedure. Thanks
to Rui Pereira for the report.
- Fix open cursor translation when using is in the query but not as keyword.
Thanks to taptarap for the report.
- Fix replacement of global variables in DECLARE section. Thanks to taptarap
for the report.
- Fix missing suffix in function name with autonomous transaction when export
schema was enabled and fix revoke and owner to wrapper function. Thanks to
Sergey Grinko for the report.
- Fix export of type declaration in packages without body. Thanks to Sergey
Grinko.
- Fix column name duplicates when exporting data of partition. Thanks to
Sergey Grinko for the report.
- Fix BLOB export with INSERT mode, call decode() was missing.
- Fix applying of DEFAULT_PARALLELISM_DEGREE hint that was not working
anymore for a long time. Thanks to Marcel Pils for the patch.
- Update documentation about PARALLEL_TABLES and view export. Thanks to
xinferum for the report.
- Fix unwanted quote escaping in global variable constant. Thanks to
sergey grinko for the report.
- Fix export of global variable when there is function in the default value.
- Fix end of statements in last merged PR.
- Add json output format to migration assessment. Thanks to Ted Yu for
the patch.
- Fix parsing of package when a comment follow the AS keyword. Thanks to
Eric Delanoe for the report.
- Adapt MAXVALUE for identity columns if the datatype has been changed to
integer.
- Fix a regression on data validation introduced with commit to fix data
export of virtual column.
- Fix Can't locate object method is_pk via package Ora2Pg error
- Exclude unique keys using expression to validate data.
- Fix ORDER BY clause for data validation.
- Fix error on open pragma when encoding is not set.
- Fix a regression in data export of virtual column. Thanks to Code-UV and
IgorM12 for the report.
- Fix a second regression with empty column name in target list to
retrieve data.
- Fix PG version to enable virtual column.
- Fix binmode when it is set to raw or locale to not call encoding() in
open pragma.
- Fix regression in export view as table. Thanks to Sebastian Albert for
the report.
- Update Copyright year.
- Quote tables names when necessary during TEST action.
- Fix undefined call to auto_set_encoding().
- Add test count of column per table and add output of the PG table struct
modified to be used with MODIFY_STRUCT.
- Fix handling of PRESERVE_CASE with update au sequences values
- Fix handling of PRESERVE_CASE with TEST_DATA
- Fix unwanted replacement of sysdate operation to epoch. Thanks to taptarap
for the report.
- Remove extra END clause at end of package function when a space or a
comment was present. Thanks to taptarap for the report.
- Fix missing import of module Encode. Thanks to Menelaos Perdikeas for
the report.
- Fix case where data type defined in function was not exported when
EXPORT_SCHEMA was enabled. Thanks to Eric Bourlon for the report.
- Fix missing EXECUTE on OPEN CURSOR statements. Thanks to taptarap for
the report.
- Fix missing declaration of min() function in Oracle.pm. Thanks to
nicscanna for the report.
- Fix SYSDATE subtract of seconds instead of days
- Fix PERFORM replacement in CTE. Thanks to taptarap for the report.
- Fix wrong stored procedure code conversion when use types named with
"default" and broken decode to case translation. Thanks to taptarap
for the report.
- Add missing import of FTS indexes in script import_all.sh. Thanks to
vijaynsheth for the report.
- Fix another procedure parsing with return. Thanks to Eric Bourlon for
the report.
- Fix case where parenthesis are not added to index creation.
- Add creation of the uuid extension when it is used.
- Add HTML report of tables and columns with name > 63 characters.
- Add report of DBMS_ERROR and Quartz Scheduler tables found.
- Add mark (date?) on columns of DATE data type in Oracle to check if
it should be translated into date instead of default timestamp.
- SHOW_COLUMN: mark column data type with (numeric?) when it is a NUMBER
without precision.
- SHOW_TABLE+SHOW_COLUMN: Add mark of tables and columns name > 63
characters
- Fix translation of TYPE ... AS TABLE OF ...
- Fix parsing of function call in check constraints. Thanks to Menelaos
Perdikeas for the report.
- Fix missing data export file for partitioned tables when TRUNCATE_TABLE
was disabled. Thanks to Menelaos Perdikeas for the report.
- Fix named parameter inserted in procedure call with inout parameters.
Thanks to Rui Pereira for the report.
- Fix unwanted quoting of index columns clause when there is an operation.
Thanks to Menelaos Perdikeas for the report.
- Move comment in procedure parameters before the BEGIN. Thanks to Eric
Bourlon for the report.
- Fix parsing of FOR CUSOR followed by a parenthesis. Thanks to Eric Bourlon
for the report.
- Fix parsing of TYPE ... IS REF CUSOR declaration in procedures. Thanks to
Eric Bourlon for the report.
- Add replacement of SDO_CS.TRANSFORM into ST_Transform. Thanks to mukesh3388
for the report.
- Add missing table namer to index renaming.
- Create a function for index renaming for code reuse.
- Fix support translation of type VARRAY from store procedure. Thanks to
Eric Bourlon for the report.
- Fix conversion of SQL%ROWCOUNT when part of a string concatenation. Thanks
to boubou191911 for the report.
- Remove other non alphanumeric character from index name. Thanks to Menelaos
Perdikeas for the report.
- Fix date formatting when error is logged with INSERT failure. Thanks to
xinjirufen for the report.
- Remove possible comma from index renaming.
- Fix drop of indexes with renaming when there is a function call. Thanks to
Menelaos Perdikeas for the report.
- Fix empty geometry type since the move of ORA2PG_SDO_GTYPE into
lib/Ora2Pg/Oracle.pm
- Move most of the Oracle specific code to a dedicated Perl library
lib/Ora2Pg/Oracle.pm with the same functions as lib/Ora2Pg/MySQL.pm
This will help to maintain and extend Ora2Pg to other RDMS. There is
still Oracle database related specific code in the main library but
it will be also moved later. There should not be any regression or
usage change with this huge patch.
- Fix translation of type with not null clause. Thanks to Yasir1811 for
the report.
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.