forked from darold/ora2pg
-
Notifications
You must be signed in to change notification settings - Fork 0
/
changelog
4425 lines (4028 loc) · 227 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
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
previous example can be written as follow: ALLOW E.* !EXP.*
it will be translated into
REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$')
in the object search expression.
- Fix quoting of PG_SCHEMA with multiple schema in search path. The
definition of the search path now follow the following behavior:
* when PG_SCHEMA is define, always set search_path to its value.
* when EXPORT_SCHEMA is enabled and SCHEMA is set, the search_path
is set the name of the schema.
- Remove forcing of export_schema when pg_schema is set at command
line. This could change the behavior of some previous use of these
variables and the resulting value of the search_path but it seems
much better understandable.
- Rewrite translation of raise_application_error to use RAISE
EXCEPTION with a message and the SQLSTATE code. Oracle user
defined code -20000 to -20999 are translated to PostgreSQL
user define code from 45000 to 45999. Call to
raise_application_error(mySQLCODE, myErrmsg);
will be translated into
RAISE EXCEPTION '%', myErrmsg USING ERRCODE = mySQLCODE;
- Remove migration assessment cost for TG_OP and NOT_FOUND they
might be fully covered now.
Here is the complete list of changes:
- Fix bad inversion of HAVING/GROUP BY clauses. Thanks to bhoot929
for the report.
- Fix case of non translation of type in CAST() function. Thanks to
Keshavkumbham for the report.
- Fix spatial data export when using direct import into PostgreSQL
and WKT or INTERNAL format. This can still be improved. Thanks to
Valeria El-Samra for the report.
- Improve translation of trunc() into date_trunc. Thanks to bhoot929
for the report.
- Translate to_char() without format into a simple cast to varchar.
Thanks to bhoot929 for the report.
- Fix line comment which does not disable multi-line comment.
Thanks to Pavel Stehule for the report.
- Fix overridden of output file global_variables.conf with
multiple packages. Thanks to Oliver Del Rosario for the report.
- Fix export of data stored in a nested user defined type. Thanks
to lupynos for the report.
- Fix data export from Oracle user defined types, where output of
ROW(...) does not distinguish numeric from string or other types
that need to be formatted. Thanks to Petr Silhak for the report.
- Fix broken replacement of package procedure name. Thanks to Pavel
Stehule for the report.
- Add FLOWS_010600 to the objects exclusion listr.
- Improve view/trigger migration assessment accuracy.
- Fix OUTER JOIN (+) translation, all join filters with constant
was written into the WHERE clause by default. Write them into the
JOIN clause.
- Fix weight of the number of triggers and views in the report with
a limit of 2 man-days, of course SQL difficulties are still add
after this limit.
- Fix alias added to condition which is not a sub query. Thanks to
nitinmverma for the report.
- Fix wrong translation of OUTER JOIN with subquery in FROM clause.
Thanks to nitinmverma for the report.
- Fix typo preventing exclusion of system SYNONYM.
- Fix an other case of bad translation of END fct_name. Thanks to
nitinmverma for the report.
- Fix unwanted translation of REGEXP_SUBSTR() in REGEXP_SUBSTRING().
Thanks to nitinmverma for the report.
- Fix broken translation of decode(). Thanks to nitinmverma for the
report.
- Fix error "Malformed UTF-8 character (unexpected continuation byte
0xbf, with no preceding start byte) in pattern match" by including
an arbitrary non-byte character into the pattern. Thanks to Bob
Sislow for the report.
- Fix missing translation of trunc() with date_trunc(). Thanks to
nitinmverma for the report.
- Add migration assessment weight to concatenation.
- Fix space between operator, ex: a < = 15 must be translated as
a <= 15. Thanks to nitinmverma for the report.
- Handle translation of named parameters in function calls. Thanks
to Julien Rouhaud for the patch.
- Fix missing renaming of _initial_command() method.
- Fix right outer join translation by converting them to left outer
join first. Thanks to Julien Rouhaud for the hint.
- Fix TEST on default value count and functions belonging to others
than public schema, especially functions of packages.
- Fix default number of man-days in migration assessment. Thanks to
Nate Fitzgerald for the report.
- Add host information into the filename of the report to prevent
some additional case of report overriding. Thanks to Aurelien
Robin for the report.
- Prevent ora2pg script to complain if no ora2pg.conf file is found
when a DSN is passed at command line and that user connection is
set in the environment variables.
- Do not declare a function stable if there is update/insert/delete
statement inside.
- Improve ora2pg_scanner to generate report of each instance schema
when the schema to audit is not set. Thanks to Thomas Reiss for
the patch.
- Fix parser failure with quote in comments. Thanks to Eric Delanoe
for the report.
- Fix case where NVL is not replaced by COALESCE.
- Add parenthesis to simple package.function call without parameter.
- Fix replacement of INSTR() with optional parameters. Thanks to
Pavel Stehule for the report.
- Translate SQL%ROWCOUNT to GET DIAGNOSTICS rowcount = ROW_COUNT.
Thanks to Pavel Stehule for the report.
- Add translation of SQL%FOUND. Thanks to Pavel Stehule.
- Remove qualifier in create type, "CREATE TYPE fusion.mytable AS
(fusion.mytable fusion.finalrecord[]);" becomes "CREATE TYPE
fusion.mytable AS (mytable fusion.finalrecord[]);". Thanks to
Julien Rouhaud for the report.
- Fix extra comma in FROM clause of triggers in outer join
translation. Thanks to Pavel Stehule fora the report.
- Use record for out parameters replacement only where there is more
than one out parameter. Thanks to Pavel Stehule for the patch.
- Add date type to the inout type array. Thanks to Pavel Stehule for
the report.
- Remove possible last spaces in inout type detection.
- Fix REGEXP_LIKE translation.
- Fix count of default values during test action.
- Fix removing of double quote over column name in view declaration.
- Do not set default value if it is NULL, this is already the case.
- Fix data export that was truncated to the last DATA_LIMIT lines.
Thanks to Michael Vitale for the report.
- Fix an other bug in rewriting call to function with OUT parameter.
Thanks to Pavel Stehule for the report.
- Fix autodetection of composite out parameters.
- Merge typo on PLPGSQL
- Fix typo to PLPGSQL keyword. Thanks to Vinayak Pokale.
- Fix regexp failure in is_reserved_words() method. Thanks to
Patrick Hajek for the patch.
- Use only one declaration of ora2pg_r RECORD, it is reusable.
- Fix transformation of procedure CALL with OUT parameter that can't
works when procedure/function has minimally one OUT parameter is
of composite type. Thanks to Pavel Stehule for the patch.
- Second attempt to fix outer join translation in triggers. Thanks
to Pavel Stehule for precious help.
- Fix RAISE NOTICE replacement with double % placeholder. Thanks to
Pavel Stehule for the report.
- Fix call to function replacement for function registered with
double quote. Thanks to Pavel Stehule for the report.
- Change assessment score of TG_OP.
- Fix replacement of outer join in triggers by adding pseudo tables
NEW and OLD to the list of tables. Thanks to Pavel Stehule for the
report.
- Handle custom exception in declare section of triggers.
- Fix FUNCTION_CHECK option, it will be set in all file header.
Thanks to Pavel Stehule for the report.
- Replace call to ALL_TABLES to USER_TABLES when USER_GRANTS is
enabled. Thanks to Bob Sislow.
- Removed PRAGMA EXCEPTION_INIT() from declare section. Thanks to
Pavel Stehule for the report.
- Fix constant string that was breaking the parser. Thanks to Pavel
Stehule for the report.
- Fix missing space between EXCEPTION and WHEN. Thanks to Pavel
Stehule for the report.
- Fix broken function header resulting in missing space before OUT
keyword. Thanks to Pave Stehule for the report.
- Fix invalid RAISE command. Thanks to Pavel Stehule for the report.
- Add port information to ORACLE_DSN in documentation and config
file. Thanks to Bob Sislow for the report.
- Fix broken declaration in triggers related to FOR cycle control
variable when there is no declare section. Thanks to Pavel
Stehule for the report.
- Fix broken declaration in triggers related to FOR cycle control
variable. Thanks to Pavel Stehule for the report.
- Fix unterminated C style comment in trigger. Thanks to Pavel
Stehule for the report.
- Fix bug in package+function precedence replacement. Thanks to
Eric Delanoe for the report.
- Fix unwanted and broken export of tables created with CREATE TABLE
tablename OF objType. Thanks to threenotrump for the report.
- Add explanation on using REPLACE_AS_BOOLEAN when REPLACE_TABLES or
REPLACE_COLS is also used on the same object. Thanks to Brendan
Le Ny for the report.
- Fix unwanted data export of materialized view. Thanks to Michael
Vitale for the report. Fix ORA-00942 with table that are not yet
physically created and has no data.
- Fix calling functions with same name declared in several packages.
The one declared in current package now takes precedence. Thanks
to Eric Delanoe for the report.
- Change zero-length lob/long to undef workaround for a bug in
DBD::Oracle with the ora_piece_lob option (used when no_lob_locator
is enabled) where null values fetch as empty string for certain
types. Thanks to Alice Maz for the patch.
- Fix createPoint() spatial method issue. Thanks to jwl920919.
- Fix comment on REPLACE_COLS directive.
- Fix an other issue in transformation of TYPE x IS REF CURSOR.
Thanks to Pavel Stehule for the report.
- Fix an other case of broken declaration in triggers related to FOR
cycle control variables. Thanks to Pavel Stehule for the report.
- Fix broken declaration in triggers related to FOR cycle control
variables with empty DECLARE section.
- Fix other case of replacement by EXIT WHEN NOT FOUND.
- Fix output of global_variables.conf file when OUTPUT_DIR is not
set. Fix non replacement of global variables.
- Remove some AUTHID garbage in procedure declaration generated by
a migration.
- Fix trigger name quoting with non supported character. Thanks to
Michael Vitale for the report.
- Fix use of nextval() in default value.
- Fix alias append in from clause of the extract() function. Thanks
to Narayanamoorthys for the report.
- Disable direct export to partition for PostgreSQL 10 if directive
PG_SUPPORTS_PARTITION is enabled, import must be done in the main
table.
- Do not export partitions of a materialized view. Thanks to Michael
Vitale for the report.
- Fix wrong replacement of keyword after END. Thanks to Pavel
Stehule for the report.
- Remove Oracle hints from queries, they are not supported and can
break comments. Thanks to Pavel Stehule for the report.
- Fix unstable transformation of TYPE x IS REF CURSOR. Thanks to
Pavel Stehule for the report.
- Fix data export failure when no table match the ALLOW/EXCLUDE
filter. Thanks to threenotrump for the report.
- Add missing search_path to FKEY dedicated file. Thanks to Michael
Vitale for the report.
- Apply default oject name exclusion to synonym.
- Skip some PL/SQL translation in migration assessment mode.
- Change default type for virtual column whit round() function.
Thanks to Julien Rouhaud for the report.
- Fix bug with EXIT WHEN command. Thanks to Pavel Stehule.
- Fix an other wrong replacement of DECODE in UPDATE statement.
Thanks to Pavel Stehule for the report.
- Fix wrong replacement of DECODE. Thanks to Pavel Stehule.
- Fix unwanted replacement of INTO STRICT when this is an INSERT
statement. Thanks to Pavel Stehule for the report.
- Fix potential regexp error with special character in outer join
filters. Thanks to Adrian Boangiu for the report.
- Fix parsing of PK from file. Thanks to Julien Rouhaud.
- Fix parsing of FK from file. Thanks to Julien Rouhaud.
- Fix count of unique and primary key in TEST export. Thanks to
Liem for the report.
- Fix reserved keyword rewrite using double quote when directive
USE_RESERVED_WORDS is enabled. Thanks to Michael Vitale.
- Remove EVALUATION CONTEXT object type from migration assessment.
- Add QUEST_SL_% pattern to the list of table that must be excluded
from export.
- Fix data export when BFILE are translated as text. Thanks to
Michael Vitale for the report.
- Fix export of package when a comment is placed just before the
AS/IS keyword. Thanks to Michael Vitale for the report.
- Fix other cases of function call replacement when they are
declared in different packages and one with OUT parameter and
the other one with only IN parameters. Thanks to Eric Delanoe.
- Fix inconsistent behavior of import_all script with -h and -d
Under Linux: When -h not specified, script defaults to unix domain
sockets for psql and localhost for perl (which may error depending
on pg_hba.conf). Now defaults to more performing sockets. -d
wasn't passing DB name to some psql calls where it's necessary.
Thanks to BracketDevs for the patch.
- Fix file handle close when compression is enabled. Thanks to
Sebastian Albert for the report.
- Add information about ora2pg behavior during data export to files
when files already exists. Thanks to Michael Vitale.
- Update readme to provide tar command for bzip2 file. Thanks to
Tom Pollard for the patch
- Fix unwanted FTS_INDEXES empty file and append unaccent extension
creation if not exists. Thanks to Michael Vitale for the report.
- Fix missing explicitly declared variable for cycle in trigger.
Thanks to Pavel Stehule for the report.
- Fix global type/cursor declaration doubled in package export.
- Always translate Oracle SELECT ... INTO to SELECT ... INTO STRICT
in plpgsql as Oracle seems to always throw an exception. Thanks
to Pavel Stehule for the report.
- Fix too much semicolons on end of function. Thanks to Pavel
Stehule for the report.
- Fix ALTER TABLE to set the owner when table is a foreign table.
Thanks to Narayanamoorthys for the report.
- Fix case of untranslated procedure call when there was parenthesis
in the parameters clause. Thanks to Pavel Stehule for the report.
- Fix broken variable declaration with name containing DEFAULT.
Thanks to Pavel Stehule for the report.
- Change query ORDER BY clause to view export query.
- Fix missing replacement of quote_reserved_words function by new
quote_object_name. Thanks to Sebastian Albert for the report.
- Fix REPLACE_COLS replacement of column name in UNIQUE constraint
definition. Thanks to Bernard Bielecki for the report.
- Fix export of Oracle unlogged table that was exported as normal
tables.
- Fix regression in package function calls rewrite leading to append
unwanted comma when replacing out parameters. Thanks to Pavel
Stehule and Eric Delanoe for the report.
- Fix removing of function name after END keyword. Thanks to Pavel
Stehule for the report.
- Fix bug in package function extraction.
- Improve VIEW export by only looking for package function name and
fix a bug that was including unwanted "system" package definition.
Also fix a potential bad rewriting of function call. Thanks to
Eric Delanoe for the report.
- Fix an other case of missing PERFORM replacement. Thanks to Pavel
Stehule for the report.
- Fix remplacement of "EXIT WHEN cursor%NOTFOUND". Thanks to Pavel
Stehule for the report.
- Fix missing conversion of type in cast function. Thanks to Michael
Vitale for the report.
- Fix TO_NUMBER that is now translated as a cast to numeric to
correspond to the default behavior in Oracle. Thanks to Pavel
Stehule for the report.
- Fix replacement of function call from different schema, especially
in overloaded cases.
- Remove OUT parameter from the argument list of function call.
Thanks to Pavel Stehule for the report.
- Fix wrong replacement in FOR ... IN loop inserting EXCLUDE in the
statement. Thanks to Pavel Stehule for the report.
- Translate Oracle proprietary VALUES without parenthesis with the
proprietary syntax of POstgreSQL. Thanks to Pavel Stehule for the
report.
- Fix function header translation when a comment is present between
closing parenthesis and the IS keyword. Thanks to Pavel Stehule
for the report.
- Fix RETURNS in autonomous transaction call when there is OUT
parameters. Thanks to Pavel Stehule for the report.
- Fix call to BMS_UTILITY.compile_schema() when COMPILE_SCHEMA is
enable. Thanks to PAvel Stehule for the report.
- Fix export of function and procedure with same name in different
schema. Thanks to Pavel Stehule for the report.
- Fix detection and replacement of global variable in package that
was producing invalid code export. Fix progress bar on procedure
export.
- Fix regression in global variables default value export.
- Rewrite multiprocess for procedure and function export to solve
some performances issues.
- Do not waste time trying to replace function call when it is not
found in the current code.
- Fix default value for FILE_PER_FUNCTION when parallel mode is
enabled.
- Output a fatal error with export type TABLE and multiple schema set
to PG_SCHEMA when EXPORT_SCHEMA is enabled.
- Fix replacement of function name with package prefix.
- Fix documentation of PG_SCHEMA directive, especially on the use of
a schema list. Thanks to Michael Vitale for the report.
- Fix translation of INSTR() function.
- Improve speed in function translation by not calling twice
Ora2Pg::PLSQL::convert_plsql_code() on declare and code section.
Thanks to Pavel Stehule for the profiling.
- Fix unwanted replacement of SYSDATE, SYSTIMESTAMP and some other
when they are part of variable or object name. Add rewrite of
REF CURSOR during type translation.
- Require support of LATERAL keyword for DATADIFF (Pg >= 9.3).
Patch from Sebastian Albert.
- Do not call replace_sdo_function(), replace_sdo_operator() and
replace_sys_context() if the string SDO_ or SYSCONTEXT is not
found. This might save some performances.
- Remove the RETURNS clause when there is an OUT parameter
PostgreSQL choose correct type by self. Thanks to Pavel Stehule
for the report.
- Add a note about performance improvement by updating stats on
Oracle. Thanks to Michael Vitale for the report.
- Remove newline characters in REVOKE statement when embedded in
a comment. Thanks to Pavel Stehule for the report.
- Fix replacement with PERFORM into package extracted from an
Oracle database. Thanks to Eric Delanoe for the report.
- Fix translation of call to function with out parameters.
Thanks to Pavel Stehule for the report.
- Fix case where call to procedure without parameter was not
prefixed by PERFORM or when called in a exception statement.
Thanks to Eric Delanoe for the report.
- Add function quote_object_name to handle all cases where object
name need to be double quoted (PRESERVE_CASE to 1, PostgreSQL
keyword, digit in front or digit only and non supported character.
Thanks to liemdt1811 for the report.
- Add a note about RAW(n) column with "SYS_GUID()" as default value
that is automatically translated to type of the column 'uuid'
by Ora2Pg.
- Remove old column count check to use char_length. Thanks to
Alice Maz for the patch.
- Fix some raise_application_error that was not replaced with a
global rewrite of remove comments and text constants to solve
some other issues like rewriting of package function call in
dynamic queries. Thanks to PAvel Stehule for the report.
- Fix cycle variable not generated for LOOP IN SELECT in trigger.
Thanks to Pavel Stehule for the report.
- Fix procedures with OUT parameters not processed in triggers.
Thanks to Pavel Stehule for the report.
- Remove other case where PERFORM must be or must not be inserted.
- Remove case where PERFORM can be inserted. Thanks to Pavel
Stehule and Eric Delanoe for the report.
- Fix missing ; in some raise_application_error translation. Thanks
to Pavel Stehule for the report.
- Fix missing PERFORM in front of direct call to function and the
rewrite of direct call to function with out parameters. Thanks
to Eric Delanoe for the report.
- Fix translation of rownum when the value is not a number. Thanks
to Pavel Stehule for the report.
- Fix missing space between cast and AS keyword. Thanks to Pavel
Stehule for the report.
- Fix translation of views and add support to comment inside views.
Thanks to Pavel Stehule for the report.
- Fix removing of AS after END keyword. Thanks to Pavel Stehule for
the report.
- Fix type in CAST clause not translated to PostgreSQL type. Thanks
to Pavel Stehule for the report.
- Treat citext type as text. Thanks to Tomasz Wrobel for the patch.
- Fix packages migration assessment that was broken with parser
rewriting on package extraction.
- Rewrite parsing of PL/SQL packages to better handle package
specification and especially types and global variables from this
section.
- Fix raise_application_error translation by removing extra boolean
parameter.
- Improve comments processing.
- Fix package function name replacement adding a dot before package
name. Thanks to Eric Delanoe for the report.
- Add collect of functions/procedures metadata when reading DDL
from file.
- Fix replacement of function prefixed with their schema name.
Thanks to Eric Delanoe for the report.
- Try to minimized comment placeholders by aggregating multiline
comments.
- Remove new line character from _get_version() output.
- Fix ENABLE_MICROSECOND test condition on NLS_TIMESTAMP_TZ_FORMAT
setting. Thanks to Didier Sterbecq for the report.
- Fix another issue with Oracle 8i and table size extraction.
- Fix query to show column information on Oracle 8i
- Fix query to look for virtual column on Oracle 8i
- Fix query to list all table by size on Oracle 8i
- Prevent ora2pg to look for external table definition in Oracle 8i.
- Fix a regression on timestamp format setting for Oracle 8i.
- Fix some regression on queries with Oracle 8i. Thanks to Didier
Sterbecq for the report.
- Add a function to collect metadata of all functions.
- Don't create empty partition index file when there's no partition.
- Fix wrong translation in OPEN ... FOR statement. Thanks to Eric
Delanoe for the report.
- Fix call of method close() on an undefined value. Thanks to Eric
Delanoe for the report.
- Fix partition data export issues introduced with previous patches.
- Fix unterminated IF / ELSIF block in subpartition export.
- Fix subpartition export. Thanks to Maurizio De Giorgi for the
report.
- Force DATA_LIMIT default value to 2000 on Windows OS instead of
10000 to try to prevent constant OOM error. Thanks to Eric Delanoe
for the report.
- Fix default partition table that was not used PREFIX_PARTITION.
Thanks to ssayyadi for the report.
- Limit datetime microsecond format to micro second (.FF6) as the
format can be FF[0..9] and PostgreSQL just have FF[0..6]
- Add to_timestamp_tz Oracle function translation. Thanks to Eric
Delanoe for the feature request.
- Fix custom data type replacement in function code. Thanks to Pavel
Stehule for the report.
- Fix non working INPUT_FILE configuration directive when action is
QUERY. Thanks to Eric Delanoe for the report.
- Fix unwanted global variable implicit declaration to handle
autonomous transaction parameters. Thanks to Eric Delanoe for the
report.
- Fix call to dblink in function with PRAGMA AUTONOMOUS_TRANSACTION
and no arguments. Thanks to Eric Delanoe for the report.
- Fix package constant translation. Thanks to Eric Delanoe.
- Fix unwanted alias on join syntax. Thanks to Eric Delanoe
for the report.
- Fix regression on dbms_output.put* translation. Thanks to Eric
Delanoe for the report.
- Fix handling of comments in statements to try to preserve them at
maximum in the outer join rewriting.
- Do not declare variable when it is an implicit range cursor, it
do not need to be declared.
- Export implicit variable in FOR ... IN ... LOOP as an integer if
it don't use a select statement and export it as a RECORD when a
statement is found. Thanks to Eric Delanoe and Pavel Stehule for
the report.
- Reduce migration assessment weight for CONNECT BY.
- Fix derived table pasted two times in from clause. Thanks to Pavel
Stehule for the report.
- Fix some other unexpected ";" in function code. Thanks to Pavel
Stehule for the report.
- Remove %ROWTYPE in return type of function. Thanks to Pavel
Stehule for the report.
- Fix doubled AND in expression when a parenthesis is in front after
rewriting. Thanks to Eric Delanoe for the report.
- Fix unexpected ";" in function after post-body END when a comment
is present. Thanks to Eric Delanoe for the report.
- Fix unexpected ";" in some function variable declaration when a
comment is at end of the declare section. Thanks to Eric Delanoe
for the report.
- Remove %ROWTYPE in function that have not been replaced with RECORD
for cursor declaration. Thanks to Eric Delanoe for the report.
- Fix removing of WHEN keyword after END. Thanks to Pavel Stehule for
the report.
- Fix missing table name with alias in from clause due to comments in
the clause. I have also merge right and left outer join translation
function into a single one, most of the code was the same.
- Fix output order of outer join. Thanks to Pavel Stehule for the
report.
- Fix untranslated outer join in nested sub query. Thanks to Pavel
Stehule for the report.
- Rewrite again the decode() translation as a single function call
for all replacement before any other translation.
- Append table filter to check constraints extraction. Thanks to
danghb for the report.
- Fix issue with parenthesis around outer join clause. Thanks to
Pavel Stehule for the report.
- Move remove_text_constant_part() and restore_text_constant_part()
function into the main module.
- Include decode() replacement in recursive function call. Thanks
to Pavel Stehule for the report.
- Prevent removing of parenthesis on a sub select. Thanks to Pavel
Stehule for the report.
- Fix missing table exclusion/inclusion in column constraint export.
Thanks to danghb for the report.
- Fix an alias issue in view parsed from file.
- Fix parsing of view from file when no semi comma is found.
- Remove FROM clause without alias from migration assessment.
- Fix order of outer join during translation. Thanks to Pavel
Stehule for the report.
- Fix case of missing alias on subquery in FROM clause. Thanks to
Pavel Stehule for the report.
- Fix missing alias replacement in nested subqueries. Thanks to
Pavel Stehule for the report.
- Fix wrong addition of aliases to using() in join clause
- Fix nested decode replacement producing invalid CASE expression.
Thanks to Pavel Stehule for the report.
- Append aliases to subqueries in the from clause that do not have
one. Thanks to Pavel Stehule for the report.
2017 02 17 - v18.1
This release fix several issues reported on outer join translation
thanks to the help of Pavel Stehule and reapply the commit on virtual
column export that was accidentally removed from v18.0. It also adds
several new features:
- Remove CHECK constraints for columns converted into boolean using
REPLACE_AS_BOOLEAN column.
- Oracle function are now marked as stable by default as they can
not modify data.
Two new configuration directives have been added:
- DATE_FUNCTION_REWRITE: by default Ora2pg rewrite add_month(),
add_year() and date_trunc() functions set it to 0 to force Ora2Pg
to not translate those functions if translated code is broken.
- GRANT_OBJECT: when exporting GRANT you can now specify a comma
separated list of objects which privileges must be exported.
Default is to export privileges for all objects. For example
set it to TABLE if you just want to export privilege on tables.
and a new command line option:
- Add -g | --grant_object command line option to ora2pg to be able
to extract privilege from the given object type. See possible values
with GRANT_OBJECT configuration directive.
Here is the complete list of changes:
- Remove empty output.sql file in current directory with direct data
import. Thanks to kuzmaka for the report.
- Fix shell replacement of $$ in function definition in Makefile.PL
embedded configuration file. Thanks to kuzmaka for the report.
- Fix shell replacement of backslash in Makefile.PL embedded
configuration file. Thanks to kuzmaka for the report.
- Add warning level to virtual column notice.
- Fix comment in where clause breaking the outer join association.
Thanks to Pavel Stehule for the report.
- Add parsing and support of virtual column from DDL file.
- Reapply commit on virtual column export that was accidentally
removed in commit d5866c9. Thanks to Alexey for the report.
- Fix mix of inner join and outer join not translated correctly.
Thanks to Pavel Stehule for the help to solve this issue.
- Fix additional comma in column DEFAULT value from DDL input file.
Thanks to Cynthia Shang for the report.
- Fix comments inside FROM clause breaking translation to ANSI outer
joins. Thanks to Pavel Stehule for the report.
- Fix replacement of sdo_geometry type into function. Thanks to
Saber Chaabane for the report.
- Fix subquery in outer join clause. Thanks to Saber Chaabane for
the report.
- Fix duplicated subqueries placeholder in the from clause.
Thanks to Saber Chaabane for the report.
- Fix replacement of subquery place older during outer join rewrite.
Thanks to Saber Chaabane for the report.
- Add DATE_FUNCTION_REWRITE configuration directive. By default
Ora2pg rewrite add_month(), add_year() and date_trunc() functions
set it to 0 to force Ora2Pg to not translate those functions if
translated code is broken. Thanks to Pavel Stehule for the feature
request.
- Do not report error when -g is used but action is not GRANT.
Thanks to Shane Jimmerson for the report.
- Oracle function can not modify data, only procedure can do that,
so mark them as stable. Thanks to Pavel Stehule for the report.
- Missed some obvious combination like upper/lower case or no space
after AND/OR on outer join parsing and some other issues.
- Add missing call to extract_subqueries() recursively. Thanks to
Pavel Stehule for the report.
- Add full support of outer join translation in sub queries.
- Add translation of mixed inner join and Oracle outer join. Thanks
to Pavel Stehule for the report.
- Fix missing space between keyword AS and END from the decode()
transformation. Thanks to Pavel Stehule for the report.
- Fix parsing of outer join with UNION and translation to left join.
Thanks to Pavel Stehule for the report.
- Remove CHECK constraints for columns converted into boolean using
REPLACE_AS_BOOLEAN column. Thanks to Shane Jimmerson for the
feature request.
- Fix regression on SQL and PLSQL rewrite when a text constant
contained a semi-comma.
- Add the GRANT_OBJECT configuration directive. When exporting GRANT
you can specify a comma separated list of objects for which the
privileges will be exported. Default is export for all objects.
Here are the possibles values TABLE, VIEW, MATERIALIZED VIEW,
SEQUENCE, PROCEDURE, FUNCTION, PACKAGE BODY, TYPE, SYNONYM and
DIRECTORY. Only one object type is allowed at a time. For example
set it to TABLE if you just want to export privilege on tables.
You can use the -g option to overwrite it.
When used this directive prevent the export of users unless it is
set to USER. In this case only users definitions are exported.
- Add the -g | --grant_object command line option to ora2pg to be able
to extract privilege from the given object type. See possible values
with GRANT_OBJECT configuration directive.
- Improve replacement of ROWNUM by LIMIT+OFFSET clause.
- Fix extra semi-colon at end of statement.
- Override ora2pg.spec with Devrim's one but with String::Random
removing as it is no more used.
2017 01 29 - v18.0
This new major release adds several new useful features and lot of
improvements.
* Automatic rewrite of simple form of (+) outer join Oracle's
syntax. This major feature makes Ora2Pg become the first free
tool that is able to rewrite automatically (+) outer join in
command line mode. This works with simple form of outer join
but this is a beginning.
* Add export of Oracle's virtual column using a real column and
a trigger.
* Allow conversion of RAW/CHAR/VARCHAR2 type with precision in
DATA_TYPE directive. Useful for example to transform all RAW(32)
or VARCHAR2(32) columns into PostgreSQL special type uuid.
* Add export NOT VALIDATED state from Oracle foreign keys and check
constraints into NOT VALID constraints in PostgreSQL.
* Replace call to SYS_GUID() with uuid_generate_v4() by default.
* Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous
transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;".
* Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function
calls and other PL/SQL to plpgsql replacement. There should not
be any limitation in rewriting when a function contains a sub
query or an other function call inside his parameters.
* Refactoring of ora2pg to not requires any dependency other than
the Perl DBI module by default. All DBD drivers are now optionals
and ora2pg will expect an Oracle DDL file as input by default.
* Add export of Oracle's global variables defined in package. They
are exported as user defined custom variables and available in
a session. If the variable is a constant or have a default value
assigned at declaration, ora2pg will create a new file with the
declaration (global_variables.conf) to be included in the main
configuration file postgresql.conf file.
* Create full text search configuration when USE_UNACCENT directive
is enabled using the auto detected stemmer or the one defined in
FTS_CONFIG. For example:
CREATE TEXT SEARCH CONFIGURATION fr (COPY = french);
ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR
hword, hword_part, word WITH unaccent, french_stem;
CREATE INDEX place_notes_cidx ON places
USING gin(to_tsvector('fr', place_notes));
Changes and incompatibilities from previous release:
* FTS_INDEX_ONLY is now enabled by default because the addition of
a column is not always possible and not always necessary where a
simple function-based index is enough.
* Remove use to setweigth() on single column FTS based indexes.
* Change default behaviour of Ora2Pg in Full Text Search index
export.
A new command line option and some configuration directive have
been added:
* Option -D | --data_type to allow custom data type replacement
at command line like in configuration file with DATA_TYPE.
* UUID_FUNCTION to be able to redefined the function called to
replace SYS_GUID() Oracle function. Default to uuid_generate_v4.
* REWRITE_OUTER_JOIN to be able to disable the rewriting of Oracle
native syntax (+) into OUTER JOIN if rewritten code is broken.
* USE_UNACCENT and USE_LOWER_UNACCENT configuration directives to
use the unaccent extension with pg_trgm with the FTS indexes.
* FTS_INDEX_ONLY, by default Ora2Pg creates an extra tsvector column
with a dedicated triggers for FTS indexes. Enable this directive
if you just want a function-based index like:
CREATE INDEX ON t_document USING
gin(to_tsvector('pg_catalog.english', title));
* FTS_CONFIG, use this directive to force the text search stemmer
used with the to_tsvector() function. Default is to auto detect
the Oracle FTS stemmer. For example, setting FTS_CONFIG to
pg_catalog.english or pg_catalog.french will override the auto
detected stemmer.
There's also lot fixes of issues reported by users from the past two
months, here is the complete list of changes:
- Fix return type in function with a single inout parameter and a
returned type.
- Prevent wrong rewrite of empty as null when a function is used.
Thanks to Pavel Stehule for the report.
- Add the UUID_FUNCTION configuration directive. By default Ora2Pg
will convert call to SYS_GUID() Oracle function with a call to
uuid_generate_v4 from uuid-ossp extension. You can redefined it
to use the gen_random_uuid function from pgcrypto extension by
changing the function name. Default to uuid_generate_v4. Thanks
to sjimmerson for the feature request.
- Add rewrite of queries with simple form of left outer join syntax
(+) into the ansi form.
- Add new command line option -D | --data_type to allow custom data
type replacement at command line like in configuration file with
DATA_TYPE.
- Fix type in ROWNUM replacement expression. Thanks to Pavel Stehule
for the report.
- Add replacement of SYS_GUID by uuid_generate_v4 and allow custom
rewriting of RAW type. Thanks to Nicolas Martin for the report.
- Fix missing WHERE clause in ROWNUM replacement with previous patch
thanks to Pavel Stehule for the report.
- Fix ROWNUM replacement when e sub select is used. Thanks to Pavel
Stehule for the report.
- Fix wrong syntax in index creation with DROP_INDEXES enabled.
Thanks to Pave Stehule for the report.
- Remove replacement of substr() by substring() as PostgreSQL have
the substr() function too. Thanks to Pavel Stehule for the report.
- Move LIMIT replacement for ROWNUM to the end of the query. Thanks
to Pavel Stehule for the report.
- Fix text default value between parenthesis in table declaration.
Thanks to Pavel Stehule for the report.
- Fix return type when a function have IN/OUT parameter. Thanks to
Pavel Stehule for the report.
- Mark uuid type to be exported as text. Thanks to sjimmerson for
the report.
- Add EXECUTE to open cursor with like "OPEN var1 FOR var2;". Thanks
to Pavel Stehule for the report.
- Fix replacement of local type ref cursor. Thanks to Pavel Stehule
for the report.
- Add EXECUTE keyword to OPEN CURSOR ... FOR with dynamic query.
Thanks to Pavel Stehule for the report.
- Fix case sensitivity issue in FOR .. IN variable declaration
replacement. Thanks to Pavel Stehule for the report.
- Fix wrong replacement of cast syntax ::. Thanks to Pavel Stehule
for the report.
- Reactivate numeric cast in call to round(value,n).
- Close main output data file at end of export.
- Add virtual column state in column info report, first stage to
export those columns as columns with associated trigger.
- Fix unwanted replacement of REGEXP_INSTR. Thanks to Bernard
Bielecki for the report.
- Allow rewrite of NUMBER(*, 0) into bigint or other type instead
numeric(38), just set DATA_TYPE to NUMBER(*\,0):bigint. Thanks to
kuzmaka for the feature request.
- Export partitions indexes into PARTITION_INDEXES_....sql separate
file named. Thanks to Nicolas Martin for the feature request.
- Fix fatal error when schema CTXSYS does not exists. Thanks to
Bernard Bielecki for the report.
- Fix missing text value replacement. Thanks to Bernard Bielecki
for the report.
- Fix type replacement in declare section when the keyword END was
present into a variable name.
- Export NOT VALIDATED Oracle foreign key and check constraint as
NOT VALID in PostgreSQL. Thanks to Alexey for the feature request.
- Add object matching of regex 'SYS_.*\$' to the default exclusion
list.
- Fix UTF8 output to file as the open pragma "use open ':utf8';"
doesn't works in a global context. binmode(':encoding(...)') is
used on each file descriptor for data output.
- Improve parsing of tables/indexes/constraints/tablespaces DDL from
file.
- Improve parsing of sequences DDL from file.
- Improve parsing of user defined types DDL from file.
- Export Oracle's TYPE REF CURSOR with a warning as not supported.
- Replace call to plsql_to_plpgsql() in Ora2Pg.pm by a call to new
function convert_plsql_code().
- Move export of constraints after indexes to be able to use USING
index in constraint creation without error complaining that index
does not exists.
- Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous
transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;".
- Improve parsing of packages DDL from file.
- When a variable in "FOR varname IN" statement is not found in the
DECLARE bloc, Ora2Pg will automatically add the variable to this
bloc declared as a RECORD. Thanks to Pavel Stehule for the report.
- Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function
calls and other PL/SQL to plpgsql replacement. There should not
be limitation in rewriting when a function contains a sub query
or an other function call inside his parameters.
- Fix unwanted SELECT to PERFORM transformation inside literal
strings. Thanks to Pavel Stehule for the report.
- Fix bug in DEFAULT value rewriting. Thanks to Pavel Stehule for
the report.
- Fix replacement of DBMS_OUTPUT.put_line with RAISE NOTICE.
- Reset global variable storage for each package.
- Improve comment parsing in packages and prevent possible infinite
loop in global variable replacement.
- Add the REWRITE_OUTER_JOIN configuration directive to be able to
disable the rewriting of Oracle native syntax (+) into OUTER JOIN
if it is broken. Default is to try to rewrite simple form of
right outer join for the moment.
- Export types and cursors declared as global objects in package
spec header into the main output file for package export. Types
and cursors declared into the package body are exported into the
output file of the first function declared in this package.