forked from darold/ora2pg
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathchangelog
2813 lines (2510 loc) · 144 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
2015 11 30 - v16.1
This release fixes several issues and adds some very useful features:
* Generate automatically a new import_all.sh shell script when using option
--init_project to help automate all import into PostgreSQL.
See sh import_all.sh -? for more information.
* Export Oracle bitmap index as PostgreSQL btree_gin index. This require the
btree_gin extension and PostgreSQL >= 9.4. This is the default.
* Auto set DEFINED_PK to the first column of a table that have a unique key
defined that is a NUMBER. This allow data of any table with a numeric
unique key to be extracted using multiple connexions to Oracle using -J
option. Tables with no numeric unique key will be exported with a single
process.
* Improve BLOB export speed by using hex encoding instead of escape. This
might speed up be BLOB export by 10.
* Allow use of LOB locator to retrieve BLOB and CLOB data to prevent having
to set LONGREADLEN. Now LONGREADLEN is set to 8KB. Old behavior using
LONGREADLEN can still be enabled by setting NO_LOB_LOCATOR to 0, given
for backward compatibility. Default is to use LOB locator.
* Ora2Pg will also auto detect table with BLOB and automatically decrease
DATA_LIMIT to a value lower or equal to 1000. This is to prevent OOM.
* Improving indexes and constraints creation speed by using the LOAD action
and a file containing SQL orders to perform. It is possible to dispatch
those orders over multiple PostgreSQL connections. To be able to use this
feature, PG_DSN, PG_USER and PG_PWD must be set. Then:
ora2pg -t LOAD -c config/ora2pg.conf -i schema/tables/INDEXES_table.sql -j 4
will dispatch indexes creation over 4 simultaneous PostgreSQL connections.
This will considerably accelerate this part of the migration process with
huge data size.
* Domain indexes are now exported as b-tree but commented to let you know
where possible FTS are required.
* Add number of refresh ON COMMIT materialized view in detailed report.
* Allow redefinition of numeric type, ex: NUMBER(3)::bigint to fix wrong
original definition in Oracle.
* Allow export of all schemas from an Oracle Instance when SCHEMA directive
is empty and EXPORT_SCHEMA is enabled. All exported objects will be
prefixed with the name of their original Oracle schema or search_path will
be set to that schema name. Thanks to Magnus Hagander for the feature
request.
* Allow use of COPY FREEZE to export data when COPY_FREEZE is enabled. This
will only works with export to file and when -J or ORACLE_COPIES is not
set or default to 1. It can be used with direct import into PostgreSQL
under the same condition but -j or JOBS must be unset or default to 1.
Thanks to Magnus Hagander for the feature request.
Some new configuration directives:
* BITMAP_AS_GIN: enable it to use btree_gin extension to create bitmap
like index with pg >= 9.4. You will need to create the extension by
yourself: "create extension btree_gin;". Default is to create GIN index,
when disabled, a btree index will be created.
* NO_LOB_LOCATOR: to disable use of LOB locator and extract BLOB "inline"
using a less or more high value in LONGREADLEN.
* BLOB_LIMIT: to force the value of DATA_LIMIT for tables with BLOB. Default
is to automatically set this limit using the following code:
BLOB_LIMIT=DATA_LIMIT; while (BLOB_LIMIT > 1000) BLOB_LIMIT /= 10
* COPY_FREEZE: use it to use COPY FREEZE instead of simple COPY to speedup
import into PostgreSQL.
Here is the complete list of other changes:
- Limite package function name rewrite to call with parenthesis after the
function name to avoid rewriting names of other objects.
- Fix extra replacement of function name with package prefix. On some
condition it was done multiple time.
- Set REPLACE_ZERO_DATE to -INFINITY in generic configuration when --mysql
is enabled.
- Fix extraction of partition with MySQL that was not limited to a single
database.
- Do some replacement on ORACLE_DNS and SCHEMA into generic configuration
when --mysql is used for better understanding.
- Add call to round() on -J parallelization when the auto detected column
is a numeric with scale.
- Add COMMIT to the difficulties migration assessment keywords as it need
context analyzing.
- Add call to cursor's %ROWCOUNT, %ISOPEN and %NOTFOUND to difficulties
migration assessment keywords.
- Replace call to CURSOR%ROWTYPE by RECORD. Thanks to Marc Cousin for the
report.
- Fix ALTER FUNCTION ... OWNER TO ... and REVOKE statement where functions
parameters were missing.
- Add Get_Env to the Oracle functions list for migration assessment.
- Disable variable NO_LOB_LOCATOR and set LONGREADLEN to 8192 bytes to use
LOB locators to extract BLOB in generic configuration file.
- Fix call method "disconnect" on unblessed reference at line 9998. Thanks
to Stephane Tachoires for the report.
- Exclude from export objects name matching /.*\$JAVA\$.*/ and /^PROF\$.*/.
- Fix migration assessment report when created during the package export.
- Force writing Oracle package body in separate files when FILE_PER_FUNCTION
is enabled and PLSQL_PGSQL disable to obtain package source code.
- Fix case where sequence max value is lower than start value, in this case,
set max value = start value.
- Fix missing newline after each package file to import in global package.sql
file when FILE_PER_FUNCTION is enabled.
- Remove export of user PUBLIC in GRANT export.
- Set DISABLE_TRIGGERS to 1 in generic configuration file auto generated when
ora2pg option --init_project is used.
- Remove call to quote_reserved_words() with index column when it we detect
a function based index, too much false positive are rewritten with SQL code
like CASE...WHEN.
- Update export_schema.sh to remove .sql files when there is not such object
leaving export directory empty.
- Prevent creating TBSP_INDEXES_tablespace.sql when no tablespaces are found
- Update documentation on WHERE clause on how to limit the number of tuples
exported for Oracle and MySQL to test data import.
- Fix unlisted spatial indexes in assessment report.
- Fix double quote on index name with index renaming and reserved keyword.
- Do not try to export tablespaces, privileges and audited queries as non DBA
user when USER_GRANT is enabled.
- Remove carriage return from list file.
- Force SCHEMA to database name with MySQL migration.
- Fix missing declaration of _extract_sequence_info(). Thank to Yannick DEVOS
for the report.
- Add documentation about COPY_FREEZE directive and add a note about export
of all schema.
- Remove systematic schema name appended to table name on KETTLE export, this
must only be true when EXPORT_SCHEMA is enabled.
- Fix TO_NUMBER() wrong replacement when a function is called as a parameter.
- Fix non converted DECODE() when they was called in an XMLELEMENT function.
- Suppress MDSYS.SDO_* from MDSYS call in migration assessment cost.
- Remove use of DBMS_STANDARD called with raise_application_error function
- Fix STRING type replacement
- Recreate README as a text file, not a man page.
- Reformat changelog to 80 characters.
- Add -t | --test command line option to ora2pg_scanner to be able to test
all connections defined into the CVS list file.
2015 10 15 - v16.1
This major release improve PL/SQL code replacement, fixes several bugs and
adds some major new features:
* Full migration of MySQL database, it just work like with Oracle database.
* Full migration assessment report for MySQL database.
* New script, ora2pg_scanner, to perform a migration assessment of all
Oracle and MySQL instances on a network.
* Add technical difficulty level in migration assessment.
* Allow migration assessment on client queries extracted from AUDIT_TRAIL
(oracle) or general_log table (mysql).
* Ora2Pg has a "made in one night" brand new Web site (still need some work).
See http://ora2pg.darold.net/
Example of technical difficulty level assessment output for the sakila database
with some more difficulties:
Total 83.90 cost migration units means approximatively 1 man-day(s).
Migration level: B-5
Here are the explanation of the migration level code:
Migration levels:
A - Migration that might be run automatically
B - Migration with code rewrite and a human-days cost up to 10 days
C - Migration with code rewrite and a human-days cost above 10 days
Technical levels:
1 = trivial: no stored functions and no triggers
2 = easy: no stored functions but with triggers, no manual rewriting
3 = simple: stored functions and/or triggers, no manual rewriting
4 = manual: no stored functions but with triggers or views with code
rewriting
5 = difficult: stored functions and/or triggers with code rewriting
This is to help you to find the database that can be migrated first with small
efforts (A and B) and those who need to conduct a full migration project (C).
This release has also some new useful features:
* Export type SHOW_TABLE now shows additional information about table type
(FOREIGN, EXTERNAL or PARTITIONED with the number of partition).
* Connection's user and password can be passed through environment variables
ORA2PG_USER and ORA2PG_PASSWD to avoid setting them at ora2pg command line.
* Improve PL/SQL replacement on ADD_MONTH(), ADD_YEAR(), TRUNC(), INSTR() and
remove the replacement limitation on DECODE().
* Add detection of migration difficulties in views, was previously reserved
to functions, procedures, packages and triggers.
* Replace values in auto generated configuration file from command line
options -s, -n, -u and -p when --init_project is used.
* Adjust lot of scores following new functionalities in Ora2Pg, ex: dblink or
synomyms are now easy to migrate.
There is some new command line options to ora2pg script:
* -m | --mysql : to be used with --init_project and -i option to inform
ora2pg that we work with a MySQL format
* -T | --temp_dir : option to be able to set a distinct temporary directory
to run ora2pg in parallel.
* --audit_user : option to set the user used in audit filter and enable
migration assessment report on queries from AUDIT_TRAIL (oracle) or
general_log table (mysql).
* --dump_as_sheet and --print_header options to be able to compute a CSV
file with all migration assessment from a list of oracle database.
* --dump_as_csv option to report assessments into a csv file. It will not
include comments or details, just objects names, numbers and cost.
Backward compatibility:
- Change NULL_EQUAL_EMPTY to be disabled by default to force change in the
application instead of transforming the PL/SQL.
This release adds some new configuration directives:
* MYSQL_PIPES_AS_CONCAT: Enable it if double pipe and double ampersand
(|| and &&) should not be taken as equivalent to OR and AND.
* MYSQL_INTERNAL_EXTRACT_FORMAT: Enable it if you want EXTRACT() replacement
to use the internal format returned as an integer.
* AUDIT_USER: Set the comma separated list of user name that must be used
to filter from the DBA_AUDIT_TRAIL or general_log tables.
* REPLACE_ZERO_DATE: "zero" date: 0000-00-00 00:00:00 it is replaced by a
NULL by default, use it to use the date of your choice.
* INDEXES_RENAMING: force renaming of all indexes using tablename_columnsname
Very useful for database that have multiple time the same index name or
that use the same name than a table.
* HUMAN_DAYS_LIMIT: default to 5 days, used to set the number of human-days
limit for migration of type C.
Here is the full list of other changes:
- Remove list of acknowledgment that was not maintained anymore and some
person may feel injured. Acknowledgment for patches or bug reports are
always written to changelog, so this part reports to it now.
- Fix bad trigger export when objects was enclosed in double quote and fix
an additional bug in WHEN clause export. Thanks to Cyrille for the report.
- Update documentation.
- Update Makefile.PL with new script to install and new configuration
directives in auto generated configuration file.
- Update with new and missing files.
- Add a Perl Module dedicated to MySQL database object discovery and export,
lib/Ora2Pg/MySQL.pm.
- Fix function based index type replacement in previous commit.
- Do not report indexes with just DESC as function based index like Oracle
report it. Thanks to Marc Cousin for the report.
- Some excluded table was missing in the previous patch.
- Remove use of DBI InactiveDestroy call when a fork is done and replace it
to a single use AutoInactiveDestroy at connection. This require DBI>=1.614.
- Add SDO_* tables and OLS_DIR_BUSINESSES in table exclusion list to fix issue
#124 when no schema is provided. Thanks to Kenny Joseph for the report.
- Fix partition prefix.
- Remove UNIQUE keyword from spatial index.
- Fix alter triggers function with missing parenthesis. Thanks to Spike Hodge
MWEB for the report.
- Fix export of foreign keys when they was defined in lowercase. Thanks to
Spike for the report.
- Fix wrong offset when rewriting ROWNUM with LIMIT+OFFSET. Thanks to Marc
Cousin for the report.
- Allow -INFINITY to be used to replace zero date.
- Migration assessment in hour-day are now set to 1 man-day, we do not need
such a precision and it is easier to process csv report. Thanks to Stephane
Tachoire for the report.
- Fix some issue with FDW and WKT spatial export. Add migration assessment
of queries from the AUDIT_TRAIL table.
- Adjust assessment units of some objects and add QUERY migration weight.
- Rewrite information about migration levels.
- Fix speedometer in progress bar, it will now shows the current speed in
tuples/sec and the speed and time related to a table when export ended for
the object. Thanks to Alex Ignatov for the report.
- Fix break line when export data using INSERT mode. Thanks to Vu Bui for
the report.
- Do not display line about non existent objects in migration assessment
reports.
- Fix date default value for date when value is 0000-00-00 00:00:00
- Suppress display of title for function and trigger details when there is
no details.
- Remove INSTR() from the list of Oracle function that are not supported.
It is now replaced by position().
- Fix condition to call _get_largest_tables().
- Fix some minor issues in OUT/INOUT type returned by a function.
- Fix default value that may appears unquoted.
- Fix several issues on partition export: column with function, index on
default partition table and plsql to plpgsql translation in check condition.
- Fix some minor issues.
- Replace values from command line options -s, -n, -u and -p in --init_project
auto generated configuration file. Thanks to Stephane Tachoire for the
feature request.
- Fix wrong object count in SHOW_REPORT. Thanks to Stephane Tachoire for
the report.
- Use DBA_SEGMENTS to find database size when USER_GRANT is disable, aka user
is a DBA
- Remove report of Migration Level when --estimate_cost is not enabled.
- Add missing BINARY_INTEGER for type replacement.
- Always exclude function squirrel_get_error_offset() that is created by the
Squirrel Oracle plug-in.
- Adjust assessment scores following new functionalities in Ora2Pg, ex:
autonomous transaction, dblink or synomyms are now easy to migrate.
- Remove man page from source, it is auto generated by Makefile.PL and make.
- Fix unterminated DECODE replacement when there was more than 5 parameters
to DECODE() and remove the limitation to 10 parameters. There is no more
limit in the number of decode parameters. Thanks to Mael Rimbault for the
report.
- Remove inclusion of unwanted object when exporting a limited list of view
with ALLOW.
- Disable unsupported recursive query used to reorder views when Oracle
version is 11gR1. Thanks to Mael Rimbault for the patch.
- Add PLPGSQL replacement of INSTR() by POSITION(). Thanks to Mael Rimbault
for the report.
- Add difficulty level information in migration assessment, this include a
new configuration directive HUMAN_DAYS_LIMIT (default to 5 days) to set
the number of human-days limit for migration of type C.
- Add MERGE with a migration cost of 3, still need work be replaced by
ON CONFLICT.
- Remove some redundant regular expressions.
- Fix escaped commas not working properly in MODIFY_TYPE. A MODIFY_TYPE
value like `TABLE1:COL4:decimal(9\,6)` was leading to a column like
`col4 decimal(8#nosep#3)` in the SQL dump file that was generated. This
fixes the output to be `col4 decimal(8,3)`. Thanks to Nick Muerdter for
the patch.
- Strip default "empty_clob()" values from table dumps. This function does
not exist in Postgres and is not necessary. Thanks to Nick Muerdter for
the patch.
- Fix undesired double quoting of column name in function based indexes.
- Fix issue with Perl < 5.8 "Modification of a read-only value attempted"
- Fix retrieving of table size on Oracle 8i.
- Add auto double quoting of object name with unauthorized characters.
Thanks to Magnus Hagander for the feature request.
- Automatically double quote object name beginning with a number
- Fix missing DESC part in descending indexes. Thanks to Magnus Hagander
for the report.
- Fix case where a column name in oracle is just a number (e.g. the column
is called "7"), it will be created in postgres without quoted identifier,
which fails. Thanks to Magnus Hagander for the report.
- Fix "reqs/sec" display in debug mode. Thanks to Laurent Martelli for
the patch
- Fix export if Oracle procedure is created without a parameter. Thanks to
dirkgently007 for the report.
- Fix CSV report output.
- Fix triggers from file parser.
- Add a test on triggers return to handle case where it is triggered on
DELETE + other(s) event(s). In this case a test is done on the TG_OP to
return OLD if event is DELETE or NEW in other case. Thanks to Dominique
Legendre for the suggestion.
- Change NULL_EQUAL_EMPTY to be disabled by default to force change of the
application instead of transforming the PL/SQL.
- Change score of SYNONYM and DBLINK in the migration assessment.
- Add conversion of Oracle type STRING into varchar(n) or text.
- Add information about libaio1 requirement for instant client
- Remove extra space when calling ora2pg_get_efile() used to export BFILE
into EFILE. Thanks to Dominique Legendre for the export.
2015 06 01 - v15.3
This is a maintenance release only that fixes several minor bugs and typos.
The configuration file have been entirely rewritten to classify configuration
directives in section for better understanding.
Here is the full list of changes:
- Ora2Pg will use DEFAULT_SRID when call to sdo_cs.map_oracle_srid_to_epsg()
returns an ORA-01741 error. Mostly because there's no SRID defined for that
column in ALL_SDO_GEOM_METADATA. The error message will still be displayed
but a warning will explain the reason and ora2pg will continue with default
value. Thanks to kazam for the report.
- Add current setting for NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT to the
SHOW_ENCODING report.
- Change default value for GEOMETRY_EXTRACT_TYPE to INTERNAL instead of WKT.
- Change generic configuration file behavior with BINMODE parameter commented
if it was previously uncommented. This will force to use the default value.
- Fix potential issue with max open file limit with unclosed temporary file.
Thanks to Marc Clement for the report.
- Fix use of SECURITY DEFINER in SYNONYM export.
- Fix parsing of editable function/procedure/package from input DML file.
- Fix case where variable $2 and $3 was null after a too early call of a new
substitution in read_view_from_file(). Thanks to Alex Ignatov for the patch.
- Add support to "create or replace editionable|noneditionable" from input DML
files. Thanks to Alex Ignatov for the report.
- Fix unknown column HIGH_VALUE from *_TAB_PARTITIONS in Oracle 8i. Thanks to
Sebastian Fischer for the patch.
- Fix call to ALL_MVIEW_LOGS object which not exists with Oracle 8i. Thanks to
Sebastian Fischer for the report.
- Fix Error ORA-22905 while Get the dimension of the geometry by looking at
number of element in the SDO_DIM_ARRAY. Thanks to jkutterer for the patch.
- Remove reordering export of view for Oracle database before 11g. Thanks to
kyiannis for the report.
- Fix several some typos and a bunch of misspelled. Thanks to Euler Taveira
for all the patches.
- Fix missing Oracle database version before looking at function security
definer. Thanks to kyiannis for the report.
2015 04 13 - v15.2
This new minor release fixes some issues and adds two new configuration
directives:
* ORA_INITIAL_COMMAND to be able to execute a custom command just after
the connection to Oracle, for example to unlock a security policy.
* INTERNAL_DATE_MAX to change the behavior of Ora2Pg with internal date
found in user defined types.
This version will also automatically re-order exported views taking into
account interdependencies.
Here is the full list of changes:
- Add INTERNAL_DATE_MAX configuration directive with default to 49 to be
used when reformatting internal date returned with a user defined type
and a timestamp column. DBD::Oracle only return the internal date format
01-JAN-77 12.00.00.000000 AM so it is difficult to know if the year value
must be added to 2000 or 1900. We takes the default behavior where date
are between 1950 and 2049.
- Remove extra CHAR and BYTE information from column type. Thanks to Magnus
Hagander for the report.
- Re-order views taking into account interdependencies. Thanks to Kuppusamy
Ravindran and Ulrike for the suggestion and the Oracle query.
- Fix case sensitivity in function based indexes. Thanks to Kuppusamy
Ravindran for the report.
- Fix PERFORM wrong replacement and infinite loop processing DECODE in some
condition. Thanks to Didier Brugat for the report.
- Fix replacement of boolean value in DEFAULT value at table creation.
Thanks to baul87 for the report.
- Add ORA_INITIAL_COMMAND configuration directive to be able to execute a
custom command just after the connection to Oracle, to unlock a policy for
example. Thanks to Didier BRUGAT for the feature request.
- Fix alias in from clause when an XML type is found. Thanks to Lance Jacob
for the record.
- Invert condition on excluding temporary file with Windows OS. Thanks to
kazam for the report.
- Remove start time and global number of rows from _dump_table() parameters
they are not used anymore.
- Remove use of temporary file on Windows operating system.
- Disable parallel table export when operating system is Windows.
- Fix export of objects with case sensitivity using ALLOW or EXCLUDE
directives. Thanks to Alexey Ignatov for the report.
- Fix export of triggers from recycle bin.
- Fix count of synonym in assessment report.
- Add list of tables created by OEM to the exclusion list.
- Fix look at default configuration file and set mode of export_schema.sh
to executable by default. Thanks to Kuppusamy Ravindran for the report.
- Add AUTHORIZATION to the list of PostgreSQL reserved word. Thanks to
Kuppusamy Ravindran for the report.
- Display a warning when an index has the same name as the table itself so
that you can renamed it before export. Thanks to Kuppusamy Ravindran for
the feature request.
- Fix export of function based indexes with multiple column. Thanks to
Kuppusamy Ravindran for the report.
- Modify ora2pg script to return 0 on success, 1 on any fatal error and 2
when a child process die is detected.
- Change the way the generic configuration file is handle during project
initialization. You can use -c option to copy your own into the project
directory. If the file has the .dist extension, ora2pg will apply the
generic configuration on it. Thanks to Kuppusamy Ravindran for the report
and features request.
- Add debug information when cloning the Oracle connection.
- Force return of OLD when the trigger is on DELETE event
2015 02 06 - v15.1
New minor release just to fix two annoying bugs in previous release.
- Fix replacement of function name which include SELECT in their name by
PERFORM. Thanks to Frederic Bamiere for the report.
- Fix creation of sources subdirectories when initializing a new migration project.
2015 02 04 - v15.0
This major release improve PL/SQL code replacement, fixes several bugs and
adds some new useful features:
- Add support to the PostgreSQL external_file extension to mimic BFILE
type from Oracle. See https://github.com/darold/external_file for
more information.
- Allow export of Oracle's DIRECTORY as external_file extension objects
This will also try to export read/write privilege on those directories.
- Allow export of Oracle's DATABASE LINK as Oracle foreign data wrapper
server using oracle_fdw.
- Allow function with PRAGMA AUTONOMOUS_TRANSACTION to be exported through
a dblink wrapper to achieve the autonomous transaction.
- Allow export of Oracle's SYNONYMS as views. Views can use foreign table
to create "synonym" on object of a remote database.
- Add trimming of data when DATA_TYPE is used to convert CHAR(n) Oracle
column into varchar(n) or text. Default is to trim both side any space
character. This behavior can be controlled using two new configuration
directives TRIM_TYPE and TRIM_CHAR.
- Add auto detection of geometry constraint type and dimensions through
spatial index parameters. This avoid the overhead of sequential scan
of the geometric column.
- Add support to export Oracle sub partition and create sub partition
for PostgreSQL with the corresponding trigger.
- ALLOW and EXCLUDE directives are now able to apply filter on the object
type. Backward compatibility can not be fully preserved, older definition
will apply to current export type only, this could change your export in
some conditions. See documentation update for more explanation.
- Add PACKAGE_AS_SCHEMA directive to change default behavior that use a
schema to emulate Oracle package function call. When disable, all calls
to package_name.function_name() will be turn into package_name_function_name()
just like a function call in current schema.
- Add FKEY_OPTIONS to force foreign keys options. List of supported options
are: ON DELETE|UPDATE CASCADE|RESTRICT|NO ACTION.
- Add rewriting of internal functions in package body, those functions will
be prefixed by the package name. Thanks to Dominique Legendre for the
feature request.
Some change can break backward compatibility and make configuration directives
obsolete:
- The ALLOW_PARTITION configuration directive has been removed. With new
extended filters in ALLOW/EXCLUDE directive, this one is obsolete.
Backward compatibility is preserved but may be removed in the future.
- ALLOW and EXCLUDE directives do not works as previously. Backward
compatibility may be preserved with some export type but may be broken
in most of them. See documentation.
- It is recommended now to leave the NLS_LANG and CLIENT_ENCODING commented
to let Ora2Pg handle automatically the encoding. Those directives may be
removed in the future.
Here is the full changelog of the release:
- Declares SYNONYM views as SECURITY DEFINER to be able to grant access to
objects in other schema.
- Fix wrong replacement of data type in function body. Thanks to Dominique
Legendre for the report.
- Fix missing column name replacement on trigger export when REPLACE_COLS
is defined. Thanks to Dominique Legendre for the report.
- Fix missing table replacement on trigger export when REPLACE_TABLES is
defined. Thanks to Dominique Legendre for the report.
- Fix case where IS NULL substitution was not working. Thanks to Dominique
Legendre for the report.
- Remove double exclusion clause when multiple export type is used with same
column name and no values defined.
- Allow parsing of DATABASE LINK and SYNONYM from a DDL file.
- Add DIRECTORY export type to export all Oracle directories as entries for
the external_file extension. This will also export read/write privilege
on those directories. Thanks to Dominique Legendre for the feature request.
- Review documentation about NULL_EQUAL_EMPTY.
- Fix missing code to replace IS NULL as coalesce(...). Thanks to Dominique
Legendre for the report.
- Add external_file schema to search_path when BFILE is set to EFILE in
directive DATA_TYPE. Thanks to Dominique Legendre for the request.
- Remove IF EXIST clause to oracle function created by Ora2Pg for BFILE
export. Thanks to Dominique Legendre for the report.
- Add support to the PostgreSQL external_file extension to mimic BFILE type
from Oracle. See https://github.com/darold/external_file for more information.
- Add auto detection of geometry constraint type and dimensions through the
spatial index parameters first. This avoid the overhead of sequential scan
of the geometric column.
- Remove lookup at package function when not required.
- Fix issue with database < 10g that do not have the DROPPED column into the
ALL_TABLES view. Thanks to Lance Jacob for the report.
- Add trimming of data when DATA_TYPE is used to convert CHAR(n) Oracle
column into varchar(n) or text column into PostgreSQL. Default is to
trim both side any whitespace character. This behavior can be controlled
using the new configuration directives TRIM_TYPE and TRIM_CHAR.
- Update copyright year.
- Add assessment cost for object TABLE SUBPARTITION and review cost for
object DATABASE LINK.
- Update documentation about SYNONYM export.
- Allow export of SYNONYMS as views with a new export type: SYNONYM.
- Fix object exclusion function with Oracle 8i and 9i. Thanks to Lance Jacob
for the report.
- Fix INTERVAL YEAR TO MONTH and DAY TO SECOND with precision.
- Remove unused pragma from the cost assessment.
- Suppress PRAGMA RESTRICT_REFERENCES, PRAGMA SERIALLY_REUSABLE and INLINE
from the PLSQL code. There is no equivalent and no use in plpgsql.
- Fix several issues in function/procedure/package extraction from file
input and some other related bug.
- Remove single slash and \\r from function code.
- Remove schema from package name with input file to avoid creating
function with SCHEMA.PKGNAME.FCTNAME
- Fix ALLOW/EXCLUDE ignored with type COPY or INSERT. Thanks to thleblond
for the patch.
- Fix setting of NLS_NUMERIC_CHARACTERS and NLS_TIMESTAMP_FORMAT with
multiprocess, the session parameters was lost with the cloning of the
database handle. Thanks to thleblond for the patch.
- Fix issue that could produce errors "invalid byte sequence" when dumping
data to pg database by forcing the client_encoding when PG_DSN is set.
Thanks to thleblond for the patch.
- Fix issue to add parenthesis with function with no parameters and wrong
use of PERFORM in cursor declaration. Thanks to hdeadman for the report.
- Fix broken export of function or procedure without parameter in package
body. Thanks to hdeadman for the report.
- Fix ERROR: "stack depth limit exceeded" generated by an infinite loop in
partition trigger when there is no default table when value is out of range.
- Add support to Oracle sub partition export.
- Fix issue with procedure in package without parameters.
- Enable DISABLE_SEQUENCE in generic configuration file.
- Fix unwanted alter sequence in data export when there is table allowed
or excluded.
- Fix initial default values of command line parameter that prevent value
in configuration file to be taken.
- Fix non working global definition of table in ALLOW and EXCLUDE directive
with COPY and INSERT export.
- Update ora2pg.spec, thanks to bbuechler for the patch.
- Close temporary files before deleting them, on Windows if they are not
explicitly closed there are not deleted. Thanks to spritchard for the
patch.
- Force schema name to be uppercase when PRESERVE_CASE is disable (default).
Thanks to Jim Longwill for the report.
- Add rewriting of internal functions in package body, those functions will
be prefixed by the package name. Thanks to Dominique Legendre for the
feature request.
- Fix type replacement in user defined type. Thanks to Dominique Legendre
for the report.
- Add filter with INSTEAD OF triggers on views to TRIGGER export type. Thanks
to Dominique Legendre for the feature request.
- Fix replacement of function name when PACKAGE_AS_SCHEMA is disabled.
- Fix PLSQL_PGSQL that was always set to 0 when -p was not used even if
configuration directive PLSQL_PGSQL was activated. Thanks to Dominique
Legendre for the report.
- Remove ALTER SCHEMA ... OWNER TO ... when CREATE_SCHEMA is not enable.
Thanks to Dominique Legendre for the report.
- Add DBLINK export to be created as foreign data wrapper server. Thanks to
the BRGM for the feature request.
- Remove ALLOW_PARTITION configuration directive, with extended filter in
ALLOW/EXCLUDE directive, this one is obsolete. Backward compatibility is
preserved.
- Add documentation about extended filters in ALLOW and EXCLUDE directive.
- Update documentation about VIEW_AS_TABLE and remove statement change with
export TYPE is VIEW.
- Add filter to grant export on functions, sequences, views, etc.
- Fix GRANT in ALLOW or EXCLUDE filters.
- Add commented order: "REVOKE ALL ON FUNCTION ... FROM PUBLIC;" when the
function is declared as SECURITY DEFINER.
- Prevent collecting column information with SHOW_TABLE export type.
- Fix default value SYSTIMESTAMP to CURRENT_TIMESTAMP, and remove DEFAULT
empty_blob(). Thanks to hdeadman for the report.
- ALLOW and EXCLUDE directives are now able to apply filter on the object
type. Backward compatibility can not be fully preserved, older definition
will apply to current export type only, this could change your export in
some conditions. See documentation update for more explanation. Thanks to
the BRGM for the feature request.
- Force function to be created with SECURITY DEFINER when AUTHID in table
ALL_PROCEDURES is set to DEFINER in Oracle. This only works with Oracle
>= 10g. Thanks to Dominique Legendre for the feature request.
- Add PACKAGE_AS_SCHEMA configuration directive to change default behavior
to use a schema to emulate Oracle package function call. When disable all
call to package_name.function_name() will be turn into package_name_function_name()
just like a function call in current schema. Thanks to the BRGM for the
feature request.
- Add a note to documentation about the way to convert srid into Oracle
database instead of in Ora2Pg. Thanks to Dominique Legendre for the hint.
- Fix documentation about SHOW_ENCODING export type.
- Remove use of REGEX_LIKE with Oracle version 9. Thanks to Lance Jacob for
the report.
- Replace new FKEY_OPTIONS by FKEY_ADD_UPDATE configuration directive with
three possible values: always, never and delete. It will force or not
Ora2Pg to add "ON UPDATE CASCADE" on foreign keys declaration.
- Allow FORCE_OWNER to work with all exported objects. Thanks to BRGM for
the feature request.
- Add FKEY_OPTIONS to force foreign keys options. List of supported options
are: ON DELETE|UPDATE CASCADE|RESTRICT|NO ACTION. Thanks to the BRGM for
the feature request.
- Fix ambiguous column in view extraction. Thanks to Dominique Legendre for
the report.
- Fix replacement of TYPE:LEN by boolean, ex: REPLACE_AS_BOOLEAN CHAR:1.
Thanks to jwiechmann for the report.
- Fix error ORA-00942 where Ora2Pg try to export data from a view defined
in VIEW_AS_TABLE configuration directive.
- Update list of excluded Oracle schema to the documentation.
- Fix export of all views with comments when VIEW_AS_TABLE is set.
- Fixed some typos in the generated sample configuration file. Thanks to
Hal Deadman for the patch.
- Limit column information export to the type of object extracted.
- Remove call to MDSYS in SQL code. Thanks to Dominique Legendre for the
report.
- Add more Oracle schema to the exclusion list.
- Fully remove join on DBA_SEGMENTS to retrieve the list of tables, views
and comments. Replaced by ALL_OBJECTS. Thanks to Dominique Legendre for
the help.
- Exclude JAVA\$.* tables and fix tables list query to include newly created
tables with no segments. Thanks to Dominique Legendre for the fix.
- Fix regex that convert all x = NULL clauses to x IS NULL to not replace
:= NULL too.
- Autodetect unusual characters in owner name when extracting data and used
it embeded into double quote.
- Replace single return with return new in trigger code. Thanks to Dominique
Legendre for the report.
2014 11 12 - v14.1
This is a maintenance release only mainly to add patches that was not
been applied in previous major release.
- Remove ALLOW_CODE_BREAK, it is no more useful.
- Change output of SHOW_ENCODING to reflect change to default encoding.
- Comment ALLOW_PARTITION in default configuration file
- Add QUERY and KETTLE export type in configuration file comments.
2014 11 05 - v14.0
This major release adds full export of Oracle Locator or Spatial geometries into
PostGis, SDO_GEOM functions and SDO_OPERATOR are also translated. This export
adds the following features:
1. Basic and complex geometry types support
2. Geometry data conversion from Oracle to PostGIS
3. Spatial Index conversion
4. Geometry metadata / constraints support
5. Spatial functions conversion
For spatial data export, you have three choice, WKT to export data using
SDO_UTIL.TO_WKTGEOMETRY(), WKB to export data using SDO_UTIL.TO_WKBGEOMETRY()
and INTERNAL to export geometry using a Pure Perl library. Unlike the first
two methods, INTERNAL is fast and do not raise Out Of Memory. The export is
done in WKT format so that you can verify your geometry before importing to
PostgreSQL.
Other additional major features are:
- Parallel table processing.
- Auto generation of migration template with a complete project tree.
- Allow user defined queries to extract data from Oracle.
Parallel table processing is controlled by the -P or --parallel command line
options or the PARALLEL_TABLE configuration directive to set the number of
tables that will be processed in parallel for data extraction. The limit is
the number of cores on your machine. Ora2Pg will the open one connection to
Oracle database for each parallel table extraction. This directive, when upper
than 1, will invalidate ORACLE_COPIES but not JOBS, so the real number of
process that will be used is (PARALLEL_TABLES * JOBS).
The two options --project_base and --init_project when used indicate to Ora2Pg
to create a project template with a work tree, a generic configuration file
and a shell script to export all objects from the Oracle database. So that you
just have to define the Oracle database connection into the configuration file
and then execute the shell script called export_schema.sh to export your
Oracle database into files. Here a sample of the command and the project's tree.
ora2pg --project_base /tmp --init_project test_project
/tmp/test_project/
config/
ora2pg.conf
data/
export_schema.sh
reports/
schema/
fdws/ functions/ grants/ kettles/
mviews/ packages/ partitions/
procedures/ sequences/ tables/
tablespaces/ triggers/ types/ views/
sources/
functions/ mviews/ packages/
partitions/ procedures/ triggers/
types/ views/
It create a generic config file where you just have to define the Oracle
database connection and a shell script called export_schema.sh. The
sources/ directory will contains the Oracle code, the schema/ will
contains the code ported to PostgreSQL. The reports/ directory will
contains the html reports with the migration cost assessment.
Sometime you may want to extract data from an Oracle table but you need a
custom query for that. Not just a "SELECT * FROM table" like Ora2Pg do but
a more complex query. The new directive REPLACE_QUERY allow you to overwrite
the query used by Ora2Pg to extract data. The format is TABLENAME[SQL_QUERY].
If you have multiple table to extract by replacing the Ora2Pg query, you can
define multiple REPLACE_QUERY lines. For example:
REPLACE_QUERY EMPLOYEES[SELECT e.id,e.fisrtname,lastname FROM EMPLOYEES e
JOIN EMP_UPDT u ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')]
Other new features are:
- Export of declaration of language C function. Previous version was
not exporting function with no code body like external C function.
- Export of COMMENT from views.
- Function to replace some call to SYS_CONTECT(USERENV, ...) by the
PostgreSQL equivalent.
- Add POSTGIS_SCHEMA configuration directive to add the dedicated
PostGis schema into the search_path.
- Add PG_SUPPORTS_IFEXISTS configuration directive to be able to suppress
IF EXISTS call in DDL statement generated by Ora2Pg.
- Triggers are now all excluded/allowed following the table names specified
in the ALLOW and EXCLUDED directives
- Allow automatic export of nested tables (TYPE+TABLE+COPY).
One change is not fully backward compatible: Ora2Pg now use UTF8 by default
on both side. On Oracle connection NLS_LANG is set to AMERICAN_AMERICA.AL32UTF8,
NLS_NCHAR to AL32UTF8. On PostgreSQL side CLIENT_ENCODING to UTF8. For export
that dump to files, Perl binmode is set to utf8. You can always change those
default setting in configuration file, but it is not recommanded.
Here is the full changlog of the release:
- Fix inline comments into function declaration. Thanks to Marcel Huber
for the report.
- Fix case where SELECT ... INTO was wrongly replaced by PERFORM.
- Fix DECODE() translation. Thanks to Dominique Legendre for the report.
- Add replacement of SDO_OPERATOR into PostGis relationships.
- Add replacement of SDO_GEOM spatial function to postgis ST_* functions.
- Add GEOMETRY_EXTRACT_TYPE configuration directive to specify the geometry
extracting mode: WKT (default), WKB and INTERNAL.
- Add a pure Perl library to export SDO_GEOMETRY as a WKT representation.
This is controlled by a new extraction type INTERNAL to use with the
GEOMETRY_EXTRACT_TYPE configuration directive.
- Remove USE_SC40_PACKAGE directive and any reference to this library,
it is not useful now that we have the INTERNAL geometry extraction mode.
- Fix replacement of varchar2 in PL/SQL function.
- Fix bug in type replacement when default values used function.
- Add export of declaration of language C function. Previous version was
not exporting function with no code body like external function.
- Fix create statement in export of view as table. Thanks to ntlis for the
report.
- Fix replacement of to_number without format.
- Add export of COMMENT from VIEWS.
- Add function to replace some call to SYS_CONTECT(USERENV, ...) by the
PostgreSQL equivalent.
- Fix parsing from file of tablespace.
- Fix wrong alias name in FROM clause when extracting XML data. Thanks
to Marc Sitges for the report.
- Fix export of comments in FDW export, might be COMMENT ON FOREIGN TABLE.
Thanks to David Fetter for the report.
- Fix broken export of function based indexes. Thanks to Floyd Brown for
the report.
- Fix sequence with negative minvalue/maxvalue and negative increment.
Thanks to jwiechmann for the report.
- Fix forced owner to schema to the value of FORCE_OWNER when it is set
to a user name.
- Fix create schema when FORCE_OWNER is enabled. Thanks to Dominique
Legendre for the report.
- Add POSTGIS_SCHEMA configuration directive to add a schema to the
search_path. Thanks to Dominique Legendre for the feature request.
- Returns NULL when a geometry is NULL instead of calling ST_AsText with
a null value. Thanks to Dominique Legendre for the report.
- Add more explanation about values of CONVERT_SID.
- Fix issue in DBMS_OUTPUT replacement.
- Fix exclusion of default objects from type export.
- When CONVERT_SRID is > 1 this value will be used to force the SRID value
on all export.
- Disable NULL_EQUAL_EMPTY in generic configuration when generating a project
tree.
- Add LOGMNR$ and RECAP$ in the exclusion objects list.
- Fix performance issue in extracting data from geometry column and add
AUDSYS,DVSYS and DVF to the list of schema to exclude.
- Prefix table name with schema name on queries for retrieving data to
avoid errors in multi schema export.
- Add SDO_* cost to migration report.
- Fix real number of Synonym that should be review.
- Fix wrong report of CTXSYS synonym.
- Enabled AUTODETECT_SPATIAL_TYPE by default.
- Remove KETTLE and FDW export from the auto generated project.
- Force the copy of /etc/ora2pg/ora2pg.conf.dist into the project directory
with no more look at the current ora2pg.conf. Force autodetection of
spatial type in the generic configuration.
- Huge performance gain on querying information about Spatial column. Thanks
to Dominique Legendre for the great help.
- Fix wrong use of table alias with SEGMENT_NAME.
- Add unified audit table (CLI_SWP$.*) from the exclusion list.
- Fix operator in check condition of range partitions. Thanks to Kaissa
Chellouche for the report.
- Add to the internal exclusion list tables generated by spatial indexes
MDRT_.*, sequences MDRS_.* and interMedia Text index DR$.*. Thanks to
Dominique Legendre for the report.
- Make REPLACE_TABLES and REPLACE_COLS work with VIEW. The view name and
the columns aliases will be replaced. Take care that the table name or
columns names in the statement will be kept untouched and need manual
rewriting. Thanks to Sven Medin for the feature request.
- Add PG_SUPPORTS_IFEXISTS configuration directive to be able to suppress
IF EXISTS call in DDL statement generated by Ora2Pg. PostgreSQL below
9.x do not support this keywords. Thanks to George Kowalski fot the
feature request.
- Fix wrong substitution in EXECUTE ... USING statement, where parameters
number was not prefixed by a $ sign. Thanks to Dominique Legendre for
the report.
- Fix document about KEEP_PKEY_NAMES that also affect unique key and not
only primary key as it was specified in the documentation. Thanks to
Dominique Legendre for the report.
- Add tables generated by statistics on spatial index (MDXT_.*) into the
internal exclusion list. This join the already excluded table generated
by partition logging (USLOG$_.*) and materialized view logs (MLOG$_.*,
RUPD$_.*)
- Add DEFAULT_SRID configuration direction to permit change of the internal
default EPSG srid 4326.
- Fix new line after search_path settings. Thanks to Dominique Legendre for
the report.
- Triggers are now all excluded/allowed following the table names specified
in the ALLOW and EXCLUDED directive, no more on there own name which had
little interest. Thanks to Dominique Legendre for the feature request.
- Add support to COPY export with Spatial objects. Thanks to Legendre
Dominique for the great help to solve this problem.
- Fix default SRID value when a NULL value is returned by Oracle, SRID 8307
and the corresponding EPSG SRID 4326.
- Update documentation on relation between PARALLEL_TABLES and FILE_PER_TABLE
- Add the -P or --parallel command line options and update documentation
about parallel table processing.
- Add PARALLEL_TABLES configuration directive to force ora2Pg to use on
process and one connection per table up to the number of CPU specified.
Thanks to menardorama for the feature request.
- Add PARALLEL_TABLES configuration directive to force ora2Pg to use on
process and one connection per table up to the number of CPU specified.
Thanks to menardorama for the feature request.
- Add --init_project and --project_base command line options to create a
migration template with a complete project tree, a generic configuration
file and script to automate export of all object in the project tree.
- Fix unwanted space before AND returned by limit_to_tables(). Thanks to
Alex Wang for the report.
- Add note about regex inclusion/exclusion not working with 8i database in
documentation
- Fix regex inclusion/exlusion of table that was not more working since the
inclusion of limit_to_tables() function. Thanks to alex wang for the patch
- Exclude dropped tables (those who are in the recycle bin) from export.
- When USER_GRANTS is disabled, aka login as dba user, force table list to
be checked against DBA_SEGMENTS with SEGMENT_TYPE of type table or table
partition. This could help solving some incomprehensible object found in
Oracle view ALL_TABLES.
- Fix query to retrieved list of tables, owner selection was set two time.
- Add support to automatic nested table export (TYPE+TABLE+COPY).
- Fix wrong export of materialized view log table. Thanks to Ronson Blossom
for the report.
- Update the SYSUSER array to exclude objects owned par those more users.
- Fix unwanted export of overflow table of an index-organized table. Thanks
to Ronson Blossom for the report.
- Update the SYSUSER array to exclude objects owned par those users.
- Display table owner in debug mode for SHOW_TABLE or SHOW_COLUMN.
- Add a section to give hint about converting Oracle outer join syntax to
ANSI. Thanks to Sven Medin for the links.
- Fix issue #82 again. Thanks to Sven Medin fro the report.
- Add first support to user defined queries to extract data from Oracle.
This feature add a new configuration directive named REPLACE_QUERY.
- Change program title when dump to file.
- Fix MODIFY_TYPE directive that was broken when using type with space
character. Thanks to Dmitry K. for the patch.
- Show missing view name in debug mode when exporting some views as table.
- Rewrite replace(a,b) with three arguments replace(a,b,'') for PostgreSQL.
Thanks to Dominique Legendre for the report.
- Convert all x <> NULL or x != NULL clauses to x IS NOT NULL. All x = NULL
are converted into x IS NULL. Thanks to Dominique Legendre for the report.
- Add warning at exit to signal when a OOM occurs. In that case, when a child
Ora2Pg process was silently killed by the OOM killer there was no information
that a failure occurs.
2014 06 02 - v13.0
This major release adds first support to export Oracle Spatial Objects to PostGis
Spatial objects. There's also a new configuration directive to allow logging of
statement failures to prevent Ora2Pg to abort and continue to load valid data.
The other main feature is the possibility to convert DDL files without needing an
Oracle database connection, until now this was reserved to files containing stored
procedures. There's also several bug fixes.
- Allow error logging during data import. This feature controlled by the
LOG_ON_ERROR directive allow you to not abort the data import process
when an error is encountered and to log to a file the COPY or INSERT
statement that generate the error. After fixing the statement you will
be able to load the missing data. Thanks to menardoram for the feature
request.
- Force export type to be INSERT when COPY is used and a table have a
GEOMETRY column. I can not find a solution to export as copy statement
for the moment. Thanks to Dominique Legendre and Vincent Picavet for
the help.
- Fix export of user defined type as object. Thanks to Shanshan Wang for
the report.
- Limit look up of objects to the ALLOW or EXCLUDE filter into the SQL
query instead of the Perl code to avoid retrieving huge list of objects
on such database. Thanks to menardorama for the feature request.
- Add support to spatial data export in INSERT mode. Still need some work
in COPY export mode if possible.
- Fix query to retrieve SRID that broken with patch on CONVERT_SRID.
- Fix wrong filter with ALLOW directive when getting list of partition.
- Add GRANT export read from an input file.
- Fix data type conversion when using input file and data type such
varchar2(10 BYTE).
- Add export of comment with TABLE and VIEW exports using an input file.
- Add extraction of TABLESPACE from an input file.
- Add support to SEQUENCE extraction from input file.
- Fix wrong filter with ALLOW directive when exporting partition. The
filter was done on partition name instead of table name, that mean
that setting ALLOW directive was resulting in no export at all. Thanks
to menardorama for the report.
- Add CONVERT_SRID configuration directive to control the automatic
conversion of SRID to standard EPSG using the Oracle SDO function
sdo_cs.map_oracle_srid_to_epsg() Oracle function. Thanks to Dominique
Legendre for the help.
- Fix a typo in the create index prefix on partitioned tables. Thanks
to menardorama for the patch.
- Fix non replacement of destination during SHOW_COLUMN and COPY export.
Using MODIFY_TYPE was only working in TABLE export.
- Force pl/sql conversion with TABLE export to replace advanced default
values. Fix code TRUNC(SYSDATE, MONTH) in default value and everywhere
that should be: date_trunc(month,LOCALTIMESTAMP). Thanks to menardorama
for the report.
- Fix code regarding unique partition index naming. Thanks to menardorama
for the report.
- Add PREFIX_PARTITION configuration directive. When enabled it will force
renaming all partition table name with the name of the parent table.
Thanks to menardoram for the feature request.
- Add AUTODETECT_SPATIAL_TYPE in configuration file and documentation
about this new directive.
- Add export of SDO_GEOMETRY column type. They are basically exported to
the non-constrained "geometry" type with SRID if defined. When the
configuration directive AUTODETECT_SPATIAL_TYPE is enable, Ora2Pg will
try to autodetect the geometry type, the dimension and the SRID used
to set a constrained geometry type. For example, in the first case
column shape with Oracle type SDO_GEOMETRY will be converted as:
shape geometry(GEOMETRY) or shape geometry(GEOMETRY, 4326)