forked from darold/ora2pg
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathchangelog
2129 lines (1888 loc) · 106 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
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
migration should be done on PG >= 9.1.
- Remove obsolete --xtable commande line option, should be replaced by
--allow, backward compatibility is preserved.
- Add EXTERNAL_TO_FDW configuration directive, disable by default, to
export all Oracle external tables as file_fdw foreign tables.
- Fix an other case where user defined type were not exported with an
ending semi-colon. Thank to Dominique Legrendre for the report.
- Fix export of user defined type with extra ");" at end of the type
definition and remove system types from export. Thanks to Dominique
Legendre for the report.
- Add PLSQL replacemement of currval. Thanks to Thomas Reiss for the
patch.
- Add PLSQL replacement of PIPELINED and PIPE ROW by SETOF and RETURN
NEXT.
- Add rewrite of Oracle DETERMINISTIC function into PostgreSQL
IMMUTABLE function.
- Fix copy during install on MacOSx and add /Y option to windows
install copy to force overwrite existing files. Thanks to Dennis
Spaag for the report.
- Fix issue exporting rows with perl ARRAYS ref. Thanks to Sorin
Gheorghiu for the report.
- Add report of number of database link in SHOW_REPORT output.
- Fix major bug on export of NUMBER with precision, they was all
exported as bigint. Thanks to Dominique Legendre for the report.
- Add progress bar during SHOW_REPORT export.
- Add detailed report about index in SHOW_REPORT output.
- Fix data export when schema was given in lower case. Thanks to
Dominique Legendre for the report.
- Add SHOW_REPORT export type to display a full summary of the Oracle
database content.
- PLPGSQL: add the name keyword to XMLELEMENT call. Thanks to Thomas
Reiss for the hint.
- Add SHOW_VERSION export type to display the version of Oracle.
- Add COLLATION to the keyword list. Thanks to Dominique Legendre for
the report
- Change documentation to add more detail on exporting Oracle views as
PostgreSQL tables based on the new VIEW_AS_TABLE directive.
- Add -a | --allow option and --view_as_table to ora2pg script.
- Add VIEW_AS_TABLE configuration option to allow export of view as
table and permit the additional use of the ALLOW or/and EXCLUDE
directive. Thanks to Dominique Legendre for the feature request.
- Removed conflict with transaction when DEFER_FKEY was enabled and
allow DEFER_FKEY and DROP_FKEY to be enabled both. Before, only
DEFER_FKEY was used in this case, now both are used and of course
DEFER_FKEY is wasted. Thanks to Dominique Legendre for the report.
- Directives ALLOW and EXCLUDE are now usable with all kind of object
following the export type.
- Rename TABLES directive as ALLOW to be less confusing, backward
compatibility is preserved.
- Thanks to Dominique Legendre for the feature request.
- Remove auto ordering of table export following the foreign keys to
fix an infinite loop. Thanks to Siva Janamanchi for the report.
- Rewrite the view as table export to reuse the same code as table
export, old code was resulting in issues with disable triggers and
deferring constraints.
- Remove alter session to set NLS_NCHAR that was returning error on some
installation. Thanks to Dominique Legendre for the report.
- Fix replacement of IS SELECT wrongly replaced by IS PERFORM in some
case. Thanks to Dominique Legendre fot the report.
UPGRADE: Almost all files have changed so a new installation is required.
2012 10 07 - v9.3
- Add auto detection of Oracle character set and the corresponding
PostgreSQL client encoding to use. NLS_LANG and CLIENT_ENCODING
configuration directives can be leaved commented, Ora2Pg will set
their values automatically.
- Add PL/SQL replacement of CURSOR IS SELECT by CURSOR FOR SELECT and
IS REF CURSOR by REFCURSOR. Thanks to Dominique Legendre for the
report.
- Fix missing set client_encoding orders into fonction or procedure
export file. Thanks to Dominique Legendre for the report.
- Fix not working SKIP configuration directive. Thanks to Siva
Janamanchi for the report.
- Add configuration directive NULL_EQUAL_EMPTY to disable the IS NULL
and IS NOT NULL replacement into PL/SQL code. Enabled by default.
Thanks to Dominique Legendre for the feature request.
- Remove exclusion of object names with the dollar sign. Thanks to
Konrad Beiske for the suggestion.
- Fix timestamp with time zone when microsecond is enabled. Thanks
to Siva Janamanchi for the report.
- Fix extra semi-column when PKEY_IN_CREATE is enabled. Thanks to
Siva Janamanchi for the report.
- Update configuration about boolean replacement.
- Allow any column type replacement as a boolean in PostgreSQL, values
will be converted as well. Thanks to Konrad Beiske for the feature
request.
- Add REPLACE_AS_BOOLEAN and BOOLEAN_VALUES configuration directives
to allow type replacement with a boolean. Thanks to Konrad Beiske
for the feature request.
- Add new configuration directive PKEY_IN_CREATE to add primary keys
definition in CREATE TABLE statement instead of creating them after
with an ALTER TABLE statement. For Greenplum database, primary key
must be created with the CREATE TABLE statement so you may want to
enable this configuration directive. Thanks to Siva Janamanchi for
the feature request.
- Add new configuration directive USE_RESERVED_WORDS to force Ora2Pg to
auto-detect PostgreSQL reserved words in Oracle object's names and
automatically double quote them. Thanks to Siva Janamanchi for the
feature request.
- SHOW_TABLE and SHOW_COLUMN will now display a warning when Oracle
object's name is a PG reserved words. Those names will need to be
renamed or double-quoted (see USE_RESERVED_WORDS).
- Add TIMESTAMP WITH LOCAL TIME ZONE Oracle type conversion to timestamp
and force timestamp with time zone format to use TZH:TZM. Thanks to
Siva Janamanchi for the report.
- Fix table and column replacement issues introduced with path that
removed double-quote when PRESERVE_CASE is disabled. Thanks to Steve
DeLong for the report.
- PLPGSQL convertion: Fix SELECT replacement with PERFORM in VIEW
declaration. Thanks to Thierry Capitaine for the report.
- Add display Ora2Pg type conversion map between Oracle originals types
and PostgreSQL's types when using export type SHOW_COLUMN. Thanks to
Thierry Capitaine for the feature request.
- Reorder command line options in ora2pg script usage and documentation.
- Add call to quote_ident() and quote_literal() into materialized
functions to secure parameters.
- Fix major issue in pl/sql to pl/pgsql conversion with multiple package
declaration in the same code record. Thanks to Marc Cousin for the
report.
- Add data type TIMESTAMP WITH TIME ZONE. Thanks to Siva Janamanchi for
the report.
- Add new export type: MVIEW to allow export of all materialized views
as snapshot materialized view (fully reload of the view).
- Add -e | --exclude option to Perl script ora2pg to exclude some given
objects from the export. It will override any value of the EXCLUDE
directive. The value is a coma separated list of object name or regex.
- Update domumentation about the EXCLUDE directive change.
- Allow exclusion from export of functions, procedures and functions in
package by specifying a list of name or regex in EXCLUDE directive.
Thanks to Keith Fiske from Omniti for the feature request.
UPGRADE: Almost all files have changed so a new installation is required.
2012 09 05 - v9.2
- In plpgsql conversion, SELECT without INTO becomes PERFORM.
- In plpgsql conversion, EXECUTE IMMEDIATE replaced by EXECUTE.
- Fix DATA_TYPE value in configuration file.
- Fix case sensitivity on data export using COPY mode.
- Directive XML_PRETTY is now disabled by default as it is better to
use getClobVal() to get the XML data out of an xmltype column.
- Add documentation about regex usage int EXCLUDE and TABLES directives.
- Remove all double-quote around object name when CASE_SENSITIVY is
disabled. Thanks to Dominique Legendre for the suggestion.
- Rename CASE_SENSITIVE by PRESERVE_CASE to be less confusing, backward
compatibility preserved. Thanks to Dominique Legendre for the request.
- Add support to user defined type data export. Before it will simply
export an array reference ARRAY(0xa555fb8), now the array is explored
and inserted as ROW(col1,col2,...). Thanks to Mathieu Wingel for the
feature request.
- Fix bug in direct data import in postgresql with COPY: pg_putcopydata
can only be called directly after issuing a COPY FROM command. Thanks
to Steve Delong for the report.
- Add warning at any debug level before abort when a data file already
exist during data export.
- Fix issue with oracle sensitivity when exporting data.
- Fix search_path on package export, indexed and constraints files on
TABLE export.
- Remove obsolete ORA_SENSITIVE configuration directive, thanks to
Dominique Legendre it is no more used.
- Force automatic conversion of PL/SQL when entry is an input file.
- Fix errors in main file for package loader with FILE_PER_FUNCTION
enabled.
- Fix case where package body where not exported.
- Add missing EXPORT_INVALID directive into default configuration file.
- Fix replacement of END followed by the name of the function, the semi-
colon was removed.
- Fix case sensitivity issue in INDEX creation.
- Fix case sensitivity issue in CHECK constraint and a typo in a
progress bar variable.
- Replace old DATA export type by INSERT in configuration file.
- Fix case sensitivity issue in ALTER TABLE ... ADD CONSTRAINT. Thanks
to David Greco for the report.
- Add set client_encoding before table output to handle character
encoding into comments and possibly objects names.
- Fix some case sensitivity issue with schema name. Thanks to Dominique
Legendre for the report.
- Do not display warning message about direct import if no connection
to a PostgreSQL database is defined.
- Allow multiple export type to be specified into the ora2pg -t command
line option.
- Dump progress bar to stderr instead of stdout to separate logs.
- Add new -q | --quiet option to perl script ora2pg to disable progress
bar.
2012 08 19 - 9.1
- Add progress bar to show data export progression.
- Add -q | --quiet option to ora2pg perl script to disable progress bar.
- Change documention about tnsnames.ora to mark it is not necessary.
- Add progress bar during data export, per table and globaly.
- Replace export type DATA by INSERT to mark the difference with COPY
and avoid confusion. Documentation is updated and full backward
compatibility preserved.
- Improve Oracle case sensitivity detection on column and update
documentation about ORA_SENSITIVE directive
- Direct import for COPY statement now used DBD::Pg and pg_putcopydata()
instead of a pipe to psql command.
- Fix case sentitivity issue on disabling/enabling all triggers.
- Add autodetection of case sensitivity with column name.
- Move trunc() to data_truc() convertion into the ALLOW_CODE_BREAK part.
- Update comment about FILE_PER_FUNCTION in configuration file.
- Fix NOT NULL constraint add twice, the first time in the column
definition and the second time in an ALTER TABLE ... ADD CONSTRAINT
... CHECK ( ... NOT NULL). Reported by Dominique Legendre.
- Add support to direct CALL of stored procedures in trigger definition.
Reported by Dominique Legendre.
- Remove index creation on primary and unique key autogenerated by
PostgreSQL.
- Fix PL/SQL to PLPGSQL automatic convertion on index when exporting
data with DROP_INDEX activated.
- Fix DROP_INDEX to only delete indexes that will be created at end.
- Fix search path when exporting data with EXPORT_SCHEMA disabled
- Add missing documentation about the LOGFILE directive
- Fix case sensitivity on sequence export. They will now always be
insensitive as in PostgreSQL its called is converted between quotes:
nextval('seq_name'). Reported by Dominique Legendre.
- Limit export of primary and unique key if KEEP_PKEY_NAMES is enabled
to those who are not autogenerated by Oracle. Reported by Dominique
Legendre.
- Trigger export is now limited to those belonging to table that are not