forked from darold/ora2pg
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathchangelog
2352 lines (2100 loc) · 119 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 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)
and in the second case, with constrained geometry type:
shape geometry(POLIGONZ, 4326)
with a three dimensional polygon. Thanks to Vincent Picavet for the
feature request and specification.
- Add support to spatial index read from file.
- Add export of Oracle spatial index. For example, index:
CREATE INDEX cola_spatial_idx ON cola_markets(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
will be exported as
CREATE INDEX cola_spatial_idx ON cola_markets USING GIST(shape);
Thanks to Vincent Picavet / Oslandia for the feature request and explanations.
- Allow TRIGGER export to parse an input file with Oracle DML orders.
- Add PG_SUPPORTS_CHECKOPTION configuration directive to not remove
WITH CHECK OPTION in create view statement. It is supported in
PostgreSQL 9.4.
- Allow VIEW export to parse an input file with Oracle DML orders.
- Allow TABLE export to parse an input file with Oracle DML orders.
- Add SYNCHRONOUS_COMMIT configuration directive disabled by default.
This is the current behavior of Ora2Pg, it set synchronous_commit
to off before data import to PostgreSQL. This is only used when you
load data directly to PostgreSQL, the default is off to disable
synchronous commit to gain speed at writing data. Some modified or
old version of PostgreSQL, like Greenplum, do not have this setting.
- Add some useful information for Windows user in documentation. Thanks
to Roger Park for the report.
- Fix case when parentheses are omitted in index creation. Thanks to
Yuri Ushakov for the report.
- Fix export type PACKAGE when ALLOW is defined to extract only some
packages. Thanks to Maciej Bak for the report.
- Fix INSERT export where backslash should be escaped and single be
doubled in standard conforming string notation. Thanks to Yuri
Ushakov for the report.
- Add important note about LONGREADLEN and DATA_LIMIT that could need
to be adjusted to avoid out of memory. Thanks to Mike Kienenberger
for the patch.
- Fix case sensitivity issue with export of comment on column. Thanks
to Pierre Crumeyrolle for the report.
- Fix export of RAW data in COPY mode, was missing a backslash. Thanks
to jwiechmann for the report.
- Fix RAW data export in COPY and INSERT mode, RAW data type is returned
in hex by DBD::Oracle. Thanks to jwiechmann for the report.
- Fix one release 8i condition.
- Fix inexistent column USE_NO_INDEX with Oracle 8i and MVIEW export.
- Enclose call to utf8::encode and utf8::valid into eval.
- Fix export of constraint with Oracle 8i release.
- Fix unrecognized fatal error with 8i database. Thanks to UnvorherSeba
for the patch.
- Revert change level of error from fatal to error, when querying
materialized view.
- Change level of error from fatal to error, when querying materialized
view.
2014 01 28 - v12.1
This is a maintenance release with some minor bug fixes and a new configuration
directive, INDEXES_SUFFIX, to allow appending a suffix to indexes names.
- Fix example given for the WHERE configuration directive. Thanks to
Bob Treumann for the report.
- Add INDEXES_SUFFIX configuration option to allow append a suffix to
indexes names.
- Replace special charater ^M by \r as they are not supported by git.
- Fix IF EXISTS in alter table of sub _drop_foreign_keys. Thanks to
Francis Corriveau for the patch.
- Fix isolation level when exporting data. Thanks to Ludovic Penet for
the report.
- Fix regression when ora2pg tries to create foreign keys on tables or
to tables that are not selected for export. Thanks to Ludovic Penet.
- Add information about backslashed comma into directive MODIFY_TYPE
into Makefile.PL.
- Add missing MODIFY_TYPE definition in documentation.
- Allow backslashed comma into MODIFY_TYPE type redefinition. Example:
TABLE1:COL3:decimal(9\,6),TABLE1:COL4:decimal(9\,6).
Thanks to Mike Kienenberger for the report
- Fix missing single cote into create_materialized_view() call. Thanks
to Jacky Rigoreau for the patch.
- Fix some typo in documentation, thanks to Mike Kienenberger for the
report.
- Add a chapter about installing DBD::Oracle into documentation. Thanks
to Raghavendra for the patch.
- Fix case sensitivity on external table name with FDW export type.
Thanks to Guillaume Lelarge for the report.
- Fix export of materialized views when PG_SUPPORTS_MVIEW is disabled.
Thanks to Christian Bjornbak for the report.
- Update copyright.
2013 10 22 - v12.0
This release fixes lot of issues and three new features. Using REORDERING_COLUMNS
directive you will be able to reorder columns to minimized the footprint on disc,
so that more rows fit on a data page. The PG_SUPPORTS_MVIEW will allow you to
export materialized with native PostgreSQL 9.3 syntaxe. The USE_TABLESPACE variable
will allow you to export object using their original tablespace.
- Skip constraints on system internal columns (sys_nc...$) from export.
- Fix missing output directory in generic psql file for data loading.
- Add missing progress bar during TYPE and PARTITION export type.
- Remove duplicated message in debug mode during Oracle reconnection.
- Allow file input with create type declaration to use ora2pg converter.
Unsupported syntax is signaled into the output file.
- Exclude MLOG$.* and RUPD$.* table from export.
- Prevent export of indexes and constraints during FDW export type.
- Fix wrong total number of sequences shown in progress bar.
- Remove warning when PG_DSN is define during a export type that do not
support direct import into PostgreSQL.
- Auto switch prefix from DBA to ALL when error 942 is returned when
looking at tables informations. A hint is also displayed to ask for
activating USER_GRANTS or connect using a user with DBA privilege.
- Add REORDERING_COLUMNS configuration directive to allow reordering
columns during the TABLE export. This could help to minimized the
footprint on disc, so that more rows fit on a data page. Thanks to
Christian Bjornbak for the feature request.
- Fix call to unblessed reference at disconnect when direct import to
pg is not used. Thanks to Christian Bjornbak for the report.
- Fix regression in drop/create foreign keys and index during data
export. Thanks to Christian Bjornbak for the report.
- Fix truncate table error with parallel and direct data copy. Thanks
to keymaper for the report.
- Fix several other issues with parallel and direct data import.
- Fix trigger export on multi files when FILE_PER_FUNCTION is enabled.
- Fix issue on converting boolean values with non default values.
Thanks to Christian Bjornbak for the report.
- Fix boolean value for disabled key in default %BOOLEAN_MAP key/value.
- Fix case where INTO was wrongly replaced by INTO STRICT. Thanks to
Jacky Rigoreau for the report.
- Fix case where label after a END was not removed. Thanks to Jacky
Rigoreau for the report.
- Fix discard of input file parsing. Fix PERFORM replacement in PL/SQL
code wirh cursor. Thanks to Jacky Rigoreau for the report.
- Enable PG_SUPPORTS_MVIEW by default and update documentation.
- Replace DBA_DATA_FILES by USER_SEGMENTS to get database size to avoid
error ORA-00942. Thanks to Pierre Boizot for the report.
- Fix trigger conversion error. Thanks to Pierre Boizot for the report.
- Add support to PostgreSQL 9.3 materialized view syntaxe, this need a
new configuration directive PG_SUPPORTS_MVIEW to be enabled.
- Update default configuration file and documentation about USE_TABLESPACE.
- Add USE_TABLESPACE configuration directive to force ora2pg to use Oracle
tablespace name with table, constraints indexes and indexes if tablespace
in not in the default (TEMP, USERS, SYSTEM). Thanks to Rob Moolhuijsen
for the feature request.
- Allow DEFER_FKEY, when enabled during TABLE export, to create all foreign
keys as DEFERRABLE and INITIALLY DEFERRED. Thanks to David Greco for the patch.
- Fix non working ON_ERROR_STOP set to 0 during data export.
- Lot of code changes to fix dump to file in multiprocess mode. Ora2Pg will
also only drop/create constraints and indexes related to the allow/exclude
tables, thanks to Maciej Bak for the report.
- Force decimal character from Oracle output to be a dot. Thanks to Maciej Bak
for the report.
- Add default exclusion of Oracle recycle bin objects with name begining by BIN$.
- Fix escaping quote in table and column comments. Thanks to realyota for the report.
- Reduce DECODE migration cost from 2 to 1 unit.
- Reduce OUTER JOIN (+) migration cost from 3 to 1 unit.
- Add Time::HiRes to the requirement chapter for Perl <= 5.8. Thanks to
Mike Kienenberger for the report.
- Replace wrong use of --config instead of --conf into the documentation. Thanks
to Mike Kienenberger for the report.
- Fix regex used to rewrite CREATE VIEW code. Thanks to David Greco for
the patch.
- Fix an issue with oracle copies when primary key was negative. Thanks
to David Greco for the patch.
- Fix case sensitivity with SEQUENCE when preserve_case is enabled.
Thanks to Jean-Max Reymond for the report.
- Fix table COMMENT export when preserve_case is enabled. Thanks to
Jean-Max Reymond for the report.
2013 05 28 - v11.4
This release fixes others several major issues on migration cost assessment that
was not addressed in previous release, please upgrade.
- Fix other major issues in migration cost assessment.
- Redefine some migration cost values to be more precise.
2013 05 27 - v11.3
This release fixes several major issues on migration cost assessment, especialy
with stored procedures with lot of lines or if you have lot of comments in that
code. You may want to run your database evaluation again as the estimated times
can be up to tree time lower on huge PL/SQL code.
- Add full details about PL/SQL evaluation by ora2pg when --estimate_cost
or ESTIMATE_COST is enable. This will display cost units per keywords
detected in the function/package code.
- Fix wrong cost unit assessment on PL/SQL code size, this bug generated
very high migration cost assessment for functions/packages with lot of
lines. Please run your tests again, estimated times can be up to tree
time lower on huge code.
- Remove comments before code evalution.
- Fix file input parser for PL/SQL packages export when IS or AS was in
the next line than the CREATE PACKAGE BODY ...
- Exclude NOT NULL constraint from the count of CHECK constraints into
the TABLE report.
- Fix decimal precision in table migration assessment cost.
- Fix typo in changelog.
2013 05 01 - v11.2
This release fixes several major issues especially with direct import of data
into PostgreSQL and Windows port that was both broken.
- Update doc about Windows multiprocess issues and acknowledgements.
- Fix Windows OS issues using multiprocessing options by disabling
multiprocess support on this plateform. When -J or -j will be used a
warning will be displayed and Ora2Pg will simply run single process
like in previous 10.x versions. Thanks to Jean Marc Yao Adingra for
the report.
- Fix RAW and LONG RAW export to ByteA. Thanks to Prabhat Tripathi for
the report and testing.
- Fix patch regression on multiple TRUNCATE call for a single table.
Thanks to David Greco for the report.
- Placed calls to DB handle InactiveDestroy outside the forked process
to prevent fatal errors on Windows. Thanks to Jean Marc Adingra for
the report.
- Forked running processes are renamed into more readable name like
"ora2pg logger" for the progress bar, "ora2pg - querying Oracle" when
used with -J option and "ora2pg - sending to PostgreSQL" to better
know what is the current job of the process.
- Removed the use of /Y flag in Windows install script, this was causing
error "dmake: Error code 130, while making install_all". Thanks to
Jean-Marc Adingra for the report.
- Fix direct import to PostgreSQL that was just producing nothing. Thank
to David Greco for the patch.
- Fix ora2pg usage documentation.
- Add an underscore to CLIENT ENCODING in SHOW_ENCODING output to be the
same as the configuration directive.
UPGRADE: please reinstall all as most of the files have changed.
2013 04 07 - v11.1
This release adds partition data speed improvement by exporting data directly
from and into the destination partitioned table. There's also some bug fix on
RAW or LONG RAW data export and PL/SQL to PL/PGSQL code rewrite.
- Adjust cost assessment for indexes, tables and tables partition.
- Add comment to report of index partition about local index only.
- Fix position of TRUNCATE TABLE in output file.
- Fix export of data from RAW or LONG RAW columns, they was exported
as hex string. Now data are converted using utl_raw.cast_to_varchar2()
function before being escaped for insert into a bytea. Thanks to Alex
Delianis for the report.
- Fix issue with Oracle TIMESTAMP(0) data export that add a single
ending point, ex: "2008-08-09 00:00:00.", this ending character is
now removed by format_data_type(). Thanks to Pierre-Marie Petit for
the report.
- Fix typo on MODIFY_STRUCT description.
- Force DEBUG to off in default configuration file.
- Change range PARTITION operators in the check conditions, >= and <
replaced by > and <=, corresponding to Oracle VALUES LESS THAN.
- Add ALLOW_PARTITION to limit data export to a list of partition name.
- PLSQL: Fix wrong replacement of SELECT by PERFORM during VIEW export.
- Partitioned tables data is now imported directly into the destination
tables instead of inserted into the main table and dispatched by the
trigger. Ora2Pg will automatically detect the in/out table partition,
there's nothing to configure.
- PL/SQL: Do not allow decode() rewrite by case/when/else when there
is a function call in it.
- Fix Error when Compress::Zlib is not installed, this module is not
mandatory.
UPGRADE: please reinstall all as all files have changed.
2013 03 24 - v11.0
This is a new major release because it adds support to multiprocessing to export
data in parallel mode, this allow to improve speed during data import by more
than ten times. This multiprocessiing capabilities allow Ora2Pg to be closer than
the speed of any ETL. To compare speed or allow using Kettle for data import,
there's now a new export type to obtain Kettle XML transformation files. This
release adds also lot of work on speed improvement to scan Oracle database with
huge number of object.
- Add documentation about JOBS, ORACLE_COPIES, DEFINED_PK configuration
directive and informations about KETTLE export type.
- Add KETTLE export type to generate XML transformation file definition
for Penthatlo Data Integrator (Kettle). Thanks to Marc Cousin for the
work. Example of use:
ora2pg -c ora2pg.conf -t KETTLE -j 12 -J 4 -o loaddata.sh
- Fix major bug in export of auto generated named constraint. Thanks to
mrojasaquino fot the report.
- Show number of rows in the top largest tables.
- Add TOP_MAX description to the documentation.
- Add the TOP_MAX directive to default configuration file and update
documentation. Directive used to control the top N tables to show.
- Add top N of largest tables in SHOW_TABLE, SHOW_COLUMN and SHOW_REPORT
export type.
- Fix progressbar output when ora2pg is interrupted by ctrl+c.
- Add JOBS, ORACLE_COPIES and DEFINED_PK directives to configuration file.
JOBS replacing THREAD_COUNT but backward compatibility is preserve.
- Add 3 new command line options, -j | --jobs and -J | --copies, used to
set the number of connection to PostgreSQL and Oracle for parallel
processing. The third, -L | --limit is used to change DATA_LIMIT at
command line.
- Add multiprocess support on data export. With the help of Thomas Ogrisegg.
- Add more schema in SYSUSERS that should not be exported.
- Add full detailed information about SYNONYM in SHOW_REPORT.
- Add MODIFY_TYPE configuration directive to allow some table/column
type to be changed on PostgreSQL side during the export.
- Fix objects type count in progressbar of SHOW_REPORT.
- Restrict table and index in SHOW_REPORT to the tables defined in ALLOW
and EXCLUDE directives.
- Show total number of rows in SHOW_TABLE and SHOW_REPORT output.
- Add top 10 of tables sorted by number of rows in SHOW_TABLE and
SHOW_REPORT output.
- Fix typo in SYNONYM objects.
- Add report of top ten tables ordered y number of rows.
- Rewrite most of the Oracle schema storage information extraction for
speed improvement.
- Use Hash to store column informations.
- Fix %unique_keys declaration in _table() method.
- Remove call to _table_info() from SHOW_REPORT code as those informations
are already loaded with the _table() method.
- Fix missing column definition on TABLE export.
- Add progress bar during output generation following export type.
- Add STOP_ON_ERROR configuration directive to enable/disable the call to
ON_ERROR_STOP into generated SQL scripts. Thanks to Ludovic Penet for
the feature request.
- Huge speed improvement on columns informations retrieving.
- Fix progress bar to keep the total number of tables related to the ALLOW
or EXCLUDE configuration directives. Thanks to Ludovic Penet for the report.
- Change return type of function _table_info(), it now returns data instead
of the database handle.
- Improve speed on indexes and constraints extraction for database with huge
number of tables.
- Improve performance to retrieve columns information and comments.
- Remove report of column details during export in debug mode, use SHOW_COLUMN
instead.
- Remove call to upper() in objects owner condition to improve performance
with database with huge number of objects.
- Add a fix to not export foreign key for exclude tables. Thanks to Ludovic
Penet for the report.
- Fix Windows install issue with copying ora2pg.conf.dist. Thanks to
Dominique Fourdrinoy for the report.
- Increase the cost of Oracle function not converted to PG automatically.
UPGRADE: reinstall all is required to override the old installation, you may use the
new ora2pg.conf.dist file which included the new configuration directives.
2013 01 15 - v10.1
This release adds HTML report for migration cost assessment and some bug fix.
- Fix global where should not be overwritten. Thanks to Dan Harbin for
the patch.
- Fix bug/typo in boolean replacement, where a colon was used instead
of a single quote. Thanks to Alex Delianis for the patch.
- Update copyright.
- Add detection of additional Oracle functions for better migration
cost assessment.
- Update documentation.
- Force report detail in lowercase.
- Added information about the migration cost value to the reports.
- Add --dump_as_html command line option and DUMP_AS_HTML configuration
directive.
- Allow migration report to be generated as HTML.
- Separate report generation code from data collection code.
2012 12 12 - v10.0
This is the first version of Ora2Pg 10.x series, that is a major release.
Overall numerous improvements and bugs fixes there's now a new export type:
SHOW_REPORT that will output a report of all objects contained in your Oracle
database and some comments on how they will be exported. With this report you
can use a new directive ESTIMATE_COST to ask to Ora2Pg to evaluate the database
migration cost in terms of man days. There's also an other new configuration
directive EXTERNAL_TO_FDW, disable by default, to permit the export of all
Oracle external tables as file_fdw foreign tables.
The database content report and the migration cost estimation is a work in
progress so all feedback on these new features are welcome. Here is the complete
changelog:
- Update documentation about ora2pg usage and new feature.
- Fix quote escaping on table comments. Thanks to Sebastian Fischer.
- Fix some other issues with 8i databases, added database version auto-
detection to avoid printinf warning. Thanks to Sebastian Fischer for
the help.
- Allow null value in BFILE to the oar2pg_get_bfilename().
- Update documentation about BFILE export.
- Add drop function ora2pg_get_bfilename() when necessary.
- Add support to BFILE external path export by creating a function
ora2pg_get_bfilename( p_bfile IN BFILE ) to retrieve path from BFILE.
BFILE will be exported as text field with the full path to the file as
value. Note that this is the first time that Ora2Pg need write access
to the Oracle database, if you do not have BFILE or you have set the
corresponding PostgreSQL type asd bytea (the default) the function
will not be created.
- Fix a performance issue when extracting BLOB with a LongReadLen upper
than 1MB.
- Fix priviledge on schema created from Oracle package body. Thanks to
Dominique Legendre for the report.
- Add object type in comment before priviledge extraction.
- Order output of grant to groups grants by object types. This is useful
to quickly disable some SQL orders corresponding of not already loaded
objects. Thanks to Dominique Legendre for the feature request.
- Fix progress bar output.
- Fix priviledge on sequence, tablespace and schema.
- Fix backward compatibility with Oracle 8i, remove query with JOIN.
Thanks to Sebastian Fischer for the report.
- Fix backward compatibility with Oracle 8i on priviledge extraction.
Thanks to Sebastian Fischer for the report.
- Fix backward compatibility with Oracle 8i on index extraction. Thanks
to Sebastian Fischer for the report.
- Add more precision in cost estimation.
- Add somme other PL/SQL uncovered code detection.
- Add more debug information during data extraction.
- Removed progress bar when debug is enabled.
- Add report and estimate cost about CHECK constraint and function
based indexes.
- Update documentation about new export directives SHOW_REPORT and
ESTIMATE_COST.
- Add --estimate_cost and --cost_unit_value command line options.
- Add ESTIMATE_COST and COST_UNIT_VALUE to default configuration file.
- Rewritte and extend support to ROWNUM replacement.
- Remove incompatible grants between Oracle and the PortgreSQL export,
especially on views.
- Limit GRANT export to VALID object. Activate EXPORT_INVALID to enable
grants export on all object.
- Add export of VALID only views. To export all with INVALID ones you
must activate the EXPORT_INVALID directive. Thanks to Dominique
Legendre for the feature request.
- Fix issue in substr() pl/sql replacement, thanks to Dominique
Legendre for the report, plus add other code replacements in pl/sql.
- Fix issue with function name not on the same line as the create
statement - was affecting file input only.
- Add report of number of JOB object in the database (SHOW_REPORT).
- Add PL/SQL replacement of various form of EXEC function call.
- Remove creation of password with users that are not requiring
password. Thanks to Dominique Legendre for the feature request.
- A sql type and a precision can now be used in REPLACE_AS_BOOLEAN to
replace all filed with that type as a boolean, example:
NUMBER:1 will replace all field of type NUMBER(1) as a boolean.
- Fix grants on partition export, will now used all_ and user_ tables.
- Fix removing of newline in the DECLARE clause. Thanks to Dominique
Legendre for the report.
- PostgreSQL client_encoding is now forced to UTF8 when BINMODE is set
to utf8. Thanks to Dominique Legendre for the report.
- Replace DISABLE TRIGGER ALL by DISABLE TRIGGER USER following the value
if USER_GRANTS to avoid permission denied on constraint trigger when
data are load under a non PG superuser. Thanks to Dominique Legendre
for the report.
- Rename DISABLE_TABLE_TRIGGERS to DISABLE_TRIGGERS and set default value
to 0. Other values are USER or ALL following the connected user.
- Fix missing newline after comment in PL/SQL code. Thanks to Dominique
Legendre for the report.
- Fix report message on external table export.
- The export TYPE have been entirely rewritten to only export supported
user defined types. Exported are: Nested Tables, Object type, Type in
herited and Subtype, Varrays. Associative Arrays, Type Body and type
with member method are not supported.
- When FILE_PER_INDEX is enable, SQL order to move indexes in their
respective tablespace will be written into a dedicated file prefixed
by TBSP_INDEXES_.
- Fix location on external table export. Thanks to Thomas Reiss for
the help.
- PG_SUPPORTS_INSTEADOF is now activated by default, that mean that