PL/SQL Conversion

PLSQL to PLPGSQL conversion

Automatic code conversion from Oracle PL/SQL to PostgreSQL PL/PGSQL is a work in progress in Ora2Pg and you will likely have manual work. The Perl code used for automatic conversion is stored in a specific Perl Module named Ora2Pg/PLSQL.pm. Feel free to modify/add your own code and send me patches. The main work is on function, procedure, package and package body headers and parameter rewrites.

• PLSQL_PGSQL

  Enable/disable PLSQL to PLPGSQL conversion. Enabled by default.

• NULL_EQUAL_EMPTY

  Ora2Pg can replace all conditions with a test on NULL by calling the coalesce() function to mimic the Oracle behavior where empty strings are considered equal to NULL.

(field1 IS NULL) is replaced by (coalesce(field1::text, '') = '')
(field2 IS NOT NULL) is replaced by (field2 IS NOT NULL AND field2::text <> '')

You might want this replacement to ensure your application will have the same behavior, but if you have control over your application, a better way is to transform empty strings into NULL because PostgreSQL differentiates between them.

• EMPTY_LOB_NULL

  Force empty_clob() and empty_blob() to be exported as NULL instead of an empty string for the first one and '\x' for the second. If NULL is allowed in your column, this might improve data export speed if you have lots of empty lobs. Default is to preserve the exact data from Oracle.

• PACKAGE_AS_SCHEMA

  If you don't want to export packages as schemas but as simple functions, you might also want to replace all calls to package_name.function_name. If you disable the PACKAGE_AS_SCHEMA directive then Ora2Pg will replace all calls to package_name.function_name() with package_name_function_name(). Default is to use a schema to emulate packages.

  The replacement will be done in all kinds of DDL or code that is parsed by the PLSQL to PLPGSQL converter. PLSQL_PGSQL must be enabled or -p used in command line.

• REWRITE_OUTER_JOIN

  Enable this directive if the rewrite of Oracle native syntax (+) of OUTER JOIN is broken. This will force Ora2Pg to not rewrite such code. Default is to try to rewrite simple forms of right outer joins for now.

• UUID_FUNCTION

  By default, Ora2Pg will convert calls to SYS_GUID() Oracle function with a call to uuid_generate_v4 from the uuid-ossp extension. You can redefine it to use the gen_random_uuid function from the pgcrypto extension by changing the function name. Default is uuid_generate_v4.

  Note that when RAW(16) or RAW(32) columns are found or when the RAW column has "SYS_GUID()" as default value, Ora2Pg will automatically translate the type of the column into uuid which might be the right translation in most cases. In this case data will be automatically migrated as PostgreSQL uuid data type provided by the "uuid-ossp" extension.

• FUNCTION_STABLE

  By default, Oracle functions are marked as STABLE as they can not modify data unless when used in PL/SQL with variable assignment or as conditional expressions. You can force Ora2Pg to create these functions as VOLATILE by disabling this configuration directive.

• COMMENT_COMMIT_ROLLBACK

  By default, calls to COMMIT/ROLLBACK are kept untouched by Ora2Pg to force the user to review the logic of the function. Once it is fixed in Oracle source code or you want to comment these calls, enable the following directive.

• COMMENT_SAVEPOINT

  It is common to see SAVEPOINT calls inside PL/SQL procedures together with a ROLLBACK TO savepoint_name. When COMMENT_COMMIT_ROLLBACK is enabled, you may want to also comment SAVEPOINT calls; in this case enable it.

• STRING_CONSTANT_REGEXP

  Ora2Pg replaces all string constants during the PL/SQL to PL/PGSQL translation. String constants are all text included between single quotes. If you have some string placeholders used in dynamic calls to queries, you can set a list of regexps to be temporarily replaced to not break the parser. For example:

STRING_CONSTANT_REGEXP         <placeholder value=".*">

The list of regexps must use the semi colon as separator.

• ALTERNATIVE_QUOTING_REGEXP

  To support the Alternative Quoting Mechanism ('Q' or 'q') for String Literals, set the regexp with the text capture to use to extract the text part. For example, with a variable declared as:

c_sample VARCHAR2(100 CHAR) := q'{This doesn't work.}';

the regexp to use must be:

ALTERNATIVE_QUOTING_REGEXP     q'{(.*)}'

Ora2pg will use the $$ delimiter; for the example the result will be:

c_sample varchar(100) := $$This doesn't work.$$;

The value of this configuration directive can be a list of regexps separated by a semicolon. The capture part (between parentheses) is mandatory in each regexp if you want to restore the string constant.

• USE_ORAFCE

  If you want to use functions defined in the Orafce library and prevent Ora2Pg from translating calls to these functions, enable this directive. The Orafce library can be found here: https://github.com/orafce/orafce

  By default, Ora2pg rewrites add_month(), add_year(), date_trunc() and to_char() functions, but you may prefer to use the orafce version of these functions that do not need any code transformation.

• AUTONOMOUS_TRANSACTION

  Enable translation of autonomous transactions into wrapper functions using dblink or pg_background extension. If you don't want to use this translation and just want the function to be exported as a normal one without the pragma call, disable this directive.