12021 07 02 - v22.1 2 3This is a maintenance release to extend the feature of data export through the 4oracle_fdw PostgreSQL extension to migration that use the public schema and 5do not preserve case. 6 7There is also some other fixes: 8 9 - Fix compile_schema() call that breaks valid function based indexes by 10 adding compile_all => FALSE to DBMS_UTILITY.compile_schema(). 11 Thanks to Pawel Fengler for the patch. 12 - Force foreign table for data export as readonly to avoid accidental 13 write if import schema is not cleaned. 14 - Fix data export to file not possible since last changes for oracle_fdw 15 export. Thanks to Niels Jespersen for the report. 16 172021 06 26 - v22.0 18 19This release fix several issues reported since past three months and 20adds some new features and improvements. I must thanks MigOps Inc who 21hire me to drive Oracle to PostgreSQL migrations and to develop Ora2Pg. 22It's been a long time that I was looking for such a company and it is 23an amazing gift for the 20 years of Ora2Pg. All improvements and new 24new features developed during my work at MigOps will be available in 25the public GitHub repository, here are the new ones. 26 27 - Add export of data using oracle_fdw when FDW_SERVER is set and export 28 type is COPY or INSERT. Multi-process using -P or -J is fully supported 29 but option -j is useless in this case. Boolean transformation of some 30 columns or data type is also supported. Actually, expect that it works 31 just like data migration without oracle_fdw. This can improve the data 32 migration speed from 30 to 40% especially for BLOB export. 33 34 - Improve export performances with huge number of objects by avoiding join 35 between Oracle catalog tables. 36 37 - Set a maximum of assessment score for tables, indexes, sequences, 38 partitions, global temporary table and synonym following the number of 39 objects. 40 41 - Add detection of XML functions to the assessment cost. 42 43 - Allow to change the assessment cost unit value in the export_all.sh script 44 when ora2pg is used with options --init_project and --cost_unit_value. 45 46 - Remove pragma restrict_references from P/PSQL code, it is useless. 47 48 - Add the oracle schema to search_path in SQL files generated and improve 49 the migration assessment when USE_ORAFCE is enabled. 50 51 - Apply ALLOW and EXCLUDED filtered stored procedures at package extraction 52 level. Previous this patch there was no way to not export some package 53 functions or to exclude them from assessment. 54 55 - Add new tests to check sequences last values and number of identity columns 56 in both side. 57 58 - Apply ALLOW/EXCLUDE without object to table object by default in TEST 59 action. 60 61New configuration directives: 62 63 - Add ORACLE_FDW_TRANSFORM configuration directive to apply a transformation 64 to a column when exporting data. Value must be a semicolon separated list of 65 TABLE[COLUMN_NAME, <replace code in SELECT target list>] 66 For example to replace string 'Oracle' by 'PostgreSQL' in a varchar2 column 67 use the following. 68 ERROR_LOG[DBMS_TYPE:regexp_replace("DBMS_TYPE",'Oracle','PostgreSQL')] 69 Thanks to MigOps for the patch. 70 71 - Add DROP_IF_EXISTS configuration directive to add a statement 72 "DROP <OBJECT> IF EXISTS" before creating the object. Can be 73 useful in an iterative work. Default is disabled. Thanks to 74 dherzhau for the feature request. 75 76Backward compatibility: 77 78 There is a backward compatibility issue with old configuration files 79 where FDW_SERVER is set by default. This directive was not used when 80 exporting data, this is not the case anymore as it instruct Ora2Pg to 81 use the given foreign server to use oracle_fdw to migrate the data. 82 83Here is the full list of changes and acknowledgements: 84 85 - Fix replacement of TO_CLOB() function, now it is just removed and the 86 parenthesis are kept. Thanks to Rui Pereira for the report. 87 - Fix incorrect detection of cursor on dynamic query. Thanks to Rui 88 Pereira for the report. 89 - Fix quoting column names with spaces and dots. Dots are replaced by 90 underscore. Thanks to Veka for the report. 91 - Fix one case where DEFINED_PKEY with PRESERVE_CASE was not handled 92 correctly. Thanks to Veka for the report. 93 - Fix quoting of reserved keywords in CREATE INDEX columns names. Thanks 94 to Veka for the report. 95 - Fix column name starting with number not quoted in COMMENT. Thanks to 96 Veka for the report. 97 - Fix addition of PERFORM on call to stored procedures not prefixed by 98 the package name. Thanks to Rui Pereira fo the report. 99 - Fix search of ora2pg_conf.dist under Windows instead of ora2pg.conf.dist 100 when --init_project is used. Thanks to Julien Monticolo for the report. 101 - Fix translation from file of check constraint when created on same column, 102 only the last one was exported. Also shortened the prefix for constraint 103 naming, ora2pg_ckey becomes o2pc, ora2pg_ukey is now o2pu and ora2pg_fkey 104 is renamed into o2pf. Thanks to anvithaprabhu8 for the report. 105 - Replace wildcard precision * for numeric by 38. 106 - Fix incomplete listagg() conversion. Thanks to avandras for the report. 107 - Fix potential problem in last_day conversion when USE_ORAFCE is off and 108 a number is added or subtracted to the last day. Thanks to atlterry for 109 the report. 110 - Do not apply utf8 conversion of comments to input files. 111 - Fix termination of last writer process when parallel and quiet mode are 112 used together. Thanks to David Harper for the patch. 113 - Remove precision in number of digit in timestamp microseconds when 114 setting NLS_TIMESTAMP_FORMAT at session startup. Now use: 115 ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF' 116 instead of '.FF6'. This was possibly the cause of sporadic error: 117 ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtFetch) 118 Default is precision 6 so it may not change anything. Thanks to 119 Abhijeet Bonde for the report. 120 - Fix call to deprecated MySQL spatial function when version is after 5.7.6. 121 Thanks to naveenjul29 for the report. 122 - Fix false positive detection for nested table with MySQL export. 123 - Fix sequence export read from file. 124 - Add PG_VERSION to the documentation. Thanks to xinjirufen for the report. 125 - Fix error report when preparing query to ALL_IND_COLUMNS IC. Thanks to 126 ganeshakorde for the report. 127 - ora2pg_scanner: Fix detection of service_name in DSN. 128 - Fix error raise_application_error() with named parameters. 129 - Fix replacement of out parameters in triggers. 130 - Change sequence export result storage to hash instead of array. 131 - Fix package export when there is a comment between PACKAGE BODY and the 132 name of the package. 133 - Fix Perl error "malformed utf-8 character in substitution" when there is 134 character not in utf8 in the comment or constants. 135 - Fix detection of MySQL FUNCTION vs PROCEDURE for version >= 5.5. Thanks 136 to naveenjul29 for the report. 137 - Exclude nested tables from the export as it is not supported and it 138 always generate an error. A warning is raised. 139 - Fix column case in check constraints when PRESERVE_CASE is enabled. 140 - Fix search_path in direct PG data export when PG_SCHEMA is set. 141 - Fix -Infinity insert for direct PG data export. 142 - Fix drop indexes when PRESERVE_CASE is enabled. 143 - Remove potential double affectation for function with out parameter. 144 - Create immutable to_char function when used in an index. 145 - Replace dmake by gmake on Windows installation instruction. Thanks to 146 Julien Monticolo for the report. 147 - Fix MySQL version conditions. Thanks to Christoph Berg for the report. 148 - Fix HASH partitioning for duplicate WITH clause. 149 - Fix tests when no schema are set to compare all objects in all schemes. 150 Thanks to gp4git and dlc75 for the report. 151 - Apply ALLOW/EXCLUDE without object to table object by default in TEST 152 action. Thanks to Yony Sade for the feature request. 153 - Add DROP_IF_EXISTS configuration directive. Thanks to dherzhau for the 154 feature request. 155 - Fix regression in removing %ROWTYPE from function parameters. Thanks to 156 Eric Delanoe for the report. 157 - Fix not adding default values to parameters when this is an OUT parameter. 158 Thanks to Eric Delanoe for the report. 159 - Fix ALL_DIRECTORIES call be using table name relative to USER_GRANTS. 160 Thanks to Yoni Sade for the report. 161 - Change all remaining call to static ALL_* tables to a call relative to 162 USER_GRANTS. Thanks to Yoni Sade for the report. 163 - Fix export or partitioned table with unsupported partitioning type 164 like PARTITION BY REFERENCE. The table is created without partition 165 and a warning it fired as well as the following message in the output 166 file as a comment: -- Unsupported partition type, please check 167 1682021 04 01 - v21.1 169 170This release fix several issues reported since past six months and 171as usual adds some new features and improvements. 172 173 * Now that Orafce 3.15.0 has a definition for the REGEXP_* function, 174 makes the translation optional to USE_ORAFCE directive. 175 * Add set application name in connection to Oracle/MySql/PostgreSQL. 176 * Add translation of REGEXP_COUNT() and change assessment cost. 177 * Rewrite the way REGEXP_LIKE() is translated into regexp_match to 178 support modifiers. This rewrite also fix default behavior between 179 Oracle and PostgreSQL. 180 * Replace DBMS_LOB.GETLENGTH() by PostgreSQL octet_length() function. 181 * Add types correspondences for VARCHAR2 and NVARCHAR2 in DATA_TYPE 182 configuration directive. 183 * Add autodetection and support of geometry type, srid and dimension 184 for ArcGis geometries. 185 * Add conversion of default value in function parameters. 186 * Add -u | --unit option to ora2pg_scanner to be able to set the 187 migration cost unit value globally. 188 * Replace DBMS_LOB.SUBSTR() by SUBSTR() 189 * Remove TO_CLOB() it is useless, manual cast could be necessary. 190 * Replace IS JSON validation clause in CHECK constraints by 191 (CASE WHEN $1::json IS NULL THEN true ELSE true END) 192 When the code is invalid an error is fired. 193 * DISTINCT and UNIQUE are synonym on Oracle 194 195Backward compatibility changes: 196 197 - Force a column to be bigint if this is an identity column. Thanks 198 to MigOps.com for the patch. 199 - Fix EMPTY_LOB_NULL, enable/disable was inverted, keeping default 200 to enabled. Take care that in old ora2pg.conf it is disabled so it 201 will break backward compatibility with old configuration. 202 - Replace NO_LOB_LOCATOR with USE_LOB_LOCATOR and NO_BLOB_EXPORT 203 with ENABLE_BLOB_EXPORT to avoid confusion with double negative 204 variable. Backward compatibility is preserved with a warning. 205 - SRID for SDO_GEOMETRY export is now taken from the value not forced 206 from the metadata table. 207 208Here is the full list of changes and acknowledgements: 209 210 - Take Geometry SRID from the data and fallback to SRID defined in 211 metadata when not found. Thanks to Sebastian Albert for the report. 212 - Fix case where Ora2Pg temporary substitution of '' by placeholder 213 was not restored. Thanks to MigOps.com for the patch. 214 - Fix identity column export on unsupported Oracle 18c options. 215 Thanks to MigOps.com for the patch. 216 - Fix export of columns indexes created with single quote. 217 Thanks to MigOps.com for the patch. 218 - Fix replacement of keyword PROCEDURE by FUNCTION in constraints 219 constants definition. Thanks to marie-joechahine for the report. 220 - Replace IS JSON validation clause in CHECK constraints. Thanks to 221 marie-joechahine for the report and MigOps.com for the patch. 222 - Add support to ON OVERFLOW clause in LISTAGG replacement. 223 Thanks to MigOps.com for the patch. 224 - Fix incorrect handling of HAVING+GROUP BY rewriting. 225 Thanks to MigOps.com for the patch. 226 - Add replacement of TO_NCHAR by a cast to varchar. Thanks to 227 MigOps.com for the patch. 228 - Fix replacement of NOTFOUND when there is extra space or new line 229 in the WHEN EXIT clause. Thanks to MigOps.com for the patch. 230 - Fix a regression in NO_VIEW_ORDERING, it was not taken in account 231 anymore. Thanks to RonJojn2 for the report. 232 - Replace DATA_TYPE with DTD_IDENTIFIER in MySQL catalog queries for 233 version prior 5.5.0. Thanks to zejeanmi for the report. 234 - Fix import script to import sequences before tables. Thanks to 235 MigOps.com for the patch. 236 - Fix detail report of custom type in migration assessment. Thanks 237 to MigOps.com for the patch. 238 - Fix duplicate schema prefixed to SYNONYM. Thanks to dlc75 for the 239 reports. 240 - Replace NO_LOB_LOCATOR with USE_LOB_LOCATOR and NO_BLOB_EXPORT with 241 ENABLE_BLOB_EXPORT to avoid confusion with double negative variable. 242 Thanks to Rob Johnson for the report. 243 - Fix some missing replacements of NVL and rewrite !=-1 into != -1. 244 Thanks to MigOps.com for the patch. 245 - Fix ROWNUM followed by + or - operator and when no aliases are 246 provided. Thanks to MigOps.com for the patch. 247 - Add DBSFWUSER to the list of user/schema exclusion. Thanks to 248 MigOps.com for the patch. 249 - Fix regexp to not append subquery aliases on JOIN clause. Thanks 250 to Rui Pereira for the report. 251 - Handle PRESERVE_CASE and EXPORT_SCHEMA in sequence name. Thanks 252 to marie-joechahine for the report. 253 - Add CREATE SCHEMA statement to sequence export when EXPORT_SCHEMA 254 is enabled. Thanks to marie-joechahine for the report. 255 - Fix duplicate index name on subpartition. Thanks to Philippe 256 Beaudoin for the report. 257 - Exclude sequences used for IDENTITY column (ISEQ$$_). Thanks to 258 marie-joechahine for the report. 259 - Fix parsing from file of CREATE SEQUENCE. Thanks to Rui Pereira 260 for the report. 261 - In export_all.sh script use the database owner provided if it is a 262 superuser instead of postgres user. Thanks to jjune235 for the 263 feature request. 264 - Fix parsing of triggers when there is a CASE inside the code. 265 Thanks to Rui Pereira for the report. 266 - Add set application name in connection to Oracle/MySql/PostgreSQL. 267 Thanks to Yoni Sade for the patch. 268 - Fix double column alias when replacing ROWNUM. Thanks to Rui 269 Pereira for the report. 270 - Add translation of the REGEXP_COUNT function and change assessment 271 cost. 272 - Rewrite the way REGEXP_LIKE is translated into regexp_match to 273 support modifiers. This rewrite also fix default behavior between 274 Oracle and PostgreSQL. Thanks to otterrisk for the report. 275 - Add IS JSON to assessment. Thanks to marie-joe Chahine for the 276 report. 277 - Fix multi-columns RANGE partitioning. Thanks to Philippe Beaudoin 278 for the report. 279 - Improve reordering columns. Sort by fieldsize first, if same size 280 then it sorts by original position. Thanks to Sebastien Caunes for 281 the patch. 282 - Append partition's column to the primary key of the table as it 283 must be part of the PK on PostgreSQL. Thanks to xinjirufen for the 284 report. 285 - Fix partition export where PRESERVE_CASE was applied to Oracle 286 side. Thanks to schleb1309 for the report. 287 - Fix trigger export with column restriction. Thanks to Sebastien 288 Caunes for the report. 289 - Update installation information. 290 - Fix table reordering following data type. Thanks to Sebastien 291 Caunes for the patch. 292 - Fix incorrect variable name corresponding to DATA_EXPORT_ORDER 293 making this directive inefficient. Thanks to Ron Johnson for the 294 report. 295 - Fix translation of check constraint when read from file 296 - Fix EMPTY_LOB_NULL, enable/disable as inverted, keep default to 297 enabled. Take care that in old ora2pg.conf it is disabled so it 298 will break backward compatibility with old configuration. 299 - Fix false positive detection of input filename is the same as 300 output file. 301 - Rename variables SCHEMA_ONLY, DATA_ONLY and CONSTRAINTS_ONLY in 302 script import_all.sh to conform to their real use. Thanks to 303 Sebastien Caunes for the report. 304 - Fix comment detection breaking the package header parsing and 305 global variable detection. 306 - Fix ROWNUM detection for replacement by LIMIT 307 - Fix escaping of psql command in configuration file comment and 308 set default value for PG_VERSION to 12. 309 - Replace precision by exactness in documentation. Thanks to 310 Sebastien Caunes for the report. 311 - Prevent reducing DATA_LIMIT when NO_BLOB_EXPORT is enabled. 312 Thanks to Thomas Reiss for the report. 313 - Fix geometry type detection. 314 - Add autodetection of geometry type, srid and dimension for 315 ArcGis geometries. Thanks to changmao01 for the feature request. 316 - Fix call to ST_GeomFromText when no SRID is found. 317 - Fix case where OVERRIDE SYSTEM VALUE clause could be added if PG 318 version is < 10. Thanks to changmao01 for the report. 319 - Fix unwanted call to internal GEOM library for ArcGis geometries. 320 Thanks to changmao01 for the report. 321 - Exclude schema SDE (ArGis) from export. Thanks to changmao01 for 322 the report. 323 - prevent looking twice to same custom data type definition. 324 - Fix previous patch to catch SDO_GEOMETRY on lowercase regexp. 325 - Limit detection of geometry data type to SDO_GEOMETRY. 326 - Fix column name replacement in view definition. Thanks to Amit 327 Sanghvi for the report. 328 - Fix REPLACE_COLS parsing to allow space in column name. Thanks 329 to Amit Sanghvi for the report. 330 - Fix translation from file of triggers with WHEN clause. Thanks 331 to Rui Pereira for the report. 332 - Fix column name kept lowercase in the MOD() clause when -J is 333 used. Thanks to Code-UV for the report. 334 - Keep case of PG_SCHEMA definition when used in TEST action. 335 - Fix data export for columns with custom data type. Thanks to 336 Aymen Zaiter for the report. 337 - Fix missing bracket with || operator in CREATE INDEX. Thanks to 338 traxverlis for the report. 339 - Fix export of single row unique function base index. Example: 340 CREATE UNIQUE INDEX single_row_idx ON single_row ((1)); 341 Thanks to unrandom123 for the report. 342 - Update documentation about schemas used in TEST action. 343 - Disable materialized view export with MySQL export it is not 344 supported. Thanks to naveenjul29 for the report. 345 - Fix table alias detection in Oracle (+) join rewrite. 346 - Fix an infinite loop in Oracle (+) join rewrite when there is no 347 table aliases and the table is prefixed by its schema. Thanks to 348 Olivier Picavet for the report. 349 - Fix MODIFY_STRUCT when column name need to be escaped. Thanks to 350 helmichamsi10 for the report. 351 - Fix empty PARTITION BY () clause. Thanks to Aymen Zaiter. 352 - Fix export of global variable from package description when there 353 is no package body. Thanks to naveenjul29 for the report. 354 - Add package description export when dumping package source, 355 previously only the package body was dump. This will allow to 356 check global variables export. 357 - Whilst working on the Reproducible Builds effort (https//reproducible-builds.org/) 358 it appears that ora2pg could not be built reproducibly. Thanks to 359 Chris Lamb for the patch. 360 - Fix case of NUMBER(*,10) declaration. Oracle has a precision of 1 to 38 361 for numeric. Even if PostgreSQL allow a precision of 1000 use 38 to 362 replace junk parameter. Thanks to xinjirufen for the report. 363 - Add conversion of default value in function parameters, like syssdate 364 rewriting for example. Thanks to unrandom123 for the report. 365 - Fix a regression in data encoding when exporting data introduced in 366 commit fa8e9de. Thanks to gp4git for the report. 367 - Add debug information about the environment variables used before 368 connecting to Oracle. 369 - Fix case of duplicate between unique index and unique constraint with 370 multiple columns. Thanks to gp4git. 371 3722020 10 12 - v21.0 373 374This release fix several issues reported since last release and adds 375several new features and improvements. 376 377 * Add clause OVERRIDING SYSTEM VALUE to INSERT statements when the 378 table has an IDENTITY column. 379 * Considerably increase the speed to generate the report about the 380 migration assessment, especially for database with huge number of 381 objects. 382 * Reduce time passed in the progress bar. Following the number of 383 database objects we were spending too much time in refreshing the 384 progress bar. 385 * Add number of identity columns in migration assessment report. 386 * Make assessment details report initially hidden using HTML5 tags 387 <details><summary> 388 * Improve speed of BLOB/CLOB data export. Oracle recommends reading 389 from and writing to a LOB in batches using a multiple of the LOB 390 chunk size. This chunk size defaults to 8k (8192). Recent tests 391 show that the best performances can be reach with higher value 392 like 512K or 4Mb. 393 * Add progress bar when --oracle_speed is use in single process mode. 394 * Automatically activate USER_GRANTS when the connection user has no DBA 395 privilege. A warning is displayed. 396 * Complete port to Windows by using the Windows separator on stdout 397 redirection into a file at ora2pg command line call and improve 398 ora2pg_scanner port on Windows OS. 399 * Add rewrite of MySQL JOIN with WHERE clause instead of ON. 400 * Add MGDSYS (Oracle E-Business Suite) and APEX_040000 to the list 401 of schemas excluded from the export. 402 * Supply credentials interactively when a password is not defined in 403 the configuration file. Need the installation of a new Perl module 404 Term::ReadKey. 405 * Add supports oracle connections "as sysdba" with username "/" and 406 an empty password to connect to a local oracle instance. 407 * Add translation of PRIVATE TEMPORARY TABLE from Oracle 18c into 408 PostgreSQL basic temporary table, only the default behavior for 409 on commit change. 410 411New command line options: 412 413 * Add new command line option to ora2pg_scanner: -b | --binpath DIR 414 to set the full path to directory where the ora2pg binary stays. 415 Might be useful only on Windows OS. 416 * Add -r | --relative command line option and PSQL_RELATIVE_PATH 417 configuration directive. By default Ora2Pg use \i psql command to 418 execute generated SQL files if you want to use a relative path 419 following the script execution file enabling this option will use 420 \ir. See psql help for more information. 421 422New configuration directives: 423 424 * NO_VIEW_ORDERING: 425 By default Ora2Pg try to order views to avoid error at import time 426 with nested views. With a huge number of views this can take a very 427 long time, you can bypass this ordering by enabling this directive. 428 * NO_FUNCTION_METADATA 429 Force Ora2Pg to not look for function declaration. Note that this 430 will prevent Ora2Pg to rewrite function replacement call if needed. 431 Do not enable it unless looking forward at function breaks other 432 export. 433 * LOB_CHUNK_SIZE 434 See explanation in the new features and improvement list. 435 * ALTERNATIVE_QUOTING_REGEXP 436 To support the Alternative Quoting Mechanism ('Q' or 'q') for String 437 Literals set the regexp with the text capture to use to extract the 438 text part. For example with a variable declared as 439 c_sample VARCHAR2(100 CHAR) := q'{This doesn't work.}'; 440 the regexp to use must be: 441 ALTERNATIVE_QUOTING_REGEXP q'{(.*)}' 442 ora2pg will use the $$ delimiter, with the example the result will 443 be: 444 c_sample varchar(100) := $$This doesn't work.$$; 445 The value of this configuration directive can be a list of regexp 446 separated by a semi colon. The capture part (between parenthesis) is 447 mandatory in each regexp if you want to restore the string constant. 448 449Backward compatibility changes: 450 451 - Default for NO_LOB_LOCATOR is now 1 to benefit from the LOB_CHUNK_SIZE 452 performances gain. 453 - Enable schema compilation (COMPILE_SCHEMA set to 1) by default to 454 speed up DDL extraction. 455 - Change the behavior of Ora2Pg with the parameters that follows a 456 parameter with a default value. Ora2Pg used to change the order of the 457 parameter's function to put all parameters with a default value at end 458 of the list which need a function call rewrite. This have been abandoned 459 now any parameter without default value after a parameter with a default 460 value will be appended DEFAULT NULL. 461 462Here is the full list of changes and acknowledgements: 463 464 - Fix unwanted references to PK/UK when DROP_INDEXES is enabled. 465 - Fix comparison between function name in TEST report. 466 - Fix duplicates on retrieving partitions information. 467 - Improve SHOW_TABLE report about partitioned tables information. 468 - Drop code about removing DEFAULT NULL in functions parameters. Thanks to 469 chaluvadi286 for the report. 470 - Fix two other case where materialized view can be listed in the table list. 471 - Fix case where materialized view can be listed in the table list. Thanks 472 to Thomas Reiss for the report. 473 - Fix %ROWTYPE removing to be restricted to REF CURSOR. Thanks to 474 jagmohankaintura-tl for the report. 475 - Fix PG functions count when comparing Oracle functions count in TEST action. 476 Remove useless -l option to import_all.sh auto generated script. 477 - Fix PRESERVE_CASE on schema name for functions extracted from a package. 478 - Fix search_path adding public default schema. 479 - Apply PRESERVE_CASE to partition by involved columns. 480 - Add IF EXIXTS to create schema to avoid error when import_all.sh is run 481 several time. 482 - Fix sort order of comment on columns for tables and views. 483 - Fix warning about data export from nonexistent table resulting of index 484 lookup on nested table. 485 - Fix infinite loop in global variables package extraction. Thanks to Thomas 486 Reiss for the report. 487 - Fix global variables and packages export when comments are present in the 488 package description. 489 - Add information about XML_PRETTY size limit to 4000 490 - Fix column name in indexes when PRESERVE_CASE is enabled. Thanks 491 to Julien traxverlis for the report. 492 - Fix Top 10 of largest tables sort order. Thanks to Tom Vanzieleghem 493 for the patch. 494 - Fix duplicates between indexes and constraints. Thanks to sdpdb and 495 Jon Betts for the report. 496 - Fix SYSDATE replacement and possible infinite loop in SYSDATE parsing. 497 Thanks to pbidault for the report. 498 - Fix export of Oracle TEXT indexes with USE_UNACCENT disabled. Thanks to 499 Eric Delanoe for the report. 500 - Add new configuration directive ALTERNATIVE_QUOTING_REGEXP to support 501 the Alternative Quoting Mechanism ('Q' or 'q') for String Literals. 502 Thanks to just-doit for the report. 503 - Fix OF clause missing in update triggers. Thanks to just-doit for 504 the report. 505 - Fix IS NULL translation in WHERE clause of UPDATE statement. Thanks 506 to Eric Delanoe for the report. 507 - Remove DDL export of LOG indexes on materialized views. 508 - Fix unexpected materialized view listed in table export. Thanks to 509 jagmohankaintura-tl for the report. 510 - Fix default values with single quote in create table DDL. Thanks to 511 justdoit for the report. 512 - Fix double quote in CREATE TRIGGER code and applying of preserve case 513 on column name. 514 - Supply credentials interactively when a password is not defined in 515 configuration file. Thanks to rpeiremans for the patch. 516 - Add supports oracle connections "as sysdba" with username "/" and 517 an empty password to connect to a local oracle instance. Thanks to 518 rpeiremans for the patch. 519 - Fix documentation about materialized view export. 520 - Fix export order of comments on columns. 521 - Fix export of views comments when no schema is used for export and 522 export schema is activated. 523 - Fix cast in replacement with TO_NUMBER and TO_CHAR in indexes. Thanks 524 to Kiran for the report. 525 - Add MGDSYS (Oracle E-Business Suite) to the list of schemas excluded 526 from the export. Thanks to naveenjul29 for the report. 527 - Add more information about PG_DSN use. Thanks to Pepan7 for the report. 528 - Update copyright year. 529 - Fix regression where "SET client_encoding TO ..." was missing data file 530 header. Thanks to Emmanuel Gaultier for the report. 531 - Fix EDITABLE vs EDITIONABLE parsing. Thanks to Naveen Kumar for the report. 532 - Fix typos in documentation. Thanks to swallow-life, ChrisYuan, Edward Betts, 533 Jack Caperon and cavpollo for the patches. 534 - Add OVERRIDING SYSTEM VALUE to INSERT statement when the table has an 535 IDENTITY column. Thanks to Robin Windey for the report 536 - Remove empty parenthesis of identity column options 537 - Limit sequence/identity column value to bigint max 538 - Add an example of DBD::Oracle DSN with 18c. 539 - Fix parsing of identity column from file. Thanks to deepakp555 for the 540 report. 541 - Fix quoting of identifier when PRESERVE_CASE is enable and no particular 542 schema is specified. Thanks to mkgrgis for the report. 543 - Move setting of search_path before truncate table. Thanks to Michael Vitale 544 for the report. 545 - Add explanation about TEST and SIZE migration assessment values. 546 - Mark XMLTYPE as having LOB locator. 547 - Fix XMLTYPE columns that are exported as lob locator. Thanks to Tamas for 548 the report. 549 - Fix a problem of data export throughput that was slowing down all along 550 the export when multiprocess for output was not used. Ora2Pg was forking 551 a process for each chunk of data (see DATA_LIMIT) which is useless when 552 write output is done on a single process (-j 1) and slow down the export. 553 Thanks to markhooper99 and Tamas for reporting, testing and finding the 554 source of the issue. 555 - Fix progress bar in multiprocess mode, update was not displayed at each 556 chunk of data processed. 557 - Add internal debug information for progress bar. 558 - Add debug information for SHOW_REPORT 559 - Fix a long pending issue with custom data type export. Thanks to 560 jhollandsworth for the patch. 561 - Fix LOB data export with value changed to NULL when the CLOB value was 0. 562 Thanks to jhollandsworth for the report. 563 - Fix escape format issue with COPY and bytea. Thanks to Christoph Noel and 564 dwbrock62 for the report. 565 - Add LD_LIBRARY_PATH and PATH prerequisite to run ora2pg. 566 - Fix use of the HIGH_VALUE column in partition listing with Oracle 9i. Thanks 567 to Francisco Puga for the report. 568 - Update the table row count logic to incorporate the PostgreSQL table FQN as 569 established through the set_pg_relation_name routine. Thanks to Jacob 570 Roberts for the patch. 571 - Add the PostgreSQL FQN when printing the results in the TEST function. Thanks 572 to Jacob Roberts for the patch. 573 - Do not look forward function with the SHOW_* action 574 - Fix BLOB export where \x was escaped. Thanks to Christophe Noel for the 575 report. 576 - Update Ora2Pg.pm to fix symbol in column name in create index statement. 577 Thanks to kpoluektov for the patch. 578 - Fix package function extraction when there is a start of comment (/*) in 579 a constant string. Thanks to Tiago Anastacio for the report. 580 - Fix type detection in package declaration. Thanks to Tiago Anastacio for 581 the report. 582 - Avoid displaying error ORA-22831 when exporting LOB. This error can 583 appears when LOB chunk size is different from default 8192. The error 584 has no incidence on the export so we can just ignore it. This patch 585 also use DBD::Oracle ora_lob_chunk_size() method to gather chunk the 586 chunk size of the LOB, fallback to 8192 if not available. Thanks to 587 joedbadmin for the report. 588 - Disable direct report of Oracle errors, all error should be handled at 589 Ora2Pg level. 590 - Fix MySQL data export with allow/exclude objects. Thanks to Manuel Pavy for 591 the report. 592 - Fix exclude/allow object feature in MySQL export that was not working since 593 release 19.0. Thanks to Manuel Pavy for the report. 594 - Add rewrite of MySQL JOIN with WHERE clause instead of ON. Thanks to Marc 595 Rechte for the report. 596 - Fix issue with custom type when multiprocess is used. 597 - Fix progress bar on final total estimated data in multiprocess mode. 598 - Fix ORACLE_HOME path in README.md. Thanks to Lubos Cisar for the patch. 599 - Fix missing replacement with PERFORM in CASE ... WHEN statements. Thanks to 600 Eric Delanoe for the report. 601 - Fix duplicate ora2pg command in iteration. 602 - Improve ora2pg_scanner port on Windows OS. Thanks to Marie Contencin for the 603 report. 604 - Add perl call to all ora2pg commands when the scanner is executed on 605 Windows system as the shebang is not recognized. Thanks to Marie Contencin 606 for the report. 607 - Fix several issue with compressed output. Thanks to Bach Nga for the report. 608 - Fix translation of CURSOR IS SELECT with a comment before the SELECT. 609 Thanks to Izaak van Niekerk for the report. 610 - Fix export of procedures as PostgreSQL procedures with version 11. 611 - Add APEX_040000 to the schemas exclusion list. Thanks to Don Seiler for the 612 report. 613 - Fix possible unquoted default values. Thanks to Marc Rechte for the report. 614 - Fix MySQL SET TRANSACTION clause when TRANSACTION is set to readonly or 615 readwrite this is not supported so fall back in READ COMMITTED isolation 616 level in this case. Thanks to Marc Rechte for the report. 617 - Fix export of functions, column DATA_TYPE does not exists in table 618 INFORMATION_SCHEMA.ROUTINES before MySQL 5.5.0. Replace it with column 619 DTD_IDENTIFIER for prior version. Thanks to Marc Rechte for the report. 620 - Fix double quote in CREATE TRIGGER code and applying of preserve case on 621 column name. 622 6232019 01 18 - v20.0 624 625This release fix several issues reported during the last three months 626and adds several new features and improvement. The change of major 627version is related to backward compatibility break with the removed of 628most PG_SUPPORTS_* configuration directives and their replacement with 629the new PG_VERSION directive. 630 631New features and configuration directives in this release: 632 633 * Add PG_VERSION configuration directive to set the PostgreSQL major 634 version number of the target database. Ex: 9.6 or 10. Default is 635 current major version at time of a new release. This replace the 636 old PG_SUPPORTS_* configuration directives. 637 * Removed all PG_SUPPORTS_* configuration directives minus 638 PG_SUPPORTS_SUBSTR that is related to Redshift engine. 639 * Export of BFILE as bytea is now done through a PL/SQL function to 640 extract the content of a BFILE and generate a bytea data suitable 641 for insert or copy into PostgreSQL. 642 * Foreign keys that reference a partitioned table are no more 643 exported. 644 * Show table name on Oracle side during export using at connection 645 time: DBMS_APPLICATION_INFO.SET_ACTION(table_name); 646 * When the date format is ISO and the value is a constant the call 647 to to_date() is removed and only the constant is preserved. For 648 example: to_date(' 2013-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS') 649 is replaced by a simple call to: ' 2013-04-01 00:00:00'. 650 This rewrite is limited to PARTITION export type when directive 651 PG_SUPPORTS_PARTITION is enabled. 652 * Add DATA_EXPORT_ORDER configuration directive. By default data 653 export order will be done by sorting on table name. If you have 654 huge tables at end of alphabetic order and are using multiprocess, 655 it can be better to set the sort order on size so that multiple 656 small tables can be processed before the largest tables finish. 657 In this case set this directive to size. Possible values are name 658 and size. Note that export type SHOW_TABLE and SHOW_COLUMN will 659 use this sort order too, not only COPY or INSERT export type. 660 * Add NO_BLOB_EXPORT configuration directive. Exporting BLOB could 661 take time and you may want to export all data except the BLOB 662 columns. In this case enable this directive and the BLOB columns 663 will not be included into data export. The BLOB column must not 664 have a NOT NULL constraint. Thanks to Ilya Vladimirovich for the 665 * Add PREFIX_SUB_PARTITION to enable/disable sub-partitioning table 666 prefixing in case of the partition names are a part of the sub- 667 partition names. 668 * Add special replacement for case of epoch syntax in Oracle: 669 (sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy'))*24*60*60 670 is replaced by the PostgreSQL equivalent: 671 (extract(epoch from now())) 672 673Here is the full list of changes and acknowledgements: 674 675 - Export indexes and constraints on partitioned table with pg >= 11. 676 - Fix incorrect replacement of NLS_SORT in indexes. 677 - Bring back DISABLE_UNLOGGED feature. Thanks to Jean-Christophe 678 Arnu for the patch 679 - Fix CREATE SCHEMA statement that was not written to dump file. 680 - Fix DBMS_APPLICATION_INFO.set_action() call, old Oracle version 681 do not support named parameters. 682 - Fix duplicate index name on partition. Thanks to buragaddapavan 683 for the report. 684 - Add support to new configuration directive PG_VERSION to control 685 the behavior of Ora2Pg following PostgreSQL version. 686 - Fix error in creation of default partition with PostgreSQL 10. 687 Thanks to buragaddapavan for the report. 688 - Fix missing export of single MAXVALUE partition, this will produce 689 the following range partition: ... FOR VALUES FROM (MINVALUE) TO 690 (MAXVALUE) Previous behavior was to not export partition as it is 691 better to not partition the table at all. However it is declared 692 in Oracle so it is better to export it to see what can be done. 693 Thanks to buragaddapavan for the report. 694 - Do not export foreign keys that reference a partitioned table. 695 Remove NOT VALID on foreign keys defined on a partitioned 696 table if present. Thanks to Denis Oleynikov for the report. 697 - Fix export of BFILE as bytea. Ora2Pg now use a PL/SQL function to 698 extract the content of a BFILE and generate a bytea data suitable 699 for insert or copy into PostgreSQL. Thanks to RickyTR for the 700 report. 701 - Add TIMEZONE_REGION and TIMEZONE_ABBR to migration assessment, no 702 direct equivalent in PostgreSQL. Remove NSLSORT not used in 703 migration assessment. Thanks to buragaddapavan for the report. 704 - Fix output of multiple export type specified in TYPE directive. 705 - Rewrite and renaming of _get_sql_data() function into 706 _get_sql_statements(). 707 - Limit CURSOR weight in migration assessment to REF CURSOR only, 708 other case are all covered. REF CURSOR might need a review to see 709 if they need to be replaced with a SET OF RECORD. 710 - Fix replacement of EMPTY_CLOB() or EMPTY_BLOB() with empty string 711 when EMPTY_LOB_NULL is disabled and NULL when it is enabled. 712 - Prefix output file with the export type in multiple export type 713 mode, ex: sequence_output.sql or table_output.sql. Thanks to 714 buragaddapavan for the report. 715 - Fix export of data from an Oracle nested table. Thanks to rejo 716 oommen for the report. 717 - Removed cast to timestamp from partition range. Thanks to 718 buragaddapavan and rejo-oommen for the report. 719 - Fix partition default syntax. Thanks to rejo-oommen for the 720 report. 721 - Apply missing SYSUSERS schemas exclusion on columns and partition 722 listing. Thanks to rejo-oommen for the report. 723 - Add warning about parameter order change in output file. 724 - Show table name on Oracle side during export using at connection 725 time: DBMS_APPLICATION_INFO.SET_ACTION(table_name); 726 Thanks to Denis Oleynikov for the feature request. 727 - Report change in ORA_RESERVED_WORDS into documentation. 728 - Add references in the keyword list of ORA_RESERVED_WORDS. 729 - Fix the missing white space in some lines while creating 730 import_all.sh file. Thanks to Fabiano for the patch. 731 - Fix translation of infinity value for float. Thanks to Damien 732 Trecu for the report. 733 - Fix default value in timestamp column definition when a timezone 734 is given. Thanks to buragaddapavan for the report. 735 - Fix missing export of index and constraint in a partitioned 736 table when DISABLE_PARTITION is enabled. Thanks to Denis Oleynikov 737 for the report. 738 - Prevent PARTITION BY when DISABLE_PARTITION is enabled. Thanks to 739 Denis Oleynikov for the report. 740 - Add DATA_EXPORT_ORDER configuration directive. By default data 741 export order will be done by sorting on table name. If you have 742 huge tables at end of alphabetic order and are using multiprocess, 743 it can be better to set the sort order on size so that multiple 744 small tables can be processed before the largest tables finish. 745 In this case set this directive to size. Possible values are name 746 and size. Note that export type SHOW_TABLE and SHOW_COLUMN will 747 use this sort order too, not only COPY or INSERT export type. 748 Thanks to Guy Browne for the feature request. 749 - Fix remove leading ':' on Oracle variable taking care of regex 750 character class. Thanks to jselbach for the report. 751 - Add NO_BLOB_EXPORT configuration directive. Exporting BLOB could 752 take time and you may want to export all data except the BLOB 753 columns. In this case enable this directive and the BLOB columns 754 will not be included into data export. The BLOB column must not 755 have a NOT NULL constraint. Thanks to Ilya Vladimirovich for the 756 feature request. 757 - Fix incorrect rewrote of the first custom type in a row. Thanks 758 to Francesco Loreti for the patch. 759 - Remove double quote in type definition en set type name in lower 760 case when PRESERVE_CASE is disabled. 761 - Add PREFIX_SUB_PARTITION to enable/disable sub-partitioning table 762 prefixing in case of the partition names are a part of the sub- 763 partition names. 764 - Fix epoch replacement case in CREATE TABLE statements. 765 - Apply epoch replacement to default value in table declaration. 766 - Add special replacement for case of epoch syntax in Oracle: 767 (sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy'))*24*60*60 768 is replaced by the PostgreSQL equivalent: 769 (extract(epoch from now())) 770 Thanks to rejo-oommen for the feature request. 771 - A few typos in --help sections. Thanks to Christophe Courtois 772 for the report. 773 - Fix export of primary key on partition table. Thanks to chmanu 774 for the patch. 775 - Fix malformed user defined type export. Thanks to Francesco Loreti 776 for the report. 777 778 7792018 09 27 - v19.1 780 781This release fix several issues reported during the last month and 782add support to PostgreSQL 11 HASH partitioning. 783 784It also adds some new features and configuration directives: 785 786 * Add export of default partition and default sub partition. 787 * Add export of HASH partition type. 788 * Add support of stored procedure object. 789 * Add replacement of NLSORT in indexes or queries. For example: 790 CREATE INDEX test_idx ON emp 791 (NLSSORT(emp_name, 'NLS_SORT=GERMAN')); 792 is translated into 793 CREATE INDEX test_idx ON emp 794 ((emp_name collate "german")); 795 The collation still need to be adapted, here probably "de_DE". 796 NLSSORT() in ORDER BY clause are also translated. 797 * Prevent duplicate index with primary key on partition to be 798 exported. 799 * PostgreSQL native partitioning does not allow direct import of 800 data into already attached partitions. We now force direct import 801 into main table but we keep Oracle export of data from individual 802 803This release also adds two new command line options: 804 805 --oracle_speed: use to know at which speed Oracle is able to send 806 data. No data will be processed or written 807 --ora2pg_speed: use to know at which speed Ora2Pg is able to send 808 transformed data. Nothing will be written 809 810Use it for debugging purpose. They are useful to see Oracle speed to 811send data and at what speed Ora2Pg is processing the data without 812reaching disk or direct import into PostgreSQL. 813 814Two new configuration directive has been added: 815 816 * PG_SUPPORTS_PROCEDURE : PostgreSQL v11 adds support to stored 817 procedure objects. Disabled by default. 818 - PARALLEL_MIN_ROWS: set the minimum number of tuples in a table 819 before calling Oracle's parallel mode during data export. 820 Default to 100000 rows. 821 822Note that PG_SUPPORTS_PARTITION and PG_SUPPORTS_IDENTITY are now 823enabled by default to use PostgreSQL declarative partionning and 824identity column instead of serial data type. 825 826Here is the full list of changes and acknowledgements: 827 828 - Fix automatic quoting of table or partition name starting with 829 a number. Thanks to Barzaqh for the report. 830 - Add information about custom directory installation. Thanks to 831 joguess for the report. 832 - Update list of action in documentation. 833 - Fix export of spatial geometries. Thanks to burak yurdakul for 834 the report. 835 - Fix translation of default value in CREATE TABLE DDL when using 836 a function. Thanks to Denis Oleynikov for the report. 837 - Prevent moving index on partition during tablespace export. 838 Thanks to Maxim Zakharov for the report. 839 - Fix upper case of partition name in triggers. 840 - Enforce KEEP_PKEY_NAMES when USE_TABLESPACE is enabled. Thanks 841 to Maxim Zakharov for the patch. 842 - Fix parsing of Oracle user login in dblink input from a file. 843 - Fix multiple duplication of range clause in partition export. 844 - Add bench of total time and rows to migrate data from Oracle 845 in debug mode with speed average. 846 - Fix sub partition prefix name. 847 - Fix unset oracle username when exporting DBLINK from database. 848 Thanks to Denis Oleynikov for the report. 849 - Remove NO VALID to foreign keys on partitioned table. Thanks to 850 Denis Oleynikov for the report. 851 - Fix crash of Ora2Pg on regexp with dynamic pattern base on package 852 code. Thank to Alain Debie and MikeCaliffCBORD for the report. 853 - PostgreSQL native partitioning does not allow direct import of 854 data into already attached partitions. When PG_SUPPORTS_PARTITION 855 is enable we now force direct import into main single table but 856 we keep Oracle export of data from individual partition. Previous 857 behavior was to use main table from both side. Thanks to Denis 858 Oleynikov for the report. 859 - Add the PARALLEL_MIN_ROWS configuration directive to prevent 860 Oracle's parallel mode to be activated during data export if the 861 table have less than a certain amount of rows. Default is 100000 862 rows. This prevent unnecessary fork of Oracle process. Thanks to 863 Denis Oleynikov for the feature request. 864 - Fix composite partition MODULUS value. Thanks to Denis Oleynikov 865 for the report. 866 - Fix count of partitions that was not including subpartition count. 867 - Force PostgreSQL user in FDW user mapping to be PG_USER when it is 868 defined. 869 - Sometimes Oracle indexes can be defined as follow: 870 CREATE INDEX idx_err_status_id 871 ON err_status (status_id, 1); 872 which generate errors on PostgreSQL. Remove column names composed 873 of digit only from the translation. Thanks to Denis Oleynikov for 874 the report. 875 - Move Oracle indexes or PK defined on partitioned tables to each 876 partition as PostgreSQL do not support UNIQUE, PRIMARY KEY, 877 EXCLUDE, or FOREIGN KEY constraints on partitioned tables. 878 Definition are created in file PARTITION_INDEXES_output.sql 879 generated with the PARTITION export type. Thanks to Denis 880 Oleynikov for the feature request. 881 - Fix parallel data load from Oracle partitioned tables by using 882 a unique alias. Thanks to Denis Oleynikov for the report. 883 - Fix export of composite partitioned (range/hash) table when 884 PG_SUPPORTS_PARTITION is disabled. Thanks to Denis Oleynikov 885 for the report. 886 - Remove composite sub partition from the list of partition, this 887 return a wrong partition count. 888 - Fix MODULUS value in hash sub partitioning. 889 - Index and table partitions could be on separate tablespaces. 890 Thanks to Maxim Zakharov for the patch. 891 - Fix case where procedure object name is wrongly double quoted. 892 Thanks to danghb for the report. 893 - Fix parser to support comment between procedure|function name 894 and IS|AS keyword. Thanks to danghb for the report. 895 - Remove dependency to List::Util for the min() function. 896 897 8982018 08 18 - v19.0 899 900This major release fix several issues reported by users during last 901year. It also adds several new features and configuration directives. 902 903New features: 904 905 - Add export of Oracle HASH partitioning when PG_SUPPORTS_PARTITION 906 is enabled. This is a PostgreSQL 11 feature. 907 - Add SUBTYPE translation into DOMAIN with TYPE and PACKAGE export. 908 - Add automatic translation of 909 KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) OVER (PARTITION BY ...) 910 into 911 FIRST|LAST_VALUE(...) OVER (PARTITION BY ... ORDER BY ...). 912 - Add PCTFREE to FILLFACTOR conversion when PCTFREE is upper than 913 the default value: 10. 914 - Replace DELETE clause not followed with FROM (optional in Oracle). 915 - Remove Oracle extra clauses in TRUNCATE command. 916 - Allow use of NUMBER(*) in DATA_TYPE directive to convert all 917 NUMBER(*) into the given type whatever is the length. Ex: 918 DATA_TYPE NUMBER(*):bigint. 919 - Add a PARALLEL hint to all Oracle queries used to migrate data. 920 - Add export of Identity Columns from Oracle Database 12c. 921 - Add translation of UROWID datatype and information in documentation 922 about why default corresponding type OID will fail at data import. 923 - Remove unwanted and unused keywords from CREATE TABLE statements: 924 PARALLEL and COMPRESS. 925 - Remove TEMPORARY in DROP statements. 926 - Improve speed of escape_copy() function used for data export. 927 - Add translation of Oracle functions NUMTOYMINTERVAL() and 928 NUMTODSINTERVAL(). 929 - Add counting of jobs defined in Oracle scheduler in the migration 930 assessment feature. 931 - Add CSMIG in the list of Oracle default system schema 932 - Fully rewrite data export for table with nested user defined types 933 DBD::Oracle fetchall_arrayref() is not able to associate complex 934 custom types to the returned arrays, changed this call to use 935 fetchrow_array() also used to export BLOB. 936 - QUERY export will now output translated queries as well as 937 untranslated ones. This break backward compatibility, previously 938 only translated query was dumped. 939 - Auto detect UTF-8 input files to automatically use utf8 encoding. 940 - Support translation of MySQL global variables. 941 - Add translation of preprocessor in Oracle external table into 942 program in foreign table definition. Allow translation of external 943 table from file. 944 - Add translation to NVL2() Oracle function. 945 - Translate CONVERT() MySQL function. 946 - Translate some form of GROUP_CONCAT() that was not translated. 947 - Remove call to CHARSET in cast() function, replace it by COLLATE 948 every where else. This must cover most of the cases but some 949 specials use might not, so please reports any issue with this 950 behavior. 951 - Add -c | --config command line option to ora2pg_scanner to set 952 custom configuration file to be used instead of ora2pg default: 953 /etc/ora2pg/ora2pg.conf 954 - Improve CONNECT BY and OUTER JOIN translation. 955 - And lot of MySQL to PostgreSQL improvements. 956 957Several new configuration directives have been added: 958 959 - Add DEFAULT_PARALLELISM_DEGREE to control PARALLEL hint use 960 when exporting data from Oracle. Default is disabled. 961 - Make documentation about KEEP_PKEY_NAMES more explicit about 962 kind of constraints affected by this directive. 963 - Add PG_SUPPORTS_IDENTITY configuration directive to enable 964 export of Oracle identity columns into PostgreSQL 10 feature. 965 If PG_SUPPORTS_IDENTITY is disabled and there is IDENTITY column 966 in the Oracle table, they are exported as serial or bigserial 967 columns. When it is enabled they are exported as IDENTITY columns 968 like: 969 970 CREATE TABLE identity_test_tab ( 971 id bigint GENERATED ALWAYS AS IDENTITY, 972 description varchar(30) 973 ) ; 974 975 If there is non default sequence option set in Oracle, they will 976 be appended after the IDENTITY keyword. Additionally in both cases 977 Ora2Pg will create a file AUTOINCREMENT_output.sql with a function 978 to update the associated sequences with the restart value set to 979 "SELECT max(colname)+1 FROM tablename". Of course this file must 980 be imported after data import otherwise sequence will be kept to 981 start value. 982 - Add DISABLE_UNLOGGED configuration directive. By default Ora2Pg 983 export Oracle tables with the NOLOGGING attribute into UNLOGGED 984 tables. You may want to fully disable this feature because you 985 will lost all data from unlogged table in case of PostgreSQL crash. 986 Set it to 1 to export all tables as normal table. When creating a 987 new migration project using --init_project, this directive is 988 activated by default. This is not the case in the default 989 configuration file for backward compatibility. 990 - Add FORCE_SECURITY_INVOKER configuration directive. Ora2Pg use 991 the function's security privileges set in Oracle and it is often 992 defined as SECURITY DEFINER. To override those security privileges 993 for all functions and use SECURITY DEFINER instead, enable this 994 directive. 995 - Add AUTONOMOUS_TRANSACTION in configuration to enable translation 996 of autonomous transactions into a wrapper function using dblink 997 or pg_background extension. If you don't want to use this feature 998 and just want to export the function as a normal one without the 999 pragma call, disable this directive. 1000 - Add documentation about COMMENT_SAVEPOINT configuration directive. 1001 - Major rewrite in PACKAGE parser to better support global variables 1002 detection. Global variable that have no default values are now 1003 always initialized to empty string in file global_variables.conf 1004 so that we see that they exists. This might not change the global 1005 behavior. 1006 1007I especially want to thank Pavel Stehule and Eric Delanoe who spent 1008lot of time this year to help me to improve the PL/SQL to plpgsql 1009translation and also Krasiyan Andreev who help a lot to finalize 1010the MySQL to PostgreSQL migration features. 1011 1012Here is a complete list of changes and acknowledgments: 1013 1014 - Fix translation of "varname cursor%ROWTYPE;". Thanks to Philippe 1015 Beaudoin for the report. 1016 - Fix return of autonomous transaction dblink call when function has 1017 OUT parameter. Thanks to Pavel Stehule for the report. 1018 - Add Oracle to PostgreSQL translation of windows functions 1019 KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) OVER (PARTITION BY ...) 1020 Thanks to Swapnil bhoot929 for the feature request. 1021 - Fix "ORA-03113: end-of-file on communication channel" that what 1022 generated by a too long query send to Oracle. The size of queries 1023 sent to Oracle to retrieve object information depend of the ALLOW 1024 and EXCLUDE directives. If you have lot of objects to filter you 1025 can experience this kind of non explicit error. Now Ora2pg use 1026 bind parameter to pass the filters values to reduce the size of 1027 the prepared query. Thanks to Stephane Tachoire for the report. 1028 - Add SUBTYPE translation into DOMAIN with TYPE and PACKAGE export. 1029 Thanks to Francesco Loreti for the feature request. 1030 - Fix PLS_INTEGER replacement. 1031 - Remove precision for RAW|BLOB as type modifier is not allowed for 1032 type "bytea". 1033 - Fix call of schema.pckg.function() in indexes with a replacement 1034 with pckg.function(). Thanks to w0pr for the report. 1035 - Fix translation of UPDATE trigger based on columns: 1036 "BEFORE UPDATE OF col1,col2 ON table". 1037 Thanks to Eric Delanoe for the report. 1038 - Remove single / from input file that was causing a double END in 1039 some case. Thanks to Philippe Beaudoin for the report. 1040 - Limit translation of PCTFREE into FILLFACTOR when PCTFREE is upper 1041 than the Oracle default value: 10. With PostgreSQL 100 (complete 1042 packing) is the default. 1043 - Add PCTFREE to FILLFACTOR conversion. Thanks to Maxim Zakharov 1044 for the patch. 1045 - Remove TRUNCATE extra clauses. Thanks to e7e6 for the patch. 1046 - Fix type conversion when extra \n added after ;. Thanks to 1047 Maxim Zakharov for the patch. 1048 - Fix DELETE clause not followed with FROM (optional in Oracle). 1049 Thanks to Philippe Beaudoin for the patch. 1050 - Limit call to ALL_TAB_IDENTITY_COLS to version 12+. Thanks to 1051 Andy Garfield for the report. 1052 - Fix comment parsing. Thanks to Philippe Beaudoin for the report. 1053 - Allow use of NUMBER(*) in DATA_TYPE directive to convert all 1054 NUMBER(*) into the given type whatever is the length. 1055 Thanks to lingeshpes for the feature request. 1056 - Fix bug in function-based index export. Thanks to apol1234 for 1057 the report. 1058 - Add PARALLEL hint to all data export queries. Thanks to jacks33 1059 for the report. 1060 - Make documentation about KEEP_PKEY_NAMES more explicit about kind 1061 of constraints affected by this directive. 1062 - Fix export of identity columns by enclosing options between 1063 parenthesis and replacing CACHE 0 by CACHE 1. Thanks to swmcguffin 1064 devtech for the report. 1065 - Add parsing of identity columns from file. 1066 - Fix unwanted replacement of IF () in MySQL code. Thanks to 1067 Krasiyan Andreev for the report. 1068 - Fix to_char() translation, thanks to Eric Delanoe for the report. 1069 - Fix untranslated PERFORM into exception. Thanks to Pavel Stehule 1070 for the report. 1071 - Add _get_entities() function to MySQL export. It returns nothing, 1072 AUTO_INCREMENT column are translated with corresponding types, 1073 smallserial/serial/bigserial. 1074 - Fix look at encrypted column on Oracle prior to 10. Thanks to 1075 Stephane Tachoires for the patch. 1076 - Add export of Identity Columns from Oracle Database 12c. Thanks 1077 to swmcguffin-devtech for the feature request. 1078 - Prevent Ora2Pg to scan ALL_SCHEDULER_JOBS for version prior to 10 1079 Thanks to Stephane Tachoires for the patch. 1080 - Fix pull request #648 to log date only when debug is enabled and 1081 use POSIX strftime instead of custom gettime function. 1082 - Add system time to debug log info. Thanks to danghb for the patch. 1083 - Fix parsing of trigger from file and exception. 1084 - Fix very slow export of mysql tablespace when number of table is 1085 large. Thanks to yafeishi for the report. 1086 - Fix translation of CAST( AS unsigned). Thanks to Krasiyan Andreev. 1087 - Fix MySQL character length to use character_maximum_length 1088 instead of equal character_octet_length. Thanks to yafeishi for 1089 the report. 1090 - Fix custom replacement of MySQL data type. Thanks to Krasiyan 1091 Andreev for the report. 1092 - Fix replacement of call to open cursor with empty parenthesis. 1093 Thanks to Philippe Beaudoin for the report. 1094 - Fix MySQL data type conversion in function declaration. Thanks to 1095 Krasiyan Andreev for the report. 1096 - Fix error with -INFINITY as default value for date or timestamp 1097 columns. 1098 - Fix procedure call rewrite with unwanted comma on begin of 1099 parameter list. Thanks to Pavel Stehule for the report. 1100 - Fix handling of foreign keys when exporting data and DROP_FKEYS 1101 is enabled and ALLOW/EXCLUDE directive is set. Now Ora2Pg will 1102 first drop all foreign keys of a table in the export list and all 1103 foreign keys of other tables pointing to the table. After data 1104 import, it will recreate all of these foreign keys. Thanks to 1105 Eric Delanoe for the report. 1106 - Fix broken transformation of procedure call with default parameter 1107 Thanks to Pavel Stehule for the report. 1108 - Translate call to TIMESTAMP in partition range values into a cast. 1109 Thanks to markiech for the report. 1110 - Fix CONNECT BY translation when the query contain an UNION. Thanks 1111 to mohammed-a-wadod for the report. 1112 - Fix CONNECT BY with PRIOR on the right side of the predicat. 1113 - Fix outer join translation when the (+) was in a function, ex: 1114 WHERE UPPER(trim(VW.FRIDAY))= UPPER(trim(FRIDAY.NAME(+))). 1115 - Order outer join pending tables in from clause. 1116 - Order by object name comments and indexes export. 1117 - Fix outer join translation when the table is not in the from 1118 clause. Thanks to Cyrille Lintz for the report. 1119 - Try to fix potential Oracle schema prefixing PostgreSQL schema 1120 name in CREATE SCHEMA. Thanks to Cyrille Lintz for the report. 1121 - Fix error in TRIM() translation. Thanks to Cyrille Lintz for the 1122 report. 1123 - Add translation of UROWID datatype and information in documentation 1124 about why default corresponding type OID will fail at data import. 1125 Thanks to Cyrille Lintz for the report. 1126 - Fix bug in exporting boolean default values in column definition. 1127 - Fix bug in column parsing in CREATE TABLE. 1128 - Adapt default value for data type changed to boolean. 1129 - Fix bad handling of -D (data_type) option. 1130 - Change behavior in the attempt to set MySQL global variable type. 1131 Now variable type will be timestamp if the variable name contains 1132 datetime, time if the name contains only time and date for date. 1133 Thanks to Krasiyan Andreev for the report. 1134 - Fix function replacement in MySQL declare section. Thanks to 1135 Krasiyan Andreev fr the report. 1136 - Apply REPLACE_ZERO_DATE to default value in table declaration. 1137 Thanks to Krasiyan Andreev for the report. 1138 - Add support to embedded comment in table DDL. 1139 - Fix replacement of data type for MySQL code. Thanks to Krasiyan 1140 Andreev for the report. 1141 - Fix MySQL type replacement in function. Thanks to Krasiyan Andreev 1142 for the report. 1143 - Improve speed of escape_copy() function used for data export. 1144 Thanks to pgnickb for the profiling. 1145 - Add translation of Oracle functions NUMTOYMINTERVAL() and 1146 NUMTODSINTERVAL(). Thanks to Pavel Stehule for the report. 1147 - Counting jobs defined in Oracle scheduler. Thanks to slfbovey 1148 for the patch. 1149 - Fix several issue in create table DDL parser: 1150 - remove double quote of object name when a list of column is 1151 entered 1152 - split of table definition to extract column and constraint 1153 parts is now more efficient 1154 - remove dot in auto generated constraint name when a schema 1155 is given in table name 1156 - fix default values with space that was breaking the parser 1157 - Remove use of bignum perl module that reports error on some 1158 installation. Thanks to Cyrille Lintz for the report. 1159 - Fix a typo preventing perldoc to complete. Thanks to slfbovey 1160 for the patch. 1161 - Fully rewrite data export for table with nested user defined types 1162 DBD::Oracle fetchall_arrayref() is not able to associate complex 1163 custom types to the returned arrays, changed this call to use 1164 fetchrow_array() also used to export BLOB. Thanks to lupynos for 1165 the report. 1166 - Fix renaming of temporary files during partitions data export. 1167 - Fix Oracle use of empty string as default value for integers. 1168 Oracle allow such declaration: SOP NUMBER(5) DEFAULT '' which 1169 PostgreSQL does not support. Ora2Pg now detect this syntax and 1170 replace empty string with NULL. Thanks to ricdba for the report. 1171 - Add detection of Oracle version before setting datetime format, 1172 needed for Oracle 8i compatibility. 1173 - Export of tables from Oracle database are now ordered by name by 1174 default. Thanks to Markus Roth for the report. 1175 - Fix an other case of missing translation of UNSIGNED into bigint. 1176 Thanks to Krasiyan Andreev for the report. 1177 - Force replacement of double quote into single quote for MySQL view 1178 and function code. 1179 - Fix case when SET @varname := ... is used multiple time in the 1180 same function. Thanks to Krasiyan Andreev for the report. 1181 - Fix case where SET @varname := ... was not translated. Thanks to 1182 Krasiyan Andreev for the report. 1183 - Adjust the regex pattern of last patch. 1184 - Fix unwanted newline after hint replacement that could break 1185 comments. Thanks to Pavel Stehule for the report. 1186 - Fix if() replacement in query. Thanks to Krasiyan Andreev for the 1187 report. 1188 - Remove extra parenthesis in some form of JOIN. Thanks to Krasiyan 1189 Andreev for the report. 1190 - Fix untranslated call to UNSIGNED, now translated as bigint. 1191 - Thanks to Krasiyan Andreev for the report. 1192 - Fix translation of double(p,s) into decimal(p,s). 1193 - Remove use of SET when an assignment is done through a SELECT 1194 statement. Thanks to Krasiyan Andreev for the report. 1195 - Fix non-quoted reserved keywords in INSERT / COPY statements when 1196 exporting data. Thanks to Pavel Stehule for the report. 1197 - Fix partition data export to file, temporary files for partition 1198 output was not renamed at export end then data was not loaded. 1199 - Fix double operator := during function with out param rewrite. 1200 - Fix commit f1166e5 to apply changes when FILE_PER_FUNCTION is 1201 disable or when an input file is given. 1202 - Fix translation of LOCATE(). Thanks to Krasiyan Andreev for the 1203 report. 1204 - Fix case where MySQL GROUP_CONCAT() function was not translated. 1205 Thanks to Krasiyan Andreev for the report. 1206 - Fix :new and :old translation in triggers. 1207 - Fully rewrite function call qualification process, the second pass 1208 now is only use to requalify call to pkg.fct into pkg_ftc when 1209 PACKAGE_AS_SCHEMA is disable. The replacement of all function 1210 calls using double quote when a non supported character is used or 1211 when PRESERVE_CASE is enabled has been completely removed as this 1212 takes too much time to process for just very few case. So by 1213 default now Ora2Pg will not go through the second pass. This can 1214 change in the future especially if this is more performant to 1215 process PERFORM replacement. Thanks a lot to Eric Delanoe for his 1216 help on this part. 1217 - Exclude function and procedure not from package to be used in 1218 requalify call. Thanks to Eric Delanoe for the report. 1219 - Fix function name qualification in multiprocess mode. 1220 - Fix unqualified function call due to unclose file handle. 1221 - Prevent try to requalify function call if the function is 1222 not found in the file content. 1223 - Remove ALGORITHM=.*, DEFINER=.* and SQL SECURITY DEFINER from 1224 MySQL DDL code. 1225 - An other missing change to previous commit on qualifying function 1226 call. 1227 - Limit function requalification to export type: VIEW, TRIGGER, 1228 QUERY, FUNCTION, PROCEDURE and PACKAGE. 1229 - Auto detect UTF-8 input files to automatically use utf8 encoding. 1230 - Remove all SHOW ERRORS and other call to SHOW in Oracle package 1231 source as they was badly interpreted as global variable. 1232 - Fix MySQL CREATE TABLE ... SELECT statement. 1233 - Fix pending translation issue on some DATE_FORMAT() case. 1234 Thanks to Krasiyan Andreev for the report. 1235 - Fix translation of IN (..) in MySQL view. Thanks to Krasiyan 1236 Andreev for the report. 1237 - Fix MySQL date format with digit. 1238 - Fix DATE_FORMAT, WHILE and IFNULL translation issues. 1239 - Fix not translated MySQL IF() function. 1240 - Fix other MySQL translation issues for @variable. Thanks to 1241 Krasiyan Andreev for the report. 1242 - Fix issue in MySQL IF translation with IN clause. Thanks to 1243 Krasiyan Andreev for the report. 1244 - Clarify comment about XML_PRETTY directive. Thanks to TWAC 1245 for the report. 1246 - Fix remaining MySQL translation issues for @variable reported 1247 in issue #590. 1248 - Fix no translated := in SET statement. 1249 - Fix output order of translated function. 1250 - Fix non printable character or special characters that make 1251 file encoding to ISO-8859 instead of utf8. Thanks to twac for 1252 the report. 1253 - Prevent MySQL global variable to be declared twice. Thanks to 1254 Krasiyan Andreev for the report. 1255 - Support translation of MySQL global variables. Session variable 1256 @@varname are translated to PostgreSQL GUC variable and global 1257 variable @varname are translated to local variable defined in a 1258 DECLARE section. Ora2Pg tries to gather the data type by using 1259 integer by default, varchar if there is a constant string ('...') 1260 in the value and a timestamp if the variable name have the keyword 1261 date or time inside. Thanks to Krasiyan Andreev for the feature 1262 request. 1263 - Fix DATE_ADD() translation. 1264 - Add translation of preprocessor in Oracle external table into 1265 program in foreign table definition. Thanks to Thomas Reiss for 1266 the report. Allow translation of external table from file. 1267 - Fix case where IF EXISTS might not be append when it is not 1268 supported by PG. 1269 - Translate CONVERT() MySQL function. Thanks to Krasiyan Andreev 1270 for the report. 1271 - Translate some form of GROUP_CONCAT() that was not translated. 1272 Thanks to Krasiyan Andreev for the report. 1273 - Apply same principe with COMMIT in MySQL function code than in 1274 Oracle code. It is kept untouched to be able to detect a possible 1275 change of code logic. It can be automatically commented if 1276 COMMENT_COMMIT_ROLLBACK is enabled. Also I have kept the START 1277 TRANSACTION call but it is automatically commented. 1278 - Add mysql_enable_utf8 => 1 to MySQL connection to avoid issues 1279 with encoding. Thanks to Krasiyan Andreev for the report. 1280 - Prevent removing of comment on MySQL function and add a "COMMENT 1281 ON FUNCTION" statement at end of the function declaration. Thanks 1282 to Krasiyan Andreev for the report. 1283 - Fix translation of types in MySQL function parameter. Thanks to 1284 Krasiyan Andreev for the report. 1285 - Remove START TRANSACTION from MySQL function code. Thanks to 1286 Krasiyan Andreev for the report. 1287 - Fix previous patch, we do not need to look forward for function 1288 or procedure definition in VIEW export and there is no package 1289 with MySQL. Thanks to Krasiyan Andreev for the report. 1290 - Fix call to useless function for MySQL function. 1291 - Add rewrite of MySQL function call in function or procedure code 1292 translation and some other translation related to MySQL code. 1293 - Fix ora2pg_scanner when exporting schema with $ in its name. 1294 Thanks to Aurelien Robin for the report. 1295 - Disable number of microsecond digit for Oracle version 9. Thanks 1296 to Aurelien Robin for the report. 1297 - Do not look at encrypted column for DB version < 10. Thanks to 1298 Aurelien Robin for the report. 1299 - Fix MySQL call to charset in cast function. MySQL charset "utf8" 1300 is also set to COLLATE "C.UTF-8". Thanks to Krasiyan Andreev for 1301 the report. 1302 - Fix two bug in CONNECT BY and OUTER JOIN translation. 1303 - Forgot to handle exception to standard call to IF in MySQL IF() 1304 translation. Thanks to Krasiyan Andreev for the report. 1305 - Forgot to apply previous changes to procedure. 1306 - Fix IF() MySQL replacement when nested and when containing an 1307 IN (...) clause. Thanks to Krasiyan Andreev for the report. 1308 - Fix double BEGIN on MySQL function export. Thanks to Krasiyan 1309 Andreev for the report. 1310 - Fix enum check constraint name when PRESERVE_CASE is enabled. 1311 - Fix case where object with LINESTRING and CIRCULARSTRING was 1312 exported as MULTILINESTRING instead of MULTICURVE. 1313 - Fix export of MULTICURVE with COMPOUNDCURVE. Thanks to Petr Silhak 1314 for the report. 1315 - Fix several issue in MySQL table DDL export. Thanks to Krasiyan 1316 Andreev for the report. 1317 - Fix MySQL auto_increment data type translation and columns export 1318 order. 1319 - Fix translation of MySQL function CURRENT_TIMESTAMP(). Thanks to 1320 Krasiyan Andreev for the report. 1321 - Fix export of MySQL alter sequence name when exporting auto 1322 increment column. Thanks to Krasiyan Andreev for the report. 1323 - Replace IF() call with CASE ... END in VIEW and QUERY export for 1324 MySQL. Thanks to Krasiyan Andreev for the feature request. 1325 - Replace backquote with double quote on mysql statements when read 1326 from file. 1327 - Fix bug in REGEXP_SUBSTR replacement. 1328 - Prevent replacement with same function name from an other package. 1329 Thanks to Eric Delanoe for the report. 1330 - Apply same STRICT rule for SELECT INTO to EXECUTE INTO. Thanks to 1331 Pavel Stehule for the report. 1332 - Fix extra parenthesis removing when a OR clause is present. Thanks 1333 to Pavel Stehule for the report. 1334 - Keep autonomous pragma commented when conversion is deactivated 1335 to be able to identify functions using this pragma. 1336 - Fix bug in replacement of package function in string constant. 1337 - Fix malformed replacement of array element calls. Thanks to Eric 1338 Delanoe for the report. 1339 - Fix unwanted replacement of TO_NUMBER function. Thanks to Torquem 1340 for the report. 1341 - Add an example of DSN for MySQL in ORACLE_DSN documentation. 1342 Thanks to François Honore for the report. 1343 - Fix typo in default dblink connection string. Thanks to Pavel 1344 Stehule for the report. 1345 - Add information about Oracle Instant Client installation. Thanks 1346 to Jan Birk for the report. 1347 - Replace Oracle array syntax arr(i).x into arr[i].x into PL/SQL 1348 code. Thanks to Eric Delanoe for the report. 1349 - Use a more generic connection string for DBLINK. It will use 1350 unix socket by default to connect and the password must be set 1351 in .pgpass. This will result in the following connection string: 1352 format('port=%s dbname=%s user=%', current_setting('port'), 1353 current_database(), current_user) 1354 If you want to redefine this connection string use DBLINK_CONN 1355 configuration directive. Thanks to Pavel Stehule for the feature 1356 request. 1357 - Fix missing RETURN NEW in some trigger translation. Thanks to 1358 Pavel Stehule for the report. 1359 - Fix a missing but non mandatory semi-comma. 1360 - Keep PKs/unique constraints which are deferrable in Oracle also 1361 deferrable in PostgreSQL. Thank to Sverre Boschman for the patch. 1362 - Fix parsing and translation of CONNECT BY. Thanks to bhoot929 1363 for the report. 1364 - Fix FDW export when exporting all schema. Thanks to Laurenz Albe 1365 for the report. 1366 - Add a note about multiple value in export type that can not 1367 include COPY or INSERT together with others export type. 1368 - Fix duplicate condition. Thanks to Eric Delanoe for the report. 1369 - Fix unwanted translation into PERFORM after INTERSECT. 1370 - Comment savepoint in code. Thanks to Pavel Stehule for the patch. 1371 - Fix "ROLLBACK TO" that was not commented. Thanks to Pavel Stehule 1372 for the report. 1373 - Fix restore of constant string when additional string constant 1374 regex are defined in configuration file. 1375 - Fix translation of nextval with sequence name prefixed with their 1376 schema. 1377 - Cast call to TO_DATE(LOCALTIMESTAMP,...) translated into 1378 TO_DATE(LOCALTIMESTAMP::text,...). Thanks to Keshav kumbham 1379 for the report. 1380 - Remove double quote added automatically by Oracle on view 1381 definition when PRESERVE_CASE is not enable. Thanks to JeeIPI for 1382 the report. 1383 - Fix translation of FROM_TZ with a call to function as first 1384 parameter. Thanks to TrungPhan for the report. 1385 - Fix package export when FILE_PER_FUNCTION is set. Thanks to 1386 Julien Rouhaud for the report. 1387 - Add translation of REGEXP_SUBSTR() with the following rules: 1388 Translation of REGEX_SUBSTR( string, pattern, [pos], [nth]) 1389 converted into 1390 SELECT array_to_string(a, '') 1391 FROM regexp_matches(substr(string, pos), pattern, 'g') 1392 AS foo(a) 1393 LIMIT 1 OFFSET (nth - 1); 1394 Optional fifth parameter of match_parameter is appended to 'g' 1395 when present. Thanks to bhoot929 for the feature request. 1396 - Add count of REGEX_SUBSTR to migration assessment cost. 1397 - Add translation support of FROM_TZ() Oracle function. Thanks 1398 to trPhan for the feature request. 1399 - Forces ora2pg to output a message when a custom exception code 1400 has less than 5 digit. 1401 - Fix errcode when Oracle custom exception number have less than 1402 five digit. Thanks to Pavel Stehule for the report. 1403 - Fix case where custom errcode are not converted. Thanks to Pavel 1404 Stehule for the report. 1405 - Fix print of single semicolon with empty line in index export. 1406 - Fix problem with TO_TIMESTAMP_TZ conversion. Thanks to Keshav- 1407 kumbham for the report. 1408 - Fix unwanted double quote in index column with DESC sorting. 1409 Thanks to JeeIPI for the report. 1410 - Fix non detection case of tables in from clause for outer join 1411 translation. Thanks to Keshav for the report. 1412 - Fix unwanted replacement of = NULL into IS NULL in update 1413 statement. Thanks to Pavel Stehule for the report. 1414 - Force schema name used in TEST action to lowercase. Thanks to 1415 venkatabn for the report. 1416 - Fix export of spatial geometries with CURVEPOLYGON + COMPOUNDCURVE 1417 Thanks to kabog for the report. 1418 14192017 09 01 - v18.2 1420 1421This release fix several issues reported during the last six months. 1422It also adds several new features and configuration directives: 1423 1424 - Add translation of SUBSTRB into substr. 1425 - Allow use of array in MODIFY_TYPE to export Oracle user defined 1426 type that are just array of some data type. For example: 1427 CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15); 1428 can be directly translated into text[] or varchar[]. In this case 1429 use the directive as follow: MODIFY_TYPE CLUB:MEMBERS:text[] 1430 Ora2Pg will take care to transform all data of this column into 1431 the correct format. Only arrays of characters and numerics types 1432 are supported. 1433 - Add translation of Oracle function LISTAGG() into string_agg(). 1434 - Add TEST_VIEW action to perform a simple count of rows returned by 1435 views on both database. 1436 - Translate SQL%ROWCOUNT into GET DIAGNOSTICS rowcount = ROW_COUNT 1437 and add translation of SQL%FOUND. 1438 - Add translation of column in trigger event test with IS DISTINCT, 1439 for example: IF updating('ID') THEN ... will be translated into: 1440 IF TG_OP = 'UPDATE' AND NEW.'ID' IS DISTINCT FROM OLD.'ID' then... 1441 - Replace UTL_MATH.EDIT_DISTANCE function by fuzzymatch levenshtein. 1442 - Allow use of MODIFY_STRUCT with TABLE export. Table creation DDL 1443 will respect the new list of columns and all indexes or foreign 1444 key pointing to or from a column removed will not be exported. 1445 - Add export of partition and subpartition using PostgreSQL native 1446 partitioning. 1447 - Auto detect encrypted columns and report them into the assessment. 1448 SHOW_COLUMN will also mark columns as encrypted. 1449 - Add information to global temporary tables in migration assessment. 1450 - Add experimental DATADIFF functionality. 1451 - Allow use of multiprocess with -j option or JOBS to FUNCTION and 1452 PROCEDURE export. Useful if you have thousands of these objects. 1453 - Force RAW(N) type with default value set to sys_guid() as UUID 1454 on PostgreSQL. 1455 - Replace function with out parameter using select into. For example 1456 a call to: get_item_attr( attr_name, p_value ); 1457 where p_value is an INOUT parameter, will be rewritten as 1458 1459 p_value := get_item_attr( attr_name, p_value ); 1460 1461 If there is multiple OUT parameters, Ora2Pg will use syntax: 1462 1463 SELECT get_item_attr( attr_name, p_value ) 1464 INTO (attr_name, p_value); 1465 1466 - Add translation of CONNECT BY using PostgreSQL CTE equivalent. 1467 This translation also include a replacement of LEVEL and 1468 SYS_CONNECT_BY_PATH native Oracle features. On complex queries 1469 there could still be manual editing but all the main work is done. 1470 - Add support to user defined exception, errcode affected to each 1471 custom exception start from 50001. 1472 - Translate call to to_char() with a single parameter into a cast 1473 to varchar. Can be disabled using USE_ORAFCE directive. 1474 - Improve ora2pg_scanner to automatically generates migration 1475 assessment reports for all schema on an Oracle instance. Before 1476 the schema name to audit was mandatory, now, when the schema 1477 is not set Ora2Pg will scan all schema. The connexion user need 1478 to have DBA privilege. Ora2Pg will also add the hostname and SID 1479 as prefix in the filename of the report. This last change forbids 1480 ora2pg_scanner to overwrite a report if the same schema name is 1481 found in several databases. 1482 1483Several new configuration directives have been added: 1484 1485 - Add USE_ORAFCE configuration directive that can be enabled if you 1486 want to use functions defined in the Orafce library and prevent 1487 Ora2Pg to translate call to these functions. The Orafce library 1488 can be found here: https://github.com/orafce/orafce 1489 By default Ora2pg rewrite add_month(), add_year(), date_trunc() 1490 and to_char() functions, but you may prefer to use the Orafce 1491 functions that do not need any code transformation. Directive 1492 DATE_FUNCTION_REWRITE has been removed as it was also used to 1493 disable replacement of add_month(), add_year() and date_trunc() 1494 when Orafce is used, useless now. 1495 - Add FILE_PER_FKEYS configuration directive to allow foreign key 1496 declaration to be saved in a separate file during schema export. 1497 By default foreign keys are exported into the main output file or 1498 in the CONSTRAINT_output.sql file. If enabled foreign keys will be 1499 exported into a file named FKEYS_output.sql 1500 - Add new COMMENT_COMMIT_ROLLBACK configuration directive. Call to 1501 COMMIT/ROLLBACK in PL/SQL code are kept untouched by Ora2Pg to 1502 force the user to review the logic of the function. Once it is 1503 fixed in Oracle source code or you want to comment this calls 1504 enable the directive. 1505 - Add CREATE_OR_REPLACE configuration directive. By default Ora2Pg 1506 use CREATE OR REPLACE in function DDL, if you need not to override 1507 existing functions disable this configuration directive, DDL will 1508 not include OR REPLACE. 1509 - Add FUNCTION_CHECK configuration directive. Disable this directive 1510 if you want to disable check_function_bodies. 1511 1512 SET check_function_bodies = false; 1513 1514 It disables validation of the function body string during CREATE 1515 FUNCTION. Default is to use de postgresql.conf setting that enable 1516 it by default. 1517 - Add PG_SUPPORTS_PARTITION directive, disabled by default. 1518 PostgreSQL version prior to 10.0 do not have native partitioning. 1519 Enable this directive if you want to use PostgreSQL declarative 1520 partitioning instead of the old style check constraint and trigger. 1521 - Add PG_SUPPORTS_SUBSTR configuration directive to replace substr() 1522 call with substring() on old PostgreSQL versions or some fork 1523 like Redshift. 1524 - Add PG_INITIAL_COMMAND to send some statements at session startup. 1525 This directive is the equivalent used for Oracle connection, 1526 ORA_INITIAL_COMMAND. Both can now be used multiple time now. 1527 - Add DBLINK_CONN configuration directive. By default if you have 1528 an autonomous transaction translated using dblink extension the 1529 connection is defined using the values set with PG_DSN, PG_USER 1530 and PG_PWD. If you want to fully override the connection string 1531 use this directive to set the connection in the autonomous 1532 transaction wrapper function. For example: 1533 1534 DBLINK_CONN port=5432 dbname=pgdb host=localhost user=pguser password=pgpass 1535 1536 - Add STRING_CONSTANT_REGEXP configuration directive. Ora2Pg replace 1537 all string constant during the pl/sql to plpgsql translation, 1538 string constant are all text include between single quote. If you 1539 have some string placeholder used in dynamic call to queries you 1540 can set a list of regexp to be temporary replaced to not break the 1541 parser. For example: 1542 1543 STRING_CONSTANT_REGEXP <cfqueryparam value=".*"> 1544 1545 The list of regexp must use the semi colon as separator. 1546 - Add FUNCTION_STABLE configuration directive. By default Oracle 1547 functions are marked as STABLE as they can not modify data unless 1548 when used in PL/SQL with variable assignment or as conditional 1549 expression. You can force Ora2Pg to create these function as 1550 VOLATILE by disabling this configuration directive. 1551 - Add new TO_NUMBER_CONVERSION configuration directive to control 1552 TO_NUMBER translation behavior. By default Oracle call to function 1553 TO_NUMBER will be translated as a cast into numeric. For example, 1554 TO_NUMBER('10.1234') is converted into PostgreSQL call: 1555 to_number('10.1234')::numeric. 1556 If you want you can cast the call to integer or bigint by changing 1557 the value of the configuration directive. If you need better 1558 control of the format, just set it as value, for example: 1559 TO_NUMBER_CONVERSION 99999999999999999999D9999999999 1560 will convert the code above as: 1561 TO_NUMBER('10.1234', '99999999999999999999D9999999999') 1562 Any value of the directive that it is not numeric, integer or 1563 bigint will be taken as a mask format. If set to none, then no 1564 conversion will be done. 1565 - Add LOOK_FORWARD_FUNCTION configuration directive which takes a 1566 list of schema to get functions/procedures meta information that 1567 are used in the current schema export. When replacing call to 1568 function with OUT or INOUT parameters, if a function is declared 1569 in an other package then the function call rewriting can not be 1570 done because Ora2Pg only knows about functions declared in the 1571 current schema. By setting a comma separated list of schema as 1572 value of the directive, Ora2Pg will look forward in these packages 1573 for all functions, procedures and packages declaration before 1574 proceeding to current schema export. 1575 - Add PG_SUPPORTS_NAMED_OPERATOR to control the replacement of the 1576 PL/SQL operator used in named parameter => with the PostgreSQL 1577 proprietary operator := Disable this directive if you are using 1578 PG < 9.5 1579 - Add a warning when Ora2Pg reorder the parameters of a function 1580 following the PostgreSQL rule that all input parameters following 1581 a parameter with a default value must have default values as well. 1582 In this case, Ora2Pg extracts all parameters with default values 1583 and put them at end of the parameter list. This is to warn you 1584 that a manual rewrite is required on calls to this function. 1585 1586New command line options have been added: 1587 1588 - Add -N | --pg_schema command line option to be able to override 1589 the PG_SCHEMA configuration directive. When this option is set 1590 at command line, EXPORT_SCHEMA is automatically activated. 1591 - Add --no_header option with equivalent NO_HEADER configuration 1592 directive to output the Ora2Pg header but just the translated 1593 code. 1594 1595There is also some behavior changes from previous release: 1596 1597 - Remove SysTimestamp() from the list of not translated function, 1598 it is replaced with CURRENT_TIMESTAMP for a long time now. 1599 - Change migration assessment cost to 84 units (1 day) for type 1600 TABLE, INDEX and SYNONYM and to 168 units (2 days) for TABLE 1601 PARTITION and GLOBAL TEMPORARY TABLE, this is more realistic. 1602 - Set minimum assessment unit to 1 when an object exists. 1603 Improve PL/SQL code translation speed. 1604 - Change behavior of COMPILE_SCHEMA directive used to force Oracle 1605 to compile schema before exporting code. When this directive is 1606 enabled and SCHEMA is set to a specific schema name, only invalid 1607 objects in this schema will be recompiled. When SCHEMA is not set 1608 then all schema will be recompiled. To force recompile invalid 1609 object in a specific schema, set COMPILE_SCHEMA to the schema name 1610 you want to recompile. This will ask to Oracle to validate the 1611 PL/SQL that could have been invalidate after a export/import for 1612 example. The 'VALID' or 'INVALID' status applies to functions, 1613 procedures, packages and user defined types. 1614 - Default transaction isolation level is now set to READ COMMITTED 1615 for all action excluding data export. 1616 - Oracle doesn't allow the use of lookahead expression but you may 1617 want to exclude some objects that match the ALLOW regexp you have 1618 defined. For example if you want to export all table starting 1619 with E but not those starting with EXP it is not possible to do 1620 that in a single expression. 1621 Now you can start a regular expression with the ! character to 1622 exclude all objects matching the regexp given just after. Our 1623 previous example can be written as follow: ALLOW E.* !EXP.* 1624 it will be translated into 1625 1626 REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$') 1627 1628 in the object search expression. 1629 - Fix quoting of PG_SCHEMA with multiple schema in search path. The 1630 definition of the search path now follow the following behavior: 1631 * when PG_SCHEMA is define, always set search_path to its value. 1632 * when EXPORT_SCHEMA is enabled and SCHEMA is set, the search_path 1633 is set the name of the schema. 1634 - Remove forcing of export_schema when pg_schema is set at command 1635 line. This could change the behavior of some previous use of these 1636 variables and the resulting value of the search_path but it seems 1637 much better understandable. 1638 - Rewrite translation of raise_application_error to use RAISE 1639 EXCEPTION with a message and the SQLSTATE code. Oracle user 1640 defined code -20000 to -20999 are translated to PostgreSQL 1641 user define code from 45000 to 45999. Call to 1642 raise_application_error(mySQLCODE, myErrmsg); 1643 will be translated into 1644 RAISE EXCEPTION '%', myErrmsg USING ERRCODE = mySQLCODE; 1645 - Remove migration assessment cost for TG_OP and NOT_FOUND they 1646 might be fully covered now. 1647 1648Here is the complete list of changes: 1649 1650 - Fix bad inversion of HAVING/GROUP BY clauses. Thanks to bhoot929 1651 for the report. 1652 - Fix case of non translation of type in CAST() function. Thanks to 1653 Keshavkumbham for the report. 1654 - Fix spatial data export when using direct import into PostgreSQL 1655 and WKT or INTERNAL format. This can still be improved. Thanks to 1656 Valeria El-Samra for the report. 1657 - Improve translation of trunc() into date_trunc. Thanks to bhoot929 1658 for the report. 1659 - Translate to_char() without format into a simple cast to varchar. 1660 Thanks to bhoot929 for the report. 1661 - Fix line comment which does not disable multi-line comment. 1662 Thanks to Pavel Stehule for the report. 1663 - Fix overridden of output file global_variables.conf with 1664 multiple packages. Thanks to Oliver Del Rosario for the report. 1665 - Fix export of data stored in a nested user defined type. Thanks 1666 to lupynos for the report. 1667 - Fix data export from Oracle user defined types, where output of 1668 ROW(...) does not distinguish numeric from string or other types 1669 that need to be formatted. Thanks to Petr Silhak for the report. 1670 - Fix broken replacement of package procedure name. Thanks to Pavel 1671 Stehule for the report. 1672 - Add FLOWS_010600 to the objects exclusion listr. 1673 - Improve view/trigger migration assessment accuracy. 1674 - Fix OUTER JOIN (+) translation, all join filters with constant 1675 was written into the WHERE clause by default. Write them into the 1676 JOIN clause. 1677 - Fix weight of the number of triggers and views in the report with 1678 a limit of 2 man-days, of course SQL difficulties are still add 1679 after this limit. 1680 - Fix alias added to condition which is not a sub query. Thanks to 1681 nitinmverma for the report. 1682 - Fix wrong translation of OUTER JOIN with subquery in FROM clause. 1683 Thanks to nitinmverma for the report. 1684 - Fix typo preventing exclusion of system SYNONYM. 1685 - Fix an other case of bad translation of END fct_name. Thanks to 1686 nitinmverma for the report. 1687 - Fix unwanted translation of REGEXP_SUBSTR() in REGEXP_SUBSTRING(). 1688 Thanks to nitinmverma for the report. 1689 - Fix broken translation of decode(). Thanks to nitinmverma for the 1690 report. 1691 - Fix error "Malformed UTF-8 character (unexpected continuation byte 1692 0xbf, with no preceding start byte) in pattern match" by including 1693 an arbitrary non-byte character into the pattern. Thanks to Bob 1694 Sislow for the report. 1695 - Fix missing translation of trunc() with date_trunc(). Thanks to 1696 nitinmverma for the report. 1697 - Add migration assessment weight to concatenation. 1698 - Fix space between operator, ex: a < = 15 must be translated as 1699 a <= 15. Thanks to nitinmverma for the report. 1700 - Handle translation of named parameters in function calls. Thanks 1701 to Julien Rouhaud for the patch. 1702 - Fix missing renaming of _initial_command() method. 1703 - Fix right outer join translation by converting them to left outer 1704 join first. Thanks to Julien Rouhaud for the hint. 1705 - Fix TEST on default value count and functions belonging to others 1706 than public schema, especially functions of packages. 1707 - Fix default number of man-days in migration assessment. Thanks to 1708 Nate Fitzgerald for the report. 1709 - Add host information into the filename of the report to prevent 1710 some additional case of report overriding. Thanks to Aurelien 1711 Robin for the report. 1712 - Prevent ora2pg script to complain if no ora2pg.conf file is found 1713 when a DSN is passed at command line and that user connection is 1714 set in the environment variables. 1715 - Do not declare a function stable if there is update/insert/delete 1716 statement inside. 1717 - Improve ora2pg_scanner to generate report of each instance schema 1718 when the schema to audit is not set. Thanks to Thomas Reiss for 1719 the patch. 1720 - Fix parser failure with quote in comments. Thanks to Eric Delanoe 1721 for the report. 1722 - Fix case where NVL is not replaced by COALESCE. 1723 - Add parenthesis to simple package.function call without parameter. 1724 - Fix replacement of INSTR() with optional parameters. Thanks to 1725 Pavel Stehule for the report. 1726 - Translate SQL%ROWCOUNT to GET DIAGNOSTICS rowcount = ROW_COUNT. 1727 Thanks to Pavel Stehule for the report. 1728 - Add translation of SQL%FOUND. Thanks to Pavel Stehule. 1729 - Remove qualifier in create type, "CREATE TYPE fusion.mytable AS 1730 (fusion.mytable fusion.finalrecord[]);" becomes "CREATE TYPE 1731 fusion.mytable AS (mytable fusion.finalrecord[]);". Thanks to 1732 Julien Rouhaud for the report. 1733 - Fix extra comma in FROM clause of triggers in outer join 1734 translation. Thanks to Pavel Stehule fora the report. 1735 - Use record for out parameters replacement only where there is more 1736 than one out parameter. Thanks to Pavel Stehule for the patch. 1737 - Add date type to the inout type array. Thanks to Pavel Stehule for 1738 the report. 1739 - Remove possible last spaces in inout type detection. 1740 - Fix REGEXP_LIKE translation. 1741 - Fix count of default values during test action. 1742 - Fix removing of double quote over column name in view declaration. 1743 - Do not set default value if it is NULL, this is already the case. 1744 - Fix data export that was truncated to the last DATA_LIMIT lines. 1745 Thanks to Michael Vitale for the report. 1746 - Fix an other bug in rewriting call to function with OUT parameter. 1747 Thanks to Pavel Stehule for the report. 1748 - Fix autodetection of composite out parameters. 1749 - Merge typo on PLPGSQL 1750 - Fix typo to PLPGSQL keyword. Thanks to Vinayak Pokale. 1751 - Fix regexp failure in is_reserved_words() method. Thanks to 1752 Patrick Hajek for the patch. 1753 - Use only one declaration of ora2pg_r RECORD, it is reusable. 1754 - Fix transformation of procedure CALL with OUT parameter that can't 1755 works when procedure/function has minimally one OUT parameter is 1756 of composite type. Thanks to Pavel Stehule for the patch. 1757 - Second attempt to fix outer join translation in triggers. Thanks 1758 to Pavel Stehule for precious help. 1759 - Fix RAISE NOTICE replacement with double % placeholder. Thanks to 1760 Pavel Stehule for the report. 1761 - Fix call to function replacement for function registered with 1762 double quote. Thanks to Pavel Stehule for the report. 1763 - Change assessment score of TG_OP. 1764 - Fix replacement of outer join in triggers by adding pseudo tables 1765 NEW and OLD to the list of tables. Thanks to Pavel Stehule for the 1766 report. 1767 - Handle custom exception in declare section of triggers. 1768 - Fix FUNCTION_CHECK option, it will be set in all file header. 1769 Thanks to Pavel Stehule for the report. 1770 - Replace call to ALL_TABLES to USER_TABLES when USER_GRANTS is 1771 enabled. Thanks to Bob Sislow. 1772 - Removed PRAGMA EXCEPTION_INIT() from declare section. Thanks to 1773 Pavel Stehule for the report. 1774 - Fix constant string that was breaking the parser. Thanks to Pavel 1775 Stehule for the report. 1776 - Fix missing space between EXCEPTION and WHEN. Thanks to Pavel 1777 Stehule for the report. 1778 - Fix broken function header resulting in missing space before OUT 1779 keyword. Thanks to Pave Stehule for the report. 1780 - Fix invalid RAISE command. Thanks to Pavel Stehule for the report. 1781 - Add port information to ORACLE_DSN in documentation and config 1782 file. Thanks to Bob Sislow for the report. 1783 - Fix broken declaration in triggers related to FOR cycle control 1784 variable when there is no declare section. Thanks to Pavel 1785 Stehule for the report. 1786 - Fix broken declaration in triggers related to FOR cycle control 1787 variable. Thanks to Pavel Stehule for the report. 1788 - Fix unterminated C style comment in trigger. Thanks to Pavel 1789 Stehule for the report. 1790 - Fix bug in package+function precedence replacement. Thanks to 1791 Eric Delanoe for the report. 1792 - Fix unwanted and broken export of tables created with CREATE TABLE 1793 tablename OF objType. Thanks to threenotrump for the report. 1794 - Add explanation on using REPLACE_AS_BOOLEAN when REPLACE_TABLES or 1795 REPLACE_COLS is also used on the same object. Thanks to Brendan 1796 Le Ny for the report. 1797 - Fix unwanted data export of materialized view. Thanks to Michael 1798 Vitale for the report. Fix ORA-00942 with table that are not yet 1799 physically created and has no data. 1800 - Fix calling functions with same name declared in several packages. 1801 The one declared in current package now takes precedence. Thanks 1802 to Eric Delanoe for the report. 1803 - Change zero-length lob/long to undef workaround for a bug in 1804 DBD::Oracle with the ora_piece_lob option (used when no_lob_locator 1805 is enabled) where null values fetch as empty string for certain 1806 types. Thanks to Alice Maz for the patch. 1807 - Fix createPoint() spatial method issue. Thanks to jwl920919. 1808 - Fix comment on REPLACE_COLS directive. 1809 - Fix an other issue in transformation of TYPE x IS REF CURSOR. 1810 Thanks to Pavel Stehule for the report. 1811 - Fix an other case of broken declaration in triggers related to FOR 1812 cycle control variables. Thanks to Pavel Stehule for the report. 1813 - Fix broken declaration in triggers related to FOR cycle control 1814 variables with empty DECLARE section. 1815 - Fix other case of replacement by EXIT WHEN NOT FOUND. 1816 - Fix output of global_variables.conf file when OUTPUT_DIR is not 1817 set. Fix non replacement of global variables. 1818 - Remove some AUTHID garbage in procedure declaration generated by 1819 a migration. 1820 - Fix trigger name quoting with non supported character. Thanks to 1821 Michael Vitale for the report. 1822 - Fix use of nextval() in default value. 1823 - Fix alias append in from clause of the extract() function. Thanks 1824 to Narayanamoorthys for the report. 1825 - Disable direct export to partition for PostgreSQL 10 if directive 1826 PG_SUPPORTS_PARTITION is enabled, import must be done in the main 1827 table. 1828 - Do not export partitions of a materialized view. Thanks to Michael 1829 Vitale for the report. 1830 - Fix wrong replacement of keyword after END. Thanks to Pavel 1831 Stehule for the report. 1832 - Remove Oracle hints from queries, they are not supported and can 1833 break comments. Thanks to Pavel Stehule for the report. 1834 - Fix unstable transformation of TYPE x IS REF CURSOR. Thanks to 1835 Pavel Stehule for the report. 1836 - Fix data export failure when no table match the ALLOW/EXCLUDE 1837 filter. Thanks to threenotrump for the report. 1838 - Add missing search_path to FKEY dedicated file. Thanks to Michael 1839 Vitale for the report. 1840 - Apply default oject name exclusion to synonym. 1841 - Skip some PL/SQL translation in migration assessment mode. 1842 - Change default type for virtual column whit round() function. 1843 Thanks to Julien Rouhaud for the report. 1844 - Fix bug with EXIT WHEN command. Thanks to Pavel Stehule. 1845 - Fix an other wrong replacement of DECODE in UPDATE statement. 1846 Thanks to Pavel Stehule for the report. 1847 - Fix wrong replacement of DECODE. Thanks to Pavel Stehule. 1848 - Fix unwanted replacement of INTO STRICT when this is an INSERT 1849 statement. Thanks to Pavel Stehule for the report. 1850 - Fix potential regexp error with special character in outer join 1851 filters. Thanks to Adrian Boangiu for the report. 1852 - Fix parsing of PK from file. Thanks to Julien Rouhaud. 1853 - Fix parsing of FK from file. Thanks to Julien Rouhaud. 1854 - Fix count of unique and primary key in TEST export. Thanks to 1855 Liem for the report. 1856 - Fix reserved keyword rewrite using double quote when directive 1857 USE_RESERVED_WORDS is enabled. Thanks to Michael Vitale. 1858 - Remove EVALUATION CONTEXT object type from migration assessment. 1859 - Add QUEST_SL_% pattern to the list of table that must be excluded 1860 from export. 1861 - Fix data export when BFILE are translated as text. Thanks to 1862 Michael Vitale for the report. 1863 - Fix export of package when a comment is placed just before the 1864 AS/IS keyword. Thanks to Michael Vitale for the report. 1865 - Fix other cases of function call replacement when they are 1866 declared in different packages and one with OUT parameter and 1867 the other one with only IN parameters. Thanks to Eric Delanoe. 1868 - Fix inconsistent behavior of import_all script with -h and -d 1869 Under Linux: When -h not specified, script defaults to unix domain 1870 sockets for psql and localhost for perl (which may error depending 1871 on pg_hba.conf). Now defaults to more performing sockets. -d 1872 wasn't passing DB name to some psql calls where it's necessary. 1873 Thanks to BracketDevs for the patch. 1874 - Fix file handle close when compression is enabled. Thanks to 1875 Sebastian Albert for the report. 1876 - Add information about ora2pg behavior during data export to files 1877 when files already exists. Thanks to Michael Vitale. 1878 - Update readme to provide tar command for bzip2 file. Thanks to 1879 Tom Pollard for the patch 1880 - Fix unwanted FTS_INDEXES empty file and append unaccent extension 1881 creation if not exists. Thanks to Michael Vitale for the report. 1882 - Fix missing explicitly declared variable for cycle in trigger. 1883 Thanks to Pavel Stehule for the report. 1884 - Fix global type/cursor declaration doubled in package export. 1885 - Always translate Oracle SELECT ... INTO to SELECT ... INTO STRICT 1886 in plpgsql as Oracle seems to always throw an exception. Thanks 1887 to Pavel Stehule for the report. 1888 - Fix too much semicolons on end of function. Thanks to Pavel 1889 Stehule for the report. 1890 - Fix ALTER TABLE to set the owner when table is a foreign table. 1891 Thanks to Narayanamoorthys for the report. 1892 - Fix case of untranslated procedure call when there was parenthesis 1893 in the parameters clause. Thanks to Pavel Stehule for the report. 1894 - Fix broken variable declaration with name containing DEFAULT. 1895 Thanks to Pavel Stehule for the report. 1896 - Change query ORDER BY clause to view export query. 1897 - Fix missing replacement of quote_reserved_words function by new 1898 quote_object_name. Thanks to Sebastian Albert for the report. 1899 - Fix REPLACE_COLS replacement of column name in UNIQUE constraint 1900 definition. Thanks to Bernard Bielecki for the report. 1901 - Fix export of Oracle unlogged table that was exported as normal 1902 tables. 1903 - Fix regression in package function calls rewrite leading to append 1904 unwanted comma when replacing out parameters. Thanks to Pavel 1905 Stehule and Eric Delanoe for the report. 1906 - Fix removing of function name after END keyword. Thanks to Pavel 1907 Stehule for the report. 1908 - Fix bug in package function extraction. 1909 - Improve VIEW export by only looking for package function name and 1910 fix a bug that was including unwanted "system" package definition. 1911 Also fix a potential bad rewriting of function call. Thanks to 1912 Eric Delanoe for the report. 1913 - Fix an other case of missing PERFORM replacement. Thanks to Pavel 1914 Stehule for the report. 1915 - Fix remplacement of "EXIT WHEN cursor%NOTFOUND". Thanks to Pavel 1916 Stehule for the report. 1917 - Fix missing conversion of type in cast function. Thanks to Michael 1918 Vitale for the report. 1919 - Fix TO_NUMBER that is now translated as a cast to numeric to 1920 correspond to the default behavior in Oracle. Thanks to Pavel 1921 Stehule for the report. 1922 - Fix replacement of function call from different schema, especially 1923 in overloaded cases. 1924 - Remove OUT parameter from the argument list of function call. 1925 Thanks to Pavel Stehule for the report. 1926 - Fix wrong replacement in FOR ... IN loop inserting EXCLUDE in the 1927 statement. Thanks to Pavel Stehule for the report. 1928 - Translate Oracle proprietary VALUES without parenthesis with the 1929 proprietary syntax of POstgreSQL. Thanks to Pavel Stehule for the 1930 report. 1931 - Fix function header translation when a comment is present between 1932 closing parenthesis and the IS keyword. Thanks to Pavel Stehule 1933 for the report. 1934 - Fix RETURNS in autonomous transaction call when there is OUT 1935 parameters. Thanks to Pavel Stehule for the report. 1936 - Fix call to BMS_UTILITY.compile_schema() when COMPILE_SCHEMA is 1937 enable. Thanks to PAvel Stehule for the report. 1938 - Fix export of function and procedure with same name in different 1939 schema. Thanks to Pavel Stehule for the report. 1940 - Fix detection and replacement of global variable in package that 1941 was producing invalid code export. Fix progress bar on procedure 1942 export. 1943 - Fix regression in global variables default value export. 1944 - Rewrite multiprocess for procedure and function export to solve 1945 some performances issues. 1946 - Do not waste time trying to replace function call when it is not 1947 found in the current code. 1948 - Fix default value for FILE_PER_FUNCTION when parallel mode is 1949 enabled. 1950 - Output a fatal error with export type TABLE and multiple schema set 1951 to PG_SCHEMA when EXPORT_SCHEMA is enabled. 1952 - Fix replacement of function name with package prefix. 1953 - Fix documentation of PG_SCHEMA directive, especially on the use of 1954 a schema list. Thanks to Michael Vitale for the report. 1955 - Fix translation of INSTR() function. 1956 - Improve speed in function translation by not calling twice 1957 Ora2Pg::PLSQL::convert_plsql_code() on declare and code section. 1958 Thanks to Pavel Stehule for the profiling. 1959 - Fix unwanted replacement of SYSDATE, SYSTIMESTAMP and some other 1960 when they are part of variable or object name. Add rewrite of 1961 REF CURSOR during type translation. 1962 - Require support of LATERAL keyword for DATADIFF (Pg >= 9.3). 1963 Patch from Sebastian Albert. 1964 - Do not call replace_sdo_function(), replace_sdo_operator() and 1965 replace_sys_context() if the string SDO_ or SYSCONTEXT is not 1966 found. This might save some performances. 1967 - Remove the RETURNS clause when there is an OUT parameter 1968 PostgreSQL choose correct type by self. Thanks to Pavel Stehule 1969 for the report. 1970 - Add a note about performance improvement by updating stats on 1971 Oracle. Thanks to Michael Vitale for the report. 1972 - Remove newline characters in REVOKE statement when embedded in 1973 a comment. Thanks to Pavel Stehule for the report. 1974 - Fix replacement with PERFORM into package extracted from an 1975 Oracle database. Thanks to Eric Delanoe for the report. 1976 - Fix translation of call to function with out parameters. 1977 Thanks to Pavel Stehule for the report. 1978 - Fix case where call to procedure without parameter was not 1979 prefixed by PERFORM or when called in a exception statement. 1980 Thanks to Eric Delanoe for the report. 1981 - Add function quote_object_name to handle all cases where object 1982 name need to be double quoted (PRESERVE_CASE to 1, PostgreSQL 1983 keyword, digit in front or digit only and non supported character. 1984 Thanks to liemdt1811 for the report. 1985 - Add a note about RAW(n) column with "SYS_GUID()" as default value 1986 that is automatically translated to type of the column 'uuid' 1987 by Ora2Pg. 1988 - Remove old column count check to use char_length. Thanks to 1989 Alice Maz for the patch. 1990 - Fix some raise_application_error that was not replaced with a 1991 global rewrite of remove comments and text constants to solve 1992 some other issues like rewriting of package function call in 1993 dynamic queries. Thanks to PAvel Stehule for the report. 1994 - Fix cycle variable not generated for LOOP IN SELECT in trigger. 1995 Thanks to Pavel Stehule for the report. 1996 - Fix procedures with OUT parameters not processed in triggers. 1997 Thanks to Pavel Stehule for the report. 1998 - Remove other case where PERFORM must be or must not be inserted. 1999 - Remove case where PERFORM can be inserted. Thanks to Pavel 2000 Stehule and Eric Delanoe for the report. 2001 - Fix missing ; in some raise_application_error translation. Thanks 2002 to Pavel Stehule for the report. 2003 - Fix missing PERFORM in front of direct call to function and the 2004 rewrite of direct call to function with out parameters. Thanks 2005 to Eric Delanoe for the report. 2006 - Fix translation of rownum when the value is not a number. Thanks 2007 to Pavel Stehule for the report. 2008 - Fix missing space between cast and AS keyword. Thanks to Pavel 2009 Stehule for the report. 2010 - Fix translation of views and add support to comment inside views. 2011 Thanks to Pavel Stehule for the report. 2012 - Fix removing of AS after END keyword. Thanks to Pavel Stehule for 2013 the report. 2014 - Fix type in CAST clause not translated to PostgreSQL type. Thanks 2015 to Pavel Stehule for the report. 2016 - Treat citext type as text. Thanks to Tomasz Wrobel for the patch. 2017 - Fix packages migration assessment that was broken with parser 2018 rewriting on package extraction. 2019 - Rewrite parsing of PL/SQL packages to better handle package 2020 specification and especially types and global variables from this 2021 section. 2022 - Fix raise_application_error translation by removing extra boolean 2023 parameter. 2024 - Improve comments processing. 2025 - Fix package function name replacement adding a dot before package 2026 name. Thanks to Eric Delanoe for the report. 2027 - Add collect of functions/procedures metadata when reading DDL 2028 from file. 2029 - Fix replacement of function prefixed with their schema name. 2030 Thanks to Eric Delanoe for the report. 2031 - Try to minimized comment placeholders by aggregating multiline 2032 comments. 2033 - Remove new line character from _get_version() output. 2034 - Fix ENABLE_MICROSECOND test condition on NLS_TIMESTAMP_TZ_FORMAT 2035 setting. Thanks to Didier Sterbecq for the report. 2036 - Fix another issue with Oracle 8i and table size extraction. 2037 - Fix query to show column information on Oracle 8i 2038 - Fix query to look for virtual column on Oracle 8i 2039 - Fix query to list all table by size on Oracle 8i 2040 - Prevent ora2pg to look for external table definition in Oracle 8i. 2041 - Fix a regression on timestamp format setting for Oracle 8i. 2042 - Fix some regression on queries with Oracle 8i. Thanks to Didier 2043 Sterbecq for the report. 2044 - Add a function to collect metadata of all functions. 2045 - Don't create empty partition index file when there's no partition. 2046 - Fix wrong translation in OPEN ... FOR statement. Thanks to Eric 2047 Delanoe for the report. 2048 - Fix call of method close() on an undefined value. Thanks to Eric 2049 Delanoe for the report. 2050 - Fix partition data export issues introduced with previous patches. 2051 - Fix unterminated IF / ELSIF block in subpartition export. 2052 - Fix subpartition export. Thanks to Maurizio De Giorgi for the 2053 report. 2054 - Force DATA_LIMIT default value to 2000 on Windows OS instead of 2055 10000 to try to prevent constant OOM error. Thanks to Eric Delanoe 2056 for the report. 2057 - Fix default partition table that was not used PREFIX_PARTITION. 2058 Thanks to ssayyadi for the report. 2059 - Limit datetime microsecond format to micro second (.FF6) as the 2060 format can be FF[0..9] and PostgreSQL just have FF[0..6] 2061 - Add to_timestamp_tz Oracle function translation. Thanks to Eric 2062 Delanoe for the feature request. 2063 - Fix custom data type replacement in function code. Thanks to Pavel 2064 Stehule for the report. 2065 - Fix non working INPUT_FILE configuration directive when action is 2066 QUERY. Thanks to Eric Delanoe for the report. 2067 - Fix unwanted global variable implicit declaration to handle 2068 autonomous transaction parameters. Thanks to Eric Delanoe for the 2069 report. 2070 - Fix call to dblink in function with PRAGMA AUTONOMOUS_TRANSACTION 2071 and no arguments. Thanks to Eric Delanoe for the report. 2072 - Fix package constant translation. Thanks to Eric Delanoe. 2073 - Fix unwanted alias on join syntax. Thanks to Eric Delanoe 2074 for the report. 2075 - Fix regression on dbms_output.put* translation. Thanks to Eric 2076 Delanoe for the report. 2077 - Fix handling of comments in statements to try to preserve them at 2078 maximum in the outer join rewriting. 2079 - Do not declare variable when it is an implicit range cursor, it 2080 do not need to be declared. 2081 - Export implicit variable in FOR ... IN ... LOOP as an integer if 2082 it don't use a select statement and export it as a RECORD when a 2083 statement is found. Thanks to Eric Delanoe and Pavel Stehule for 2084 the report. 2085 - Reduce migration assessment weight for CONNECT BY. 2086 - Fix derived table pasted two times in from clause. Thanks to Pavel 2087 Stehule for the report. 2088 - Fix some other unexpected ";" in function code. Thanks to Pavel 2089 Stehule for the report. 2090 - Remove %ROWTYPE in return type of function. Thanks to Pavel 2091 Stehule for the report. 2092 - Fix doubled AND in expression when a parenthesis is in front after 2093 rewriting. Thanks to Eric Delanoe for the report. 2094 - Fix unexpected ";" in function after post-body END when a comment 2095 is present. Thanks to Eric Delanoe for the report. 2096 - Fix unexpected ";" in some function variable declaration when a 2097 comment is at end of the declare section. Thanks to Eric Delanoe 2098 for the report. 2099 - Remove %ROWTYPE in function that have not been replaced with RECORD 2100 for cursor declaration. Thanks to Eric Delanoe for the report. 2101 - Fix removing of WHEN keyword after END. Thanks to Pavel Stehule for 2102 the report. 2103 - Fix missing table name with alias in from clause due to comments in 2104 the clause. I have also merge right and left outer join translation 2105 function into a single one, most of the code was the same. 2106 - Fix output order of outer join. Thanks to Pavel Stehule for the 2107 report. 2108 - Fix untranslated outer join in nested sub query. Thanks to Pavel 2109 Stehule for the report. 2110 - Rewrite again the decode() translation as a single function call 2111 for all replacement before any other translation. 2112 - Append table filter to check constraints extraction. Thanks to 2113 danghb for the report. 2114 - Fix issue with parenthesis around outer join clause. Thanks to 2115 Pavel Stehule for the report. 2116 - Move remove_text_constant_part() and restore_text_constant_part() 2117 function into the main module. 2118 - Include decode() replacement in recursive function call. Thanks 2119 to Pavel Stehule for the report. 2120 - Prevent removing of parenthesis on a sub select. Thanks to Pavel 2121 Stehule for the report. 2122 - Fix missing table exclusion/inclusion in column constraint export. 2123 Thanks to danghb for the report. 2124 - Fix an alias issue in view parsed from file. 2125 - Fix parsing of view from file when no semi comma is found. 2126 - Remove FROM clause without alias from migration assessment. 2127 - Fix order of outer join during translation. Thanks to Pavel 2128 Stehule for the report. 2129 - Fix case of missing alias on subquery in FROM clause. Thanks to 2130 Pavel Stehule for the report. 2131 - Fix missing alias replacement in nested subqueries. Thanks to 2132 Pavel Stehule for the report. 2133 - Fix wrong addition of aliases to using() in join clause 2134 - Fix nested decode replacement producing invalid CASE expression. 2135 Thanks to Pavel Stehule for the report. 2136 - Append aliases to subqueries in the from clause that do not have 2137 one. Thanks to Pavel Stehule for the report. 2138 21392017 02 17 - v18.1 2140 2141This release fix several issues reported on outer join translation 2142thanks to the help of Pavel Stehule and reapply the commit on virtual 2143column export that was accidentally removed from v18.0. It also adds 2144several new features: 2145 2146 - Remove CHECK constraints for columns converted into boolean using 2147 REPLACE_AS_BOOLEAN column. 2148 - Oracle function are now marked as stable by default as they can 2149 not modify data. 2150 2151Two new configuration directives have been added: 2152 2153 - DATE_FUNCTION_REWRITE: by default Ora2pg rewrite add_month(), 2154 add_year() and date_trunc() functions set it to 0 to force Ora2Pg 2155 to not translate those functions if translated code is broken. 2156 - GRANT_OBJECT: when exporting GRANT you can now specify a comma 2157 separated list of objects which privileges must be exported. 2158 Default is to export privileges for all objects. For example 2159 set it to TABLE if you just want to export privilege on tables. 2160 2161and a new command line option: 2162 2163 - Add -g | --grant_object command line option to ora2pg to be able 2164 to extract privilege from the given object type. See possible values 2165 with GRANT_OBJECT configuration directive. 2166 2167Here is the complete list of changes: 2168 2169 - Remove empty output.sql file in current directory with direct data 2170 import. Thanks to kuzmaka for the report. 2171 - Fix shell replacement of $$ in function definition in Makefile.PL 2172 embedded configuration file. Thanks to kuzmaka for the report. 2173 - Fix shell replacement of backslash in Makefile.PL embedded 2174 configuration file. Thanks to kuzmaka for the report. 2175 - Add warning level to virtual column notice. 2176 - Fix comment in where clause breaking the outer join association. 2177 Thanks to Pavel Stehule for the report. 2178 - Add parsing and support of virtual column from DDL file. 2179 - Reapply commit on virtual column export that was accidentally 2180 removed in commit d5866c9. Thanks to Alexey for the report. 2181 - Fix mix of inner join and outer join not translated correctly. 2182 Thanks to Pavel Stehule for the help to solve this issue. 2183 - Fix additional comma in column DEFAULT value from DDL input file. 2184 Thanks to Cynthia Shang for the report. 2185 - Fix comments inside FROM clause breaking translation to ANSI outer 2186 joins. Thanks to Pavel Stehule for the report. 2187 - Fix replacement of sdo_geometry type into function. Thanks to 2188 Saber Chaabane for the report. 2189 - Fix subquery in outer join clause. Thanks to Saber Chaabane for 2190 the report. 2191 - Fix duplicated subqueries placeholder in the from clause. 2192 Thanks to Saber Chaabane for the report. 2193 - Fix replacement of subquery place older during outer join rewrite. 2194 Thanks to Saber Chaabane for the report. 2195 - Add DATE_FUNCTION_REWRITE configuration directive. By default 2196 Ora2pg rewrite add_month(), add_year() and date_trunc() functions 2197 set it to 0 to force Ora2Pg to not translate those functions if 2198 translated code is broken. Thanks to Pavel Stehule for the feature 2199 request. 2200 - Do not report error when -g is used but action is not GRANT. 2201 Thanks to Shane Jimmerson for the report. 2202 - Oracle function can not modify data, only procedure can do that, 2203 so mark them as stable. Thanks to Pavel Stehule for the report. 2204 - Missed some obvious combination like upper/lower case or no space 2205 after AND/OR on outer join parsing and some other issues. 2206 - Add missing call to extract_subqueries() recursively. Thanks to 2207 Pavel Stehule for the report. 2208 - Add full support of outer join translation in sub queries. 2209 - Add translation of mixed inner join and Oracle outer join. Thanks 2210 to Pavel Stehule for the report. 2211 - Fix missing space between keyword AS and END from the decode() 2212 transformation. Thanks to Pavel Stehule for the report. 2213 - Fix parsing of outer join with UNION and translation to left join. 2214 Thanks to Pavel Stehule for the report. 2215 - Remove CHECK constraints for columns converted into boolean using 2216 REPLACE_AS_BOOLEAN column. Thanks to Shane Jimmerson for the 2217 feature request. 2218 - Fix regression on SQL and PLSQL rewrite when a text constant 2219 contained a semi-comma. 2220 - Add the GRANT_OBJECT configuration directive. When exporting GRANT 2221 you can specify a comma separated list of objects for which the 2222 privileges will be exported. Default is export for all objects. 2223 Here are the possibles values TABLE, VIEW, MATERIALIZED VIEW, 2224 SEQUENCE, PROCEDURE, FUNCTION, PACKAGE BODY, TYPE, SYNONYM and 2225 DIRECTORY. Only one object type is allowed at a time. For example 2226 set it to TABLE if you just want to export privilege on tables. 2227 You can use the -g option to overwrite it. 2228 When used this directive prevent the export of users unless it is 2229 set to USER. In this case only users definitions are exported. 2230 - Add the -g | --grant_object command line option to ora2pg to be able 2231 to extract privilege from the given object type. See possible values 2232 with GRANT_OBJECT configuration directive. 2233 - Improve replacement of ROWNUM by LIMIT+OFFSET clause. 2234 - Fix extra semi-colon at end of statement. 2235 - Override ora2pg.spec with Devrim's one but with String::Random 2236 removing as it is no more used. 2237 22382017 01 29 - v18.0 2239 2240This new major release adds several new useful features and lot of 2241improvements. 2242 2243 * Automatic rewrite of simple form of (+) outer join Oracle's 2244 syntax. This major feature makes Ora2Pg become the first free 2245 tool that is able to rewrite automatically (+) outer join in 2246 command line mode. This works with simple form of outer join 2247 but this is a beginning. 2248 * Add export of Oracle's virtual column using a real column and 2249 a trigger. 2250 * Allow conversion of RAW/CHAR/VARCHAR2 type with precision in 2251 DATA_TYPE directive. Useful for example to transform all RAW(32) 2252 or VARCHAR2(32) columns into PostgreSQL special type uuid. 2253 * Add export NOT VALIDATED state from Oracle foreign keys and check 2254 constraints into NOT VALID constraints in PostgreSQL. 2255 * Replace call to SYS_GUID() with uuid_generate_v4() by default. 2256 * Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous 2257 transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;". 2258 * Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function 2259 calls and other PL/SQL to plpgsql replacement. There should not 2260 be any limitation in rewriting when a function contains a sub 2261 query or an other function call inside his parameters. 2262 * Refactoring of ora2pg to not requires any dependency other than 2263 the Perl DBI module by default. All DBD drivers are now optionals 2264 and ora2pg will expect an Oracle DDL file as input by default. 2265 * Add export of Oracle's global variables defined in package. They 2266 are exported as user defined custom variables and available in 2267 a session. If the variable is a constant or have a default value 2268 assigned at declaration, ora2pg will create a new file with the 2269 declaration (global_variables.conf) to be included in the main 2270 configuration file postgresql.conf file. 2271 * Create full text search configuration when USE_UNACCENT directive 2272 is enabled using the auto detected stemmer or the one defined in 2273 FTS_CONFIG. For example: 2274 CREATE TEXT SEARCH CONFIGURATION fr (COPY = french); 2275 ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR 2276 hword, hword_part, word WITH unaccent, french_stem; 2277 CREATE INDEX place_notes_cidx ON places 2278 USING gin(to_tsvector('fr', place_notes)); 2279 2280Changes and incompatibilities from previous release: 2281 2282 * FTS_INDEX_ONLY is now enabled by default because the addition of 2283 a column is not always possible and not always necessary where a 2284 simple function-based index is enough. 2285 * Remove use to setweigth() on single column FTS based indexes. 2286 * Change default behaviour of Ora2Pg in Full Text Search index 2287 export. 2288 2289A new command line option and some configuration directive have 2290been added: 2291 2292 * Option -D | --data_type to allow custom data type replacement 2293 at command line like in configuration file with DATA_TYPE. 2294 * UUID_FUNCTION to be able to redefined the function called to 2295 replace SYS_GUID() Oracle function. Default to uuid_generate_v4. 2296 * REWRITE_OUTER_JOIN to be able to disable the rewriting of Oracle 2297 native syntax (+) into OUTER JOIN if rewritten code is broken. 2298 * USE_UNACCENT and USE_LOWER_UNACCENT configuration directives to 2299 use the unaccent extension with pg_trgm with the FTS indexes. 2300 * FTS_INDEX_ONLY, by default Ora2Pg creates an extra tsvector column 2301 with a dedicated triggers for FTS indexes. Enable this directive 2302 if you just want a function-based index like: 2303 CREATE INDEX ON t_document USING 2304 gin(to_tsvector('pg_catalog.english', title)); 2305 * FTS_CONFIG, use this directive to force the text search stemmer 2306 used with the to_tsvector() function. Default is to auto detect 2307 the Oracle FTS stemmer. For example, setting FTS_CONFIG to 2308 pg_catalog.english or pg_catalog.french will override the auto 2309 detected stemmer. 2310 2311There's also lot fixes of issues reported by users from the past two 2312months, here is the complete list of changes: 2313 2314 - Fix return type in function with a single inout parameter and a 2315 returned type. 2316 - Prevent wrong rewrite of empty as null when a function is used. 2317 Thanks to Pavel Stehule for the report. 2318 - Add the UUID_FUNCTION configuration directive. By default Ora2Pg 2319 will convert call to SYS_GUID() Oracle function with a call to 2320 uuid_generate_v4 from uuid-ossp extension. You can redefined it 2321 to use the gen_random_uuid function from pgcrypto extension by 2322 changing the function name. Default to uuid_generate_v4. Thanks 2323 to sjimmerson for the feature request. 2324 - Add rewrite of queries with simple form of left outer join syntax 2325 (+) into the ansi form. 2326 - Add new command line option -D | --data_type to allow custom data 2327 type replacement at command line like in configuration file with 2328 DATA_TYPE. 2329 - Fix type in ROWNUM replacement expression. Thanks to Pavel Stehule 2330 for the report. 2331 - Add replacement of SYS_GUID by uuid_generate_v4 and allow custom 2332 rewriting of RAW type. Thanks to Nicolas Martin for the report. 2333 - Fix missing WHERE clause in ROWNUM replacement with previous patch 2334 thanks to Pavel Stehule for the report. 2335 - Fix ROWNUM replacement when e sub select is used. Thanks to Pavel 2336 Stehule for the report. 2337 - Fix wrong syntax in index creation with DROP_INDEXES enabled. 2338 Thanks to Pave Stehule for the report. 2339 - Remove replacement of substr() by substring() as PostgreSQL have 2340 the substr() function too. Thanks to Pavel Stehule for the report. 2341 - Move LIMIT replacement for ROWNUM to the end of the query. Thanks 2342 to Pavel Stehule for the report. 2343 - Fix text default value between parenthesis in table declaration. 2344 Thanks to Pavel Stehule for the report. 2345 - Fix return type when a function have IN/OUT parameter. Thanks to 2346 Pavel Stehule for the report. 2347 - Mark uuid type to be exported as text. Thanks to sjimmerson for 2348 the report. 2349 - Add EXECUTE to open cursor with like "OPEN var1 FOR var2;". Thanks 2350 to Pavel Stehule for the report. 2351 - Fix replacement of local type ref cursor. Thanks to Pavel Stehule 2352 for the report. 2353 - Add EXECUTE keyword to OPEN CURSOR ... FOR with dynamic query. 2354 Thanks to Pavel Stehule for the report. 2355 - Fix case sensitivity issue in FOR .. IN variable declaration 2356 replacement. Thanks to Pavel Stehule for the report. 2357 - Fix wrong replacement of cast syntax ::. Thanks to Pavel Stehule 2358 for the report. 2359 - Reactivate numeric cast in call to round(value,n). 2360 - Close main output data file at end of export. 2361 - Add virtual column state in column info report, first stage to 2362 export those columns as columns with associated trigger. 2363 - Fix unwanted replacement of REGEXP_INSTR. Thanks to Bernard 2364 Bielecki for the report. 2365 - Allow rewrite of NUMBER(*, 0) into bigint or other type instead 2366 numeric(38), just set DATA_TYPE to NUMBER(*\,0):bigint. Thanks to 2367 kuzmaka for the feature request. 2368 - Export partitions indexes into PARTITION_INDEXES_....sql separate 2369 file named. Thanks to Nicolas Martin for the feature request. 2370 - Fix fatal error when schema CTXSYS does not exists. Thanks to 2371 Bernard Bielecki for the report. 2372 - Fix missing text value replacement. Thanks to Bernard Bielecki 2373 for the report. 2374 - Fix type replacement in declare section when the keyword END was 2375 present into a variable name. 2376 - Export NOT VALIDATED Oracle foreign key and check constraint as 2377 NOT VALID in PostgreSQL. Thanks to Alexey for the feature request. 2378 - Add object matching of regex 'SYS_.*\$' to the default exclusion 2379 list. 2380 - Fix UTF8 output to file as the open pragma "use open ':utf8';" 2381 doesn't works in a global context. binmode(':encoding(...)') is 2382 used on each file descriptor for data output. 2383 - Improve parsing of tables/indexes/constraints/tablespaces DDL from 2384 file. 2385 - Improve parsing of sequences DDL from file. 2386 - Improve parsing of user defined types DDL from file. 2387 - Export Oracle's TYPE REF CURSOR with a warning as not supported. 2388 - Replace call to plsql_to_plpgsql() in Ora2Pg.pm by a call to new 2389 function convert_plsql_code(). 2390 - Move export of constraints after indexes to be able to use USING 2391 index in constraint creation without error complaining that index 2392 does not exists. 2393 - Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous 2394 transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;". 2395 - Improve parsing of packages DDL from file. 2396 - When a variable in "FOR varname IN" statement is not found in the 2397 DECLARE bloc, Ora2Pg will automatically add the variable to this 2398 bloc declared as a RECORD. Thanks to Pavel Stehule for the report. 2399 - Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function 2400 calls and other PL/SQL to plpgsql replacement. There should not 2401 be limitation in rewriting when a function contains a sub query 2402 or an other function call inside his parameters. 2403 - Fix unwanted SELECT to PERFORM transformation inside literal 2404 strings. Thanks to Pavel Stehule for the report. 2405 - Fix bug in DEFAULT value rewriting. Thanks to Pavel Stehule for 2406 the report. 2407 - Fix replacement of DBMS_OUTPUT.put_line with RAISE NOTICE. 2408 - Reset global variable storage for each package. 2409 - Improve comment parsing in packages and prevent possible infinite 2410 loop in global variable replacement. 2411 - Add the REWRITE_OUTER_JOIN configuration directive to be able to 2412 disable the rewriting of Oracle native syntax (+) into OUTER JOIN 2413 if it is broken. Default is to try to rewrite simple form of 2414 right outer join for the moment. 2415 - Export types and cursors declared as global objects in package 2416 spec header into the main output file for package export. Types 2417 and cursors declared into the package body are exported into the 2418 output file of the first function declared in this package. 2419 - Globals variables declared into the package spec header are now 2420 identified and replaced into the package code with the call to 2421 user defined custom variable. It works just like globals variables 2422 declared into the package body. 2423 - Add auto detection of Oracle FTS stemmer and disable FTS_CONFIG 2424 configuration directive per default. When FTS_CONFIG is set its 2425 value will overwrite the auto detected value. 2426 - Create full text search configuration when USE_UNACCENT directive 2427 is enabled using the auto detected stemmer or the one defined in 2428 FTS_CONFIG. For example: 2429 CREATE TEXT SEARCH CONFIGURATION fr (COPY = french); 2430 ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR 2431 hword, hword_part, word WITH unaccent, french_stem; 2432 CREATE INDEX place_notes_cidx ON places 2433 USING gin(to_tsvector('fr', place_notes)); 2434 - Remove CONTAINS(ABOUT()) from the migration assessment, there no 2435 additional difficulty to CONTAINS rewrite. 2436 - Add ANYDATA to the migration assessment keyword to detect. 2437 - Allow conversion of CHAR/VARCHAR2 type with precision in DATA_TYPE 2438 directive. For example it's possible to transform all VARCHAR2(32) 2439 columns only into PostgreSQL special type uuid by setting: 2440 DATA_TYPE VARCHAR2(32):uuid 2441 Thanks to sjimmerson for the feature request. 2442 - Update year in copyrights 2443 - Fix creation of schema when CREATE_SCHEMA+PG_SCHEMA are defined. 2444 - Fix renaming of temporary file when exporting partitions. 2445 - Move MODIFY_TYPE to the type section 2446 - Update documentation about globals variables. 2447 - Add export of Oracle's global variables defined in package. They 2448 are exported as user defined custom variables and available in 2449 a session. Oracle variables assignment are exported as call to: 2450 PERFORM set_config('pkgname.varname', value, false); 2451 Use of these variable in the code is replaced by: 2452 current_setting('pkgname.varname')::global_variables_type; 2453 the variable type is extracted from the pacjkage definition. If 2454 the variable is a constant or have a default value assigned at 2455 declaration, ora2pg will create file global_variables.conf with 2456 the definition to include in postgresql.conf file so that their 2457 values will already be set at database connection. Note that the 2458 value can always modified by the user so you can not have exactly 2459 a constant. 2460 - Fix migration assessment of view. 2461 - Remove call to FROM SYS.DUAL, only FROM DUAL was replaced. 2462 - Replace call to trim into btrim. 2463 - Improve rewrite of DECODE when there is function call inside. 2464 - Add function replace_right_outer_join() to rewrite Oracle (+) 2465 right outer join. 2466 - Improve view migration assessment. 2467 - Create a FTS section in the configuration file dedicated to FTS 2468 control. 2469 - Add USE_UNACCENT and USE_LOWER_UNACCENT configuration directives 2470 to use the unaccent extension with pg_trgm. 2471 - Do not create FTS_INDEXES_* file when there is no Oracle Text 2472 indexes. 2473 - Update query test score when CONTAINS, SCORE, FUZZY, ABOUT, NEAR 2474 keyword are found. 2475 - Remove use to setweigth() on single column FTS based indexes. 2476 Thanks to Adrien Nayrat for the report. 2477 - Update documentation on FTS_INDEX_ONLY with full explanation on 2478 the Ora2Pg transformation. 2479 - Refactoring ora2pg to not requires any dependency other than the 2480 Perl DBI module by default. All DBD drivers are now optionals and 2481 ora2pg will expect to received an Oracle DDL file as input by 2482 default. This makes easiest packaging or for any distribution that 2483 can not build a package because of the DBD::Oracle requirement. 2484 DBD::Oracle, DBD::MySQL and DBD::Pg are still required if you want 2485 Ora2Pg to migrate your database "on-line" but they are optional 2486 because Ora2Pg can also convert input DDL file, this is the 2487 default now. Thanks to Gustavo Panizzo for the feature request and 2488 the work on Debian packaging. 2489 - Remove String::Random dependency in rpm spec file, it is no used 2490 even if it was mentioned into a comment. 2491 - Exclude internal Oracle Streams AQ JMS types from the export. 2492 Thanks to Joanna Xu for the report. 2493 - Fix some other spelling issues. Thanks to Gustavo Panizzo for the 2494 patch. 2495 - Fix some spelling errors. Thanks to Gustavo Panizzo for the patch. 2496 - Revert patch 697f09d that was breaking encoding with input file 2497 (-i). Thanks to Gary Evans for the report. 2498 - Add two new configuration directive to control FTS settings, 2499 FTS_INDEX_ONLY and FTS_CONFIG. 2500 25012016 11 17 - v17.6 2502 2503This release adds several new features: 2504 2505 * Adds export of Oracle Text Indexes into FTS or pg_trgm 2506 based indexes, 2507 * Add export of indexes defined on materialized views 2508 * Allow export of materialized views as foreign tables 2509 when export type is FDW. 2510 * Add replacement of trim() by btrim(). 2511 2512Two new configuration directives have been added: 2513 2514 * USE_INDEX_OPCLASS: when value is set to 1, this will force 2515 Ora2Pg to export all indexes defined on varchar2() and char() 2516 columns using *_pattern_ops operators. If you set it to a value 2517 greater than 1 it will only change indexes on columns where the 2518 character limit is greater or equal than this value. 2519 2520 * CONTEXT_AS_TRGM: when enabled it forces Ora2Pg to translate 2521 Oracle Text indexes into PostgreSQL indexes using pg_trgm 2522 extension. Default is to translate CONTEXT indexes into FTS 2523 indexes and CTXCAT indexes using pg_trgm. Some time using 2524 pg_trgm based indexes is enough. 2525 2526There's also some fixes of issues reported by users, here is the 2527complete list of changes: 2528 2529 - Fixed non-use of custom temp_dir (-T). Thanks to Sebastian 2530 Albert for the patch. 2531 - Make export of FTS indexes from materialized view work as 2532 for tables. 2533 - Fix drop of indexes during export of data when DROP_INDEXES 2534 is enabled. 2535 - Remove double quote in function and procedure name from an input 2536 file to avoid creating a file with double quote in its name. 2537 - Fix export of unique index associated to a primary key. 2538 - Move OPTION (key "yes") of FDW table before NOT NUL constraint 2539 and default clause. 2540 - Fix some encoding issue during data export into file. 2541 - Rename FTS indexes prefix output file into FTS_INDEXES and 2542 export CTXCAT Oracle indexes as GIN pg_trgm indexes instead of 2543 FTS indexes. 2544 - Add export of indexes of type CTXCAT as FTS indexes. 2545 - Export triggers and update order for FTS indexes to separate file 2546 prefixed with FTS_INDEXES. 2547 - Exclude from export synonyms starting with a slash that correspond 2548 to incomplete deleted synonyms. Thanks to Nouredine Mallem for the 2549 report. 2550 - Add export of indexes defined on materialized views. Thanks to 2551 Nouredine Mallem for the report. 2552 - Fix export of foreign key and FTS indexes when looking at dba_* 2553 tables and multiple different schemas have the same fk or context 2554 indexes definition. Thanks to Nouredine Mallemfor the patch. 2555 - Fix export of CONTEXT or FULLTEXT Oracle index into PostgreSQL 2556 FTS with trigger and initial update statement. 2557 - Add configuration directive USE_INDEX_OPCLASS to force Ora2Pg to 2558 export all indexes defined on varchar2() and char() columns using 2559 those operators. A value greater than 1 will only change indexes 2560 on columns where the character limit is greater or equal than 2561 this value. 2562 - Fix FDW export of mysql tables. Thanks to yafeishi for the report. 2563 - Fix decode() rewrite. Thanks to Jean-Yves Julliot for the report. 2564 - Fix regression introduced into the export of NUMBER to integer 2565 like PG types. 2566 - Show partition name in progress bar instead of main table name. 2567 25682016 10 20 - v17.5 2569 2570This is a maintenance release to fix several issues reported by users. 2571There is also some major improvement and new feature. 2572 2573There is a new configuration directive or change default behavior: 2574 2575 * Fix export of CLOBs and NCLOB that was truncated to 64 Kb. 2576 * PG_BACKGROUND : when enabled autonomous transactions will be 2577 built using Robert Haas extension pg_background instead of dblink. 2578 Default is to still used dblink as pg_background is available 2579 only for PostgreSQL >= 9.5. 2580 * All Perl I/O now use the open pragma instead of calling method 2581 binmode(). This will force input and output to utf8 using the 2582 Perl pragma: 2583 use open ':encoding(utf8)'; 2584 when configuration directive BINMODE is not set or NLS_LANG is 2585 set to UTF8. 2586 * Ora2Pg will now export empty lob as empty string instead of NULL 2587 when the source column has NOT NULL constraint and that directive 2588 EMPTY_LOB_NULL is not activated. 2589 * Improve and fix progress bar especially when using JOBS/-J option. 2590 * Allow LOAD action to apply all settings defined in the input file 2591 on each opened session, this allow to use LOAD with export schema 2592 enabled. If settings are not set in the input file encoding and 2593 search_path is set from the ora2pg configuration settings. 2594 * NUMBER(*,0) is now exported as numeric(38) as well as a NUMBER 2595 with DATA_SCALE set to 0, no DATA_PRECISION and a DATA_LENGTH 2596 of 22. The last correspond to Oracle type INTEGER or INT. 2597 * Allow conversion of type with precision in DATA_TYPE directive. 2598 For example it is possible to transform all NUMBER(12,2) only 2599 into numeric(12,2) by escaping the comma. Example: 2600 DATA_TYPE NUMBER(12\,2):numeric(12\,2);... 2601 * Write data exported into temporary files (prefixed by tmp_) and 2602 renamed them at end of the export to be able to detect incomplete 2603 export and override it at next export. 2604 * Add export of type created in package declaration. 2605 * Export foreign key when the referenced table is not in the 2606 same schema. 2607 * Enabled by default PG_SUPPORTS_CHECKOPTION assuming that your Pg 2608 destination database is at least a 9.4 version. 2609 * Add 12 units to migration assessment report per table/column 2610 conflicting with a reserved word in PostgreSQL to reflect the 2611 need of code rewriting. 2612 * Output a warning when a column has the same name than a system 2613 column (xmin,xmax,ctid,etc.) 2614 * Replace SYSDATE by a call to clock_timestamp() instead of a call 2615 to LOCALTIMESTAMP in plpgsql code. 2616 * Add missing documentation about DISABLE_PARTITION directive used 2617 to not reproduce partitioning into PostgreSQL and only export 2618 partitioned data into the main table. 2619 * Show partition name in progress bar instead of main table name. 2620 2621Here is the complete list of other changes: 2622 2623 - Fix broken parallel table export (-P option). 2624 - Fix export of CLOBs and NCLOB that was truncated to 64Kb. Thanks 2625 to Paul Mzko for the patch. 2626 - Fix database handle in error report. 2627 - Fix use of wrong database handle to set search_path. Thanks to 2628 Paul Mzko for the report. 2629 - Ora2pg doesn't export schema ForeignKey constraint when connected 2630 as different DBA user. Thanks to Paul Mzko for the patch. 2631 - Fix Perl I/O encoding using open pragma instead of calling method 2632 binmode(). Thanks to Gary Evans for the report. 2633 - Force input to utf8 using Perl pragma: use open ':encoding(utf8)'; 2634 when BINMODE is not set or NLS_LANG is UTF8. 2635 - Force ora2pg to export empty lob as empty string instead of NULL 2636 when the source column has a NOT NULL constraint and directive 2637 EMPTY_LOB_NULL is not activated. Thanks to Valeriy for the report. 2638 - Fix missing CASCADE attribute on fkey creation during data export 2639 when DROP_FKEY was enabled. Thanks to ilya makarov for the report. 2640 - Fix issue on converting NUMBER(*,0) to numeric, should be ported 2641 to numeric(38). Thanks to ilya makarov for the report. 2642 - Correct query for ForeignKey export from oracle. Thanks to ilya 2643 makarov for the patch. 2644 - Fix schema change in direct import of data to PostgreSQL. 2645 - Change query for foreign key extraction to keep the column order. 2646 Thanks to ilya makarov for the report. 2647 - Write data exported into temporary files (prefixed by tmp_) and 2648 renamed them at end of the export to be able to detect incomplete 2649 export and override it at next export. Thanks to Paul Mkzo for 2650 the feature request. 2651 - Fix infinite loop in blob extraction when error ORA-25408 occurs 2652 during ora_lob_read() call. Thanks to Paul Mzko for the report. 2653 - Fix order of columns in foreign keys definition. Thanks to ilya 2654 makarov for the report. 2655 - Fix export of partition by range on multicolumn. Thanks to Rupesh 2656 Admane for the report. 2657 - Update reserved keywords list. Thanks to Nicolas Gollet for the 2658 report. 2659 - Add ON DELETE NO ACTION on foreign key creation (DROP_FKEY) to 2660 obtain the same output than during constraints export. 2661 - Fix export of foreign key that was duplicating the columns in both 2662 part, local and foreign. Thanks to ilya makarov for the report. 2663 - Remove call to to_char(datecol, format) when exporting date and 2664 timestamp. This formating is no more needed as we are now forcing 2665 NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT when opening a connection 2666 to Oracle using: 2667 ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS 2668 and 2669 ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS 2670 This may result on some speed improvment during data export. 2671 - Fix parsing of packages from input file. 2672 - Add export of type created in package declaration. Thanks to 2673 dezdechado for the report. 2674 - Fix converting of procedures with out arguments. Thanks to 2675 dezdechado for the report. 2676 - Update documentation about project management. 2677 - Fix replacement of = NULL by IS NULL in update statement. 2678 Thanks to dezdechado for the report. 2679 - Fix parsing of trigger from file that was broken and new line 2680 removed. Thanks to dezdechado for the report. 2681 - Fix erasing of quotes from text in triggers. Thanks to dezdechado 2682 for the report. 2683 - Fix "return new" on trigger function when there is exception. 2684 Thanks to dezdechado for the report and solution. 2685 - Fix conversion of INTEGER and INT into numeric(38) instead of 2686 numeric without precision. Thanks to dezdechado for the report. 2687 - Fix export of foreign key when the referenced table is not in the 2688 same schema. Thanks to Juju for the report. 2689 - Fix ddl create schema when EXPORT_SCHEMA and CREATE_SCHEMA are 2690 enabled but no schema is specified. 2691 - Fix export of NCHAR that was converted as char but was loosing its 2692 length definition. Thanks to lgerlandsen for the report. 2693 - Fix parsing of views using WITH statements. Thank to dezdechado 2694 for the report. 2695 - Fix case that breaks views/triggers definition when a semicolon 2696 is encountered in a string value when reading definition from 2697 file. Thanks to dezdechado for the report. 2698 - Fix included/excluded of sequences when using ALLOW/EXCLUDE 2699 directives. Thanks to Roman Sindelar for the report. 2700 - prepare options modified with some escaping improvements. Thanks 2701 to ioxgrey for the patch. 2702 - It seems that for a NUMBER with a DATA_SCALE set to 0, no 2703 DATA_PRECISION and a DATA_LENGTH of 22 in ALL_TAB_COLUMNS, Oracle 2704 use a NUMBER(38) instead. This correspond to Oracle type INTEGER 2705 or INT. I don't really understand the reason of this behavior, 2706 why not just using a data length of 38? ALL_TAB_COLUMNS and Ora2Pg 2707 reports a data length of 22, now Ora2Pg will report NUMBER(38) like 2708 the resulting type of the DESC command. 2709 2710 The following Oracle table: 2711 2712 CREATE TABLE TEST_TABLE ( FIELD_1 INTEGER, FIELD_2 NUMBER ); 2713 2714 will be exported as follow by Ora2Pg: 2715 2716 [1] TABLE TEST_TABLE (owner: HR, 0 rows) 2717 FIELD_1 : NUMBER(38) => numeric 2718 FIELD_2 : NUMBER(22) => bigint 2719 2720 Oracle data type INTEGER and INT will be exported as numeric by 2721 default instead of an integer. 2722 - Fix parsing of function/procedure from file with comments after 2723 BEGIN statement. 2724 - Remove DEFERRABLE INITIALLY DEFERRED from CHECK constraints when 2725 parsed from file. Thanks to Felipe Lavoura for the report. 2726 - Fix double parenthesis in index definition when parsing index 2727 creation from file. Thanks to Felipe Lavoura for the report. 2728 - Fix parsing of COMMENT from file. 2729 - Fix undetected native Oracle type bug. Thanks to kvnema for the 2730 report. 2731 - Fix unwanted text formatting with bind value in INSERT action 2732 with direct import to PostgreSQL. Thanks to Oleg for the report. 2733 - Fix inversion of UPDATE_RULE and DELETE_RULE in foreign key 2734 creation for MySQL export. Thanks to Sebastian Albert for the 2735 report. 2736 - Update documentation about DEFER_FKEY and DROP_FKEY to report 2737 the new behavior. 2738 - Remove call to SET CONSTRAINTS ALL DEFERRED with direct import. 2739 - Fix use of NULL value in bind parameter that should be undefined 2740 (INSERT export mode only). Thanks to Oleg barabaka for the report. 2741 - Remove replacement of direct call to functions with PERFORM, there 2742 is too much false positive. Thanks to dezdechado for the reports. 2743 - Fix a typo in SYSDATE replacement. Thank to dezdechado for report. 2744 - Remove rewrite of concatenation in RAISE EXCEPTION. Thanks to 2745 dezdechado for the report. 2746 - Fix replacement of raise_application_error() when first argument 2747 is a variable. Thanks to dezdechado for the report. 2748 - Fix wrong insertion of PERFORM before some function calls. Thanks 2749 to dezdechado for the report. 2750 - Replace SYSDATE by a call to clock_timestamp() instead of call to 2751 LOCALTIMESTAMP in plpgsql code. Thanks to aleksaan for the report. 2752 - Allow use of comma for object name list separator instead of space 2753 as workaround on Window OS. 2754 - Fix documentation about MODIFY_TYPE. Thanks to Nicolas Gollet for 2755 the report. 2756 - Add missing documentation about DISABLE_PARTITION directive used 2757 to not reproduce partitioning into PostgreSQL and only export 2758 partitioned data into the main table. Thanks to Nicolas Gollet 2759 for the report. 2760 - Add information about how to export LONG RAW data type. They need 2761 to be exported as BLOB before into Oracle to be exported as BYTEA. 2762 - Fix case where select was wrongly replaced by perform in INSERT 2763 INTO with SELECT statement. Thanks to dezdechado for the report. 2764 - Fix links to ora2pg presentation. Thanks to Daniel Lenski for the 2765 patch. 2766 - Fix input parameters after one with a default value must also have 2767 defaults. Thanks to v.agapov fot the patch. 2768 - Fix debug mode that was interromping the last running table dump. 2769 Thanks to calbiston for the report. 2770 27712016 04 21 - v17.4 2772 2773Errata in first release attempt. 2774 2775 - Fix previous patch that does not handle blob case but just clob 2776 - Forgot to change back the query when EMPTY_LOB_NULL is not activated. 2777 - Put parenthesis around AT TIME ZONE expression 2778 2779This is a maintenance release to fix several issues reported by users. 2780There is also some major data export speed improvement thanks to the 2781work of PostgreSQL Pro and a new RPM spec file provided by Devrim 2782Gunduz to be able to build RPM package for Ora2Pg. 2783 2784There is a new configuration directive: 2785 2786 - EMPTY_LOB_NULL: when enabled force empty_clob() and empty_blob() 2787 to be exported as NULL instead as empty string. 2788 2789Here is the complete list of other changes: 2790 2791 - Add EMPTY_LOB_NULL directive to force empty_clob() and empty_blob() 2792 to be exported as NULL instead as empty string. This might improve 2793 data export speed if you have lot of empty lob. Thanks to Alex 2794 Ignatov for the report. 2795 - Fix import_all.sh script to import grant and tablespace separately 2796 as postgres user and just after indexes and constraints creation. 2797 - Add parsing of tablespace from "alter table ... add constraint" 2798 with DDL input file. Thanks to Felipe Lavoura. 2799 - Remove --single-transaction in import_all.sh script with TABLESPACE 2800 import. Thanks to Guillaume Lelarge for the report. 2801 - Fix Makefile.PL to used with latest spec file from Devrim Gunduz 2802 and following the advice of calbiston. 2803 - Update spec file to v17.6 and latest change to Makefile.PL 2804 - Replace ora2pg.spec by postgressql.org spec file by Devrim Gunduz. 2805 - Generate man page to avoids rpmbuild error. 2806 - Fix Windows install. Thanks to Lorena Figueredo for the report. 2807 - Remove "deferrability" call for mysql foreign keys. Thanks to 2808 Jean-Eric Cuendet for the report. 2809 - Fix issue in restoring foreign key for mysql data export. Thanks 2810 to Jean-Eric Cuendet for the report. 2811 - Remove connection test to PostgreSQL instance as postgres or any 2812 superuser in import_all.sh 2813 - Fix creation of configuration directory. 2814 - Fix Makefile to dissociate CONFDIR and DOCDIR from PREFIX or 2815 DESTDIR. Thanks to Stephane Schildknecht for the report. 2816 - Fix date_trunc+add_month replacement issue. Thanks to Lorena 2817 Figueredo for the report. 2818 - Do not replace configuration directory in scripts/ora2pg if this 2819 is a RPM build. Thanks to calbiston for the report. 2820 - Return empty bytea when a LOB is empty and not NULL. 2821 - Regular expressions and conditions checks improvement in method 2822 format_data_type() to make it a bit faster on huge tables. Thanks 2823 to Svetlana Shorina for the patch. 2824 - Fix INSERT using on the fly data import with boolean values. 2825 Thanks to jecuendet for the report. 2826 - Allow MySQL data type to be converted into boolean. Thanks to 2827 jecuendet for the report. 2828 - Fix export of BIT mysql data type into bit bit varying. Thanks 2829 to jecuendet for the report. 2830 - Fix call to escape_copy/escape_insert function call. 2831 28322016 03 26 - v17.3 2833 2834This release fix two regressions introduced in latest release. 2835 2836 * Fix major bug in data export. Thanks to Frederic Guiet for the report. 2837 * Fix another regression with character data that was not escaped. Thanks 2838 to Frederic Guiet for the report. 2839 28402016 03 24 - v17.2 2841 2842This is a maintenance release to fix several issues reported in new 2843LOB extraction method. There is also some feature improvement: 2844 2845 * Allow NUMBER with precision to be replaced as boolean. 2846 * Allow full relocation of Ora2Pg installation using for 2847 example: perl Makefile.PL DESTDIR=/opt/ora2pg 2848 2849Here is the complete list of other changes: 2850 2851 - Allow NUMBER with precision to be replaced as boolean. Thanks 2852 to Silvan Auer for the report. 2853 - Force empty LOB to be exported as NULL when NO_LOB_LOCATOR is 2854 activated to have the same behavior. 2855 - Fix case where a LOB is NULL and ora2pg reports error : 2856 DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr 2857 LOB initialised with EMPTY_CLOB() are also exported as NULL 2858 instead of \\x 2859 - Fix replacement with PERFORM after MINUS. Thanks to Stephane 2860 Tachoires for the report. 2861 - Comment DBMS_OUTPUT.ENABLE calls. Thanks to Stephane Tachoire for 2862 the report. 2863 - Fix wrong replacement of SELECT by PERFORM after EXCEPT. Thanks 2864 to Stephane Tachoire for the report. 2865 - Apply ORACLE_COPIES automatic predicate on custom queries set with 2866 REPLACE_QUERY if possible. Thanks to pawelbs for the report. 2867 - Fix install of ora2pg.conf file in /etc/ instead of /etc/ora2pg/. 2868 Thanks to pawelbs for the report. 2869 - Add debug information before searching for custom type. 2870 - Attempt to fix error "ORA-01002: fetch out of sequence" when exporting 2871 data from a table with user defined types and ORACLE_COPIES. Thanks to 2872 pawelbs and Alex Ignatov fir the report. 2873 - Fix replacement of path to configuration file in scripts/ora2pg 2874 - Remove report sample from documentation about migration assessment 2875 report and replace it with a href link. Fix comment about export of 2876 domain index. 2877 - Always prefix table name with schema in Oracle row count, to prevent 2878 failure when the schema is not the connexion default. 2879 - Add pattern TOAD_PLAN_.* to the internal table exclusion list. 2880 - Fix modification of database owner search_path in import_all.sh auto 2881 generated script. Thanks to Stephane Tachoire for the report. 2882 28832016 02 29 - v17.1 2884 2885This is a maintenance release to fix several issues reported in new 2886TEST action. There is also some feature improvement: 2887 2888 * Add OPTIONS (key 'true') on table FDW export when a column is 2889 detected as a primary key. 2890 * Add DELETE configuration directive that allow a similar feature 2891 than the WHERE clause to replace TRUNCATE call by a "DELETE FROM 2892 table WHERE condition". This feature can be useful with regular 2893 "updates". Thanks to Sebastien Albert for the feature request. 2894 2895Here is the complete list of other changes: 2896 2897 - Fix the counter of user defined types and sequences in TEST action 2898 - Fix COPY import of data from column with user defined type with 2899 NULL value. 2900 - Fix DBD::Pg segmentation fault with direct INSERT import from 2901 column with user defined type. 2902 - Fix TEST action with multiple PG_SCHEMA export. Thanks to Michael 2903 Vitale for the report. 2904 - Fix documentation about PG_SCHEMA 2905 29062016 02 22 - v17.0 2907 2908This new major release adds a new action type TEST to obtain a count 2909of all objects at both sides, Oracle and PostgreSQL, to perform a 2910diff between the two database and verify that everything have been 2911well imported. It also fixes several issues reported by users. 2912 2913A new ora2pg command line option have been added to ora2pg script: 2914 2915 * Add --count_rows command line option to perform a real row count 2916 on both side, Oracle and PostgreSQL, in TEST report. 2917 2918Here is the complete list of changes and bugfixes: 2919 2920 - Prefix direct call to function with a call to PERFORM. Thanks to 2921 Michael Vitale for the feature request. 2922 - Fix revoke call on function with multiline parameters declaration. 2923 - Fix auto setting of internal schema variable with mysql. 2924 - Define ORACLE_HOME with the corresponding environment variable in 2925 generic configuration when available and --init_project is used. 2926 Thanks to Stephane Tachoires for the report. 2927 - Fix documentation about exporting view as table. 2928 - Remove some obsolete code and display information when a view is 2929 exported as table. 2930 - Fix empty LOB data export with Oracle Lob locator (NO_LOB_LOCATOR 2931 set to 0). 2932 - Fix data export of partitions with single process mode and when 2933 FILE_PER_TABLE is enabled. 2934 - Fix export of RAW data type. 2935 - Fix missing $ to call to self variable. Thanks to NTLIS and Sirko 2936 for the report. 2937 - Force FKey to be initially immediate when deferred is not set. 2938 Thanks to Stephane Tachoire for the report. 2939 - Fix count of check constraint when a schema is forced. 2940 - Allow TEST action on mysql database too with some improvements 2941 and bug fix on the feature. 2942 - Fix index column renaming in mysql export. 2943 - Fix dblink extraction query when an exclusion is set. 2944 - Fix sequence name auto generation for mysql serial number. 2945 - Add --count_rows command line option to make optional the real 2946 row count in TEST report. This is useful when you have lot of 2947 data and do not want to loose time in call to count(*). 2948 - Update documentation about the TEST action and usage, see 2949 chapter "Test the migration". 2950 - Apply schema context on PostgreSQL side with TEST action. 2951 - Add TEST action type to ask Ora2Pg to count rows and all objects 2952 at both sides, Oracle and PostgreSQL, to verify that everything 2953 have been well imported. 2954 - Fix missing export of foreign keys on multiple columns, ex: 2955 ALTER TABLE products ADD CONSTRAINT fk_supplier_comp 2956 FOREIGN KEY (supplier_id,supplier_name) 2957 REFERENCES supplier(supplier_id,supplier_name)... 2958 - Fix import of BLOB data using INSERT statements into the bytea. 2959 Thanks to rballer for the patch. 2960 - Fix missing export of FK when no schema is provided. 2961 29622016 01 13 - v16.2 2963 2964This release fixes several issues, is more accurates on migration 2965assessment report and adds some new ora2pg command line options: 2966 2967 * Add --pg_dsn, --pg_user and --pg_pwd to be able to set the 2968 connection to import directly into PostgreSQL at command line. 2969 * Add -f option to script import_all.sh to force to not check 2970 user/database existing and skip their creation. 2971 2972Potential backward compatibility issues: 2973 2974 * PG_SUPPORTS_CHECKOPTION is now enabled by default, you may want 2975 to migrate to PostgreSQL 9.4 or above. 2976 * Remove modification of CLIENT_ENCODING in generic configuration 2977 file with --init_project, use the default instead. 2978 * Remove modification of directive NLS_LANG to AMERICAN_AMERICA.UTF8 2979 in generic configuration file with --init_project, use the default 2980 instead. 2981 2982Here is the complete list of other changes: 2983 2984 - Adjust DBMS_OUTPUT calls to the migration assessment count. 2985 - Fix migration assessment count of call to cursor%ISOPEN and 2986 cursor%ROWCOUNT. 2987 - Replace zero date also with prepared statement with online 2988 PostgreSQL import and INSERT action. Thanks to Sebastian Albert 2989 for the report. 2990 - Remove REFERENCING clause in conditional triggers. Thanks to Raqua 2991 for the report. 2992 - Fix position of TG_OP condition when an exception is defined. 2993 Thanks to Raqua for the report. 2994 - Fix wrong replacement of SELECT with PERFORM when a comment was 2995 found between an open parenthesis and the select statement. 2996 Thanks to Raqua for the report. 2997 - Fix procedure return type with OUT and INOUT parameter. Thanks to 2998 Raqua for the report. 2999 - Fix rewrite of triggers with referencing clause. Thanks to Raqua 3000 for the report. 3001 - Fix default number of --human_days_limit in usage. 3002 - Fix replacement of placeholder %TEXTVALUE-d% to hide text string 3003 in query during function call rewrite. Thanks to Lorena Figueredo 3004 for the report. 3005 - Fix progress bar when a WHERE clause is used to limit the number 3006 of row to export. 3007 - Fix error "DBD::Pg::db do failed: SSL error: decryption failed or 3008 bad record mac" with pararellel table export (-P) and direct 3009 import to PostgreSQL via a ssl connection. Thanks to pbe-axelor 3010 for the report. 3011 - Fix missing index name in indexes creation. Thanks to Raqua for 3012 the report. 3013 - Fix pg DSN in import_all.sh autogenerated script. 3014 - Fix extraction of trigger. When the name of a column or something 3015 contained INSERTING, DELETING or UPDATING was converted to TG_OP 3016 = 'INSERT' or corresponding event. Thanks to Stanislaw Jankowski 3017 for the patch. 3018 - Fix multiple use of same column in check constraint and indexes 3019 of partitions when there was several schema with the same objects. 3020 - Fix default value for HUMAN_DAY_LIMIT to 5 when it is not defined 3021 in ora2pg.conf. 3022 - Fix double quote on column name in COPY export of partition tables 3023 Thanks to Chris Brick for the report. 3024 - Prevent case with several time same column in multicolumns unique 3025 constraints. Fix typo in previous patch. 3026 - Fix double quoted name with auto incremented sequence exported as 3027 serial. 3028 - Fix syntax error with MySQL data export with a WHERE clause using 3029 LIMIT. 3030 30312015 11 30 - v16.1 3032 3033This release fixes several issues and adds some very useful features: 3034 3035 * Generate automatically a new import_all.sh shell script when using option 3036 --init_project to help automate all import into PostgreSQL. 3037 3038 See sh import_all.sh -? for more information. 3039 3040 * Export Oracle bitmap index as PostgreSQL btree_gin index. This require the 3041 btree_gin extension and PostgreSQL >= 9.4. This is the default. 3042 3043 * Auto set DEFINED_PK to the first column of a table that have a unique key 3044 defined that is a NUMBER. This allow data of any table with a numeric 3045 unique key to be extracted using multiple connexions to Oracle using -J 3046 option. Tables with no numeric unique key will be exported with a single 3047 process. 3048 3049 * Improve BLOB export speed by using hex encoding instead of escape. This 3050 might speed up be BLOB export by 10. 3051 3052 * Allow use of LOB locator to retrieve BLOB and CLOB data to prevent having 3053 to set LONGREADLEN. Now LONGREADLEN is set to 8KB. Old behavior using 3054 LONGREADLEN can still be enabled by setting NO_LOB_LOCATOR to 0, given 3055 for backward compatibility. Default is to use LOB locator. 3056 3057 * Ora2Pg will also auto detect table with BLOB and automatically decrease 3058 DATA_LIMIT to a value lower or equal to 1000. This is to prevent OOM. 3059 3060 * Improving indexes and constraints creation speed by using the LOAD action 3061 and a file containing SQL orders to perform. It is possible to dispatch 3062 those orders over multiple PostgreSQL connections. To be able to use this 3063 feature, PG_DSN, PG_USER and PG_PWD must be set. Then: 3064 3065 ora2pg -t LOAD -c config/ora2pg.conf -i schema/tables/INDEXES_table.sql -j 4 3066 3067 will dispatch indexes creation over 4 simultaneous PostgreSQL connections. 3068 3069 This will considerably accelerate this part of the migration process with 3070 huge data size. 3071 3072 * Domain indexes are now exported as b-tree but commented to let you know 3073 where possible FTS are required. 3074 3075 * Add number of refresh ON COMMIT materialized view in detailed report. 3076 3077 * Allow redefinition of numeric type, ex: NUMBER(3)::bigint to fix wrong 3078 original definition in Oracle. 3079 3080 * Allow export of all schemas from an Oracle Instance when SCHEMA directive 3081 is empty and EXPORT_SCHEMA is enabled. All exported objects will be 3082 prefixed with the name of their original Oracle schema or search_path will 3083 be set to that schema name. Thanks to Magnus Hagander for the feature 3084 request. 3085 3086 * Allow use of COPY FREEZE to export data when COPY_FREEZE is enabled. This 3087 will only works with export to file and when -J or ORACLE_COPIES is not 3088 set or default to 1. It can be used with direct import into PostgreSQL 3089 under the same condition but -j or JOBS must be unset or default to 1. 3090 Thanks to Magnus Hagander for the feature request. 3091 3092Some new configuration directives: 3093 3094 * BITMAP_AS_GIN: enable it to use btree_gin extension to create bitmap 3095 like index with pg >= 9.4. You will need to create the extension by 3096 yourself: "create extension btree_gin;". Default is to create GIN index, 3097 when disabled, a btree index will be created. 3098 * NO_LOB_LOCATOR: to disable use of LOB locator and extract BLOB "inline" 3099 using a less or more high value in LONGREADLEN. 3100 * BLOB_LIMIT: to force the value of DATA_LIMIT for tables with BLOB. Default 3101 is to automatically set this limit using the following code: 3102 BLOB_LIMIT=DATA_LIMIT; while (BLOB_LIMIT > 1000) BLOB_LIMIT /= 10 3103 * COPY_FREEZE: use it to use COPY FREEZE instead of simple COPY to speedup 3104 import into PostgreSQL. 3105 3106Here is the complete list of other changes: 3107 3108 - Limite package function name rewrite to call with parenthesis after the 3109 function name to avoid rewriting names of other objects. 3110 - Fix extra replacement of function name with package prefix. On some 3111 condition it was done multiple time. 3112 - Set REPLACE_ZERO_DATE to -INFINITY in generic configuration when --mysql 3113 is enabled. 3114 - Fix extraction of partition with MySQL that was not limited to a single 3115 database. 3116 - Do some replacement on ORACLE_DNS and SCHEMA into generic configuration 3117 when --mysql is used for better understanding. 3118 - Add call to round() on -J parallelization when the auto detected column 3119 is a numeric with scale. 3120 - Add COMMIT to the difficulties migration assessment keywords as it need 3121 context analyzing. 3122 - Add call to cursor's %ROWCOUNT, %ISOPEN and %NOTFOUND to difficulties 3123 migration assessment keywords. 3124 - Replace call to CURSOR%ROWTYPE by RECORD. Thanks to Marc Cousin for the 3125 report. 3126 - Fix ALTER FUNCTION ... OWNER TO ... and REVOKE statement where functions 3127 parameters were missing. 3128 - Add Get_Env to the Oracle functions list for migration assessment. 3129 - Disable variable NO_LOB_LOCATOR and set LONGREADLEN to 8192 bytes to use 3130 LOB locators to extract BLOB in generic configuration file. 3131 - Fix call method "disconnect" on unblessed reference at line 9998. Thanks 3132 to Stephane Tachoires for the report. 3133 - Exclude from export objects name matching /.*\$JAVA\$.*/ and /^PROF\$.*/. 3134 - Fix migration assessment report when created during the package export. 3135 - Force writing Oracle package body in separate files when FILE_PER_FUNCTION 3136 is enabled and PLSQL_PGSQL disable to obtain package source code. 3137 - Fix case where sequence max value is lower than start value, in this case, 3138 set max value = start value. 3139 - Fix missing newline after each package file to import in global package.sql 3140 file when FILE_PER_FUNCTION is enabled. 3141 - Remove export of user PUBLIC in GRANT export. 3142 - Set DISABLE_TRIGGERS to 1 in generic configuration file auto generated when 3143 ora2pg option --init_project is used. 3144 - Remove call to quote_reserved_words() with index column when it we detect 3145 a function based index, too much false positive are rewritten with SQL code 3146 like CASE...WHEN. 3147 - Update export_schema.sh to remove .sql files when there is not such object 3148 leaving export directory empty. 3149 - Prevent creating TBSP_INDEXES_tablespace.sql when no tablespaces are found 3150 - Update documentation on WHERE clause on how to limit the number of tuples 3151 exported for Oracle and MySQL to test data import. 3152 - Fix unlisted spatial indexes in assessment report. 3153 - Fix double quote on index name with index renaming and reserved keyword. 3154 - Do not try to export tablespaces, privileges and audited queries as non DBA 3155 user when USER_GRANT is enabled. 3156 - Remove carriage return from list file. 3157 - Force SCHEMA to database name with MySQL migration. 3158 - Fix missing declaration of _extract_sequence_info(). Thank to Yannick DEVOS 3159 for the report. 3160 - Add documentation about COPY_FREEZE directive and add a note about export 3161 of all schema. 3162 - Remove systematic schema name appended to table name on KETTLE export, this 3163 must only be true when EXPORT_SCHEMA is enabled. 3164 - Fix TO_NUMBER() wrong replacement when a function is called as a parameter. 3165 - Fix non converted DECODE() when they was called in an XMLELEMENT function. 3166 - Suppress MDSYS.SDO_* from MDSYS call in migration assessment cost. 3167 - Remove use of DBMS_STANDARD called with raise_application_error function 3168 - Fix STRING type replacement 3169 - Recreate README as a text file, not a man page. 3170 - Reformat changelog to 80 characters. 3171 - Add -t | --test command line option to ora2pg_scanner to be able to test 3172 all connections defined into the CVS list file. 3173 31742015 10 15 - v16.0 3175 3176This major release improve PL/SQL code replacement, fixes several bugs and 3177adds some major new features: 3178 3179 * Full migration of MySQL database, it just work like with Oracle database. 3180 * Full migration assessment report for MySQL database. 3181 * New script, ora2pg_scanner, to perform a migration assessment of all 3182 Oracle and MySQL instances on a network. 3183 * Add technical difficulty level in migration assessment. 3184 * Allow migration assessment on client queries extracted from AUDIT_TRAIL 3185 (oracle) or general_log table (mysql). 3186 * Ora2Pg has a "made in one night" brand new Web site (still need some work). 3187 See http://ora2pg.darold.net/ 3188 3189Example of technical difficulty level assessment output for the sakila database 3190with some more difficulties: 3191 3192 Total 83.90 cost migration units means approximatively 1 man-day(s). 3193 Migration level: B-5 3194 3195Here are the explanation of the migration level code: 3196 3197 Migration levels: 3198 A - Migration that might be run automatically 3199 B - Migration with code rewrite and a human-days cost up to 10 days 3200 C - Migration with code rewrite and a human-days cost above 10 days 3201 Technical levels: 3202 1 = trivial: no stored functions and no triggers 3203 2 = easy: no stored functions but with triggers, no manual rewriting 3204 3 = simple: stored functions and/or triggers, no manual rewriting 3205 4 = manual: no stored functions but with triggers or views with code 3206 rewriting 3207 5 = difficult: stored functions and/or triggers with code rewriting 3208 3209This is to help you to find the database that can be migrated first with small 3210efforts (A and B) and those who need to conduct a full migration project (C). 3211 3212This release has also some new useful features: 3213 3214 * Export type SHOW_TABLE now shows additional information about table type 3215 (FOREIGN, EXTERNAL or PARTITIONED with the number of partition). 3216 * Connection's user and password can be passed through environment variables 3217 ORA2PG_USER and ORA2PG_PASSWD to avoid setting them at ora2pg command line. 3218 * Improve PL/SQL replacement on ADD_MONTH(), ADD_YEAR(), TRUNC(), INSTR() and 3219 remove the replacement limitation on DECODE(). 3220 * Add detection of migration difficulties in views, was previously reserved 3221 to functions, procedures, packages and triggers. 3222 * Replace values in auto generated configuration file from command line 3223 options -s, -n, -u and -p when --init_project is used. 3224 * Adjust lot of scores following new functionalities in Ora2Pg, ex: dblink or 3225 synomyms are now easy to migrate. 3226 3227There is some new command line options to ora2pg script: 3228 3229 * -m | --mysql : to be used with --init_project and -i option to inform 3230 ora2pg that we work with a MySQL format 3231 * -T | --temp_dir : option to be able to set a distinct temporary directory 3232 to run ora2pg in parallel. 3233 * --audit_user : option to set the user used in audit filter and enable 3234 migration assessment report on queries from AUDIT_TRAIL (oracle) or 3235 general_log table (mysql). 3236 * --dump_as_sheet and --print_header options to be able to compute a CSV 3237 file with all migration assessment from a list of oracle database. 3238 * --dump_as_csv option to report assessments into a csv file. It will not 3239 include comments or details, just objects names, numbers and cost. 3240 3241Backward compatibility: 3242 3243 - Change NULL_EQUAL_EMPTY to be disabled by default to force change in the 3244 application instead of transforming the PL/SQL. 3245 3246This release adds some new configuration directives: 3247 3248 * MYSQL_PIPES_AS_CONCAT: Enable it if double pipe and double ampersand 3249 (|| and &&) should not be taken as equivalent to OR and AND. 3250 * MYSQL_INTERNAL_EXTRACT_FORMAT: Enable it if you want EXTRACT() replacement 3251 to use the internal format returned as an integer. 3252 * AUDIT_USER: Set the comma separated list of user name that must be used 3253 to filter from the DBA_AUDIT_TRAIL or general_log tables. 3254 * REPLACE_ZERO_DATE: "zero" date: 0000-00-00 00:00:00 it is replaced by a 3255 NULL by default, use it to use the date of your choice. 3256 * INDEXES_RENAMING: force renaming of all indexes using tablename_columnsname 3257 Very useful for database that have multiple time the same index name or 3258 that use the same name than a table. 3259 * HUMAN_DAYS_LIMIT: default to 5 days, used to set the number of human-days 3260 limit for migration of type C. 3261 3262Here is the full list of other changes: 3263 3264 - Remove list of acknowledgment that was not maintained anymore and some 3265 person may feel injured. Acknowledgment for patches or bug reports are 3266 always written to changelog, so this part reports to it now. 3267 - Fix bad trigger export when objects was enclosed in double quote and fix 3268 an additional bug in WHEN clause export. Thanks to Cyrille for the report. 3269 - Update documentation. 3270 - Update Makefile.PL with new script to install and new configuration 3271 directives in auto generated configuration file. 3272 - Update with new and missing files. 3273 - Add a Perl Module dedicated to MySQL database object discovery and export, 3274 lib/Ora2Pg/MySQL.pm. 3275 - Fix function based index type replacement in previous commit. 3276 - Do not report indexes with just DESC as function based index like Oracle 3277 report it. Thanks to Marc Cousin for the report. 3278 - Some excluded table was missing in the previous patch. 3279 - Remove use of DBI InactiveDestroy call when a fork is done and replace it 3280 to a single use AutoInactiveDestroy at connection. This require DBI>=1.614. 3281 - Add SDO_* tables and OLS_DIR_BUSINESSES in table exclusion list to fix issue 3282 #124 when no schema is provided. Thanks to Kenny Joseph for the report. 3283 - Fix partition prefix. 3284 - Remove UNIQUE keyword from spatial index. 3285 - Fix alter triggers function with missing parenthesis. Thanks to Spike Hodge 3286 MWEB for the report. 3287 - Fix export of foreign keys when they was defined in lowercase. Thanks to 3288 Spike for the report. 3289 - Fix wrong offset when rewriting ROWNUM with LIMIT+OFFSET. Thanks to Marc 3290 Cousin for the report. 3291 - Allow -INFINITY to be used to replace zero date. 3292 - Migration assessment in hour-day are now set to 1 man-day, we do not need 3293 such a precision and it is easier to process csv report. Thanks to Stephane 3294 Tachoire for the report. 3295 - Fix some issue with FDW and WKT spatial export. Add migration assessment 3296 of queries from the AUDIT_TRAIL table. 3297 - Adjust assessment units of some objects and add QUERY migration weight. 3298 - Rewrite information about migration levels. 3299 - Fix speedometer in progress bar, it will now shows the current speed in 3300 tuples/sec and the speed and time related to a table when export ended for 3301 the object. Thanks to Alex Ignatov for the report. 3302 - Fix break line when export data using INSERT mode. Thanks to Vu Bui for 3303 the report. 3304 - Do not display line about non existent objects in migration assessment 3305 reports. 3306 - Fix date default value for date when value is 0000-00-00 00:00:00 3307 - Suppress display of title for function and trigger details when there is 3308 no details. 3309 - Remove INSTR() from the list of Oracle function that are not supported. 3310 It is now replaced by position(). 3311 - Fix condition to call _get_largest_tables(). 3312 - Fix some minor issues in OUT/INOUT type returned by a function. 3313 - Fix default value that may appears unquoted. 3314 - Fix several issues on partition export: column with function, index on 3315 default partition table and plsql to plpgsql translation in check condition. 3316 - Fix some minor issues. 3317 - Replace values from command line options -s, -n, -u and -p in --init_project 3318 auto generated configuration file. Thanks to Stephane Tachoire for the 3319 feature request. 3320 - Fix wrong object count in SHOW_REPORT. Thanks to Stephane Tachoire for 3321 the report. 3322 - Use DBA_SEGMENTS to find database size when USER_GRANT is disable, aka user 3323 is a DBA 3324 - Remove report of Migration Level when --estimate_cost is not enabled. 3325 - Add missing BINARY_INTEGER for type replacement. 3326 - Always exclude function squirrel_get_error_offset() that is created by the 3327 Squirrel Oracle plug-in. 3328 - Adjust assessment scores following new functionalities in Ora2Pg, ex: 3329 autonomous transaction, dblink or synomyms are now easy to migrate. 3330 - Remove man page from source, it is auto generated by Makefile.PL and make. 3331 - Fix unterminated DECODE replacement when there was more than 5 parameters 3332 to DECODE() and remove the limitation to 10 parameters. There is no more 3333 limit in the number of decode parameters. Thanks to Mael Rimbault for the 3334 report. 3335 - Remove inclusion of unwanted object when exporting a limited list of view 3336 with ALLOW. 3337 - Disable unsupported recursive query used to reorder views when Oracle 3338 version is 11gR1. Thanks to Mael Rimbault for the patch. 3339 - Add PLPGSQL replacement of INSTR() by POSITION(). Thanks to Mael Rimbault 3340 for the report. 3341 - Add difficulty level information in migration assessment, this include a 3342 new configuration directive HUMAN_DAYS_LIMIT (default to 5 days) to set 3343 the number of human-days limit for migration of type C. 3344 - Add MERGE with a migration cost of 3, still need work be replaced by 3345 ON CONFLICT. 3346 - Remove some redundant regular expressions. 3347 - Fix escaped commas not working properly in MODIFY_TYPE. A MODIFY_TYPE 3348 value like `TABLE1:COL4:decimal(9\,6)` was leading to a column like 3349 `col4 decimal(8#nosep#3)` in the SQL dump file that was generated. This 3350 fixes the output to be `col4 decimal(8,3)`. Thanks to Nick Muerdter for 3351 the patch. 3352 - Strip default "empty_clob()" values from table dumps. This function does 3353 not exist in Postgres and is not necessary. Thanks to Nick Muerdter for 3354 the patch. 3355 - Fix undesired double quoting of column name in function based indexes. 3356 - Fix issue with Perl < 5.8 "Modification of a read-only value attempted" 3357 - Fix retrieving of table size on Oracle 8i. 3358 - Add auto double quoting of object name with unauthorized characters. 3359 Thanks to Magnus Hagander for the feature request. 3360 - Automatically double quote object name beginning with a number 3361 - Fix missing DESC part in descending indexes. Thanks to Magnus Hagander 3362 for the report. 3363 - Fix case where a column name in oracle is just a number (e.g. the column 3364 is called "7"), it will be created in postgres without quoted identifier, 3365 which fails. Thanks to Magnus Hagander for the report. 3366 - Fix "reqs/sec" display in debug mode. Thanks to Laurent Martelli for 3367 the patch 3368 - Fix export if Oracle procedure is created without a parameter. Thanks to 3369 dirkgently007 for the report. 3370 - Fix CSV report output. 3371 - Fix triggers from file parser. 3372 - Add a test on triggers return to handle case where it is triggered on 3373 DELETE + other(s) event(s). In this case a test is done on the TG_OP to 3374 return OLD if event is DELETE or NEW in other case. Thanks to Dominique 3375 Legendre for the suggestion. 3376 - Change NULL_EQUAL_EMPTY to be disabled by default to force change of the 3377 application instead of transforming the PL/SQL. 3378 - Change score of SYNONYM and DBLINK in the migration assessment. 3379 - Add conversion of Oracle type STRING into varchar(n) or text. 3380 - Add information about libaio1 requirement for instant client 3381 - Remove extra space when calling ora2pg_get_efile() used to export BFILE 3382 into EFILE. Thanks to Dominique Legendre for the export. 3383 3384 33852015 06 01 - v15.3 3386 3387This is a maintenance release only that fixes several minor bugs and typos. 3388The configuration file have been entirely rewritten to classify configuration 3389directives in section for better understanding. 3390 3391Here is the full list of changes: 3392 3393 - Ora2Pg will use DEFAULT_SRID when call to sdo_cs.map_oracle_srid_to_epsg() 3394 returns an ORA-01741 error. Mostly because there's no SRID defined for that 3395 column in ALL_SDO_GEOM_METADATA. The error message will still be displayed 3396 but a warning will explain the reason and ora2pg will continue with default 3397 value. Thanks to kazam for the report. 3398 - Add current setting for NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT to the 3399 SHOW_ENCODING report. 3400 - Change default value for GEOMETRY_EXTRACT_TYPE to INTERNAL instead of WKT. 3401 - Change generic configuration file behavior with BINMODE parameter commented 3402 if it was previously uncommented. This will force to use the default value. 3403 - Fix potential issue with max open file limit with unclosed temporary file. 3404 Thanks to Marc Clement for the report. 3405 - Fix use of SECURITY DEFINER in SYNONYM export. 3406 - Fix parsing of editable function/procedure/package from input DML file. 3407 - Fix case where variable $2 and $3 was null after a too early call of a new 3408 substitution in read_view_from_file(). Thanks to Alex Ignatov for the patch. 3409 - Add support to "create or replace editionable|noneditionable" from input DML 3410 files. Thanks to Alex Ignatov for the report. 3411 - Fix unknown column HIGH_VALUE from *_TAB_PARTITIONS in Oracle 8i. Thanks to 3412 Sebastian Fischer for the patch. 3413 - Fix call to ALL_MVIEW_LOGS object which not exists with Oracle 8i. Thanks to 3414 Sebastian Fischer for the report. 3415 - Fix Error ORA-22905 while Get the dimension of the geometry by looking at 3416 number of element in the SDO_DIM_ARRAY. Thanks to jkutterer for the patch. 3417 - Remove reordering export of view for Oracle database before 11g. Thanks to 3418 kyiannis for the report. 3419 - Fix several some typos and a bunch of misspelled. Thanks to Euler Taveira 3420 for all the patches. 3421 - Fix missing Oracle database version before looking at function security 3422 definer. Thanks to kyiannis for the report. 3423 34242015 04 13 - v15.2 3425 3426This new minor release fixes some issues and adds two new configuration 3427directives: 3428 3429 * ORA_INITIAL_COMMAND to be able to execute a custom command just after 3430 the connection to Oracle, for example to unlock a security policy. 3431 * INTERNAL_DATE_MAX to change the behavior of Ora2Pg with internal date 3432 found in user defined types. 3433 3434This version will also automatically re-order exported views taking into 3435account interdependencies. 3436 3437Here is the full list of changes: 3438 3439 - Add INTERNAL_DATE_MAX configuration directive with default to 49 to be 3440 used when reformatting internal date returned with a user defined type 3441 and a timestamp column. DBD::Oracle only return the internal date format 3442 01-JAN-77 12.00.00.000000 AM so it is difficult to know if the year value 3443 must be added to 2000 or 1900. We takes the default behavior where date 3444 are between 1950 and 2049. 3445 - Remove extra CHAR and BYTE information from column type. Thanks to Magnus 3446 Hagander for the report. 3447 - Re-order views taking into account interdependencies. Thanks to Kuppusamy 3448 Ravindran and Ulrike for the suggestion and the Oracle query. 3449 - Fix case sensitivity in function based indexes. Thanks to Kuppusamy 3450 Ravindran for the report. 3451 - Fix PERFORM wrong replacement and infinite loop processing DECODE in some 3452 condition. Thanks to Didier Brugat for the report. 3453 - Fix replacement of boolean value in DEFAULT value at table creation. 3454 Thanks to baul87 for the report. 3455 - Add ORA_INITIAL_COMMAND configuration directive to be able to execute a 3456 custom command just after the connection to Oracle, to unlock a policy for 3457 example. Thanks to Didier BRUGAT for the feature request. 3458 - Fix alias in from clause when an XML type is found. Thanks to Lance Jacob 3459 for the record. 3460 - Invert condition on excluding temporary file with Windows OS. Thanks to 3461 kazam for the report. 3462 - Remove start time and global number of rows from _dump_table() parameters 3463 they are not used anymore. 3464 - Remove use of temporary file on Windows operating system. 3465 - Disable parallel table export when operating system is Windows. 3466 - Fix export of objects with case sensitivity using ALLOW or EXCLUDE 3467 directives. Thanks to Alexey Ignatov for the report. 3468 - Fix export of triggers from recycle bin. 3469 - Fix count of synonym in assessment report. 3470 - Add list of tables created by OEM to the exclusion list. 3471 - Fix look at default configuration file and set mode of export_schema.sh 3472 to executable by default. Thanks to Kuppusamy Ravindran for the report. 3473 - Add AUTHORIZATION to the list of PostgreSQL reserved word. Thanks to 3474 Kuppusamy Ravindran for the report. 3475 - Display a warning when an index has the same name as the table itself so 3476 that you can renamed it before export. Thanks to Kuppusamy Ravindran for 3477 the feature request. 3478 - Fix export of function based indexes with multiple column. Thanks to 3479 Kuppusamy Ravindran for the report. 3480 - Modify ora2pg script to return 0 on success, 1 on any fatal error and 2 3481 when a child process die is detected. 3482 - Change the way the generic configuration file is handle during project 3483 initialization. You can use -c option to copy your own into the project 3484 directory. If the file has the .dist extension, ora2pg will apply the 3485 generic configuration on it. Thanks to Kuppusamy Ravindran for the report 3486 and features request. 3487 - Add debug information when cloning the Oracle connection. 3488 - Force return of OLD when the trigger is on DELETE event 3489 34902015 02 06 - v15.1 3491 3492New minor release just to fix two annoying bugs in previous release. 3493 3494 - Fix replacement of function name which include SELECT in their name by 3495 PERFORM. Thanks to Frederic Bamiere for the report. 3496 - Fix creation of sources subdirectories when initializing a new migration project. 3497 34982015 02 04 - v15.0 3499 3500This major release improve PL/SQL code replacement, fixes several bugs and 3501adds some new useful features: 3502 3503 - Add support to the PostgreSQL external_file extension to mimic BFILE 3504 type from Oracle. See https://github.com/darold/external_file for 3505 more information. 3506 - Allow export of Oracle's DIRECTORY as external_file extension objects 3507 This will also try to export read/write privilege on those directories. 3508 - Allow export of Oracle's DATABASE LINK as Oracle foreign data wrapper 3509 server using oracle_fdw. 3510 - Allow function with PRAGMA AUTONOMOUS_TRANSACTION to be exported through 3511 a dblink wrapper to achieve the autonomous transaction. 3512 - Allow export of Oracle's SYNONYMS as views. Views can use foreign table 3513 to create "synonym" on object of a remote database. 3514 - Add trimming of data when DATA_TYPE is used to convert CHAR(n) Oracle 3515 column into varchar(n) or text. Default is to trim both side any space 3516 character. This behavior can be controlled using two new configuration 3517 directives TRIM_TYPE and TRIM_CHAR. 3518 - Add auto detection of geometry constraint type and dimensions through 3519 spatial index parameters. This avoid the overhead of sequential scan 3520 of the geometric column. 3521 - Add support to export Oracle sub partition and create sub partition 3522 for PostgreSQL with the corresponding trigger. 3523 - ALLOW and EXCLUDE directives are now able to apply filter on the object 3524 type. Backward compatibility can not be fully preserved, older definition 3525 will apply to current export type only, this could change your export in 3526 some conditions. See documentation update for more explanation. 3527 - Add PACKAGE_AS_SCHEMA directive to change default behavior that use a 3528 schema to emulate Oracle package function call. When disable, all calls 3529 to package_name.function_name() will be turn into package_name_function_name() 3530 just like a function call in current schema. 3531 - Add FKEY_OPTIONS to force foreign keys options. List of supported options 3532 are: ON DELETE|UPDATE CASCADE|RESTRICT|NO ACTION. 3533 - Add rewriting of internal functions in package body, those functions will 3534 be prefixed by the package name. Thanks to Dominique Legendre for the 3535 feature request. 3536 3537Some change can break backward compatibility and make configuration directives 3538obsolete: 3539 3540 - The ALLOW_PARTITION configuration directive has been removed. With new 3541 extended filters in ALLOW/EXCLUDE directive, this one is obsolete. 3542 Backward compatibility is preserved but may be removed in the future. 3543 - ALLOW and EXCLUDE directives do not works as previously. Backward 3544 compatibility may be preserved with some export type but may be broken 3545 in most of them. See documentation. 3546 - It is recommended now to leave the NLS_LANG and CLIENT_ENCODING commented 3547 to let Ora2Pg handle automatically the encoding. Those directives may be 3548 removed in the future. 3549 3550Here is the full changelog of the release: 3551 3552 - Declares SYNONYM views as SECURITY DEFINER to be able to grant access to 3553 objects in other schema. 3554 - Fix wrong replacement of data type in function body. Thanks to Dominique 3555 Legendre for the report. 3556 - Fix missing column name replacement on trigger export when REPLACE_COLS 3557 is defined. Thanks to Dominique Legendre for the report. 3558 - Fix missing table replacement on trigger export when REPLACE_TABLES is 3559 defined. Thanks to Dominique Legendre for the report. 3560 - Fix case where IS NULL substitution was not working. Thanks to Dominique 3561 Legendre for the report. 3562 - Remove double exclusion clause when multiple export type is used with same 3563 column name and no values defined. 3564 - Allow parsing of DATABASE LINK and SYNONYM from a DDL file. 3565 - Add DIRECTORY export type to export all Oracle directories as entries for 3566 the external_file extension. This will also export read/write privilege 3567 on those directories. Thanks to Dominique Legendre for the feature request. 3568 - Review documentation about NULL_EQUAL_EMPTY. 3569 - Fix missing code to replace IS NULL as coalesce(...). Thanks to Dominique 3570 Legendre for the report. 3571 - Add external_file schema to search_path when BFILE is set to EFILE in 3572 directive DATA_TYPE. Thanks to Dominique Legendre for the request. 3573 - Remove IF EXIST clause to oracle function created by Ora2Pg for BFILE 3574 export. Thanks to Dominique Legendre for the report. 3575 - Add support to the PostgreSQL external_file extension to mimic BFILE type 3576 from Oracle. See https://github.com/darold/external_file for more information. 3577 - Add auto detection of geometry constraint type and dimensions through the 3578 spatial index parameters first. This avoid the overhead of sequential scan 3579 of the geometric column. 3580 - Remove lookup at package function when not required. 3581 - Fix issue with database < 10g that do not have the DROPPED column into the 3582 ALL_TABLES view. Thanks to Lance Jacob for the report. 3583 - Add trimming of data when DATA_TYPE is used to convert CHAR(n) Oracle 3584 column into varchar(n) or text column into PostgreSQL. Default is to 3585 trim both side any whitespace character. This behavior can be controlled 3586 using the new configuration directives TRIM_TYPE and TRIM_CHAR. 3587 - Update copyright year. 3588 - Add assessment cost for object TABLE SUBPARTITION and review cost for 3589 object DATABASE LINK. 3590 - Update documentation about SYNONYM export. 3591 - Allow export of SYNONYMS as views with a new export type: SYNONYM. 3592 - Fix object exclusion function with Oracle 8i and 9i. Thanks to Lance Jacob 3593 for the report. 3594 - Fix INTERVAL YEAR TO MONTH and DAY TO SECOND with precision. 3595 - Remove unused pragma from the cost assessment. 3596 - Suppress PRAGMA RESTRICT_REFERENCES, PRAGMA SERIALLY_REUSABLE and INLINE 3597 from the PLSQL code. There is no equivalent and no use in plpgsql. 3598 - Fix several issues in function/procedure/package extraction from file 3599 input and some other related bug. 3600 - Remove single slash and \\r from function code. 3601 - Remove schema from package name with input file to avoid creating 3602 function with SCHEMA.PKGNAME.FCTNAME 3603 - Fix ALLOW/EXCLUDE ignored with type COPY or INSERT. Thanks to thleblond 3604 for the patch. 3605 - Fix setting of NLS_NUMERIC_CHARACTERS and NLS_TIMESTAMP_FORMAT with 3606 multiprocess, the session parameters was lost with the cloning of the 3607 database handle. Thanks to thleblond for the patch. 3608 - Fix issue that could produce errors "invalid byte sequence" when dumping 3609 data to pg database by forcing the client_encoding when PG_DSN is set. 3610 Thanks to thleblond for the patch. 3611 - Fix issue to add parenthesis with function with no parameters and wrong 3612 use of PERFORM in cursor declaration. Thanks to hdeadman for the report. 3613 - Fix broken export of function or procedure without parameter in package 3614 body. Thanks to hdeadman for the report. 3615 - Fix ERROR: "stack depth limit exceeded" generated by an infinite loop in 3616 partition trigger when there is no default table when value is out of range. 3617 - Add support to Oracle sub partition export. 3618 - Fix issue with procedure in package without parameters. 3619 - Enable DISABLE_SEQUENCE in generic configuration file. 3620 - Fix unwanted alter sequence in data export when there is table allowed 3621 or excluded. 3622 - Fix initial default values of command line parameter that prevent value 3623 in configuration file to be taken. 3624 - Fix non working global definition of table in ALLOW and EXCLUDE directive 3625 with COPY and INSERT export. 3626 - Update ora2pg.spec, thanks to bbuechler for the patch. 3627 - Close temporary files before deleting them, on Windows if they are not 3628 explicitly closed there are not deleted. Thanks to spritchard for the 3629 patch. 3630 - Force schema name to be uppercase when PRESERVE_CASE is disable (default). 3631 Thanks to Jim Longwill for the report. 3632 - Add rewriting of internal functions in package body, those functions will 3633 be prefixed by the package name. Thanks to Dominique Legendre for the 3634 feature request. 3635 - Fix type replacement in user defined type. Thanks to Dominique Legendre 3636 for the report. 3637 - Add filter with INSTEAD OF triggers on views to TRIGGER export type. Thanks 3638 to Dominique Legendre for the feature request. 3639 - Fix replacement of function name when PACKAGE_AS_SCHEMA is disabled. 3640 - Fix PLSQL_PGSQL that was always set to 0 when -p was not used even if 3641 configuration directive PLSQL_PGSQL was activated. Thanks to Dominique 3642 Legendre for the report. 3643 - Remove ALTER SCHEMA ... OWNER TO ... when CREATE_SCHEMA is not enable. 3644 Thanks to Dominique Legendre for the report. 3645 - Add DBLINK export to be created as foreign data wrapper server. Thanks to 3646 the BRGM for the feature request. 3647 - Remove ALLOW_PARTITION configuration directive, with extended filter in 3648 ALLOW/EXCLUDE directive, this one is obsolete. Backward compatibility is 3649 preserved. 3650 - Add documentation about extended filters in ALLOW and EXCLUDE directive. 3651 - Update documentation about VIEW_AS_TABLE and remove statement change with 3652 export TYPE is VIEW. 3653 - Add filter to grant export on functions, sequences, views, etc. 3654 - Fix GRANT in ALLOW or EXCLUDE filters. 3655 - Add commented order: "REVOKE ALL ON FUNCTION ... FROM PUBLIC;" when the 3656 function is declared as SECURITY DEFINER. 3657 - Prevent collecting column information with SHOW_TABLE export type. 3658 - Fix default value SYSTIMESTAMP to CURRENT_TIMESTAMP, and remove DEFAULT 3659 empty_blob(). Thanks to hdeadman for the report. 3660 - ALLOW and EXCLUDE directives are now able to apply filter on the object 3661 type. Backward compatibility can not be fully preserved, older definition 3662 will apply to current export type only, this could change your export in 3663 some conditions. See documentation update for more explanation. Thanks to 3664 the BRGM for the feature request. 3665 - Force function to be created with SECURITY DEFINER when AUTHID in table 3666 ALL_PROCEDURES is set to DEFINER in Oracle. This only works with Oracle 3667 >= 10g. Thanks to Dominique Legendre for the feature request. 3668 - Add PACKAGE_AS_SCHEMA configuration directive to change default behavior 3669 to use a schema to emulate Oracle package function call. When disable all 3670 call to package_name.function_name() will be turn into package_name_function_name() 3671 just like a function call in current schema. Thanks to the BRGM for the 3672 feature request. 3673 - Add a note to documentation about the way to convert srid into Oracle 3674 database instead of in Ora2Pg. Thanks to Dominique Legendre for the hint. 3675 - Fix documentation about SHOW_ENCODING export type. 3676 - Remove use of REGEX_LIKE with Oracle version 9. Thanks to Lance Jacob for 3677 the report. 3678 - Replace new FKEY_OPTIONS by FKEY_ADD_UPDATE configuration directive with 3679 three possible values: always, never and delete. It will force or not 3680 Ora2Pg to add "ON UPDATE CASCADE" on foreign keys declaration. 3681 - Allow FORCE_OWNER to work with all exported objects. Thanks to BRGM for 3682 the feature request. 3683 - Add FKEY_OPTIONS to force foreign keys options. List of supported options 3684 are: ON DELETE|UPDATE CASCADE|RESTRICT|NO ACTION. Thanks to the BRGM for 3685 the feature request. 3686 - Fix ambiguous column in view extraction. Thanks to Dominique Legendre for 3687 the report. 3688 - Fix replacement of TYPE:LEN by boolean, ex: REPLACE_AS_BOOLEAN CHAR:1. 3689 Thanks to jwiechmann for the report. 3690 - Fix error ORA-00942 where Ora2Pg try to export data from a view defined 3691 in VIEW_AS_TABLE configuration directive. 3692 - Update list of excluded Oracle schema to the documentation. 3693 - Fix export of all views with comments when VIEW_AS_TABLE is set. 3694 - Fixed some typos in the generated sample configuration file. Thanks to 3695 Hal Deadman for the patch. 3696 - Limit column information export to the type of object extracted. 3697 - Remove call to MDSYS in SQL code. Thanks to Dominique Legendre for the 3698 report. 3699 - Add more Oracle schema to the exclusion list. 3700 - Fully remove join on DBA_SEGMENTS to retrieve the list of tables, views 3701 and comments. Replaced by ALL_OBJECTS. Thanks to Dominique Legendre for 3702 the help. 3703 - Exclude JAVA\$.* tables and fix tables list query to include newly created 3704 tables with no segments. Thanks to Dominique Legendre for the fix. 3705 - Fix regex that convert all x = NULL clauses to x IS NULL to not replace 3706 := NULL too. 3707 - Autodetect unusual characters in owner name when extracting data and used 3708 it embeded into double quote. 3709 - Replace single return with return new in trigger code. Thanks to Dominique 3710 Legendre for the report. 3711 37122014 11 12 - v14.1 3713 3714This is a maintenance release only mainly to add patches that was not 3715been applied in previous major release. 3716 3717 - Remove ALLOW_CODE_BREAK, it is no more useful. 3718 - Change output of SHOW_ENCODING to reflect change to default encoding. 3719 - Comment ALLOW_PARTITION in default configuration file 3720 - Add QUERY and KETTLE export type in configuration file comments. 3721 37222014 11 05 - v14.0 3723 3724This major release adds full export of Oracle Locator or Spatial geometries into 3725PostGis, SDO_GEOM functions and SDO_OPERATOR are also translated. This export 3726adds the following features: 3727 3728 1. Basic and complex geometry types support 3729 2. Geometry data conversion from Oracle to PostGIS 3730 3. Spatial Index conversion 3731 4. Geometry metadata / constraints support 3732 5. Spatial functions conversion 3733 3734For spatial data export, you have three choice, WKT to export data using 3735SDO_UTIL.TO_WKTGEOMETRY(), WKB to export data using SDO_UTIL.TO_WKBGEOMETRY() 3736and INTERNAL to export geometry using a Pure Perl library. Unlike the first 3737two methods, INTERNAL is fast and do not raise Out Of Memory. The export is 3738done in WKT format so that you can verify your geometry before importing to 3739PostgreSQL. 3740 3741Other additional major features are: 3742 3743 - Parallel table processing. 3744 - Auto generation of migration template with a complete project tree. 3745 - Allow user defined queries to extract data from Oracle. 3746 3747Parallel table processing is controlled by the -P or --parallel command line 3748options or the PARALLEL_TABLE configuration directive to set the number of 3749tables that will be processed in parallel for data extraction. The limit is 3750the number of cores on your machine. Ora2Pg will the open one connection to 3751Oracle database for each parallel table extraction. This directive, when upper 3752than 1, will invalidate ORACLE_COPIES but not JOBS, so the real number of 3753process that will be used is (PARALLEL_TABLES * JOBS). 3754 3755The two options --project_base and --init_project when used indicate to Ora2Pg 3756to create a project template with a work tree, a generic configuration file 3757and a shell script to export all objects from the Oracle database. So that you 3758just have to define the Oracle database connection into the configuration file 3759and then execute the shell script called export_schema.sh to export your 3760Oracle database into files. Here a sample of the command and the project's tree. 3761 3762 ora2pg --project_base /tmp --init_project test_project 3763 3764 /tmp/test_project/ 3765 config/ 3766 ora2pg.conf 3767 data/ 3768 export_schema.sh 3769 reports/ 3770 schema/ 3771 fdws/ functions/ grants/ kettles/ 3772 mviews/ packages/ partitions/ 3773 procedures/ sequences/ tables/ 3774 tablespaces/ triggers/ types/ views/ 3775 sources/ 3776 functions/ mviews/ packages/ 3777 partitions/ procedures/ triggers/ 3778 types/ views/ 3779 3780It create a generic config file where you just have to define the Oracle 3781database connection and a shell script called export_schema.sh. The 3782sources/ directory will contains the Oracle code, the schema/ will 3783contains the code ported to PostgreSQL. The reports/ directory will 3784contains the html reports with the migration cost assessment. 3785 3786Sometime you may want to extract data from an Oracle table but you need a 3787custom query for that. Not just a "SELECT * FROM table" like Ora2Pg do but 3788a more complex query. The new directive REPLACE_QUERY allow you to overwrite 3789the query used by Ora2Pg to extract data. The format is TABLENAME[SQL_QUERY]. 3790If you have multiple table to extract by replacing the Ora2Pg query, you can 3791define multiple REPLACE_QUERY lines. For example: 3792 3793 REPLACE_QUERY EMPLOYEES[SELECT e.id,e.fisrtname,lastname FROM EMPLOYEES e 3794 JOIN EMP_UPDT u ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')] 3795 3796Other new features are: 3797 3798 - Export of declaration of language C function. Previous version was 3799 not exporting function with no code body like external C function. 3800 - Export of COMMENT from views. 3801 - Function to replace some call to SYS_CONTECT(USERENV, ...) by the 3802 PostgreSQL equivalent. 3803 - Add POSTGIS_SCHEMA configuration directive to add the dedicated 3804 PostGis schema into the search_path. 3805 - Add PG_SUPPORTS_IFEXISTS configuration directive to be able to suppress 3806 IF EXISTS call in DDL statement generated by Ora2Pg. 3807 - Triggers are now all excluded/allowed following the table names specified 3808 in the ALLOW and EXCLUDED directives 3809 - Allow automatic export of nested tables (TYPE+TABLE+COPY). 3810 3811One change is not fully backward compatible: Ora2Pg now use UTF8 by default 3812on both side. On Oracle connection NLS_LANG is set to AMERICAN_AMERICA.AL32UTF8, 3813NLS_NCHAR to AL32UTF8. On PostgreSQL side CLIENT_ENCODING to UTF8. For export 3814that dump to files, Perl binmode is set to utf8. You can always change those 3815default setting in configuration file, but it is not recommanded. 3816 3817Here is the full changelog of the release: 3818 3819 - Fix inline comments into function declaration. Thanks to Marcel Huber 3820 for the report. 3821 - Fix case where SELECT ... INTO was wrongly replaced by PERFORM. 3822 - Fix DECODE() translation. Thanks to Dominique Legendre for the report. 3823 - Add replacement of SDO_OPERATOR into PostGis relationships. 3824 - Add replacement of SDO_GEOM spatial function to postgis ST_* functions. 3825 - Add GEOMETRY_EXTRACT_TYPE configuration directive to specify the geometry 3826 extracting mode: WKT (default), WKB and INTERNAL. 3827 - Add a pure Perl library to export SDO_GEOMETRY as a WKT representation. 3828 This is controlled by a new extraction type INTERNAL to use with the 3829 GEOMETRY_EXTRACT_TYPE configuration directive. 3830 - Remove USE_SC40_PACKAGE directive and any reference to this library, 3831 it is not useful now that we have the INTERNAL geometry extraction mode. 3832 - Fix replacement of varchar2 in PL/SQL function. 3833 - Fix bug in type replacement when default values used function. 3834 - Add export of declaration of language C function. Previous version was 3835 not exporting function with no code body like external function. 3836 - Fix create statement in export of view as table. Thanks to ntlis for the 3837 report. 3838 - Fix replacement of to_number without format. 3839 - Add export of COMMENT from VIEWS. 3840 - Add function to replace some call to SYS_CONTECT(USERENV, ...) by the 3841 PostgreSQL equivalent. 3842 - Fix parsing from file of tablespace. 3843 - Fix wrong alias name in FROM clause when extracting XML data. Thanks 3844 to Marc Sitges for the report. 3845 - Fix export of comments in FDW export, might be COMMENT ON FOREIGN TABLE. 3846 Thanks to David Fetter for the report. 3847 - Fix broken export of function based indexes. Thanks to Floyd Brown for 3848 the report. 3849 - Fix sequence with negative minvalue/maxvalue and negative increment. 3850 Thanks to jwiechmann for the report. 3851 - Fix forced owner to schema to the value of FORCE_OWNER when it is set 3852 to a user name. 3853 - Fix create schema when FORCE_OWNER is enabled. Thanks to Dominique 3854 Legendre for the report. 3855 - Add POSTGIS_SCHEMA configuration directive to add a schema to the 3856 search_path. Thanks to Dominique Legendre for the feature request. 3857 - Returns NULL when a geometry is NULL instead of calling ST_AsText with 3858 a null value. Thanks to Dominique Legendre for the report. 3859 - Add more explanation about values of CONVERT_SID. 3860 - Fix issue in DBMS_OUTPUT replacement. 3861 - Fix exclusion of default objects from type export. 3862 - When CONVERT_SRID is > 1 this value will be used to force the SRID value 3863 on all export. 3864 - Disable NULL_EQUAL_EMPTY in generic configuration when generating a project 3865 tree. 3866 - Add LOGMNR$ and RECAP$ in the exclusion objects list. 3867 - Fix performance issue in extracting data from geometry column and add 3868 AUDSYS,DVSYS and DVF to the list of schema to exclude. 3869 - Prefix table name with schema name on queries for retrieving data to 3870 avoid errors in multi schema export. 3871 - Add SDO_* cost to migration report. 3872 - Fix real number of Synonym that should be review. 3873 - Fix wrong report of CTXSYS synonym. 3874 - Enabled AUTODETECT_SPATIAL_TYPE by default. 3875 - Remove KETTLE and FDW export from the auto generated project. 3876 - Force the copy of /etc/ora2pg/ora2pg.conf.dist into the project directory 3877 with no more look at the current ora2pg.conf. Force autodetection of 3878 spatial type in the generic configuration. 3879 - Huge performance gain on querying information about Spatial column. Thanks 3880 to Dominique Legendre for the great help. 3881 - Fix wrong use of table alias with SEGMENT_NAME. 3882 - Add unified audit table (CLI_SWP$.*) from the exclusion list. 3883 - Fix operator in check condition of range partitions. Thanks to Kaissa 3884 Chellouche for the report. 3885 - Add to the internal exclusion list tables generated by spatial indexes 3886 MDRT_.*, sequences MDRS_.* and interMedia Text index DR$.*. Thanks to 3887 Dominique Legendre for the report. 3888 - Make REPLACE_TABLES and REPLACE_COLS work with VIEW. The view name and 3889 the columns aliases will be replaced. Take care that the table name or 3890 columns names in the statement will be kept untouched and need manual 3891 rewriting. Thanks to Sven Medin for the feature request. 3892 - Add PG_SUPPORTS_IFEXISTS configuration directive to be able to suppress 3893 IF EXISTS call in DDL statement generated by Ora2Pg. PostgreSQL below 3894 9.x do not support this keywords. Thanks to George Kowalski fot the 3895 feature request. 3896 - Fix wrong substitution in EXECUTE ... USING statement, where parameters 3897 number was not prefixed by a $ sign. Thanks to Dominique Legendre for 3898 the report. 3899 - Fix document about KEEP_PKEY_NAMES that also affect unique key and not 3900 only primary key as it was specified in the documentation. Thanks to 3901 Dominique Legendre for the report. 3902 - Add tables generated by statistics on spatial index (MDXT_.*) into the 3903 internal exclusion list. This join the already excluded table generated 3904 by partition logging (USLOG$_.*) and materialized view logs (MLOG$_.*, 3905 RUPD$_.*) 3906 - Add DEFAULT_SRID configuration direction to permit change of the internal 3907 default EPSG srid 4326. 3908 - Fix new line after search_path settings. Thanks to Dominique Legendre for 3909 the report. 3910 - Triggers are now all excluded/allowed following the table names specified 3911 in the ALLOW and EXCLUDED directive, no more on there own name which had 3912 little interest. Thanks to Dominique Legendre for the feature request. 3913 - Add support to COPY export with Spatial objects. Thanks to Legendre 3914 Dominique for the great help to solve this problem. 3915 - Fix default SRID value when a NULL value is returned by Oracle, SRID 8307 3916 and the corresponding EPSG SRID 4326. 3917 - Update documentation on relation between PARALLEL_TABLES and FILE_PER_TABLE 3918 - Add the -P or --parallel command line options and update documentation 3919 about parallel table processing. 3920 - Add PARALLEL_TABLES configuration directive to force ora2Pg to use on 3921 process and one connection per table up to the number of CPU specified. 3922 Thanks to menardorama for the feature request. 3923 - Add PARALLEL_TABLES configuration directive to force ora2Pg to use on 3924 process and one connection per table up to the number of CPU specified. 3925 Thanks to menardorama for the feature request. 3926 - Add --init_project and --project_base command line options to create a 3927 migration template with a complete project tree, a generic configuration 3928 file and script to automate export of all object in the project tree. 3929 - Fix unwanted space before AND returned by limit_to_tables(). Thanks to 3930 Alex Wang for the report. 3931 - Add note about regex inclusion/exclusion not working with 8i database in 3932 documentation 3933 - Fix regex inclusion/exlusion of table that was not more working since the 3934 inclusion of limit_to_tables() function. Thanks to alex wang for the patch 3935 - Exclude dropped tables (those who are in the recycle bin) from export. 3936 - When USER_GRANTS is disabled, aka login as dba user, force table list to 3937 be checked against DBA_SEGMENTS with SEGMENT_TYPE of type table or table 3938 partition. This could help solving some incomprehensible object found in 3939 Oracle view ALL_TABLES. 3940 - Fix query to retrieved list of tables, owner selection was set two time. 3941 - Add support to automatic nested table export (TYPE+TABLE+COPY). 3942 - Fix wrong export of materialized view log table. Thanks to Ronson Blossom 3943 for the report. 3944 - Update the SYSUSER array to exclude objects owned par those more users. 3945 - Fix unwanted export of overflow table of an index-organized table. Thanks 3946 to Ronson Blossom for the report. 3947 - Update the SYSUSER array to exclude objects owned par those users. 3948 - Display table owner in debug mode for SHOW_TABLE or SHOW_COLUMN. 3949 - Add a section to give hint about converting Oracle outer join syntax to 3950 ANSI. Thanks to Sven Medin for the links. 3951 - Fix issue #82 again. Thanks to Sven Medin fro the report. 3952 - Add first support to user defined queries to extract data from Oracle. 3953 This feature add a new configuration directive named REPLACE_QUERY. 3954 - Change program title when dump to file. 3955 - Fix MODIFY_TYPE directive that was broken when using type with space 3956 character. Thanks to Dmitry K. for the patch. 3957 - Show missing view name in debug mode when exporting some views as table. 3958 - Rewrite replace(a,b) with three arguments replace(a,b,'') for PostgreSQL. 3959 Thanks to Dominique Legendre for the report. 3960 - Convert all x <> NULL or x != NULL clauses to x IS NOT NULL. All x = NULL 3961 are converted into x IS NULL. Thanks to Dominique Legendre for the report. 3962 - Add warning at exit to signal when a OOM occurs. In that case, when a child 3963 Ora2Pg process was silently killed by the OOM killer there was no information 3964 that a failure occurs. 3965 39662014 06 02 - v13.0 3967 3968This major release adds first support to export Oracle Spatial Objects to PostGis 3969Spatial objects. There's also a new configuration directive to allow logging of 3970statement failures to prevent Ora2Pg to abort and continue to load valid data. 3971The other main feature is the possibility to convert DDL files without needing an 3972Oracle database connection, until now this was reserved to files containing stored 3973procedures. There's also several bug fixes. 3974 3975 - Allow error logging during data import. This feature controlled by the 3976 LOG_ON_ERROR directive allow you to not abort the data import process 3977 when an error is encountered and to log to a file the COPY or INSERT 3978 statement that generate the error. After fixing the statement you will 3979 be able to load the missing data. Thanks to menardoram for the feature 3980 request. 3981 - Force export type to be INSERT when COPY is used and a table have a 3982 GEOMETRY column. I can not find a solution to export as copy statement 3983 for the moment. Thanks to Dominique Legendre and Vincent Picavet for 3984 the help. 3985 - Fix export of user defined type as object. Thanks to Shanshan Wang for 3986 the report. 3987 - Limit look up of objects to the ALLOW or EXCLUDE filter into the SQL 3988 query instead of the Perl code to avoid retrieving huge list of objects 3989 on such database. Thanks to menardorama for the feature request. 3990 - Add support to spatial data export in INSERT mode. Still need some work 3991 in COPY export mode if possible. 3992 - Fix query to retrieve SRID that broken with patch on CONVERT_SRID. 3993 - Fix wrong filter with ALLOW directive when getting list of partition. 3994 - Add GRANT export read from an input file. 3995 - Fix data type conversion when using input file and data type such 3996 varchar2(10 BYTE). 3997 - Add export of comment with TABLE and VIEW exports using an input file. 3998 - Add extraction of TABLESPACE from an input file. 3999 - Add support to SEQUENCE extraction from input file. 4000 - Fix wrong filter with ALLOW directive when exporting partition. The 4001 filter was done on partition name instead of table name, that mean 4002 that setting ALLOW directive was resulting in no export at all. Thanks 4003 to menardorama for the report. 4004 - Add CONVERT_SRID configuration directive to control the automatic 4005 conversion of SRID to standard EPSG using the Oracle SDO function 4006 sdo_cs.map_oracle_srid_to_epsg() Oracle function. Thanks to Dominique 4007 Legendre for the help. 4008 - Fix a typo in the create index prefix on partitioned tables. Thanks 4009 to menardorama for the patch. 4010 - Fix non replacement of destination during SHOW_COLUMN and COPY export. 4011 Using MODIFY_TYPE was only working in TABLE export. 4012 - Force pl/sql conversion with TABLE export to replace advanced default 4013 values. Fix code TRUNC(SYSDATE, MONTH) in default value and everywhere 4014 that should be: date_trunc(month,LOCALTIMESTAMP). Thanks to menardorama 4015 for the report. 4016 - Fix code regarding unique partition index naming. Thanks to menardorama 4017 for the report. 4018 - Add PREFIX_PARTITION configuration directive. When enabled it will force 4019 renaming all partition table name with the name of the parent table. 4020 Thanks to menardoram for the feature request. 4021 - Add AUTODETECT_SPATIAL_TYPE in configuration file and documentation 4022 about this new directive. 4023 - Add export of SDO_GEOMETRY column type. They are basically exported to 4024 the non-constrained "geometry" type with SRID if defined. When the 4025 configuration directive AUTODETECT_SPATIAL_TYPE is enable, Ora2Pg will 4026 try to autodetect the geometry type, the dimension and the SRID used 4027 to set a constrained geometry type. For example, in the first case 4028 column shape with Oracle type SDO_GEOMETRY will be converted as: 4029 4030 shape geometry(GEOMETRY) or shape geometry(GEOMETRY, 4326) 4031 4032 and in the second case, with constrained geometry type: 4033 4034 shape geometry(POLIGONZ, 4326) 4035 4036 with a three dimensional polygon. Thanks to Vincent Picavet for the 4037 feature request and specification. 4038 - Add support to spatial index read from file. 4039 - Add export of Oracle spatial index. For example, index: 4040 CREATE INDEX cola_spatial_idx ON cola_markets(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX; 4041 will be exported as 4042 CREATE INDEX cola_spatial_idx ON cola_markets USING GIST(shape); 4043 Thanks to Vincent Picavet / Oslandia for the feature request and explanations. 4044 - Allow TRIGGER export to parse an input file with Oracle DML orders. 4045 - Add PG_SUPPORTS_CHECKOPTION configuration directive to not remove 4046 WITH CHECK OPTION in create view statement. It is supported in 4047 PostgreSQL 9.4. 4048 - Allow VIEW export to parse an input file with Oracle DML orders. 4049 - Allow TABLE export to parse an input file with Oracle DML orders. 4050 - Add SYNCHRONOUS_COMMIT configuration directive disabled by default. 4051 This is the current behavior of Ora2Pg, it set synchronous_commit 4052 to off before data import to PostgreSQL. This is only used when you 4053 load data directly to PostgreSQL, the default is off to disable 4054 synchronous commit to gain speed at writing data. Some modified or 4055 old version of PostgreSQL, like Greenplum, do not have this setting. 4056 - Add some useful information for Windows user in documentation. Thanks 4057 to Roger Park for the report. 4058 - Fix case when parentheses are omitted in index creation. Thanks to 4059 Yuri Ushakov for the report. 4060 - Fix export type PACKAGE when ALLOW is defined to extract only some 4061 packages. Thanks to Maciej Bak for the report. 4062 - Fix INSERT export where backslash should be escaped and single be 4063 doubled in standard conforming string notation. Thanks to Yuri 4064 Ushakov for the report. 4065 - Add important note about LONGREADLEN and DATA_LIMIT that could need 4066 to be adjusted to avoid out of memory. Thanks to Mike Kienenberger 4067 for the patch. 4068 - Fix case sensitivity issue with export of comment on column. Thanks 4069 to Pierre Crumeyrolle for the report. 4070 - Fix export of RAW data in COPY mode, was missing a backslash. Thanks 4071 to jwiechmann for the report. 4072 - Fix RAW data export in COPY and INSERT mode, RAW data type is returned 4073 in hex by DBD::Oracle. Thanks to jwiechmann for the report. 4074 - Fix one release 8i condition. 4075 - Fix inexistent column USE_NO_INDEX with Oracle 8i and MVIEW export. 4076 - Enclose call to utf8::encode and utf8::valid into eval. 4077 - Fix export of constraint with Oracle 8i release. 4078 - Fix unrecognized fatal error with 8i database. Thanks to UnvorherSeba 4079 for the patch. 4080 - Revert change level of error from fatal to error, when querying 4081 materialized view. 4082 - Change level of error from fatal to error, when querying materialized 4083 view. 4084 40852014 01 28 - v12.1 4086 4087This is a maintenance release with some minor bug fixes and a new configuration 4088directive, INDEXES_SUFFIX, to allow appending a suffix to indexes names. 4089 4090 - Fix example given for the WHERE configuration directive. Thanks to 4091 Bob Treumann for the report. 4092 - Add INDEXES_SUFFIX configuration option to allow append a suffix to 4093 indexes names. 4094 - Replace special charater ^M by \r as they are not supported by git. 4095 - Fix IF EXISTS in alter table of sub _drop_foreign_keys. Thanks to 4096 Francis Corriveau for the patch. 4097 - Fix isolation level when exporting data. Thanks to Ludovic Penet for 4098 the report. 4099 - Fix regression when ora2pg tries to create foreign keys on tables or 4100 to tables that are not selected for export. Thanks to Ludovic Penet. 4101 - Add information about backslashed comma into directive MODIFY_TYPE 4102 into Makefile.PL. 4103 - Add missing MODIFY_TYPE definition in documentation. 4104 - Allow backslashed comma into MODIFY_TYPE type redefinition. Example: 4105 TABLE1:COL3:decimal(9\,6),TABLE1:COL4:decimal(9\,6). 4106 Thanks to Mike Kienenberger for the report 4107 - Fix missing single cote into create_materialized_view() call. Thanks 4108 to Jacky Rigoreau for the patch. 4109 - Fix some typo in documentation, thanks to Mike Kienenberger for the 4110 report. 4111 - Add a chapter about installing DBD::Oracle into documentation. Thanks 4112 to Raghavendra for the patch. 4113 - Fix case sensitivity on external table name with FDW export type. 4114 Thanks to Guillaume Lelarge for the report. 4115 - Fix export of materialized views when PG_SUPPORTS_MVIEW is disabled. 4116 Thanks to Christian Bjornbak for the report. 4117 - Update copyright. 4118 41192013 10 22 - v12.0 4120 4121This release fixes lot of issues and three new features. Using REORDERING_COLUMNS 4122directive you will be able to reorder columns to minimized the footprint on disc, 4123so that more rows fit on a data page. The PG_SUPPORTS_MVIEW will allow you to 4124export materialized with native PostgreSQL 9.3 syntaxe. The USE_TABLESPACE variable 4125will allow you to export object using their original tablespace. 4126 4127 - Skip constraints on system internal columns (sys_nc...$) from export. 4128 - Fix missing output directory in generic psql file for data loading. 4129 - Add missing progress bar during TYPE and PARTITION export type. 4130 - Remove duplicated message in debug mode during Oracle reconnection. 4131 - Allow file input with create type declaration to use ora2pg converter. 4132 Unsupported syntax is signaled into the output file. 4133 - Exclude MLOG$.* and RUPD$.* table from export. 4134 - Prevent export of indexes and constraints during FDW export type. 4135 - Fix wrong total number of sequences shown in progress bar. 4136 - Remove warning when PG_DSN is define during a export type that do not 4137 support direct import into PostgreSQL. 4138 - Auto switch prefix from DBA to ALL when error 942 is returned when 4139 looking at tables informations. A hint is also displayed to ask for 4140 activating USER_GRANTS or connect using a user with DBA privilege. 4141 - Add REORDERING_COLUMNS configuration directive to allow reordering 4142 columns during the TABLE export. This could help to minimized the 4143 footprint on disc, so that more rows fit on a data page. Thanks to 4144 Christian Bjornbak for the feature request. 4145 - Fix call to unblessed reference at disconnect when direct import to 4146 pg is not used. Thanks to Christian Bjornbak for the report. 4147 - Fix regression in drop/create foreign keys and index during data 4148 export. Thanks to Christian Bjornbak for the report. 4149 - Fix truncate table error with parallel and direct data copy. Thanks 4150 to keymaper for the report. 4151 - Fix several other issues with parallel and direct data import. 4152 - Fix trigger export on multi files when FILE_PER_FUNCTION is enabled. 4153 - Fix issue on converting boolean values with non default values. 4154 Thanks to Christian Bjornbak for the report. 4155 - Fix boolean value for disabled key in default %BOOLEAN_MAP key/value. 4156 - Fix case where INTO was wrongly replaced by INTO STRICT. Thanks to 4157 Jacky Rigoreau for the report. 4158 - Fix case where label after a END was not removed. Thanks to Jacky 4159 Rigoreau for the report. 4160 - Fix discard of input file parsing. Fix PERFORM replacement in PL/SQL 4161 code wirh cursor. Thanks to Jacky Rigoreau for the report. 4162 - Enable PG_SUPPORTS_MVIEW by default and update documentation. 4163 - Replace DBA_DATA_FILES by USER_SEGMENTS to get database size to avoid 4164 error ORA-00942. Thanks to Pierre Boizot for the report. 4165 - Fix trigger conversion error. Thanks to Pierre Boizot for the report. 4166 - Add support to PostgreSQL 9.3 materialized view syntaxe, this need a 4167 new configuration directive PG_SUPPORTS_MVIEW to be enabled. 4168 - Update default configuration file and documentation about USE_TABLESPACE. 4169 - Add USE_TABLESPACE configuration directive to force ora2pg to use Oracle 4170 tablespace name with table, constraints indexes and indexes if tablespace 4171 in not in the default (TEMP, USERS, SYSTEM). Thanks to Rob Moolhuijsen 4172 for the feature request. 4173 - Allow DEFER_FKEY, when enabled during TABLE export, to create all foreign 4174 keys as DEFERRABLE and INITIALLY DEFERRED. Thanks to David Greco for the patch. 4175 - Fix non working ON_ERROR_STOP set to 0 during data export. 4176 - Lot of code changes to fix dump to file in multiprocess mode. Ora2Pg will 4177 also only drop/create constraints and indexes related to the allow/exclude 4178 tables, thanks to Maciej Bak for the report. 4179 - Force decimal character from Oracle output to be a dot. Thanks to Maciej Bak 4180 for the report. 4181 - Add default exclusion of Oracle recycle bin objects with name begining by BIN$. 4182 - Fix escaping quote in table and column comments. Thanks to realyota for the report. 4183 - Reduce DECODE migration cost from 2 to 1 unit. 4184 - Reduce OUTER JOIN (+) migration cost from 3 to 1 unit. 4185 - Add Time::HiRes to the requirement chapter for Perl <= 5.8. Thanks to 4186 Mike Kienenberger for the report. 4187 - Replace wrong use of --config instead of --conf into the documentation. Thanks 4188 to Mike Kienenberger for the report. 4189 - Fix regex used to rewrite CREATE VIEW code. Thanks to David Greco for 4190 the patch. 4191 - Fix an issue with oracle copies when primary key was negative. Thanks 4192 to David Greco for the patch. 4193 - Fix case sensitivity with SEQUENCE when preserve_case is enabled. 4194 Thanks to Jean-Max Reymond for the report. 4195 - Fix table COMMENT export when preserve_case is enabled. Thanks to 4196 Jean-Max Reymond for the report. 4197 41982013 05 28 - v11.4 4199 4200This release fixes others several major issues on migration cost assessment that 4201was not addressed in previous release, please upgrade. 4202 4203 - Fix other major issues in migration cost assessment. 4204 - Redefine some migration cost values to be more precise. 4205 42062013 05 27 - v11.3 4207 4208This release fixes several major issues on migration cost assessment, especialy 4209with stored procedures with lot of lines or if you have lot of comments in that 4210code. You may want to run your database evaluation again as the estimated times 4211can be up to tree time lower on huge PL/SQL code. 4212 4213 - Add full details about PL/SQL evaluation by ora2pg when --estimate_cost 4214 or ESTIMATE_COST is enable. This will display cost units per keywords 4215 detected in the function/package code. 4216 - Fix wrong cost unit assessment on PL/SQL code size, this bug generated 4217 very high migration cost assessment for functions/packages with lot of 4218 lines. Please run your tests again, estimated times can be up to tree 4219 time lower on huge code. 4220 - Remove comments before code evalution. 4221 - Fix file input parser for PL/SQL packages export when IS or AS was in 4222 the next line than the CREATE PACKAGE BODY ... 4223 - Exclude NOT NULL constraint from the count of CHECK constraints into 4224 the TABLE report. 4225 - Fix decimal precision in table migration assessment cost. 4226 - Fix typo in changelog. 4227 42282013 05 01 - v11.2 4229 4230This release fixes several major issues especially with direct import of data 4231into PostgreSQL and Windows port that was both broken. 4232 4233 - Update doc about Windows multiprocess issues and acknowledgements. 4234 - Fix Windows OS issues using multiprocessing options by disabling 4235 multiprocess support on this plateform. When -J or -j will be used a 4236 warning will be displayed and Ora2Pg will simply run single process 4237 like in previous 10.x versions. Thanks to Jean Marc Yao Adingra for 4238 the report. 4239 - Fix RAW and LONG RAW export to ByteA. Thanks to Prabhat Tripathi for 4240 the report and testing. 4241 - Fix patch regression on multiple TRUNCATE call for a single table. 4242 Thanks to David Greco for the report. 4243 - Placed calls to DB handle InactiveDestroy outside the forked process 4244 to prevent fatal errors on Windows. Thanks to Jean Marc Adingra for 4245 the report. 4246 - Forked running processes are renamed into more readable name like 4247 "ora2pg logger" for the progress bar, "ora2pg - querying Oracle" when 4248 used with -J option and "ora2pg - sending to PostgreSQL" to better 4249 know what is the current job of the process. 4250 - Removed the use of /Y flag in Windows install script, this was causing 4251 error "dmake: Error code 130, while making install_all". Thanks to 4252 Jean-Marc Adingra for the report. 4253 - Fix direct import to PostgreSQL that was just producing nothing. Thank 4254 to David Greco for the patch. 4255 - Fix ora2pg usage documentation. 4256 - Add an underscore to CLIENT ENCODING in SHOW_ENCODING output to be the 4257 same as the configuration directive. 4258 4259UPGRADE: please reinstall all as most of the files have changed. 4260 42612013 04 07 - v11.1 4262 4263This release adds partition data speed improvement by exporting data directly 4264from and into the destination partitioned table. There's also some bug fix on 4265RAW or LONG RAW data export and PL/SQL to PL/PGSQL code rewrite. 4266 4267 - Adjust cost assessment for indexes, tables and tables partition. 4268 - Add comment to report of index partition about local index only. 4269 - Fix position of TRUNCATE TABLE in output file. 4270 - Fix export of data from RAW or LONG RAW columns, they was exported 4271 as hex string. Now data are converted using utl_raw.cast_to_varchar2() 4272 function before being escaped for insert into a bytea. Thanks to Alex 4273 Delianis for the report. 4274 - Fix issue with Oracle TIMESTAMP(0) data export that add a single 4275 ending point, ex: "2008-08-09 00:00:00.", this ending character is 4276 now removed by format_data_type(). Thanks to Pierre-Marie Petit for 4277 the report. 4278 - Fix typo on MODIFY_STRUCT description. 4279 - Force DEBUG to off in default configuration file. 4280 - Change range PARTITION operators in the check conditions, >= and < 4281 replaced by > and <=, corresponding to Oracle VALUES LESS THAN. 4282 - Add ALLOW_PARTITION to limit data export to a list of partition name. 4283 - PLSQL: Fix wrong replacement of SELECT by PERFORM during VIEW export. 4284 - Partitioned tables data is now imported directly into the destination 4285 tables instead of inserted into the main table and dispatched by the 4286 trigger. Ora2Pg will automatically detect the in/out table partition, 4287 there's nothing to configure. 4288 - PL/SQL: Do not allow decode() rewrite by case/when/else when there 4289 is a function call in it. 4290 - Fix Error when Compress::Zlib is not installed, this module is not 4291 mandatory. 4292 4293UPGRADE: please reinstall all as all files have changed. 4294 42952013 03 24 - v11.0 4296 4297This is a new major release because it adds support to multiprocessing to export 4298data in parallel mode, this allow to improve speed during data import by more 4299than ten times. This multiprocessiing capabilities allow Ora2Pg to be closer than 4300the speed of any ETL. To compare speed or allow using Kettle for data import, 4301there's now a new export type to obtain Kettle XML transformation files. This 4302release adds also lot of work on speed improvement to scan Oracle database with 4303huge number of object. 4304 4305 - Add documentation about JOBS, ORACLE_COPIES, DEFINED_PK configuration 4306 directive and informations about KETTLE export type. 4307 - Add KETTLE export type to generate XML transformation file definition 4308 for Penthatlo Data Integrator (Kettle). Thanks to Marc Cousin for the 4309 work. Example of use: 4310 ora2pg -c ora2pg.conf -t KETTLE -j 12 -J 4 -o loaddata.sh 4311 - Fix major bug in export of auto generated named constraint. Thanks to 4312 mrojasaquino fot the report. 4313 - Show number of rows in the top largest tables. 4314 - Add TOP_MAX description to the documentation. 4315 - Add the TOP_MAX directive to default configuration file and update 4316 documentation. Directive used to control the top N tables to show. 4317 - Add top N of largest tables in SHOW_TABLE, SHOW_COLUMN and SHOW_REPORT 4318 export type. 4319 - Fix progressbar output when ora2pg is interrupted by ctrl+c. 4320 - Add JOBS, ORACLE_COPIES and DEFINED_PK directives to configuration file. 4321 JOBS replacing THREAD_COUNT but backward compatibility is preserve. 4322 - Add 3 new command line options, -j | --jobs and -J | --copies, used to 4323 set the number of connection to PostgreSQL and Oracle for parallel 4324 processing. The third, -L | --limit is used to change DATA_LIMIT at 4325 command line. 4326 - Add multiprocess support on data export. With the help of Thomas Ogrisegg. 4327 - Add more schema in SYSUSERS that should not be exported. 4328 - Add full detailed information about SYNONYM in SHOW_REPORT. 4329 - Add MODIFY_TYPE configuration directive to allow some table/column 4330 type to be changed on PostgreSQL side during the export. 4331 - Fix objects type count in progressbar of SHOW_REPORT. 4332 - Restrict table and index in SHOW_REPORT to the tables defined in ALLOW 4333 and EXCLUDE directives. 4334 - Show total number of rows in SHOW_TABLE and SHOW_REPORT output. 4335 - Add top 10 of tables sorted by number of rows in SHOW_TABLE and 4336 SHOW_REPORT output. 4337 - Fix typo in SYNONYM objects. 4338 - Add report of top ten tables ordered y number of rows. 4339 - Rewrite most of the Oracle schema storage information extraction for 4340 speed improvement. 4341 - Use Hash to store column informations. 4342 - Fix %unique_keys declaration in _table() method. 4343 - Remove call to _table_info() from SHOW_REPORT code as those informations 4344 are already loaded with the _table() method. 4345 - Fix missing column definition on TABLE export. 4346 - Add progress bar during output generation following export type. 4347 - Add STOP_ON_ERROR configuration directive to enable/disable the call to 4348 ON_ERROR_STOP into generated SQL scripts. Thanks to Ludovic Penet for 4349 the feature request. 4350 - Huge speed improvement on columns informations retrieving. 4351 - Fix progress bar to keep the total number of tables related to the ALLOW 4352 or EXCLUDE configuration directives. Thanks to Ludovic Penet for the report. 4353 - Change return type of function _table_info(), it now returns data instead 4354 of the database handle. 4355 - Improve speed on indexes and constraints extraction for database with huge 4356 number of tables. 4357 - Improve performance to retrieve columns information and comments. 4358 - Remove report of column details during export in debug mode, use SHOW_COLUMN 4359 instead. 4360 - Remove call to upper() in objects owner condition to improve performance 4361 with database with huge number of objects. 4362 - Add a fix to not export foreign key for exclude tables. Thanks to Ludovic 4363 Penet for the report. 4364 - Fix Windows install issue with copying ora2pg.conf.dist. Thanks to 4365 Dominique Fourdrinoy for the report. 4366 - Increase the cost of Oracle function not converted to PG automatically. 4367 4368UPGRADE: reinstall all is required to override the old installation, you may use the 4369new ora2pg.conf.dist file which included the new configuration directives. 4370 43712013 01 15 - v10.1 4372 4373This release adds HTML report for migration cost assessment and some bug fix. 4374 4375 - Fix global where should not be overwritten. Thanks to Dan Harbin for 4376 the patch. 4377 - Fix bug/typo in boolean replacement, where a colon was used instead 4378 of a single quote. Thanks to Alex Delianis for the patch. 4379 - Update copyright. 4380 - Add detection of additional Oracle functions for better migration 4381 cost assessment. 4382 - Update documentation. 4383 - Force report detail in lowercase. 4384 - Added information about the migration cost value to the reports. 4385 - Add --dump_as_html command line option and DUMP_AS_HTML configuration 4386 directive. 4387 - Allow migration report to be generated as HTML. 4388 - Separate report generation code from data collection code. 4389 43902012 12 12 - v10.0 4391 4392This is the first version of Ora2Pg 10.x series, that is a major release. 4393Overall numerous improvements and bugs fixes there's now a new export type: 4394SHOW_REPORT that will output a report of all objects contained in your Oracle 4395database and some comments on how they will be exported. With this report you 4396can use a new directive ESTIMATE_COST to ask to Ora2Pg to evaluate the database 4397migration cost in terms of man days. There's also an other new configuration 4398directive EXTERNAL_TO_FDW, disable by default, to permit the export of all 4399Oracle external tables as file_fdw foreign tables. 4400 4401The database content report and the migration cost estimation is a work in 4402progress so all feedback on these new features are welcome. Here is the complete 4403changelog: 4404 4405 - Update documentation about ora2pg usage and new feature. 4406 - Fix quote escaping on table comments. Thanks to Sebastian Fischer. 4407 - Fix some other issues with 8i databases, added database version auto- 4408 detection to avoid printinf warning. Thanks to Sebastian Fischer for 4409 the help. 4410 - Allow null value in BFILE to the oar2pg_get_bfilename(). 4411 - Update documentation about BFILE export. 4412 - Add drop function ora2pg_get_bfilename() when necessary. 4413 - Add support to BFILE external path export by creating a function 4414 ora2pg_get_bfilename( p_bfile IN BFILE ) to retrieve path from BFILE. 4415 BFILE will be exported as text field with the full path to the file as 4416 value. Note that this is the first time that Ora2Pg need write access 4417 to the Oracle database, if you do not have BFILE or you have set the 4418 corresponding PostgreSQL type asd bytea (the default) the function 4419 will not be created. 4420 - Fix a performance issue when extracting BLOB with a LongReadLen upper 4421 than 1MB. 4422 - Fix priviledge on schema created from Oracle package body. Thanks to 4423 Dominique Legendre for the report. 4424 - Add object type in comment before priviledge extraction. 4425 - Order output of grant to groups grants by object types. This is useful 4426 to quickly disable some SQL orders corresponding of not already loaded 4427 objects. Thanks to Dominique Legendre for the feature request. 4428 - Fix progress bar output. 4429 - Fix priviledge on sequence, tablespace and schema. 4430 - Fix backward compatibility with Oracle 8i, remove query with JOIN. 4431 Thanks to Sebastian Fischer for the report. 4432 - Fix backward compatibility with Oracle 8i on priviledge extraction. 4433 Thanks to Sebastian Fischer for the report. 4434 - Fix backward compatibility with Oracle 8i on index extraction. Thanks 4435 to Sebastian Fischer for the report. 4436 - Add more precision in cost estimation. 4437 - Add somme other PL/SQL uncovered code detection. 4438 - Add more debug information during data extraction. 4439 - Removed progress bar when debug is enabled. 4440 - Add report and estimate cost about CHECK constraint and function 4441 based indexes. 4442 - Update documentation about new export directives SHOW_REPORT and 4443 ESTIMATE_COST. 4444 - Add --estimate_cost and --cost_unit_value command line options. 4445 - Add ESTIMATE_COST and COST_UNIT_VALUE to default configuration file. 4446 - Rewritte and extend support to ROWNUM replacement. 4447 - Remove incompatible grants between Oracle and the PortgreSQL export, 4448 especially on views. 4449 - Limit GRANT export to VALID object. Activate EXPORT_INVALID to enable 4450 grants export on all object. 4451 - Add export of VALID only views. To export all with INVALID ones you 4452 must activate the EXPORT_INVALID directive. Thanks to Dominique 4453 Legendre for the feature request. 4454 - Fix issue in substr() pl/sql replacement, thanks to Dominique 4455 Legendre for the report, plus add other code replacements in pl/sql. 4456 - Fix issue with function name not on the same line as the create 4457 statement - was affecting file input only. 4458 - Add report of number of JOB object in the database (SHOW_REPORT). 4459 - Add PL/SQL replacement of various form of EXEC function call. 4460 - Remove creation of password with users that are not requiring 4461 password. Thanks to Dominique Legendre for the feature request. 4462 - A sql type and a precision can now be used in REPLACE_AS_BOOLEAN to 4463 replace all filed with that type as a boolean, example: 4464 NUMBER:1 will replace all field of type NUMBER(1) as a boolean. 4465 - Fix grants on partition export, will now used all_ and user_ tables. 4466 - Fix removing of newline in the DECLARE clause. Thanks to Dominique 4467 Legendre for the report. 4468 - PostgreSQL client_encoding is now forced to UTF8 when BINMODE is set 4469 to utf8. Thanks to Dominique Legendre for the report. 4470 - Replace DISABLE TRIGGER ALL by DISABLE TRIGGER USER following the value 4471 if USER_GRANTS to avoid permission denied on constraint trigger when 4472 data are load under a non PG superuser. Thanks to Dominique Legendre 4473 for the report. 4474 - Rename DISABLE_TABLE_TRIGGERS to DISABLE_TRIGGERS and set default value 4475 to 0. Other values are USER or ALL following the connected user. 4476 - Fix missing newline after comment in PL/SQL code. Thanks to Dominique 4477 Legendre for the report. 4478 - Fix report message on external table export. 4479 - The export TYPE have been entirely rewritten to only export supported 4480 user defined types. Exported are: Nested Tables, Object type, Type in 4481 herited and Subtype, Varrays. Associative Arrays, Type Body and type 4482 with member method are not supported. 4483 - When FILE_PER_INDEX is enable, SQL order to move indexes in their 4484 respective tablespace will be written into a dedicated file prefixed 4485 by TBSP_INDEXES_. 4486 - Fix location on external table export. Thanks to Thomas Reiss for 4487 the help. 4488 - PG_SUPPORTS_INSTEADOF is now activated by default, that mean that 4489 migration should be done on PG >= 9.1. 4490 - Remove obsolete --xtable commande line option, should be replaced by 4491 --allow, backward compatibility is preserved. 4492 - Add EXTERNAL_TO_FDW configuration directive, disable by default, to 4493 export all Oracle external tables as file_fdw foreign tables. 4494 - Fix an other case where user defined type were not exported with an 4495 ending semi-colon. Thank to Dominique Legrendre for the report. 4496 - Fix export of user defined type with extra ");" at end of the type 4497 definition and remove system types from export. Thanks to Dominique 4498 Legendre for the report. 4499 - Add PLSQL replacemement of currval. Thanks to Thomas Reiss for the 4500 patch. 4501 - Add PLSQL replacement of PIPELINED and PIPE ROW by SETOF and RETURN 4502 NEXT. 4503 - Add rewrite of Oracle DETERMINISTIC function into PostgreSQL 4504 IMMUTABLE function. 4505 - Fix copy during install on MacOSx and add /Y option to windows 4506 install copy to force overwrite existing files. Thanks to Dennis 4507 Spaag for the report. 4508 - Fix issue exporting rows with perl ARRAYS ref. Thanks to Sorin 4509 Gheorghiu for the report. 4510 - Add report of number of database link in SHOW_REPORT output. 4511 - Fix major bug on export of NUMBER with precision, they was all 4512 exported as bigint. Thanks to Dominique Legendre for the report. 4513 - Add progress bar during SHOW_REPORT export. 4514 - Add detailed report about index in SHOW_REPORT output. 4515 - Fix data export when schema was given in lower case. Thanks to 4516 Dominique Legendre for the report. 4517 - Add SHOW_REPORT export type to display a full summary of the Oracle 4518 database content. 4519 - PLPGSQL: add the name keyword to XMLELEMENT call. Thanks to Thomas 4520 Reiss for the hint. 4521 - Add SHOW_VERSION export type to display the version of Oracle. 4522 - Add COLLATION to the keyword list. Thanks to Dominique Legendre for 4523 the report 4524 - Change documentation to add more detail on exporting Oracle views as 4525 PostgreSQL tables based on the new VIEW_AS_TABLE directive. 4526 - Add -a | --allow option and --view_as_table to ora2pg script. 4527 - Add VIEW_AS_TABLE configuration option to allow export of view as 4528 table and permit the additional use of the ALLOW or/and EXCLUDE 4529 directive. Thanks to Dominique Legendre for the feature request. 4530 - Removed conflict with transaction when DEFER_FKEY was enabled and 4531 allow DEFER_FKEY and DROP_FKEY to be enabled both. Before, only 4532 DEFER_FKEY was used in this case, now both are used and of course 4533 DEFER_FKEY is wasted. Thanks to Dominique Legendre for the report. 4534 - Directives ALLOW and EXCLUDE are now usable with all kind of object 4535 following the export type. 4536 - Rename TABLES directive as ALLOW to be less confusing, backward 4537 compatibility is preserved. 4538 - Thanks to Dominique Legendre for the feature request. 4539 - Remove auto ordering of table export following the foreign keys to 4540 fix an infinite loop. Thanks to Siva Janamanchi for the report. 4541 - Rewrite the view as table export to reuse the same code as table 4542 export, old code was resulting in issues with disable triggers and 4543 deferring constraints. 4544 - Remove alter session to set NLS_NCHAR that was returning error on some 4545 installation. Thanks to Dominique Legendre for the report. 4546 - Fix replacement of IS SELECT wrongly replaced by IS PERFORM in some 4547 case. Thanks to Dominique Legendre fot the report. 4548 4549UPGRADE: Almost all files have changed so a new installation is required. 4550 45512012 10 07 - v9.3 4552 4553 - Add auto detection of Oracle character set and the corresponding 4554 PostgreSQL client encoding to use. NLS_LANG and CLIENT_ENCODING 4555 configuration directives can be leaved commented, Ora2Pg will set 4556 their values automatically. 4557 - Add PL/SQL replacement of CURSOR IS SELECT by CURSOR FOR SELECT and 4558 IS REF CURSOR by REFCURSOR. Thanks to Dominique Legendre for the 4559 report. 4560 - Fix missing set client_encoding orders into fonction or procedure 4561 export file. Thanks to Dominique Legendre for the report. 4562 - Fix not working SKIP configuration directive. Thanks to Siva 4563 Janamanchi for the report. 4564 - Add configuration directive NULL_EQUAL_EMPTY to disable the IS NULL 4565 and IS NOT NULL replacement into PL/SQL code. Enabled by default. 4566 Thanks to Dominique Legendre for the feature request. 4567 - Remove exclusion of object names with the dollar sign. Thanks to 4568 Konrad Beiske for the suggestion. 4569 - Fix timestamp with time zone when microsecond is enabled. Thanks 4570 to Siva Janamanchi for the report. 4571 - Fix extra semi-column when PKEY_IN_CREATE is enabled. Thanks to 4572 Siva Janamanchi for the report. 4573 - Update configuration about boolean replacement. 4574 - Allow any column type replacement as a boolean in PostgreSQL, values 4575 will be converted as well. Thanks to Konrad Beiske for the feature 4576 request. 4577 - Add REPLACE_AS_BOOLEAN and BOOLEAN_VALUES configuration directives 4578 to allow type replacement with a boolean. Thanks to Konrad Beiske 4579 for the feature request. 4580 - Add new configuration directive PKEY_IN_CREATE to add primary keys 4581 definition in CREATE TABLE statement instead of creating them after 4582 with an ALTER TABLE statement. For Greenplum database, primary key 4583 must be created with the CREATE TABLE statement so you may want to 4584 enable this configuration directive. Thanks to Siva Janamanchi for 4585 the feature request. 4586 - Add new configuration directive USE_RESERVED_WORDS to force Ora2Pg to 4587 auto-detect PostgreSQL reserved words in Oracle object's names and 4588 automatically double quote them. Thanks to Siva Janamanchi for the 4589 feature request. 4590 - SHOW_TABLE and SHOW_COLUMN will now display a warning when Oracle 4591 object's name is a PG reserved words. Those names will need to be 4592 renamed or double-quoted (see USE_RESERVED_WORDS). 4593 - Add TIMESTAMP WITH LOCAL TIME ZONE Oracle type conversion to timestamp 4594 and force timestamp with time zone format to use TZH:TZM. Thanks to 4595 Siva Janamanchi for the report. 4596 - Fix table and column replacement issues introduced with path that 4597 removed double-quote when PRESERVE_CASE is disabled. Thanks to Steve 4598 DeLong for the report. 4599 - PLPGSQL convertion: Fix SELECT replacement with PERFORM in VIEW 4600 declaration. Thanks to Thierry Capitaine for the report. 4601 - Add display Ora2Pg type conversion map between Oracle originals types 4602 and PostgreSQL's types when using export type SHOW_COLUMN. Thanks to 4603 Thierry Capitaine for the feature request. 4604 - Reorder command line options in ora2pg script usage and documentation. 4605 - Add call to quote_ident() and quote_literal() into materialized 4606 functions to secure parameters. 4607 - Fix major issue in pl/sql to pl/pgsql conversion with multiple package 4608 declaration in the same code record. Thanks to Marc Cousin for the 4609 report. 4610 - Add data type TIMESTAMP WITH TIME ZONE. Thanks to Siva Janamanchi for 4611 the report. 4612 - Add new export type: MVIEW to allow export of all materialized views 4613 as snapshot materialized view (fully reload of the view). 4614 - Add -e | --exclude option to Perl script ora2pg to exclude some given 4615 objects from the export. It will override any value of the EXCLUDE 4616 directive. The value is a coma separated list of object name or regex. 4617 - Update domumentation about the EXCLUDE directive change. 4618 - Allow exclusion from export of functions, procedures and functions in 4619 package by specifying a list of name or regex in EXCLUDE directive. 4620 Thanks to Keith Fiske from Omniti for the feature request. 4621 4622UPGRADE: Almost all files have changed so a new installation is required. 4623 46242012 09 05 - v9.2 4625 4626 - In plpgsql conversion, SELECT without INTO becomes PERFORM. 4627 - In plpgsql conversion, EXECUTE IMMEDIATE replaced by EXECUTE. 4628 - Fix DATA_TYPE value in configuration file. 4629 - Fix case sensitivity on data export using COPY mode. 4630 - Directive XML_PRETTY is now disabled by default as it is better to 4631 use getClobVal() to get the XML data out of an xmltype column. 4632 - Add documentation about regex usage int EXCLUDE and TABLES directives. 4633 - Remove all double-quote around object name when CASE_SENSITIVY is 4634 disabled. Thanks to Dominique Legendre for the suggestion. 4635 - Rename CASE_SENSITIVE by PRESERVE_CASE to be less confusing, backward 4636 compatibility preserved. Thanks to Dominique Legendre for the request. 4637 - Add support to user defined type data export. Before it will simply 4638 export an array reference ARRAY(0xa555fb8), now the array is explored 4639 and inserted as ROW(col1,col2,...). Thanks to Mathieu Wingel for the 4640 feature request. 4641 - Fix bug in direct data import in postgresql with COPY: pg_putcopydata 4642 can only be called directly after issuing a COPY FROM command. Thanks 4643 to Steve Delong for the report. 4644 - Add warning at any debug level before abort when a data file already 4645 exist during data export. 4646 - Fix issue with oracle sensitivity when exporting data. 4647 - Fix search_path on package export, indexed and constraints files on 4648 TABLE export. 4649 - Remove obsolete ORA_SENSITIVE configuration directive, thanks to 4650 Dominique Legendre it is no more used. 4651 - Force automatic conversion of PL/SQL when entry is an input file. 4652 - Fix errors in main file for package loader with FILE_PER_FUNCTION 4653 enabled. 4654 - Fix case where package body where not exported. 4655 - Add missing EXPORT_INVALID directive into default configuration file. 4656 - Fix replacement of END followed by the name of the function, the semi- 4657 colon was removed. 4658 - Fix case sensitivity issue in INDEX creation. 4659 - Fix case sensitivity issue in CHECK constraint and a typo in a 4660 progress bar variable. 4661 - Replace old DATA export type by INSERT in configuration file. 4662 - Fix case sensitivity issue in ALTER TABLE ... ADD CONSTRAINT. Thanks 4663 to David Greco for the report. 4664 - Add set client_encoding before table output to handle character 4665 encoding into comments and possibly objects names. 4666 - Fix some case sensitivity issue with schema name. Thanks to Dominique 4667 Legendre for the report. 4668 - Do not display warning message about direct import if no connection 4669 to a PostgreSQL database is defined. 4670 - Allow multiple export type to be specified into the ora2pg -t command 4671 line option. 4672 - Dump progress bar to stderr instead of stdout to separate logs. 4673 - Add new -q | --quiet option to perl script ora2pg to disable progress 4674 bar. 4675 46762012 08 19 - 9.1 4677 4678 - Add progress bar to show data export progression. 4679 - Add -q | --quiet option to ora2pg perl script to disable progress bar. 4680 - Change documention about tnsnames.ora to mark it is not necessary. 4681 - Add progress bar during data export, per table and globaly. 4682 - Replace export type DATA by INSERT to mark the difference with COPY 4683 and avoid confusion. Documentation is updated and full backward 4684 compatibility preserved. 4685 - Improve Oracle case sensitivity detection on column and update 4686 documentation about ORA_SENSITIVE directive 4687 - Direct import for COPY statement now used DBD::Pg and pg_putcopydata() 4688 instead of a pipe to psql command. 4689 - Fix case sentitivity issue on disabling/enabling all triggers. 4690 - Add autodetection of case sensitivity with column name. 4691 - Move trunc() to data_truc() convertion into the ALLOW_CODE_BREAK part. 4692 - Update comment about FILE_PER_FUNCTION in configuration file. 4693 - Fix NOT NULL constraint add twice, the first time in the column 4694 definition and the second time in an ALTER TABLE ... ADD CONSTRAINT 4695 ... CHECK ( ... NOT NULL). Reported by Dominique Legendre. 4696 - Add support to direct CALL of stored procedures in trigger definition. 4697 Reported by Dominique Legendre. 4698 - Remove index creation on primary and unique key autogenerated by 4699 PostgreSQL. 4700 - Fix PL/SQL to PLPGSQL automatic convertion on index when exporting 4701 data with DROP_INDEX activated. 4702 - Fix DROP_INDEX to only delete indexes that will be created at end. 4703 - Fix search path when exporting data with EXPORT_SCHEMA disabled 4704 - Add missing documentation about the LOGFILE directive 4705 - Fix case sensitivity on sequence export. They will now always be 4706 insensitive as in PostgreSQL its called is converted between quotes: 4707 nextval('seq_name'). Reported by Dominique Legendre. 4708 - Limit export of primary and unique key if KEEP_PKEY_NAMES is enabled 4709 to those who are not autogenerated by Oracle. Reported by Dominique 4710 Legendre. 4711 - Trigger export is now limited to those belonging to table that are not 4712 excluded from the export (see TABLES and EXCLUDE directives). Reported 4713 by Dominique Legendre 4714 - Fix case sensitivity on trigger export. 4715 - Fix data export failure in table with column name with accent. 4716 Reported by Dominique Legendre. 4717 - Fix set client_encoding syntax. Reported by Dominique Legendre 4718 - Add automatic try with oracle sensitivity when an error occurs during 4719 retreving table information. This additionaly also fixes an error when 4720 table has accent on his name. 4721 - Fix replacement of user defined data type with directive DATA_TYPE. 4722 Reported by Dominique Legendre. 4723 - Fix function or procedure detection with external input file. Reported 4724 by Arul Shaji. 4725 - Update documentation about Windows installation and ActiveState Perl 4726 distribution. Thanks to Stephan Hilb for the report. 4727 - Fix date format issue by forcing NLS_DATE_FORMAT to format: 4728 YYYY-MM-DD HH24:MI:SS. Thanks to Stephan Hilb for the report. 4729 - Remove obsolete pod documentation in Ora2Pg.pm. 4730 - Add new configuration directive CREATE_SCHEMA to disable the sql 4731 command of schema creation at top of the output file during TABLE 4732 export type. Patch by David Greco. 4733 - Added converting INSERTING/UPDATING/DELETING to PG_OP=INSERT, etc. 4734 Patch by David Greco. 4735 - Fix parsing leading ':' on triggers, as they generally have :NEW and 4736 :OLD to denote the new and old recordsets. Patch by David Greco 4737 - Add new PG_INTEGER_TYPE configuration directive activated by default, 4738 to limit conversion into postgresql integer or bigint of Oracle number 4739 without scale - NUMBER(p), PG_NUMERIC_TYPE is now reserved to convert 4740 NUMBER(p,s). Patch by David Greco. 4741 - Limit numeric with precision <= 9 to be converted as integer, numeric 4742 with precision >= 10 will be converted to bigint to handle integer 4743 above 2147483647. Patch by David Greco. 4744 - Add plsql to plpgsql automatic conversion on check constraints. Patch 4745 by David Greco. 4746 - Add plpgsql replacement, patch by David Greco: 4747 REGEX_LIKE( string, pattern ) => string ~ pattern 4748 - Update documentation about NOESCAPE and STANDARD_CONFORMING_STRING 4749 - Change place of the ENABLE_MICROSECOND into the documentation. 4750 - Fix forgot to add documentation about encryption with Oracle server. 4751 - Add missing DISABLE_COMMENT configuraton directive in default 4752 configuration file and update documentation 4753 47542012 07 15 - 9.0 4755 4756 - Remove call to obsolete BINDIR and MANDIR variables into packaging 4757 scripts to reflect the changes in Makefile.PL. 4758 - Update documentation about installation of Ora2Pg under Windows. 4759 - Automatically set LONGREADLEN to ORA_PIECE_SIZE length if the last one 4760 is larger, for CLOB export. 4761 - Change Makefile.PL and source tree to fully support installation under 4762 Windows OSes. 4763 - Change double quote by single in Makefile.PL perl replacement call. 4764 - Replace double quote by single one in $CONFIG_FILE default setting to 4765 simplify automatic replacement at install. 4766 - Fix CLOB export that was limited to 64Kb even with LONGREADLEN defined 4767 to an upper value. Patch use the ora_piece_size DBD::Oracle prepare 4768 attribute. Patch by Mohamed Gargouri. See here for more detail: 4769 http://search.cpan.org/~pythian/DBD-Oracle-1.46/lib/DBD/Oracle.pm#Piecewise_Fetch_with_Polling 4770 - Add a note into documentation about encrypted communication between 4771 Ora2Pg and Oracle. Note by Jenny Palomino. 4772 - Change documentation to reflect change to the format of the Oracle 4773 timestamp with millisecond. This format is now enabled by default in 4774 configuration file. 4775 - Fix bug with LONGREADLEN and LONGTRUNCOK when exporting LOB that was 4776 not applied even after change into the configuration file. Reported 4777 by Mohamed Gargouri 4778 - Fix microsecond format FF3 not compatible with Oracle 8i. Set to FF. 4779 - Add a warning to stderr when a table export need that ORA_SENSITIVE 4780 be enabled. 4781 - Fix case where Oracle indexes with same name as a constraint was not 4782 exported - Rodrigo 4783 4784 The following are old patches that was not applied to v8.10 and the git 4785 repository: 4786 4787 - Fix creation of bad constraint for each indexes. 4788 - Add DISABLE_COMMENT configuration directive to remove comments from 4789 TABLE export type. Comments are exported by default. 4790 - Fix a bug in removing function name repetion at end 4791 - Add PL/SQL to PLGPSQL replacement of the to_number function 4792 - Fix PL/SQL to PLGPSQL replacement of substr into substring 4793 - Add replacement of specials IEEE 754 values BINARY_(FLOAT|DOUBLE)_NAN 4794 and BINARY_(FLOAT|DOUBLE)_INFINITY by NaN and Infinity on PLPGSQL 4795 conversion and on data export - Thanks to Daniel Lyons. 4796 - Fix return type of function with just OUT or INOUT params. Thanks to 4797 Krasi Zlatev for the patch. 4798 - Add schema name on functions or procedures export when EXPORT_SCHEMA 4799 is activated. Thanks to Krasi Zlatev for the patch. 4800 - Fix case sensitivity issue with schema on partition export. 4801 - Fix case sensitivity issue with --xtable option. 4802 - Fix issues with case sensitivity on the schema owner set into the 4803 SCHEMA configuration directive. 4804 - Add default search_path on schema for contraints, index and data 4805 export when EXPORT_SCHEMA is activated. 4806 - Fix case sensitivity issue in search_path. 4807 - Force Oracle datetime format to be YYYY-MM-DD HH24:MI:SS.FF in client 4808 session to prevent other defined output format. Thanks to Aaron Culich 4809 for the patch. 4810 - Add export/import of table and column comment. Thanks to Krasi Zlatev 4811 for the patch. 4812 48132012 06 26 - 8.13 4814 4815 - Fix broken export with missing single quote in Oracle timestamp export 4816 formating with to_char(timestampcolumn, YYYY-MM-DD HH24:mi:ss). Thanks 4817 to Steve Delong for the report. 4818 48192012 06 22 - 8.12 4820 4821 - Add nex configuration directive ENABLE_MICROSECOND to allow timestamp 4822 to be exported with millisecond precision. Thanks to Patrick King for 4823 the feature request. 4824 - Fix multiple quote on foreign keys column names. Thanks to Vitaliy for 4825 the report. 4826 - Add new export type FDW to allow table export as foreign table for 4827 oracle_fdw. Thanks to David Fetter for the feature request. 4828 - Fix typo in LongTruncOk variable name. Thanks to Magnus Hagander for 4829 the patch. 4830 - Add XML_PRETTY configuration directive to replace getStringVal() by 4831 getClobVal() when extracting XML data. Thanks to Magnus Hagander for 4832 the patch. 4833 - Fix case sensitivity issue in ALTER TABLE and TRUNCATE statement. 4834 Thanks to Magnus Hagander for the patch. 4835 4836UPGRADE: Ora2Pg.pm and ora2pg perl scripts have changed as well as configuration 4837file. Documentation has been updated too so you'd better install all again. 4838 48392012 04 30 - 8.11 4840 4841 - Fix an error when running ora2pg directly against PG, index and 4842 constraints are created against PG instead of being written to 4843 the output file. Thanks to David Greco for the report. 4844 - Ora2Pg will now output a warning message when direct import to PG 4845 is set with other import type than COPY and DATA. 4846 - Fix NUL character removing on LOB to bytea export. Thanks to info31 4847 on PostgresqlFr for the report. 4848 48492012 03 11 - 8.10 4850 4851 - Add two configuration directives to control the BLOB export. 4852 LONGREADLEN to set the database handle's 'LongReadLen' attribute 4853 to a value that will be the larger than the expected size of the 4854 LOB. LONGTRUNKOK to bypass the 'ORA-24345: A Truncation' error. 4855 Thanks to Dirk Treger for the report. 4856 - Fix install problem on non-threaded Perl and the threads package. 4857 Replace use() by require() call. Thanks to Ian Sillitoe for the patch. 4858 - Fix strange Oracle behaviour where binary_double infinity is exported 4859 from Oracle as '~'. Replaced by 'inf'. Thanks to Daniel Lyons for the 4860 report. 4861 4862UPGRADE: only Ora2Pg.pm have changed so you can just override it. See also 4863documentation for new configuration directives: LONGREADLEN and LONGTRUNKOK. 4864 48652011 11 07 - 8.9 4866 4867 - Fix double quote into file name of package function export when case 4868 sensitivity is preserved. 4869 - Add support to XMLType data extraction. Thanks to Aaron Culich for 4870 the report. Before this release, xml data was exported as a Perl 4871 array reference. 4872 - Fix bug during foreign key export when foreign keys have different 4873 owners. Thanks to Krasi Zlatev for the patch. 4874 - Add support to plpgsql conversion during index extraction as many 4875 index use some Oracle function on their declaration. Thanks to 4876 Sriram Chandrasekaran fot the feature request. 4877 - PLSQL: Add replacement of Oracle subtr() by PostgreSQL substring(). 4878 Thanks to Sriram Chandrasekaran fot the feature request. 4879 - PLSQL: Add replacement of Oracle decode() by PostgreSQL CASE/THEN/ELSE. 4880 Thanks to Sriram Chandrasekaran fot the feature request. 4881 Note that this two replacement was not implemented because they could 4882 break the code if there's complex subqueries inside their declaration. 4883 This is why you can enable it by setting ALLOW_CODE_BREAK to 1 (new). 4884 In later release this directive will be enable by default. 4885 - Add output ordering on some object name so that results between two 4886 runs can be compared. Thanks to Henk Enting for the patch. 4887 - Fix misshandling of all cases of RAISE_APPLICATION_ERROR rewrite into 4888 RAISE EXCEPTION concatenations. Thanks to Krasi Zlatev for the report. 4889 4890UPGRADE: only Ora2Pg.pm and Ora2Pg/PSQL.pm have changed so you can just override them 4891if you dont want to reinstall all. 4892 48932011 10 13 - 8.8 4894 4895 - Before that release when you upgraded Ora2Pg using Makefile, the old 4896 ora2pg.conf was renamed as ora2pg.old. This can lead to lost previous 4897 configuration, the old ora2pg.conf is now untouched and the new one is 4898 installed as ora2pg.conf.new. 4899 - Renamed ora2pg.pl into ora2pg_pl in the package before installation 4900 to avoid the copy of the perl script into the site Perl install dir. 4901 It is still installed as ora2pg in /usr/local/bin by default. 4902 - Fix errors that appeared to be due to no quoting on the field names 4903 when ORA_SENSITIVE is enabled. Thank to Sam Nelson for the patch. 4904 - Limit case sensitivity on check constraints to column names only, 4905 before that if there was a value between double quote into the check 4906 constraint, it was wrongly changed to lower case. 4907 - Fix broken case sensitivity at data export when disabling/enabling 4908 triggers and truncating tables with copy or insert statement. 4909 - Change Ora2Pg version in packaging files that was still in 8.5. 4910 4911UPGRADE: only Ora2Pg.pm have changed so you can just override it. 4912 49132011 09 07 - 8.7 4914 4915 - The escape_bytea() function has been rewritten using a prebuild array 4916 to gain twice of performances. Thanks to Marc Cousin from Dalibo for 4917 the patch. 4918 - Improve speed of bulkload data by disabling autocommit by issuing a 4919 BEGIN at the start and COMMIT at the end. 4920 - Add multi-threading support. It is only used to do the escaping to 4921 convert LOBs to byteas, as it is very cpu hungry. There's a lot of 4922 CPU-waste here. The threads number is controlled by a new configuration 4923 directive: THREAD_COUNT. Putting 6 threads will only triple your 4924 throughput, if your machine has enough cores. If zero (default value), 4925 do not use threads, do not waste CPU, but be slower with bytea. 4926 Performance seems to peak at 5 threads, if you have enough cores, and 4927 triples throughput on tables having LOB. Another important thing: 4928 because of the way threading works in perl, threads consume a lot of 4929 memory. Put a low (5000 for instance) DATA_LIMIT if you activate 4930 threading. Many thanks to Marc Cousin for this great patch. 4931 - Fix standard_conforming_string usage on export as INSERT statement. 4932 - Fix an issue with importing Oracle NULL character (\0 or char(0)) with 4933 bytea and character data with UTF8 encoding. Now whatever is the data 4934 type or the encoding, this character is simply removed to prevent the 4935 well known 'ERROR: invalid byte sequence for encoding "UTF8": 0x00.' 4936 Thanks to Jean-Paul Argudo from Dalibo for the report. 4937 - Fix an incorrect syntax for "for each statement" triggers. 4938 Thanks to Henk Enting for the report. 4939 - Add comment at end of line to signal on which cursor the replacement 4940 on " EXIT WHEN (...)%NOTFOUND " is done. This will return something 4941 like "IF NOT FOUND THEN EXIT; END IF; -- apply on $1". Thanks to 4942 jehan Guillaume De Rorthais from Dalibo for the report this help a lot 4943 during Pl/Pgsql code review. 4944 - Fix table/column name replacement on constraint creation and dropping 4945 when REPLACE_TABLES/REPLACE_COLS is set during DATA/COPY export. 4946 - Fix table/column name replacement on indexes creation and dropping 4947 when REPLACE_TABLES/REPLACE_COLS is set during DATA/COPY export. 4948 - Remove unused table name parameter in _drop_indexes() function. 4949 - Add support to REPLACE_TABLES/REPLACE_COLS during schema export. 4950 Before this release those replacements were only applied to DATA or 4951 COPY export. You can now use it in schema export, it will replace 4952 table and/or column names in the TABLE/INDEX/CONSTRAINT schema export. 4953 MODIFY_STRUCT is still limited to DATA or COPY export as it have no 4954 sense outside this export. Unfortunately those replacements can not be 4955 done easilly in other export type like TRIGGER, FUNCTION, etc. so you 4956 must still edit this code by hand. 4957 - Use the bundled Perl Config module to detect if Perl is compiled with 4958 useithread. This mean that the old local defined %Config hash has been 4959 replaced by %AConfig. 4960 - SKIP indices is now obsolete and must be replaced with SKIP indexes. 4961 backward compatibility is preserved. 4962 - The file generated when FILE_PER_INDEX is activated has been renamed 4963 into INDEXES_... instead of INDICES_... 4964 - Add a warning on tablespace export when Oracle user is not a dba. 4965 - Fix fatal error when dumping to one file per function with double 4966 directory output. 4967 - Fix double print of FATAL messages and dirty database disconnect on 4968 fatal errors. 4969 - Add setting of client_encoding into each export type as defined in 4970 the configuration file. 4971 - Update web site documentation. 4972 4973UPGRADE: Ora2Pg.pm, Ora2Pg/PGSQL.pm and ora2pg have changed so they must be 4974overwritten. There's also changes in the configuration file and documentation 4975has changed as well. Backward compatibility is fully preserved. 4976 4977 49782011 07 07 - 8.6 4979 4980 - Remove "use strict" from head of Ora2Pg.pm that breaks view export. 4981 This is usually removed before public release, but not this time. 4982 Thanks to Jehan Guillaume de Rorthais from Dalibo for the report. 4983 - Add a new configuration directive called COMPILE_SCHEMA that force 4984 Oracle to compile the PL/SQL before code extraction to validate 4985 code that was invalidate for any reason before. If you set it to 1, 4986 you will force compiling of the user session schema, but you can 4987 specify the name of the schema to compile as the value too. Thanks 4988 to Jean-Paul Argudo from Dalibo for the solution. 4989 - Add new configuration directive EXPORT_INVALID to allow export of all 4990 PL/SQL code even if it is marked as invalid status. The 'VALID' or 4991 'INVALID' status applies to functions, procedures, packages and user 4992 defined types. 4993 - Excluded from export all tables, types, views, functions and packages 4994 that contains a $ character. Most of the time they don't need to be 4995 exported. 4996 - PLSQL: add automatic conversion of Oracle SYS_REFURSOR as PostgreSQL 4997 REFCURSOR. 4998 - Rewrite entirely the parser of DBMS_OUTPUT du to concatenation errors. 4999 - PLSQL: add automatic replacement of some Oracle exception errors: 5000 INVALID_CURSOR=>INVALID_CURSOR_STATE, ZERO_DIVIDE=>DIVISION_BY_ZERO, 5001 STORAGE_ERROR=>OUT_OF_MEMORY. 5002 5003UPGRADE: Ora2Pg.pm and Ora2Pg/PGSQL.pm have changed so they must be overwritten. 5004There's also changes in the configuration file and documentation has changed as 5005well. Backward compatibility is fully preserved. 5006 5007 50082011 07 01 - 8.5 5009 5010 - When FILE_PER_FUNCTION is activated and export type is PACKAGE, Ora2Pg 5011 will now save all functions/procedures of a package body into a 5012 directory named as the package name and into different files. This 5013 will allow to load each function separatly or load them all with the 5014 OUTPUT SQL script generated by Ora2Pg. 5015 - Fix Oracle package body parsing failure when a procedure is declared 5016 inside an other. 5017 - Add new configuration options FILE_PER_CONSTRAINT and FILE_PER_INDEX 5018 to generate three files during the schema extraction. One for the 5019 'CREATE TABLE' statements, one for the constraints (primary keys, 5020 foreign keys, etc.) and the last one for indices. Thanks to Daniel 5021 Scott for the feature request. 5022 - Allow to process PL/SQL Oracle code from file instead of a database 5023 to apply Ora2Pg code conversion. Thank to Mindy Markowitz for the 5024 feature request. See -i or --input_file command line option to ora2pg 5025 perl script or INPUT_FILE new configuration option. 5026 - Add new configuration directive STANDARD_CONFORMING_STRINGS that is 5027 used only during DATA export type to build INSERT statements. This 5028 should avoid 'WARNING: nonstandard use of \\ in a string literal'. 5029 Please check that this behavior is backward compatible with your 5030 PostgreSQL usage as this is enabled by default now. 5031 5032UPGRADE: The new features has changed Ora2Pg.pm and ora2pg.pl so that they must 5033be overwritten. There's also changes in the configuration file and documentation 5034has changed as well. Take care of backward compatibility with escaped strings in 5035DATA export type and the new behavior on PACKAGE export. 5036 50372011 06 07 - 8.4 5038 5039 - Moves Ora2Pg to SourceForge.net. 5040 - Fix an issue on setting owner in "ALTER SEQUENCE ... SET OWNER TO". 5041 Thanks to Herve Girres for the report. 5042 - Bugfix on lower case convertion for check constraints extraction. 5043 Thanks to Alexander Korotkov for the patch. 5044 5045UPGRADE: There's no new functionality, this is a bug fix release. 5046 50472011 05 11 - 8.3 5048 5049 - Fix issue on inherited user defined types converted to inherited tables. 5050 Add comment on unsupported inherited type in PostgreSQL too. Thanks to 5051 Mathieu Wingel for the report. 5052 - Fix issue on column default values. Oracle all this kind of strange 5053 syntax: counter NUMBER(4) default '' not null, that was translated to 5054 counter smallint DEFAULT '' by Ora2Pg. Thanks to Mathieu Wingel this is 5055 now rewritten as DEFAULT NOT NULL. 5056 - Fix case sensitivity on create view when there was double quote on the 5057 column name statement part. Thanks to Mathieu Wingel or the report. 5058 - Fix bad patch applied on column name case sensitivity issue during check 5059 constraint export. Thanks to Philippe Rimbault for the report. 5060 - Fix bug on package export introduced into version v8.2. The issue was 5061 related to end of package procedure detection. hanks to Mathieu Wingel 5062 or the report. 5063 5064UPGRADE: There's no new functionality, this is a bug fix release and every one 5065should upgrade to it. 5066 5067 50682011 05 01 - 8.2 5069 5070 - PLSQL: automatic replacement of EXIT WHEN cursor%NOTFOUND; by Pg 5071 synthax: IF NOT FOUND THEN EXIT; END IF;. Works with additional 5072 condition too. 5073 - PLSQL: Automatic replacement of SQL%NOTFOUND by NOT FOUND. 5074 - PLSQL: Add detection of TOO_MANY_ROW to NO_DATA_FOUND to add STRICT. 5075 - Completely rewrite the parsing of Oracle package body to handle all 5076 cases and especially prodedure declared into an other procedure. 5077 Those procedure are renamed INTERNAL_FUNCTION and must be rewritten. 5078 - Fix type usage of ora2pg Perl script. 5079 - Add a new directive FORCE_OWNER. By default the owner of the database 5080 objects is the one you're using to connect to PostgreSQL. If you use 5081 an other user (postgres for exemple) you can force Ora2Pg to set the 5082 object owner to be the one used in the Oracle database by setting the 5083 directive to 1, or to a completely different username by setting the 5084 directive value to that username. Thanks to Herve Girres from Meteo 5085 France for the suggestion and patch. 5086 - Add --forceowner or -f command line option to ora2pg program. 5087 - Add SHOW_ENCODING extract type to return the Oracle session encoding. 5088 For example it can return: NLS_LANG AMERICAN_AMERICA.AL32UTF8 5089 - Remove SYS_EXTRACT_UTC from index creation as Pg always stores them 5090 in UTC. Thanks to Daniel Scott for the patch. 5091 - In PLSQL code SYS_EXTRACT_UTC is replaced by the Pg syntaxe: 5092 field AT TIME ZONE 'UTC'. 5093 - Fix a pending problem with "Wide character in print at" on COPY mode. 5094 Thanks to Bernd Helmle from Credativ GmbH for the patch. 5095 - PLSQL: Add automatic rewrite of FOR ... IN REVERSE ... into Pg synthax 5096 - Fix column name case sensitivity issue during check constraint export. 5097 Thanks to Daniel Berger for the report. 5098 - Remove the possibility to add comment after a configuration directive 5099 it may not be used and it was generating an issue with the passwords 5100 configuration directives for examples. Thanks to Daniel Berger for the 5101 report. 5102 - Complete rewrite of user defined type extraction. Add support of inherited 5103 type using Oracle UNDER keyword as well as better support to custom type 5104 with BODY. Thanks to Mathieu Wingel for the report. 5105 - Fix case sensitivity on user defined types. Thanks to Mathieu Wingel for 5106 the report. 5107 5108UPGRADE: All files have changed so you need a fresh install/upgrade. 5109Previous release used to remove any string starting from a # in the config file, 5110this was to allow comments after a configuration directive. This possibility 5111have been removed in this release so you can no more add comments after a 5112configuration directive. 5113 5114 51152011 03 28 - 8.1 5116 5117 - Prevent Ora2PG to export twice datas when using FILE_PER_TABLE and 5118 the data output file exist. This is useful in case of export failure 5119 and you don't want to export all data again. This also mean that if 5120 you want to get new data you have to remove the old files before. 5121 - Fix parsing of procedure/function into pl/sql Oracle package. 5122 - Fix bug in IS NULL/IS NOT NULL replacement. Thanks to Jean-Paul Argudo 5123 from Dalibo for the report. 5124 - Add CREATE OR REPLACE on RULE creation. 5125 - Add DROP TRIGGER IF EXISTS before trigger creation. 5126 - Replace Oracle date "0000-00-00" by NULL. 5127 - Fix infinite loop in package/fonction type replacement. 5128 - Add one file per package creation if FILE_PER_FUNCTION is enabled. 5129 - Fix double quote in name of custom type extraction. 5130 - Add extraction of custom type IS VARRAY as an custom type of table 5131 array. Thank to Jean-Paul Argudo from Dalibo for the patch. 5132 - Fix multiple double quote in name of create index definition. 5133 - Apply excluded and limited table to tablespace extraction. 5134 - Fix function and procedure detection/parsing on package content. 5135 - Fix schema prefix in function name declaration in package export. 5136 - PLSQL: Replace some way of extracting date part of a date : 5137 TO_NUMBER(TO_CHAR(...)) rewritten into TO_CHAR(...)::integer when 5138 TO_NUMBER just have one argument. 5139 - Fix Makefile.pl error when trying to modify file ora2pg now renamed 5140 into ora2pg.pl 5141 - Add 3 new export types SHOW_SCHEMA, SHOW_TABLE and SHOW_COLUMN. Those 5142 new extraction keyword are use to only display the requested information 5143 and exit. This allow you to quickly know on what you are going to work. 5144 The SHOW_COLUMN allow a new ora2pg command line option: '--xtable relname' 5145 or '-x relname' to limit the displayed information to the given table. 5146 - Add type replacement for BINARY_INTEGER and PLS_INTEGER as integer. 5147 5148UPGRADE: Please make a full upgrade asap to this release. 5149 51502011 03 15 - 8.0 5151 5152This major release simplify and improve Oracle to PostgreSQL export. Ora2Pg v8.x 5153now assume that you have a modern PostgreSQL release to take full advantage of 5154the Oracle compatibility effort of the PostgreSQL development team. Ora2Pg since 5155v8.x release will only be compatible with Pg >= 8.4. 5156 5157 - Remove addition of AS for alias as with modern PG version this can 5158 be optional (Pg >= 8.4). 5159 - Fix CREATE with missing USER/ROLE for grant extraction. Thanks to 5160 Herve Girres for the report. 5161 - Apply missing psql_pgsql converter to view definition. 5162 - PLSQL : Normalize HAVING ... GROUP BY into GROUP BY ... HAVING clause 5163 - PLSQL : Convert call to Oracle function add_months() in Pg syntax 5164 - PLSQL : Convert call to Oracle function add_years() in Pg syntax 5165 - Apply missing psql_pgsql converter to triggers WHEN clause. 5166 - Fix DECLARE CURSOR rewrite. 5167 - Allow one file per function / procedure / package exported with a new 5168 configuration option FILE_PER_FUNCTION. Useful to editing and testing. 5169 Thank to Jean-Paul Argudo from DALIBO for the feature request. 5170 - The FILE_PER_TABLE configuration option is now also applied to views. 5171 - Remove obsolete PG_SUPPORTS_INOUT as it is supported by with modern 5172 PG version (Pg >= 8.4). 5173 - Remove obsolete PG_SUPPORTS_DEFAULT as it is supported by with modern 5174 PG version (Pg >= 8.4). 5175 - Allow to adjust PostgreSQL client encoding with a new configuration 5176 directive: CLIENT_ENCODING. 5177 - Add TRUNCATE_TABLE configuration directive to add TRUNCATE TABLE 5178 instruction before loading data. 5179 - Add type conversion of Oracle XMLTYPE into PostgreSQL xml type. 5180 - PLSQL: SYSDATE is now replaced by LOCALTIMESTAMP to not use timezone. 5181 Thanks to Jean-Paul Argudo from DALIBO for the report. 5182 - Use 'CREATE OR REPLACE' on create trigger function instruction. 5183 - Fix prefixing by OUTPUT_DIR when file per table/function is enabled. 5184 - Use 'CREATE OR REPLACE' on create view. 5185 - PLSQL_PGSQL is now enabled by default. If you want to export Oracle 5186 original function/procedure/package code, disable it. 5187 - PLSQL: WHERE|AND ROWNUM = N; is automatically replaced by LIMIT N; 5188 - PLSQL: Rewrite comment in CASE between WHEN and THEN that makes Pg 5189 parser unhappy. 5190 - PLSQL: Replace SQLCODE by SQLSTATE 5191 5192UPGRADE: You must reinstall all and review your configuration file 5193 51942011 02 14 - 7.3 5195 5196 - Remove PG_SUPPORTS_INOUT, now Ora2Pg assumes the PostgreSQL database 5197 destination support it (Pg > v8.1). 5198 - Remove PG_SUPPORT_ROLES, now Ora2Pg assumes the PostgreSQL database 5199 destination support it (Pg > v8.1). 5200 - Complete rewrite of the GRANT (user/role/grant) export type. It now 5201 should be only related to the current Oracle database. Note that do 5202 not try to import rights asis as you may import have errors or worse 5203 miss handling of the rights! Just remember that for example in Oracle 5204 a schema is nothing else than a user so it must not be imported like 5205 this. 5206 - Fix multiple errors in partitionning definition. Thank to Reto Buchli 5207 for the report. 5208 - PLSQL: reordering cursor Oracle declaration "DECLARE CURSOR name" into 5209 "DECLARE name CURSOR". Thank to Reto Buchli (WSL IT) for the report. 5210 - Fix miss handling of DEFAULT parameters value in convert_function(). 5211 Thanks to Leonardo Cezar for the patch. 5212 - Fix Oracle tablespace export where Pg tablespace location was based on 5213 Oracle filename. This fix extract the path and replace the filename 5214 with tablespace name. Thank to Reto Buchli (WSL IT) for the report. 5215 - Fix parsing of ending function code. Thanks to Leonardo Cezar for the 5216 patch. 5217 - Fix call to _convert_procedure() that is in fact the same function as 5218 _convert_function(). Thanks to Leonardo Cezar for the report. 5219 - Fix multiple call on tablespace alter index on the same object. Thank 5220 to Reto Buchli (WSL IT) for the report. 5221 - PSQL: Rewrite RAISE EXCEPTION concatenations. Double pipe (||) are 5222 replaced by % and value is set as parameter a la sprintf. Thank to 5223 Reto Buchli (WSL IT) for the report. 5224 - Add missing comment of PARTITION export type into configutation file. 5225 - Complete rewrite of the table partition export part has it was not 5226 handling all case and was really buggy. 5227 - PLSQL: add normalisation of the to_date() function. 5228 - Ora2Pg now warns during grant export when it is not connected as an 5229 Oracle DBA user. GRANT export need rights of Oracle DBA or it fail. 5230 - Fix install of changelog into Makefile.PL, name was wrong. Thanks to 5231 Julian Moreno Patino for the patch. 5232 5233 52342011 01 12 - 7.2 5235 5236 - Fix escaping of BLOB/RAW to bytea data import causing import to crash. 5237 Thanks to Ozmen Emre Demirkol for the report. 5238 - Add support to default value into functions parameter (PG >= 8.4). 5239 Can be activated with a new configuration directive: PG_SUPPORTS_DEFAULT. 5240 Default is 1, activated. 5241 - Fix bad ending of exported function: remove trailing chars after END. 5242 - Add support to WHEN clause on triggers (PG >= 9.0), can be activated 5243 with a new configuration directive: PG_SUPPORTS_WHEN. 5244 - Add support to INSTEAD OF usage on triggers (incoming PG >= 9.1). Can 5245 be activated with a new configuration directive: PG_SUPPORTS_INSTEADOF. 5246 - Fix error using SKIP directive. Thanks to Laurent Renard from Cap Gemini 5247 for the report. 5248 - Fix missing perl object instance in format_data() function. 5249 - Fix duplicate procedure or function when export type use both FUNCTION 5250 and PROCEDURE. 5251 52522010 12 04 - 7.1 5253 5254 - Improve direct DBD::Pg data export/import speed by 10. 5255 - Add --section=3 in pod2man call into Makefile.PL. Thanks to Julian 5256 Moreno Patino for the report. 5257 - Renamed ChangeLog into changelog to avoid upstream warning with Debian 5258 package. Thanks to Julian Moreno Patino for the suggestion. 5259 - Fix some spelling mistakes in doc/Ora2Pg.pod. Thanks to Julian Moreno 5260 Patino for the fix. 5261 - Fix release version into Ora2Pg.pm and PLSQL.pm, was still in 6.5. 5262 - Fix direct data export/import using DBD::Pg. Thanks to Laurent Renard 5263 from Cap Gemini for the report. 5264 - Fix drop/create contraints and index during direct data export/import 5265 using DBD::Pg. Thanks to Thierry Grasland from Cap Gemini for the report. 5266 52672010 11 23 - 7.0 5268 5269 - Rename ora2pg perl script into ora2pg.pl in sources because Windows 5270 users can't extract the tarball. During install it is renamed into 5271 ora2pg. Thanks to Andrew Marlow for the report. 5272 - Fix doinst.sh for SlackWare Slackbuid packaging. 5273 - The DEFER_FKEY configuration directive has been fixed as it only 5274 works in a transaction. Note that foreign keys must have been created 5275 as DEFERRABLE or it also will not works. Thanks to Igor Gelman for the 5276 report. 5277 - Add DROP_FKEY configuration directive to force deletion of foreign keys 5278 before the import and recreate them and the end of the import. This may 5279 help if DEFER_FKEY not works for you. 5280 - Add DROP_INDEX configuration directive to force deletion of all indexes 5281 except the automatic index (primary keys) before data import and to 5282 recreate them at end. This can be used to gain speed during import. 5283 - Add TSMSYS, FLOWS_020100 and FLOWS_FILES to the owners exclude list. 5284 This concern the SRS$ table and all tables begining with 'WWV_FLOW_' 5285 - Change the way DATA_LIMIT is working. It must be used now to set the 5286 bulk size of tuples return at once. Default is 10000. 5287 - Improve data export speed by 6! The data export code has been entierly 5288 rewritten and the speed gain is really fun. 5289 - Add OUTPUT_DIR configuration directive to set a base directory where all 5290 dumped files must be written. Default: current directory. 5291 - Change value of default numeric(x) type from float to bigint and change 5292 default numeric(x,y) type to double precision. 5293 - Change conversion type for BFILE from text to bytea. 5294 52952010 09 10 - 6.4 5296 5297 - Configuration directives SHOWTABLEID, MIN and MAX are now obsolete 5298 and has been definitively removed. They were never used and add too 5299 much confusion. 5300 - Fix bug in column name replacement where table name was also replaced. 5301 Thank to Jean-Paul Argudo from DALIBO for the report. 5302 - Fix case sensitive errata in PG schema search path. Thank to Jean-Paul 5303 Argudo from DALIBO for the report. 5304 - Remove double \n at end of debug message. 5305 - Fix debug mode not activated if the DEBUG directive is enable and 5306 the -d command line is not present. 5307 - Add unbuffered output for debug message. 5308 5309UPGRADE: simply override the Ora2Pg.pm Perl module where it is installed. 5310 5311 53122010 07 22 - 6.3 5313 5314 - Fix Oracle 8i compatibility error during schema extraction complaining 5315 that column CHAR_LENGTH doesn't exist. Thanks to Philippe Rimbault for 5316 the report. Note that the error message is still displayed but tagged 5317 as WARNING only. 5318 - Fix error using the IMPORT option on a read_conf method call. Thanks 5319 to Diogo Biazus for the report. 5320 - Fix export of sequences that does not handle maxvalue well and can be 5321 lower than minvalue. Thanks to Nathalie Doremieux for the report. 5322 5323UPGRADE: Just override Ora2Pg.pm 5324 53252010 06 15 - 6.2 5326 5327 - Change default transaction isolation level from READ ONLY to 5328 SERIALIZABLE to ensure consistency during data export. Thanks to 5329 Hans-Jurgen Schonig from postgresql-support.de 5330 - Add the TRANSACTION configuration directive to allow overriding of 5331 the isolation level. Value can be readonly, readwrite, committed and 5332 serializable. The last is the default. 5333 53342010 05 07 - 6.1 5335 5336 - Fix error on partition export following schema definition. 5337 - Add first support to export Oracle user defined types. 5338 - Add CTXSYS,XDB,WMSYS,SYSMAN,SQLTXPLAIN,MDSYS,EXFSYS,ORDSYS,DMSYS, 5339 OLAPSYS to the sysuser default exclusion list. 5340 - PLSQL.pm: Add automatic translation of Oracle raise_application_error 5341 and dup_val_on_index to PG RAISE EXCEPTION and UNIQUE_VIOLATION. 5342 - Change/fallback to a lower case package name (ora2pg-6.x.tar.gz). 5343 - Change default convert type for 'LONG RAW' to bytea. 5344 - Add PG_SCHEMA configuration directive to defined a coma delimited 5345 list of schema to use in SET search_path PostgreSQL command. 5346 5347 53482010 02 28 - 6.0 5349 5350 - Publish a dedicated site to Ora2Pg at http://ora2pg.darold.net/ 5351 - Add export of Oracle table partitoning. See export type PARTITION. 5352 - Add command line arguments to perl script ora2pg. See --help for a 5353 full listing of these option. The most interesting is --type to change 5354 the export type directly at command execution without needing to edit 5355 the configuration file, --plsql to directly enable PLSQL to PLPSQL 5356 code conversion and --source, --user --password to set Oracle data 5357 source. There's also --namespace to set the Oracle schema. 5358 - Create all file for standard Perl module install. Install is now done 5359 with: perl Makefile.PL && make && make install 5360 - Move Ora2Pg license from Perl Artistics to GPLv3. 5361 - Move PLSQL package as Ora2Pg::PLSQL for standard Perl module install. 5362 - Remove use of Perl module String::Random. 5363 - Rename program ora2pg.pl into ora2pg for standard usage. 5364 - Fix extra double quote on column name of index export. Thanks to 5365 Guillaume Lelarge for the patch. 5366 - Add packaging facilities to build RPM, SlackBuild and Debian packages. 5367 - Fix miss handling of Ora2Pg.pm options at object instance init. 5368 - Configuration file ora2pg.conf is now generated by Makefile.PL 5369 53702009 12 18 - 5.5 5371 5372 - Fix CONSTANT declaration in Procedure/Function/Package export. 5373 - Fix length of char and varchar on multibyte like UTF8 encoding. Thanks 5374 to Ali Pouya for the patch. 5375 - Fix view export where alias to column in Oracle not use 'AS' and 5376 PostgreSQL required it. Thanks to Ali Pouya for the report. 5377 - Add type replacement of sql variable in PLSQL code (PLSQL.pm). Thanks 5378 to vijay for the patch. 5379 53802009 07 15 - 5.4 5381 5382 - Fix bug introduced in multiple output file feature. This bug force 5383 Ora2pg to crach after the first table export when output is wanted in 5384 a single file. Thanks to Leo Mannhart for the report. 5385 - Fix debug filename output on multiple export file. Thanks to Leo 5386 Mannhart for the report. 5387 53882009 07 07 - version 5.3 5389 5390 - Fix wrong escaping of data named as column during view export. Thank 5391 to Andrea Agosti for the patch. 5392 - Allow export of datas into one file per table. See FILE_PER_TABLE 5393 configuration directive. Thanks to Alexandre - Aldeia Digital for the 5394 idea. 5395 53962009 06 19 - version 5.2 5397 5398 - Fix order of the column name of the view which was not preserved. Now 5399 ordered by COLUMN_ID. Thank to Andrea Agosti for the report. 5400 - Fix case sensitivity in VIEW extraction. Thank to Andrea Agosti for 5401 the patch. 5402 54032009 03 06 - version 5.1 5404 5405 - Fix missing -U username at Pg connection. Thanks to Brendan Richards. 5406 - Fix $ENV{ORACLE_HOME} and $ENV{NLS_LANG} to not being overwritten 5407 by configuration settings if they are already defined in environment. 5408 - Fix typo in ora2pg.pl where keep_pkey_names was replaced by 5409 keep_pkeys_name and so prevent use of KEEP_PKEY_NAMES in configuration. 5410 Thanks to Olivier Mazain for the report. 5411 - Configuration file directives are now case insensitive. 5412 - Force $type parameter given to _sql_type() to be uppercase in that 5413 methode instead of during function call. Thanks to Ali Pouya for the 5414 report. 5415 - Modify ora2pg.pl to remove the obsolete call to export_data(). Use 5416 only export_schema() now. 5417 - Modify ora2pg.pl to simplify it. Reading configuration is now done 5418 internally by Ora2Pg.pm as well as all other initialization process. 5419 You can always overwrite all configuration options into call to new 5420 Ora2Pg(). Now ora2pg.pl can be as simple as: 5421 5422 use Ora2Pg; 5423 my $schema = new Ora2Pg('config' => "/etc/ora2pg.conf"); 5424 $schema->export_schema(); 5425 exit(0); 5426 5427 This will be less confusing. You can upgrade Ora2Pg.pm without carring 5428 about that, backward compatibility with previous version is preserved. 5429 - Review entire documentation with the great help of Ali Pouya. 5430 - Add type BOOLEAN converted to boolean. 5431 - PG_SUPPORTS_INOUT is now enabled by default in the configuration file 5432 - SQL and PL/SQL to PLPGSQL converter: 5433 .Replace MINUS call to EXCEPT 5434 .Replace DBMS_OUTPUT.put, DBMS_OUTPUT.put_line, DBMS_OUTPUT.new_line 5435 by the PLPGSQL equivalent: RAISE NOTICE 5436 .Rewrite function/procedure/package convertion functions. 5437 This Oracle SQL converter for function/procedure/package is now only 5438 applied if the configuration directive PLSQL_PGSQL is enable, else 5439 these Oracle code are exported as is. Thanks to Ali Pouya for the help. 5440 5441 - Reserved call to sql transaction only for DATA export type. Others 5442 export type now use \set ON_ERROR_STOP ON. Thanks to Ali Pouya. 5443 - Fix tablespace creation into schema (missing search_path). Thanks to 5444 Olivier Mazain. 5445 - Fix the type returned by the _sql_type() method in the case of a 5446 numeric with null len and pg_numeric_type is set. Thanks to Ali Pouya. 5447 - Change function body delimiter to $body$ to allow use of $$ into the 5448 body as quote replacement. Thanks to Ali Pouya. 5449 - Fix returns type from function. If multiple OUT parameters: RECORD, 5450 if only one OUT parameter, return his type. If no out parameter: return 5451 VOID. Thanks to Ali Pouya. 5452 - Fix export DATA when the name of a column in the table match COMMENT, 5453 AUDIT or any other defined reserved words. These reserved words are 5454 defined in a new configuration variable ORA_RESERVED_WORDS. It accept 5455 a list of comma separated reserved words. Thanks to Andrea Agosti for 5456 the report. 5457 - Fix configuration parser that omit custom SYSUSERS definition. 5458 54592009 02 13 - version 5.0 5460 5461 - Fix places where $self->{prefix} where not used. This prefix is 5462 used to replace DBA_... objects into ALL_... objects. Thanks to Daniel 5463 Scott report and patch. 5464 - Fix some problem on trigger export (missing ending semicolon, return 5465 opaque replaced by return trigger, add missing return new value, single 5466 quote for delimitating the function body hits against quotes inside the 5467 function). Thanks to Luca DallOlio for reports and patches. 5468 - Add first attempt to rewrite plsql code to plpgsql code (see function 5469 plsql_to_plpgsql in new perl module PLSQL.pm). There's a configuration 5470 option named PLSQL_PGSQL to activate the convertion. 5471 54722008 12 16 - version 4.11 5473 5474 - Fix Ora2Pg failure on Oracle database with case sensitive tables. 5475 Thanks to Marc Cousin for report and patch. 5476 - Fix missing schema name in query when extract views as tables. 5477 54782008 12 04 - version 4.10 5479 5480 - Fix missing replacement of table name on disable triggers when 5481 required. 5482 - Fix some malformed debug output messages. 5483 - Add the capability to extract data from view as if it was a table. 5484 This is usefull if you want to export/import data from an Oracle 5485 view into a Pg table. There's nothing special to do, just to give 5486 the view name into the TABLES configuration directive and set TYPE 5487 to DATA or COPY. If views are not specified in the TABLES directive 5488 there's not view export but only table data. 5489 - Add capability to extract views structure as table schema. There's 5490 nothing special to do, just to give the view name into the TABLES 5491 configuration directive and set TYPE to TABLE. This will not extract 5492 constraints or other table tuning from table used in the view. Thanks 5493 to Groupe SAMSE for the feature request. 5494 54952008 10 27 - version 4.9 5496 5497 - Modify the DISABLE_TABLE_TRIGGERS configuration option. Should be now 5498 replaced by DISABLE_TRIGGERS, but compatibility is preserved. 5499 - Add DISABLE_SEQUENCE configuration option to not export alter 5500 sequence after COPY or DATA export. 5501 - Fix extraction of function based index that appears as SYS_NC.... 5502 Thanks to Bozkurt Erkut from SONY for the report 5503 55042008 09 04 - version 4.8 5505 5506 - Add SYSUSERS configuration option that allow you to specify a coma 5507 separated list of Oracle System user/schema to exclude from extracted 5508 object. By default it only exclude user SYS,SYSTEM,DBSNMP,OUTLN and 5509 PERFSTAT 5510 - Add support to other binary mode output than ':raw' to avoid the Perl 5511 error message:"Wide character in print". See the BINMODE configuration 5512 directive. This will help a lot if you have UTF-8 records. 5513 Thank to Guillaume Demillecamps for the report. 5514 - Fix double escaping of special character. 5515 Thank to Guillaume Demillecamps for the report. 5516 55172008 01 25 - version 4.7 5518 5519 - Add support to regular expressions in the exclusion list. Thanks to 5520 Peter Eisentraut 5521 - Fix misformatted SQL string in function _extract_sequence_info. 5522 Thanks to Bernd Helmle. 5523 - Add escaping of backslash on COPY output. Thanks to Peter Eisentraut 5524 55252008 01 03 - version 4.6 5526 5527 - Applied a patch to add ALTER SEQUENCE statements to the dump to 5528 adjust the sequence to the correct values after DATA and COPY dumps. 5529 Thanks to Bernd Helmle. 5530 - Applied a patch which fixes problems with broken COPY output when 5531 extracting data from Orace databases with embedded tabs, carriage 5532 returns and line feeds. Thanks to Bernd Helmle. 5533 - Move the project to PgFoundry 5534 55352007 06 20 - version 4.5 5536 5537 - Fix columns order in index extraction. Thanks to Ugo Brunel from BULL. 5538 55392007 04 30 - version 4.4 5540 5541 - Fix missing single quote in role extraction. 5542 - Add configuration directive NOESCAPE to enable/disable 5543 escaping characters during data extraction. Default is enabled. 5544 - Add TIMESTAMP, BINARY_FLOAT and BINARY_DOUBLE data type translation. 5545 - Add DATA_TYPE configuration directive to allow user defined data type 5546 translation. 5547 - Add NLS_LANG configuration directive to set Oracle database encoding 5548 and enforce a default language-setting in ora2pg.pl. Thanks to Lars 5549 Weber 5550 55512007 04 03 - version 4.3 5552 5553 - Fix duplicate view export. Add schema selector to views. Thank to 5554 Ugo BRUNEL from BULL for the fix. 5555 - Remove 'use strict' to prevent failure on certain condition. 5556 Thank to Andrea Schnabl for the report. 5557 55582006 06 08 - version 4.2 5559 5560 - Fix a miss taping on constraint type search that convert unique key 5561 to primary key. Thank to Ugo BRUNEL (BULL) for the patch. 5562 - Fix case sensitivity on CHECK constraint that could cause problem when 5563 check value is uppercase. Thank to Ugo BRUNEL (BULL) for the patch. 5564 55652006 03 28 - version 4.1 5566 5567 - Fix a problem when using data_limit and where clause. Thank to 5568 Rene Bentzen for the patch. 5569 - Add enable/disable trigger on data import. Thank to Bernd Helmle. 5570 - Fix escaping of chr(13) MS crashing data import into PG. Thank 5571 to Ugo Brunel (BULL). 5572 55732006 03 22 - version 4.0 5574 5575 - Add validation of the requested schema in the database before all. 5576 Thanks to Max Walton for the idea. 5577 - Add multiple export type at the same time. Thanks to Max Walton 5578 for the idea. 5579 - Add support for in/out/inout function parameter. See PG_SUPPORTS_INOUT 5580 configuration option. Thanks to Bernd Helmle for this great 5581 contribution/patch. 5582 - Add support for ROLES with Pg v8.1+. See PG_SUPPORTS_ROLE configure 5583 option. 5584 55852006 02 10 - version 3.4 5586 5587 This release add better support to Oracle grant, function and grant 5588 extraction. Great thanks to the Pg team! 5589 5590 - Add preservation of oracle primary key names. See KEEP_PKEY_NAMES 5591 configuration option. Thanks to Antonios Christofides for this patch. 5592 - Fix bug in case insensitive check constrainte. Thanks to Wojciech 5593 Szenajch for the patch. 5594 - Fix saving data to files correctly (binmod) when the oracle database 5595 contains utf8 chars. Thanks to Richard Chen for the report. 5596 - Fix bug on view extraction when a column contains the word WITH. 5597 Thanks to Richard Chen for the patch. 5598 - Fix wrong mapping between tge data type in Oracle "number(10)" and 5599 Postgresql, which should be "integer" and not "bigint". Thanks to 5600 Sergio Freire for the patch. 5601 - Fix bug in EXCLUDE configuration directive parsing. Thanks to Matt 5602 Miller for the patch. 5603 56042005 02 22 - version 3.3 5605 5606 - Fix bug "Modification of a read-only value attempted" 5607 56082005 02 11 - version 3.2 5609 5610 - Fix patch error on column position sort 5611 - Replace 'now' by CURRENT_TIMESTAMP on SYSDATE replacement 5612 - Fix bytea type that was not quoted. 5613 56142005 02 10 - version 3.1 5615 5616 - Fix bug on deferrable constraint. Thanks to Antonios Christofide for 5617 the patch. 5618 - Fix problem on defer_fkey that should be in a transaction. Thanks to 5619 Antonios Christofide for the patch. 5620 - Add sort by column position during schema extraction. 5621 - Add support to SYSDATE. Thanks to David Cotter-Alatto Technologies Ltd 5622 56232004 12 24 - version 3.0 5624 5625 - Add 'TABLESPACE' extraction type to create PostgreSQL v8 tablespace. 5626 56272004 12 24 - version 2.9 5628 5629 - Debuging output rewrite. Thanks to Antonios Christofide for help. 5630 - Add 'PG_NUMERIC_TYPE' configuration option to replace portable 5631 numeric type into PostgreSQL internal type (smallint, integer, 5632 bigint, real and float). 5633 56342004 12 24 - version 2.8 5635 5636 - Fix/add support to data export of type BLOB, RAW and LONG RAW. 5637 Thanks to Antonios Christofide for help. 5638 56392004 12 23 - version 2.7 5640 5641 - Add 'FKEY_DEFERRABLE' configuration option to force foreign key 5642 constraints to be exported as deferrable. Thanks to Antonios 5643 Christofide for help. 5644 - Add 'DEFER_FKEY' configuration option to defer all foreign key 5645 constraints during data export. Thanks to Antonios Christofide 5646 for help. 5647 56482004 12 23 - version 2.6 5649 5650 - Fix duplicate output during export. Thanks to Adriano Bonat for the 5651 report. 5652 - Fix data limit infinite loop during data extraction. Thanks to Thomas 5653 REISS for the report. 5654 - Add 'GEN_USER_PWD' configuration option allowing to generate a random 5655 password. Thanks to Antonios Christofide for help. 5656 (Require String::Random from CPAN). 5657 - Fix USER/ROLES/GRANT extraction problem. Now all users are dumped. 5658 All roles are translated to PostgreSQL groups. All grants are 5659 exported. YOU MUST EDIT the output file to rewrite real privilege 5660 and match your needs. Thanks to Antonios Christofide for help. 5661 - Fix split COPY export into multiple transaction for large data export. 5662 The number of row per transaction is set to 'DATA_LIMIT' value. A 5663 value of O mean all in a single transaction. 5664 56652004 10 13 - version 2.5 5666 5667 - Fix extraction problem when the connection to Oracle DB is not as DBA. 5668 56692004 08 22 - version 2.4 5670 5671 - Fix bug in DBI errstr call. 5672 - Add CASE_SENSITIVE configuration option to allow case sensitivity on 5673 Add a new configuration directive 'USER_GRANTS' to do that. Thanks to 5674 Octavi Fors for the report. 5675 object name. Thanks to Thomas Wegner. 5676 - Fix major bug in unique keys extraction. Thanks to Andreas Haumer and 5677 Marco Lombardo for their great help. 5678 - Add CHECK constraint extration. Thanks again to Andreas Haumer. 5679 - Add IMPORT configuration option to include common configuration file 5680 throught multiple configuration files.Thanks to Adam Sah and Zedo Inc. 5681 - Add SKIP configuration option to turning off extraction of certain 5682 - schema features. Thanks to Adam Sah and Zedo Inc. 5683 - Fix bug in excluded tables 5684 - Fix backslash escaping. Thanks to Adam Sah and Zedo Inc. 5685 - Add REPLACE_TABLES configuration option to change table name during 5686 data extraction. 5687 - Add REPLACE_COLS configuration option to change columns name during 5688 data extraction. 5689 - Add WHERE configuration option to add where clause to each table or 5690 specific tables during extraction. Usefull for replication. Thanks 5691 to Adam Sah and Zedo Inc. 5692 - Add progress indicators (per 1000 rows) and performance results 5693 during data extraction in debug mod. Thanks to Adam Sah and Zedo Inc. 5694 - Add Gzip and Bzip2 compress to output file if extension .gz or .bz2. 5695 Gzip compress require perl module Compress::Zlib from CPAN. Thanks 5696 to Adam Sah for the idea. 5697 56982004 04 13 - Version 2.3 5699 5700 - Fix bug in date/time conversion when using data export limit. Thanks 5701 to Andreas Haumer. 5702 - Add sort order when extracting tables and data to respect the TABLES 5703 limited extraction array write order. Usefull if you have foreign key 5704 constraints. Thanks to Andreas Haumer for the idea. 5705 57062004 04 13 - Version 2.2 5707 5708 - Add EXCLUDE configuration option to allow table exclusion 5709 from all extraction. 5710 - Fix a bug in escaping single quote on data export. 5711 57122004 03 09 - Version 2.1 5713 5714 - Fix COPY output by replacing special character. 5715 - Add configuration file usefull for people who don't have Perl in mind 5716 Thank's to Tanya Krasnokutsky to force me to do that :-) 5717 - Fix other minor problem. 5718 57192002 12 26 - Version 2.0 5720 5721 - Clean code. 5722 - Fix COPY output on column value with EOL and add column naming. 5723 - Add support to the PostgreSQL 7.3 schema. So Oracle schema can now be 5724 exported. (see export_schema init option) 5725 - Remove data extraction limit (old default: 10) so each tuple will be 5726 dump by default. 5727 57282002 12 03 - Version 1.12 5729 5730 I have fixed 2 bugs when using it against Oracle 817R3 on linux. 5731 5732 - Fix problem regarding RI constraints, the owner name was not 5733 getting into the sql statement. Thank to Ian Boston. 5734 - Moved all the RI constraints out of the create table statement. 5735 Thank to Ian Boston for this contribution. This was a major request 5736 from Ora2pg users. 5737 57382002 09 27 - Version 1.11 5739 5740 - Fix a problem when retrieving package+package body. Thanks to Mike 5741 WILHELM-HILTZ. 5742 - Set LongReadLen to 100000 when exporting table information. Many 5743 users reports this kind of error: A-01406 LongReadLen too small and/or 5744 LongTruncOk not set. This should fix the problem else you must 5745 increase the value. 5746 - Filtering by owner for better performance when retreiving database 5747 schema. Thanks to Jefferson MEDEIROS. 5748 57492002 07 29 - Version 1.10 5750 5751 - Fix a problem with local settings regarding decimal separator (all , 5752 are changed to .) Thank to Jan Kester. 5753 57542002 06 04 - Version 1.9 5755 5756 - Fix a problem on exporting data which fill NULL instead of 0 or 5757 empty string. Thanks to Jan Kester. 5758 - Add time + date when export data [ tochar('YYYY-MM-DD HH24:MI:SS') ]. 5759 Thanks to Paolo Mattioli. 5760 57612002 03 05 - Version 1.8 5762 5763 - Add Oracle type FLOAT conversion to float8. 5764 - Add column alias extraction on view. 5765 Thanks to Jean-Francois RIPOUTEAU 5766 - Add PACKAGE extraction (type => DATA). 5767 57682002 02 14 - Version 1.7 5769 5770 - Remove export of OUTLINE object type. Thanks to Jean-Paul ARGUDO. 5771 57722002 01 07 - Version 1.6 5773 5774 - Fix problem exporting NULL value. Thanks to Stephane Schildknecht. 5775 57762001 12 28 - Version 1.5 5777 5778 - Fix LongReadLen problem when exporting Oracle data on LONG and LOB 5779 types. Thanks to Stephane Schildknecht for report and test. 5780 - Add more precision on NUMBER type conversion 5781 - Add conversion of type LONG, LOB, FILE 5782 - Fix a problem when extracting data, sometime table could need to be 5783 prefixed by the schema name. 5784 - Fix output of Oracle data extraction. It now require a call to 5785 function export_data(). 5786 57872001 06 27 - Version 1.4 5788 5789 - Add online Oracle data extraction and insertion into PG database. 5790 - Data export as insert statement (type => DATA) 5791 - Data export as copy from stdin statement (type => COPY) 5792 57932001 06 20 - Version 1.3 5794 5795 - Grant/privilege extraction are now done separatly with option 5796 type=>'GRANT' 5797 - Sequence extraction with the option type=>'SEQUENCE' 5798 - Trigger extraction with the option type=>'TRIGGER' 5799 - Function extraction with the option type=>'FUNCTION' and 5800 type=>'PROCEDURE' 5801 - Complete rewrite of the foreign key extraction 5802 - Fix incorrect type translation and many other bug fix 5803 - Add schema only extraction by option schema => 'MYSCHEM' 5804 58052001 05 11 - Version 1.2 5806 5807 - Views extraction is now really done with the option type=>'VIEW' 5808 - Add indexes extraction on tables. 5809 - Changes name of constraints, default is now used. 5810 - Add debug printing to see that the process is running :-) 5811 - Add extraction of only required tablename. 5812 - Add extraction of only n to n table indice. Indices of extraction 5813 can be obtained with the option showtableid set to 1. 5814 - Fix print of NOT NULL field. 5815 - Complete rewrite of the grant extraction 5816 - Complete rewrite of most things 5817 58182001 05 09 - Version 1.1 5819 5820 - Add table grant extraction based on group. 5821 Oracle ROLES are exported as groups in PG 5822 58232001 05 09 - Initial version 1.0 5824 5825------------------------------------------------------------------------------ 5826 5827Special thanks to Ali Pouya for documentation review. All my recognition 5828to Ali Pouya and Olivier Mazain for their great work in the package and 5829function export. Thanks to Jean-Paul Argudo for the time spent to heavily 5830testing Ora2Pg. 5831 5832Special thanks to Josian Larcheveque and Stephane Silly as Oracle DBA 5833and their "patience". 5834 5835Special Thanks to Dominique Legendre for his help on Spatial support and 5836all the tests performed. 5837 5838Many thanks for all congratulation message, idea and bug report+fix I received. 5839 5840Very special thanks to Jean-Paul Argudo that represent Ora2Pg at Linux Solution Paris 2005. 5841 5842Gilles DAROLD <gilles (at) darold (dot) net> 5843 5844