forked from darold/ora2pg
-
Notifications
You must be signed in to change notification settings - Fork 0
/
changelog
5221 lines (4793 loc) · 269 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
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 specifed 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.
Here is the full list of changes and acknowledgements:
- Fix automatic quoting of table or partition name starting with
a number. Thanks to Barzaqh for the report.
- Add information about custom directory installation. Thanks to
joguess for the report.
- Update list of action in documentation.
- Fix export of spatial geometries. Thanks to burak yurdakul for
the report.
- Fix translation of default value in CREATE TABLE DDL when using
a function. Thanks to Denis Oleynikov for the report.
- Prevent moving index on partition during tablespace export.
Thanks to Maxim Zakharov for the report.
- Fix upper case of partition name in triggers.
- Enforce KEEP_PKEY_NAMES when USE_TABLESPACE is enabled. Thanks
to Maxim Zakharov for the patch.
- Fix parsing of Oracle user login in dblink input from a file.
- Fix multiple duplication of range clause in partition export.
- Add bench of total time and rows to migrate data from Oracle
in debug mode with speed average.
- Fix sub partition prefix name.
- Fix unset oracle username when exporting DBLINK from database.
Thanks to Denis Oleynikov for the report.
- Remove NO VALID to foreign keys on partitioned table. Thanks to
Denis Oleynikov for the report.
- Fix crash of Ora2Pg on regexp with dynamic pattern base on package
code. Thank to Alain Debie and MikeCaliffCBORD for the report.
- PostgreSQL native partitioning does not allow direct import of
data into already attached partitions. When PG_SUPPORTS_PARTITION
is enable we now force direct import into main single table but
we keep Oracle export of data from individual partition. Previous
behavior was to use main table from both side. Thanks to Denis
Oleynikov for the report.
- Add the PARALLEL_MIN_ROWS configuration directive to prevent
Oracle's parallel mode to be activated during data export if the
table have less than a certain amount of rows. Default is 100000
rows. This prevent unnecessary fork of Oracle process. Thanks to
Denis Oleynikov for the feature request.
- Fix composite partition MODULUS value. Thanks to Denis Oleynikov
for the report.
- Fix count of partitions that was not including subpartition count.
- Force PostgreSQL user in FDW user mapping to be PG_USER when it is
defined.
- Sometimes Oracle indexes can be defined as follow:
CREATE INDEX idx_err_status_id
ON err_status (status_id, 1);
which generate errors on PostgreSQL. Remove column names composed
of digit only from the translation. Thanks to Denis Oleynikov for
the report.
- Move Oracle indexes or PK defined on partitioned tables to each
partition as PostgreSQL do not support UNIQUE, PRIMARY KEY,
EXCLUDE, or FOREIGN KEY constraints on partitioned tables.
Definition are created in file PARTITION_INDEXES_output.sql
generated with the PARTITION export type. Thanks to Denis
Oleynikov for the feature request.
- Fix parallel data load from Oracle partitioned tables by using
a unique alias. Thanks to Denis Oleynikov for the report.
- Fix export of composite partitioned (range/hash) table when
PG_SUPPORTS_PARTITION is disabled. Thanks to Denis Oleynikov
for the report.
- Remove composite sub partition from the list of partition, this
return a wrong partition count.
- Fix MODULUS value in hash sub partitioning.
- Index and table partitions could be on separate tablespaces.
Thanks to Maxim Zakharov for the patch.
- Fix case where procedure object name is wrongly double quoted.
Thanks to danghb for the report.
- Fix parser to support comment between procedure|function name
and IS|AS keyword. Thanks to danghb for the report.
- Remove dependency to List::Util for the min() function.
2018 08 18 - v19.0
This major release fix several issues reported by users during last
year. It also adds several new features and configuration directives.
New features:
- Add export of Oracle HASH partitioning when PG_SUPPORTS_PARTITION
is enabled. This is a PostgreSQL 11 feature.
- Add SUBTYPE translation into DOMAIN with TYPE and PACKAGE export.
- Add automatic translation of
KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) OVER (PARTITION BY ...)
into
FIRST|LAST_VALUE(...) OVER (PARTITION BY ... ORDER BY ...).
- Add PCTFREE to FILLFACTOR conversion when PCTFREE is upper than
the default value: 10.
- Replace DELETE clause not followed with FROM (optional in Oracle).
- Remove Oracle extra clauses in TRUNCATE command.
- Allow use of NUMBER(*) in DATA_TYPE directive to convert all
NUMBER(*) into the given type whatever is the length. Ex:
DATA_TYPE NUMBER(*):bigint.
- Add a PARALLEL hint to all Oracle queries used to migrate data.
- Add export of Identity Columns from Oracle Database 12c.
- Add translation of UROWID datatype and information in documentation
about why default corresponding type OID will fail at data import.
- Remove unwanted and unused keywords from CREATE TABLE statements:
PARALLEL and COMPRESS.
- Remove TEMPORARY in DROP statements.
- Improve speed of escape_copy() function used for data export.
- Add translation of Oracle functions NUMTOYMINTERVAL() and
NUMTODSINTERVAL().
- Add counting of jobs defined in Oracle scheduler in the migration
assessment feature.
- Add CSMIG in the list of Oracle default system schema
- Fully rewrite data export for table with nested user defined types
DBD::Oracle fetchall_arrayref() is not able to associate complex
custom types to the returned arrays, changed this call to use
fetchrow_array() also used to export BLOB.
- QUERY export will now output translated queries as well as
untranslated ones. This break backward compatibility, previously
only translated query was dumped.
- Auto detect UTF-8 input files to automatically use utf8 encoding.
- Support translation of MySQL global variables.
- Add translation of preprocessor in Oracle external table into
program in foreign table definition. Allow translation of external
table from file.
- Add translation to NVL2() Oracle function.
- Translate CONVERT() MySQL function.
- Translate some form of GROUP_CONCAT() that was not translated.
- Remove call to CHARSET in cast() function, replace it by COLLATE
every where else. This must cover most of the cases but some
specials use might not, so please reports any issue with this
behavior.
- Add -c | --config command line option to ora2pg_scanner to set
custom configuration file to be used instead of ora2pg default:
/etc/ora2pg/ora2pg.conf
- Improve CONNECT BY and OUTER JOIN translation.
- And lot of MySQL to PostgreSQL improvements.
Several new configuration directives have been added:
- Add DEFAULT_PARALLELISM_DEGREE to control PARALLEL hint use
when exporting data from Oracle. Default is disabled.
- Make documentation about KEEP_PKEY_NAMES more explicit about
kind of constraints affected by this directive.
- Add PG_SUPPORTS_IDENTITY configuration directive to enable
export of Oracle identity columns into PostgreSQL 10 feature.
If PG_SUPPORTS_IDENTITY is disabled and there is IDENTITY column
in the Oracle table, they are exported as serial or bigserial
columns. When it is enabled they are exported as IDENTITY columns
like:
CREATE TABLE identity_test_tab (
id bigint GENERATED ALWAYS AS IDENTITY,
description varchar(30)
) ;
If there is non default sequence option set in Oracle, they will
be appended after the IDENTITY keyword. Additionally in both cases
Ora2Pg will create a file AUTOINCREMENT_output.sql with a function
to update the associated sequences with the restart value set to
"SELECT max(colname)+1 FROM tablename". Of course this file must
be imported after data import otherwise sequence will be kept to
start value.
- Add DISABLE_UNLOGGED configuration directive. By default Ora2Pg
export Oracle tables with the NOLOGGING attribute into UNLOGGED
tables. You may want to fully disable this feature because you
will lost all data from unlogged table in case of PostgreSQL crash.
Set it to 1 to export all tables as normal table. When creating a
new migration project using --init_project, this directive is
activated by default. This is not the case in the default
configuration file for backward compatibility.
- Add FORCE_SECURITY_INVOKER configuration directive. Ora2Pg use
the function's security privileges set in Oracle and it is often
defined as SECURITY DEFINER. To override those security privileges
for all functions and use SECURITY DEFINER instead, enable this
directive.
- Add AUTONOMOUS_TRANSACTION in configuration to enable translation
of autonomous transactions into a wrapper function using dblink
or pg_background extension. If you don't want to use this feature
and just want to export the function as a normal one without the
pragma call, disable this directive.
- Add documentation about COMMENT_SAVEPOINT configuration directive.
- Major rewrite in PACKAGE parser to better support global variables
detection. Global variable that have no default values are now
always initialized to empty string in file global_variables.conf
so that we see that they exists. This might not change the global
behavior.
I especially want to thank Pavel Stehule and Eric Delanoe who spent
lot of time this year to help me to improve the PL/SQL to plpgsql
translation and also Krasiyan Andreev who help a lot to finalize
the MySQL to PostgreSQL migration features.
Here is a complete list of changes and acknowledgments:
- Fix translation of "varname cursor%ROWTYPE;". Thanks to Philippe
Beaudoin for the report.
- Fix return of autonomous transaction dblink call when function has
OUT parameter. Thanks to Pavel Stehule for the report.
- Add Oracle to PostgreSQL translation of windows functions
KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) OVER (PARTITION BY ...)
Thanks to Swapnil bhoot929 for the feature request.
- Fix "ORA-03113: end-of-file on communication channel" that what
generated by a too long query send to Oracle. The size of queries
sent to Oracle to retrieve object information depend of the ALLOW
and EXCLUDE directives. If you have lot of objects to filter you
can experience this kind of non explicit error. Now Ora2pg use
bind parameter to pass the filters values to reduce the size of
the prepared query. Thanks to Stephane Tachoire for the report.
- Add SUBTYPE translation into DOMAIN with TYPE and PACKAGE export.
Thanks to Francesco Loreti for the feature request.
- Fix PLS_INTEGER replacement.
- Remove precision for RAW|BLOB as type modifier is not allowed for
type "bytea".
- Fix call of schema.pckg.function() in indexes with a replacement
with pckg.function(). Thanks to w0pr for the report.
- Fix translation of UPDATE trigger based on columns:
"BEFORE UPDATE OF col1,col2 ON table".
Thanks to Eric Delanoe for the report.
- Remove single / from input file that was causing a double END in
some case. Thanks to Philippe Beaudoin for the report.
- Limit translation of PCTFREE into FILLFACTOR when PCTFREE is upper
than the Oracle default value: 10. With PostgreSQL 100 (complete
packing) is the default.
- Add PCTFREE to FILLFACTOR conversion. Thanks to Maxim Zakharov
for the patch.
- Remove TRUNCATE extra clauses. Thanks to e7e6 for the patch.
- Fix type conversion when extra \n added after ;. Thanks to
Maxim Zakharov for the patch.
- Fix DELETE clause not followed with FROM (optional in Oracle).
Thanks to Philippe Beaudoin for the patch.
- Limit call to ALL_TAB_IDENTITY_COLS to version 12+. Thanks to
Andy Garfield for the report.
- Fix comment parsing. Thanks to Philippe Beaudoin for the report.
- Allow use of NUMBER(*) in DATA_TYPE directive to convert all
NUMBER(*) into the given type whatever is the length.
Thanks to lingeshpes for the feature request.
- Fix bug in function-based index export. Thanks to apol1234 for
the report.
- Add PARALLEL hint to all data export queries. Thanks to jacks33
for the report.
- Make documentation about KEEP_PKEY_NAMES more explicit about kind
of constraints affected by this directive.
- Fix export of identity columns by enclosing options between
parenthesis and replacing CACHE 0 by CACHE 1. Thanks to swmcguffin
devtech for the report.
- Add parsing of identity columns from file.
- Fix unwanted replacement of IF () in MySQL code. Thanks to
Krasiyan Andreev for the report.
- Fix to_char() translation, thanks to Eric Delanoe for the report.
- Fix untranslated PERFORM into exception. Thanks to Pavel Stehule
for the report.
- Add _get_entities() function to MySQL export. It returns nothing,
AUTO_INCREMENT column are translated with corresponding types,
smallserial/serial/bigserial.
- Fix look at encrypted column on Oracle prior to 10. Thanks to
Stephane Tachoires for the patch.
- Add export of Identity Columns from Oracle Database 12c. Thanks
to swmcguffin-devtech for the feature request.
- Prevent Ora2Pg to scan ALL_SCHEDULER_JOBS for version prior to 10
Thanks to Stephane Tachoires for the patch.
- Fix pull request #648 to log date only when debug is enabled and
use POSIX strftime instead of custom gettime function.
- Add system time to debug log info. Thanks to danghb for the patch.
- Fix parsing of trigger from file and exception.
- Fix very slow export of mysql tablespace when number of table is
large. Thanks to yafeishi for the report.
- Fix translation of CAST( AS unsigned). Thanks to Krasiyan Andreev.
- Fix MySQL character length to use character_maximum_length
instead of equal character_octet_length. Thanks to yafeishi for
the report.
- Fix custom replacement of MySQL data type. Thanks to Krasiyan
Andreev for the report.
- Fix replacement of call to open cursor with empty parenthesis.
Thanks to Philippe Beaudoin for the report.
- Fix MySQL data type conversion in function declaration. Thanks to
Krasiyan Andreev for the report.
- Fix error with -INFINITY as default value for date or timestamp
columns.
- Fix procedure call rewrite with unwanted comma on begin of
parameter list. Thanks to Pavel Stehule for the report.
- Fix handling of foreign keys when exporting data and DROP_FKEYS
is enabled and ALLOW/EXCLUDE directive is set. Now Ora2Pg will
first drop all foreign keys of a table in the export list and all
foreign keys of other tables pointing to the table. After data
import, it will recreate all of these foreign keys. Thanks to
Eric Delanoe for the report.
- Fix broken transformation of procedure call with default parameter
Thanks to Pavel Stehule for the report.
- Translate call to TIMESTAMP in partition range values into a cast.
Thanks to markiech for the report.
- Fix CONNECT BY translation when the query contain an UNION. Thanks
to mohammed-a-wadod for the report.
- Fix CONNECT BY with PRIOR on the right side of the predicat.
- Fix outer join translation when the (+) was in a function, ex:
WHERE UPPER(trim(VW.FRIDAY))= UPPER(trim(FRIDAY.NAME(+))).
- Order outer join pending tables in from clause.
- Order by object name comments and indexes export.
- Fix outer join translation when the table is not in the from
clause. Thanks to Cyrille Lintz for the report.
- Try to fix potential Oracle schema prefixing PostgreSQL schema
name in CREATE SCHEMA. Thanks to Cyrille Lintz for the report.
- Fix error in TRIM() translation. Thanks to Cyrille Lintz for the
report.
- Add translation of UROWID datatype and information in documentation
about why default corresponding type OID will fail at data import.
Thanks to Cyrille Lintz for the report.
- Fix bug in exporting boolean default values in column definition.
- Fix bug in column parsing in CREATE TABLE.
- Adapt default value for data type changed to boolean.
- Fix bad handling of -D (data_type) option.
- Change behavior in the attempt to set MySQL global variable type.
Now variable type will be timestamp if the variable name contains
datetime, time if the name contains only time and date for date.
Thanks to Krasiyan Andreev for the report.
- Fix function replacement in MySQL declare section. Thanks to
Krasiyan Andreev fr the report.
- Apply REPLACE_ZERO_DATE to default value in table declaration.
Thanks to Krasiyan Andreev for the report.
- Add support to embedded comment in table DDL.
- Fix replacement of data type for MySQL code. Thanks to Krasiyan
Andreev for the report.
- Fix MySQL type replacement in function. Thanks to Krasiyan Andreev
for the report.
- Improve speed of escape_copy() function used for data export.
Thanks to pgnickb for the profiling.
- Add translation of Oracle functions NUMTOYMINTERVAL() and
NUMTODSINTERVAL(). Thanks to Pavel Stehule for the report.
- Counting jobs defined in Oracle scheduler. Thanks to slfbovey
for the patch.
- Fix several issue in create table DDL parser:
- remove double quote of object name when a list of column is
entered
- split of table definition to extract column and constraint
parts is now more efficient
- remove dot in auto generated constraint name when a schema
is given in table name
- fix default values with space that was breaking the parser
- Remove use of bignum perl module that reports error on some
installation. Thanks to Cyrille Lintz for the report.
- Fix a typo preventing perldoc to complete. Thanks to slfbovey
for the patch.
- Fully rewrite data export for table with nested user defined types
DBD::Oracle fetchall_arrayref() is not able to associate complex
custom types to the returned arrays, changed this call to use
fetchrow_array() also used to export BLOB. Thanks to lupynos for
the report.
- Fix renaming of temporary files during partitions data export.
- Fix Oracle use of empty string as default value for integers.
Oracle allow such declaration: SOP NUMBER(5) DEFAULT '' which
PostgreSQL does not support. Ora2Pg now detect this syntax and
replace empty string with NULL. Thanks to ricdba for the report.
- Add detection of Oracle version before setting datetime format,
needed for Oracle 8i compatibility.
- Export of tables from Oracle database are now ordered by name by
default. Thanks to Markus Roth for the report.
- Fix an other case of missing translation of UNSIGNED into bigint.
Thanks to Krasiyan Andreev for the report.
- Force replacement of double quote into single quote for MySQL view
and function code.
- Fix case when SET @varname := ... is used multiple time in the
same function. Thanks to Krasiyan Andreev for the report.
- Fix case where SET @varname := ... was not translated. Thanks to
Krasiyan Andreev for the report.
- Adjust the regex pattern of last patch.
- Fix unwanted newline after hint replacement that could break
comments. Thanks to Pavel Stehule for the report.
- Fix if() replacement in query. Thanks to Krasiyan Andreev for the
report.
- Remove extra parenthesis in some form of JOIN. Thanks to Krasiyan
Andreev for the report.
- Fix untranslated call to UNSIGNED, now translated as bigint.
- Thanks to Krasiyan Andreev for the report.
- Fix translation of double(p,s) into decimal(p,s).
- Remove use of SET when an assignment is done through a SELECT
statement. Thanks to Krasiyan Andreev for the report.
- Fix non-quoted reserved keywords in INSERT / COPY statements when
exporting data. Thanks to Pavel Stehule for the report.
- Fix partition data export to file, temporary files for partition
output was not renamed at export end then data was not loaded.
- Fix double operator := during function with out param rewrite.
- Fix commit f1166e5 to apply changes when FILE_PER_FUNCTION is
disable or when an input file is given.
- Fix translation of LOCATE(). Thanks to Krasiyan Andreev for the
report.
- Fix case where MySQL GROUP_CONCAT() function was not translated.
Thanks to Krasiyan Andreev for the report.
- Fix :new and :old translation in triggers.
- Fully rewrite function call qualification process, the second pass
now is only use to requalify call to pkg.fct into pkg_ftc when
PACKAGE_AS_SCHEMA is disable. The replacement of all function
calls using double quote when a non supported character is used or
when PRESERVE_CASE is enabled has been completely removed as this
takes too much time to process for just very few case. So by
default now Ora2Pg will not go through the second pass. This can
change in the future especially if this is more performant to
process PERFORM replacement. Thanks a lot to Eric Delanoe for his
help on this part.
- Exclude function and procedure not from package to be used in
requalify call. Thanks to Eric Delanoe for the report.
- Fix function name qualification in multiprocess mode.
- Fix unqualified function call due to unclose file handle.
- Prevent try to requalify function call if the function is
not found in the file content.
- Remove ALGORITHM=.*, DEFINER=.* and SQL SECURITY DEFINER from
MySQL DDL code.
- An other missing change to previous commit on qualifying function
call.
- Limit function requalification to export type: VIEW, TRIGGER,
QUERY, FUNCTION, PROCEDURE and PACKAGE.
- Auto detect UTF-8 input files to automatically use utf8 encoding.
- Remove all SHOW ERRORS and other call to SHOW in Oracle package
source as they was badly interpreted as global variable.
- Fix MySQL CREATE TABLE ... SELECT statement.
- Fix pending translation issue on some DATE_FORMAT() case.
Thanks to Krasiyan Andreev for the report.
- Fix translation of IN (..) in MySQL view. Thanks to Krasiyan
Andreev for the report.
- Fix MySQL date format with digit.
- Fix DATE_FORMAT, WHILE and IFNULL translation issues.
- Fix not translated MySQL IF() function.
- Fix other MySQL translation issues for @variable. Thanks to
Krasiyan Andreev for the report.
- Fix issue in MySQL IF translation with IN clause. Thanks to
Krasiyan Andreev for the report.
- Clarify comment about XML_PRETTY directive. Thanks to TWAC
for the report.
- Fix remaining MySQL translation issues for @variable reported
in issue #590.
- Fix no translated := in SET statement.
- Fix output order of translated function.
- Fix non printable character or special characters that make
file encoding to ISO-8859 instead of utf8. Thanks to twac for
the report.
- Prevent MySQL global variable to be declared twice. Thanks to
Krasiyan Andreev for the report.
- Support translation of MySQL global variables. Session variable
@@varname are translated to PostgreSQL GUC variable and global
variable @varname are translated to local variable defined in a
DECLARE section. Ora2Pg tries to gather the data type by using
integer by default, varchar if there is a constant string ('...')
in the value and a timestamp if the variable name have the keyword
date or time inside. Thanks to Krasiyan Andreev for the feature
request.
- Fix DATE_ADD() translation.
- Add translation of preprocessor in Oracle external table into
program in foreign table definition. Thanks to Thomas Reiss for
the report. Allow translation of external table from file.
- Fix case where IF EXISTS might not be append when it is not
supported by PG.
- Translate CONVERT() MySQL function. Thanks to Krasiyan Andreev
for the report.
- Translate some form of GROUP_CONCAT() that was not translated.
Thanks to Krasiyan Andreev for the report.
- Apply same principe with COMMIT in MySQL function code than in
Oracle code. It is kept untouched to be able to detect a possible
change of code logic. It can be automatically commented if
COMMENT_COMMIT_ROLLBACK is enabled. Also I have kept the START
TRANSACTION call but it is automatically commented.
- Add mysql_enable_utf8 => 1 to MySQL connection to avoid issues
with encoding. Thanks to Krasiyan Andreev for the report.
- Prevent removing of comment on MySQL function and add a "COMMENT
ON FUNCTION" statement at end of the function declaration. Thanks
to Krasiyan Andreev for the report.
- Fix translation of types in MySQL function parameter. Thanks to
Krasiyan Andreev for the report.
- Remove START TRANSACTION from MySQL function code. Thanks to
Krasiyan Andreev for the report.
- Fix previous patch, we do not need to look forward for function
or procedure definition in VIEW export and there is no package
with MySQL. Thanks to Krasiyan Andreev for the report.
- Fix call to useless function for MySQL function.
- Add rewrite of MySQL function call in function or procedure code
translation and some other translation related to MySQL code.
- Fix ora2pg_scanner when exporting schema with $ in its name.
Thanks to Aurelien Robin for the report.
- Disable number of microsecond digit for Oracle version 9. Thanks
to Aurelien Robin for the report.
- Do not look at encrypted column for DB version < 10. Thanks to
Aurelien Robin for the report.
- Fix MySQL call to charset in cast function. MySQL charset "utf8"
is also set to COLLATE "C.UTF-8". Thanks to Krasiyan Andreev for
the report.
- Fix two bug in CONNECT BY and OUTER JOIN translation.
- Forgot to handle exception to standard call to IF in MySQL IF()
translation. Thanks to Krasiyan Andreev for the report.
- Forgot to apply previous changes to procedure.
- Fix IF() MySQL replacement when nested and when containing an
IN (...) clause. Thanks to Krasiyan Andreev for the report.
- Fix double BEGIN on MySQL function export. Thanks to Krasiyan
Andreev for the report.
- Fix enum check constraint name when PRESERVE_CASE is enabled.
- Fix case where object with LINESTRING and CIRCULARSTRING was
exported as MULTILINESTRING instead of MULTICURVE.
- Fix export of MULTICURVE with COMPOUNDCURVE. Thanks to Petr Silhak
for the report.
- Fix several issue in MySQL table DDL export. Thanks to Krasiyan
Andreev for the report.
- Fix MySQL auto_increment data type translation and columns export
order.
- Fix translation of MySQL function CURRENT_TIMESTAMP(). Thanks to
Krasiyan Andreev for the report.
- Fix export of MySQL alter sequence name when exporting auto
increment column. Thanks to Krasiyan Andreev for the report.
- Replace IF() call with CASE ... END in VIEW and QUERY export for
MySQL. Thanks to Krasiyan Andreev for the feature request.
- Replace backquote with double quote on mysql statements when read
from file.
- Fix bug in REGEXP_SUBSTR replacement.
- Prevent replacement with same function name from an other package.
Thanks to Eric Delanoe for the report.
- Apply same STRICT rule for SELECT INTO to EXECUTE INTO. Thanks to
Pavel Stehule for the report.
- Fix extra parenthesis removing when a OR clause is present. Thanks
to Pavel Stehule for the report.
- Keep autonomous pragma commented when conversion is desactivated
to be able to identify functions using this pragma.
- Fix bug in replacement of package function in string constant.
- Fix malformed replacement of array element calls. Thanks to Eric
Delanoe for the report.
- Fix unwanted replacement of TO_NUMBER function. Thanks to Torquem
for the report.
- Add an example of DSN for MySQL in ORACLE_DSN documentation.
Thanks to François Honore for the report.
- Fix typo in default dblink connection string. Thanks to Pavel
Stehule for the report.
- Add information about Oracle Instant Client installation. Thanks
to Jan Birk for the report.
- Replace Oracle array syntax arr(i).x into arr[i].x into PL/SQL
code. Thanks to Eric Delanoe for the report.
- Use a more generic connection string for DBLINK. It will use
unix socket by default to connect and the password must be set
in .pgpass. This will result in the following connection string:
format('port=%s dbname=%s user=%', current_setting('port'),
current_database(), current_user)
If you want to redefine this connection string use DBLINK_CONN
configuration directive. Thanks to Pavel Stehule for the feature
request.
- Fix missing RETURN NEW in some trigger translation. Thanks to
Pavel Stehule for the report.
- Fix a missing but non mandatory semi-comma.
- Keep PKs/unique constraints which are deferrable in Oracle also
deferrable in PostgreSQL. Thank to Sverre Boschman for the patch.
- Fix parsing and translation of CONNECT BY. Thanks to bhoot929
for the report.
- Fix FDW export when exporting all schema. Thanks to Laurenz Albe
for the report.
- Add a note about multiple value in export type that can not
include COPY or INSERT together with others export type.
- Fix duplicate condition. Thanks to Eric Delanoe for the report.
- Fix unwanted translation into PERFORM after INTERSECT.
- Comment savepoint in code. Thanks to Pavel Stehule for the patch.
- Fix "ROLLBACK TO" that was not commented. Thanks to Pavel Stehule
for the report.
- Fix restore of constant string when additional string constant
regex are defined in configuration file.
- Fix translation of nextval with sequence name prefixed with their
schema.
- Cast call to TO_DATE(LOCALTIMESTAMP,...) translated into
TO_DATE(LOCALTIMESTAMP::text,...). Thanks to Keshav kumbham
for the report.
- Remove double quote added automatically by Oracle on view
definition when PRESERVE_CASE is not enable. Thanks to JeeIPI for
the report.
- Fix translation of FROM_TZ with a call to function as first
parameter. Thanks to TrungPhan for the report.
- Fix package export when FILE_PER_FUNCTION is set. Thanks to
Julien Rouhaud for the report.
- Add translation of REGEXP_SUBSTR() with the following rules:
Translation of REGEX_SUBSTR( string, pattern, [pos], [nth])
converted into
SELECT array_to_string(a, '')
FROM regexp_matches(substr(string, pos), pattern, 'g')
AS foo(a)
LIMIT 1 OFFSET (nth - 1);
Optional fifth parameter of match_parameter is appended to 'g'
when present. Thanks to bhoot929 for the feature request.
- Add count of REGEX_SUBSTR to migration assessment cost.
- Add translation support of FROM_TZ() Oracle function. Thanks
to trPhan for the feature request.
- Forces ora2pg to output a message when a custom exception code
has less than 5 digit.
- Fix errcode when Oracle custom exception number have less than
five digit. Thanks to Pavel Stehule for the report.
- Fix case where custom errcode are not converted. Thanks to Pavel
Stehule for the report.
- Fix print of single semicolon with empty line in index export.
- Fix problem with TO_TIMESTAMP_TZ conversion. Thanks to Keshav-
kumbham for the report.
- Fix unwanted double quote in index column with DESC sorting.
Thanks to JeeIPI for the report.
- Fix non detection case of tables in from clause for outer join
translation. Thanks to Keshav for the report.
- Fix unwanted replacement of = NULL into IS NULL in update
statement. Thanks to Pavel Stehule for the report.
- Force schema name used in TEST action to lowercase. Thanks to
venkatabn for the report.
- Fix export of spatial geometries with CURVEPOLYGON + COMPOUNDCURVE
Thanks to kabog for the report.
2017 09 01 - v18.2
This release fix several issues reported during the last six months.
It also adds several new features and configuration directives:
- Add translation of SUBSTRB into substr.
- Allow use of array in MODIFY_TYPE to export Oracle user defined
type that are just array of some data type. For example:
CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15);
can be directly translated into text[] or varchar[]. In this case
use the directive as follow: MODIFY_TYPE CLUB:MEMBERS:text[]
Ora2Pg will take care to transform all data of this column into
the correct format. Only arrays of characters and numerics types
are supported.
- Add translation of Oracle function LISTAGG() into string_agg().
- Add TEST_VIEW action to perform a simple count of rows returned by
views on both database.
- Translate SQL%ROWCOUNT into GET DIAGNOSTICS rowcount = ROW_COUNT
and add translation of SQL%FOUND.
- Add translation of column in trigger event test with IS DISTINCT,
for example: IF updating('ID') THEN ... will be translated into:
IF TG_OP = 'UPDATE' AND NEW.'ID' IS DISTINCT FROM OLD.'ID' then...
- Replace UTL_MATH.EDIT_DISTANCE function by fuzzymatch levenshtein.
- Allow use of MODIFY_STRUCT with TABLE export. Table creation DDL
will respect the new list of columns and all indexes or foreign
key pointing to or from a column removed will not be exported.
- Add export of partition and subpartition using PostgreSQL native
partitioning.
- Auto detect encrypted columns and report them into the assessment.
SHOW_COLUMN will also mark columns as encrypted.
- Add information to global temporary tables in migration assessment.
- Add experimental DATADIFF functionality.
- Allow use of multiprocess with -j option or JOBS to FUNCTION and
PROCEDURE export. Useful if you have thousands of these objects.
- Force RAW(N) type with default value set to sys_guid() as UUID
on PostgreSQL.
- Replace function with out parameter using select into. For example
a call to: get_item_attr( attr_name, p_value );
where p_value is an INOUT parameter, will be rewritten as
p_value := get_item_attr( attr_name, p_value );
If there is multiple OUT parameters, Ora2Pg will use syntax:
SELECT get_item_attr( attr_name, p_value )
INTO (attr_name, p_value);
- Add translation of CONNECT BY using PostgreSQL CTE equivalent.
This translation also include a replacement of LEVEL and
SYS_CONNECT_BY_PATH native Oracle features. On complex queries
there could still be manual editing but all the main work is done.
- Add support to user defined exception, errcode affected to each
custom exception start from 50001.
- Translate call to to_char() with a single parameter into a cast
to varchar. Can be disabled using USE_ORAFCE directive.
- Improve ora2pg_scanner to automatically generates migration
assessment reports for all schema on an Oracle instance. Before
the schema name to audit was mandatory, now, when the schema
is not set Ora2Pg will scan all schema. The connexion user need
to have DBA privilege. Ora2Pg will also add the hostname and SID
as prefix in the filename of the report. This last changee forbids
ora2pg_scanner to overwrite a report if the same schema name is
found in several databases.
Several new configuration directives have been added:
- Add USE_ORAFCE configuration directive that can be enabled if you
want to use functions defined in the Orafce library and prevent
Ora2Pg to translate call to these functions. The Orafce library
can be found here: https://github.com/orafce/orafce
By default Ora2pg rewrite add_month(), add_year(), date_trunc()
and to_char() functions, but you may prefer to use the Orafce
functions that do not need any code transformation. Directive
DATE_FUNCTION_REWRITE has been removed as it was also used to
disable replacement of add_month(), add_year() and date_trunc()
when Orafce is used, useless now.
- Add FILE_PER_FKEYS configuration directive to allow foreign key
declaration to be saved in a separate file during schema export.
By default foreign keys are exported into the main output file or
in the CONSTRAINT_output.sql file. If enabled foreign keys will be
exported into a file named FKEYS_output.sql
- Add new COMMENT_COMMIT_ROLLBACK configuration directive. Call to
COMMIT/ROLLBACK in PL/SQL code are kept untouched by Ora2Pg to
force the user to review the logic of the function. Once it is
fixed in Oracle source code or you want to comment this calls
enable the directive.
- Add CREATE_OR_REPLACE configuration directive. By default Ora2Pg
use CREATE OR REPLACE in function DDL, if you need not to override
existing functions disable this configuration directive, DDL will
not include OR REPLACE.
- Add FUNCTION_CHECK configuration directive. Disable this directive
if you want to disable check_function_bodies.
SET check_function_bodies = false;
It disables validation of the function body string during CREATE
FUNCTION. Default is to use de postgresql.conf setting that enable
it by default.
- Add PG_SUPPORTS_PARTITION directive, disabled by default.
PostgreSQL version prior to 10.0 do not have native partitioning.
Enable this directive if you want to use PostgreSQL declarative
partitioning instead of the old style check constraint and trigger.
- Add PG_SUPPORTS_SUBSTR configuration directive to replace substr()
call with substring() on old PostgreSQL versions or some fork
like Redshift.
- Add PG_INITIAL_COMMAND to send some statements at session startup.
This directive is the equivalent used for Oracle connection,
ORA_INITIAL_COMMAND. Both can now be used multiple time now.
- Add DBLINK_CONN configuration directive. By default if you have
an autonomous transaction translated using dblink extension the
connection is defined using the values set with PG_DSN, PG_USER
and PG_PWD. If you want to fully override the connection string
use this directive to set the connection in the autonomous
transaction wrapper function. For example:
DBLINK_CONN port=5432 dbname=pgdb host=localhost user=pguser password=pgpass
- Add STRING_CONSTANT_REGEXP configuration directive. Ora2Pg replace
all string constant during the pl/sql to plpgsql translation,
string constant are all text include between single quote. If you
have some string placeholder used in dynamic call to queries you
can set a list of regexp to be temporary replaced to not break the
parser. For example:
STRING_CONSTANT_REGEXP <cfqueryparam value=".*">
The list of regexp must use the semi colon as separator.
- Add FUNCTION_STABLE configuration directive. By default Oracle
functions are marked as STABLE as they can not modify data unless
when used in PL/SQL with variable assignment or as conditional
expression. You can force Ora2Pg to create these function as
VOLATILE by disabling this configuration directive.
- Add new TO_NUMBER_CONVERSION configuration directive to control
TO_NUMBER translation behavior. By default Oracle call to function
TO_NUMBER will be translated as a cast into numeric. For example,
TO_NUMBER('10.1234') is converted into PostgreSQL call:
to_number('10.1234')::numeric.
If you want you can cast the call to integer or bigint by changing
the value of the configuration directive. If you need better
control of the format, just set it as value, for example:
TO_NUMBER_CONVERSION 99999999999999999999D9999999999
will convert the code above as:
TO_NUMBER('10.1234', '99999999999999999999D9999999999')
Any value of the directive that it is not numeric, integer or
bigint will be taken as a mask format. If set to none, then no
conversion will be done.
- Add LOOK_FORWARD_FUNCTION configuration directive which takes a
list of schema to get functions/procedures meta information that
are used in the current schema export. When replacing call to
function with OUT or INOUT parameters, if a function is declared
in an other package then the function call rewriting can not be
done because Ora2Pg only knows about functions declared in the
current schema. By setting a comma separated list of schema as
value of the directive, Ora2Pg will look forward in these packages
for all functions, procedures and packages declaration before
proceeding to current schema export.
- Add PG_SUPPORTS_NAMED_OPERATOR to control the replacement of the
PL/SQL operator used in named parameter => with the PostgreSQL
proprietary operator := Disable this directive if you are using
PG < 9.5
- Add a warning when Ora2Pg reorder the parameters of a function
following the PostgreSQL rule that all input parameters following
a parameter with a default value must have default values as well.
In this case, Ora2Pg extracts all parameters with default values
and put them at end of the parameter list. This is to warn you
that a manual rewrite is required on calls to this function.
New command line options have been added:
- Add -N | --pg_schema command line option to be able to override
the PG_SCHEMA configuration directive. When this option is set
at command line, EXPORT_SCHEMA is automatically activated.
- Add --no_header option with equivalent NO_HEADER configuration
directive to output the Ora2Pg header but just the translated
code.
There is also some behavior changes from previous release:
- Remove SysTimestamp() from the list of not translated function,
it is replaced with CURRENT_TIMESTAMP for a long time now.
- Change migration assessment cost to 84 units (1 day) for type
TABLE, INDEX and SYNONYM and to 168 units (2 days) for TABLE
PARTITION and GLOBAL TEMPORARY TABLE, this is more realistic.
- Set minimum assessment unit to 1 when an object exists.
Improve PL/SQL code translation speed.
- Change behavior of COMPILE_SCHEMA directive used to force Oracle
to compile schema before exporting code. When this directive is
enabled and SCHEMA is set to a specific schema name, only invalid
objects in this schema will be recompiled. When SCHEMA is not set
then all schema will be recompiled. To force recompile invalid
object in a specific schema, set COMPILE_SCHEMA to the schema name
you want to recompile. This will ask to Oracle to validate the
PL/SQL that could have been invalidate after a export/import for
example. The 'VALID' or 'INVALID' status applies to functions,
procedures, packages and user defined types.
- Default transaction isolation level is now set to READ COMMITTED
for all action excluding data export.
- Oracle doesn't allow the use of lookahead expression but you may
want to exclude some objects that match the ALLOW regexp you have
defined. For example if you want to export all table starting
with E but not those starting with EXP it is not possible to do
that in a single expression.
Now you can start a regular expression with the ! character to
exclude all objects matching the regexp given just after. Our