Contents
Chapter 5 Migrating PL/SQL
This chapter explains how to migrate Oracle database PL/SQL. Note that in this document, PL/SQL refers to the language to be migrated to PostgreSQL PL/pgSQL.
5.1 Notes on Migrating from PL/SQL to PL/pgSQL
This section provides notes on migration from PL/SQL to PL/pgSQL.
5.1.1 Transaction Control
PL/pgSQL does not allow transaction control within a process. Terminate a procedure whenever a transaction is terminated in the Oracle database and execute a transaction control statement from the application.
5.2 Basic Elements
This section explains how to migrate the basic elements of PL/SQL.
5.2.1 Migrating Data Types
The table below lists the PostgreSQL data types that correspond to data types unique to PL/SQL.
Data type correspondence with PL/SQL
- Character
|Oracle database Data type|Remarks|Migratability|PostgreSQL Data type|Remarks| |:---|:---|:---:|:---|:---| | STRING | The number of bytes or number of characters can be specified. | MR | varchar | Only the number of characters can be specified. |
- Numeric
|Oracle database Data type|Remarks|Migratability|PostgreSQL Data type|Remarks| |:---|:---|:---:|:---|:---| | BINARY_INTEGER | | M | integer | | | NATURAL | | M | integer | | | NATURALN | Type with NOT NULL constraints | MR | integer | Set "not null" constraints for variable declarations. | | PLS_INTEGER | | M | integer | | | POSITIVE | | M | integer | | | POSITIVEN | Type with NOT NULL constraints | MR | integer | Set "not null" constraints for variable declarations. | | SIGNTYPE | | M | smallint | | | SIMPLE_DOUBLE | Type with NOT NULL constraints | MR | double precision | Set "not null" constraints for variable declarations. | | SIMPLE_FLOAT | Type with NOT NULL constraints | MR | real | Set "not null" constraints for variable declarations. | | SIMPLE_INTEGER | Type with NOT NULL constraints | MR | integer | Set "not null" constraints for variable declarations. |
- Date and time
|Oracle database Data type|Remarks|Migratability|PostgreSQL Data type|Remarks| |:---|:---|:---:|:---|:---| | DSINTERVAL_UNCONSTRAINED | | N | | | | TIME_TZ_UNCONSTRAINED | | N | | | | TIME_UNCONSTRAINED | | N | | | | TIMESTAMP_LTZ_UNCONSTRAINED | | N | | | | TIMESTAMP_TZ_UNCONSTRAINED | | N | | | | TIMESTAMP_UNCONSTRAINED | | N | | | | YMINTERVAL_UNCONSTRAINED | | N | | |
- Other
|Oracle database Data type|Remarks|Migratability|PostgreSQL Data type|Remarks| |:---|:---|:---:|:---|:---| | BOOLEAN | | Y | boolean | | | RECORD | | M | Complex type | | | REF CURSOR (cursor variable) | | M | refcursor type | | | Subtype with constraints | | N | | | | Subtype that uses the base type within the same data type family | | N | | | | Unconstrained subtype | | N | | |
Y: Data type can be migrated as is
M: Modified data type can be migrated
N: Cannot be migrated
MR: Modified data type can be migrated with restrictions
See
Refer to "Data Types" for information on migrating data types other than those unique to PL/SQL.
5.2.2 Error-Related Elements
This section explains elements related to PL/SQL errors.
5.2.2.1 Predefined Exceptions
Description
A predefined exception is an error defined beforehand in an Oracle database.
Functional differences
- Oracle database
- Predefined exceptions can be used.
- PostgreSQL
- Predefined exceptions cannot be used. Use PostgreSQL error codes instead.
Migration procedure
Use the following procedure to perform migration:
- Identify where predefined exceptions are used.
- Refer to the table below and replace the values of predefined exceptions with PostgreSQL error codes.
|Predefined exception
(Oracle database)|Migratability|Corresponding PostgreSQL error code|
|:---|:---:|:---|
| ACCESS_INTO_NULL | N | Not generated |
| CASE_NOT_FOUND | Y | case_not_found |
| COLLECTION_IS_NULL | N | Not generated |
| CURSOR_ALREADY_OPEN | Y | duplicate_cursor |
| DUP_VAL_ON_INDEX | Y | unique_violation |
| INVALID_CURSOR | Y | invalid_cursor_name |
| INVALID_NUMBER | Y | invalid_text_representation |
| LOGIN_DENIED | Y | invalid_authorization_specification
invalid_password |
| NO_DATA_FOUND | Y | no_data_found |
| NO_DATA_NEEDED | N | Not generated |
| NOT_LOGGED_ON | N | Not generated |
| PROGRAM_ERROR | Y | internal_error |
| ROWTYPE_MISMATCH | N | Not generated |
| SELF_IS_NULL | N | Not generated |
| STORAGE_ERROR | Y | out_of_memory |
| SUBSCRIPT_BEYOND_COUNT | N | Not generated |
| SUBSCRIPT_OUTSIDE_LIMIT | N | Not generated |
| SYS_INVALID_ROWID | N | Not generated |
| TIMEOUT_ON_RESOURCE | N | Not generated |
| TOO_MANY_ROWS | Y | too_many_rows |
| VALUE_ERROR | Y | null_value_not_allowed
invalid_text_representation
string_data_right_truncation
invalid_parameter_value |
| ZERO_DIVIDE | Y | division_by_zero |
Y: Can be migrated
N: Cannot be migrated
Migration example
The example below shows how to migrate the VALUE_ERROR exception. Note that OR is used in the migration example to group error codes so that VALUE_ERROR corresponds to multiple PostgreSQL error codes.
Oracle database | PostgreSQL |
---|---|
|
|
5.2.2.2 SQLCODE
Description
SQLCODE returns the error code of an error.
Functional differences
- Oracle database
- SQLCODE can be specified to obtain an error code.
- PostgreSQL
- SQLCODE cannot be specified to obtain an error code.
Migration procedure
Use the following procedure to perform migration:
- Search for the keyword SQLCODE and identify where it is used.
- Change the portion that calls SQLCODE to SQLSTATE.
Migration example
The example below shows migration when the code of an error is displayed.
Oracle database | PostgreSQL |
---|---|
|
|
Note
Oracle databases and PostgreSQL have different error codes, so the set SQLCODE values and SQLSTATE values are different. Refer to "Appendix A. PostgreSQL Error Codes" in the PostgreSQL Documentation for information on the error codes to be defined in PostgreSQL.
5.2.2.3 EXCEPTION Declarations
Description
An EXCEPTION declaration defines an error.
Functional differences
- Oracle database
- EXCEPTION declarations can be used to define errors.
- PostgreSQL
- EXCEPTION declarations cannot be used.
Migration procedure
EXCEPTION declarations cannot be used, so specify the error number in a RAISE statement to achieve equivalent operation. Use the following procedure to perform migration:
- Search for the keyword EXCEPTION, identify where an EXCEPTION declaration is used, and check the error name.
- Search for the keyword RAISE and identify where the error created using the EXCEPTION declaration is used.
- Delete the error name from the RAISE statement and instead specify the error code using ERRCODE in a USING clause.
- Change the portion of the EXCEPTION clause where the error name is used to capture the error to SQLSTATE 'errCodeSpecifiedInStep3'.
- Delete the EXCEPTION declaration.
Migration example
The example below shows migration when a user-defined error is generated.
Oracle database | PostgreSQL |
---|---|
|
|
5.2.2.4 PRAGMA EXCEPTION_INIT and RAISE_APPLICATION_ERROR
Description
An EXCEPTION_INIT pragma associates a user-defined error name with an Oracle database error code. RAISE_APPLICATION_ERROR uses a user-defined error code and error message to issue an error.
Functional differences
- Oracle database
- EXCEPTION_INIT pragmas and RAISE_APPLICATION_ERROR statements can be used.
- PostgreSQL
- EXCEPTION_INIT pragmas and RAISE_APPLICATION_ERROR statements cannot be used.
Migration procedure
EXCEPTION_INIT pragmas and RAISE_APPLICATION_ERROR statements cannot be used, so specify an error message and error code in a RAISE statement to achieve equivalent operation. Use the following procedure to perform migration:
- Search for the keywords EXCEPTION and PRAGMA, and check for an EXCEPTION_INIT pragma and the specified error and error code.
- Search for the keyword RAISE_APPLICATION_ERROR and check where an error is used.
- Replace the error message and error code called by RAISE_APPLICATION_ERROR with syntax that uses a USING clause in RAISE.
- Change the portion of the EXCEPTION clause where the user-defined error name is used to capture the error to SQLSTATE 'errCodeSpecifiedInStep3'. To display the error message and error code in the EXCEPTION clause, use SQLERRM and SQLSTATE.
- Delete the EXCEPTION declaration and EXCEPTION INIT pragma.
Migration example
The example below shows migration when an EXCEPTION INIT pragma and RAISE APPLICATION ERROR statement are used.
Oracle database | PostgreSQL |
---|---|
|
|
Note
SQLERRM provided by PostgreSQL cannot specify an error code in its argument.
5.2.2.5 WHENEVER
Description
WHENEVER SQLERROR predefines the processing to be run when an error occurs in an SQL statement or PL/SQL. WHENEVER OSERROR predefines the processing to be run when an operating system error occurs.
Functional differences
- Oracle database
- WHENEVER can be used to predefine the processing to be run when an error occurs.
- PostgreSQL
- WHENEVER cannot be used.
Migration procedure
Use the following procedure to perform migration:
- Search for the keyword WHENEVER and identify where it is used.
- Replace WHENEVER SQLERROR EXIT FAILURE syntax or WHENEVER OSERROR EXIT FAILURE syntax with \set ON_ERROR_STOP ON.
Migration example
The example below shows migration when an active script that encounters an error is stopped.
Oracle database | PostgreSQL |
---|---|
|
|
Note
- WHENEVER SQLERROR and WHENEVER OSERROR are SQL*Plus features. Migrate them to the psql feature in PostgreSQL.
- Of the values that can be specified in WHENEVER, only EXIT FAILURE and CONTINUE NONE can be migrated. If CONTINUE NONE is specified, replace it with \set ON_ERROR_ROLLBACK ON.
5.2.3 Cursor-Related Elements
This section explains elements related to PL/SQL cursors.
5.2.3.1 %FOUND
Description
%FOUND obtains information on whether an SQL statement affected one or more rows.
Functional differences
- Oracle database
- %FOUND can be used.
- PostgreSQL
- %FOUND cannot be used. Use FOUND instead.
Migration procedure
Use the following procedure to perform migration with FOUND:
- When there is one implicit or explicit cursor
- Search for the keyword %FOUND and identify where it is used.
- Change the portion that calls cursorName%FOUND to FOUND.
- When there are multiple explicit cursors
- Search for the keyword %FOUND and identify where it is used.
- Using DECLARE, declare the same number of BOOLEAN variables as explicit cursors.
- Immediately after each FETCH statement, store the value obtained by FOUND in the variable declared in step 2.
- Replace the portion that calls cursorName%FOUND with the variable used in step 3.
Migration example
The example below shows migration when update of a row by an implicit cursor is checked.
Oracle database | PostgreSQL |
---|---|
|
|
Note
Statements in which %FOUND is determined to be NULL cannot be migrated. If SQL has not been executed at all, FOUND is set to FALSE, which is the same return value as when no row has been affected.
5.2.3.2 %NOTFOUND
Description
%NOTFOUND obtains information on whether an SQL statement affected no rows.
Functional differences
- Oracle database
- %NOTFOUND can be used.
- PostgreSQL
- %NOTFOUND cannot be used. Use NOT FOUND instead.
Migration procedure
Use the following procedure to perform migration:
- When there is one implicit or explicit cursor
- Search for the keyword %NOTFOUND and identify where it is used.
- Change the portion that calls cursorName%NOTFOUND to NOT FOUND.
- When there are multiple explicit cursors
- Search for the keyword %NOTFOUND and identify where it is used.
- Using DECLARE, declare the same number of BOOLEAN variables as explicit cursors.
- Immediately after each FETCH statement, store the value obtained by FOUND in the variable declared in step 2.
- Replace the portion that calls cursorName%NOTFOUND with negation of the variable used in step 3.
Migration example
The example below shows migration when multiple explicit cursors are used to repeat FETCH until there are no more rows in one of the tables.
Oracle database | PostgreSQL |
---|---|
|
|
Note
Statements in which %NOTFOUND is determined to be NULL cannot be migrated. If SQL has not been executed at all, FOUND is set to FALSE, which is the same return value as when no row has been affected.
5.2.3.3 %ROWCOUNT
Description
%ROWCOUNT indicates the number of rows processed by an SQL statement.
Functional differences
- Oracle database
- %ROWCOUNT can be used.
- PostgreSQL
- %ROWCOUNT cannot be used. Use ROW_COUNT instead.
Migration procedure
Use the following procedure to perform migration:
- Search for the keyword %ROWCOUNT and identify where it is used.
- Declare the variable that will store the value obtained by ROW_COUNT.
- Use GET DIAGNOSTICS immediately in front of %ROWCOUNT identified in step 1. It obtains ROW_COUNT and stores its value in the variable declared in step 2.
- Replace the portion that calls %ROWCOUNT with the variable used in step 3.
Migration example
The example below shows migration when the number of deleted rows is obtained.
Oracle database | PostgreSQL |
---|---|
|
|
Note
Statements in which %ROWCOUNT is determined to be NULL cannot be migrated. If SQL has not been executed at all, 0 is set.
5.2.3.4 REF CURSOR
Description
REF CURSOR is a data type that represents the cursor in Oracle databases.
Functional differences
- Oracle database
- REF CURSOR type variables can be defined.
- PostgreSQL
- REF CURSOR type variables cannot be defined. Use refcursor type variables instead.
Migration procedure
Use the following procedure to perform migration:
- Search for the keyword REF CURSOR and identify where it is used.
- Delete the REF CURSOR type definition and the portion where the cursor variable is declared using that type.
- Change the specification so that the cursor variable is declared using the refcursor type.
Migration example
The example below shows migration when the cursor variable is used to FETCH a row.
Oracle database | PostgreSQL |
---|---|
|
|
Note
The RETURN clause (specifies the return type of the cursor itself) cannot be specified in the refcursor type provided by PostgreSQL.
5.2.3.5 FORALL
Description
FORALL uses the changing value of the VALUES clause or WHERE clause to execute a single command multiple times.
Functional differences
- Oracle database
- FORALL statements can be used.
- PostgreSQL
- FORALL statements cannot be used.
Migration procedure
FORALL statements cannot be used, so replace them with FOR statements so that the same result is returned. Use the following procedure to perform migration:
- Search for the keyword FORALL and identify where it is used.
- Store the elements used by commands within FORALL in array type variables. In addition, delete Oracle database array definitions.
- Replace FORALL statements with FOR - LOOP statements.
- Replace portions that reference an array in the Oracle database with referencing of the array type variable defined in step 2. The portions changed in the migration example and details of the changes are as follows:
- Start of the loop: Change i_numL.FIRST to 1.
- End of the loop: Replace i_numL.LAST with ARRAY_LENGTH.
- Referencing of array elements: Change i_numL(i) to i_numL[i].
Migration example
The example below shows migration when FORALL is used to execute INSERT.
Oracle database | PostgreSQL |
---|---|
|
|
5.3 Migrating Functions
This section explains how to migrate PL/SQL functions.
Description
A stored function is a user-defined function that returns a value.
5.3.1 Defining Functions
Functional differences
- Oracle database
- A RETURN clause within a function prototype is specified as RETURN.
DECLARE does not need to be specified as the definition portion of a variable used within a function.
- A RETURN clause within a function prototype is specified as RETURN.
- PostgreSQL
- Use RETURNS to specify a RETURN clause within a function prototype.
DECLARE must be specified as the definition portion of a variable to be used within a function.
- Use RETURNS to specify a RETURN clause within a function prototype.
Migration procedure
Use the following procedure to perform migration:
- Search for the keywords CREATE and FUNCTION, and identify where user-defined functions are created.
- If an IN or OUT qualifier is specified in an argument, move it to the beginning of the parameters.
- Change RETURN within the function prototype to RETURNS.
- Change the AS clause to AS $$. (If the keyword is IS, change it to AS.)
- If a variable is defined, add the DECLARE keyword after $$.
- Delete the final slash (/) and specify $$ and a LANGUAGE clause.
Migration example
The example below shows migration when CREATE FUNCTION is used to define a function.
Oracle database | PostgreSQL |
---|---|
|
|
5.4 Migrating Procedures
This section explains how to migrate PL/SQL procedures.
Description
A stored procedure is a single procedure into which multiple processes have been grouped.
5.4.1 Defining Procedures
Functional differences
- Oracle database
- Procedures can be created.
- PostgreSQL
- Procedures cannot be created.
Migration procedure
Procedures cannot be created in PostgreSQL. Therefore, replace them with functions. Use the following procedure to perform migration:
- Search for the keywords CREATE and PROCEDURE, and identify where a procedure is defined.
- Replace the CREATE PROCEDURE statement with the CREATE FUNCTION statement.
- Change the AS clause to RETURNS VOID AS $$. (If the keyword is IS, change it to AS.)
- If a variable is defined, add the DECLARE keyword after $$.
- Delete the final slash (/) and specify $$ and a LANGUAGE clause.
Note
If the OUT or INOUT keywords are specified in the arguments, a different migration method must be used. Refer to "Defining Procedures That Return a Value".
Migration example
The example below shows migration when a procedure is defined.
Oracle database | PostgreSQL |
---|---|
|
|
5.4.2 Calling Procedures
Functional differences
- Oracle database
- A procedure can be called as a statement.
- PostgreSQL
- Procedures cannot be used. Instead, call the procedure as a function that does not return a value.
Migration procedure
Use the following procedure to perform migration:
- Identify where each procedure is called.
- Specify PERFORM in front of the procedure call.
Migration example
The example below shows migration when a procedure is called.
Oracle database | PostgreSQL |
---|---|
|
|
5.4.3 Defining Procedures That Return a Value
Functional differences
- Oracle database
- Procedures that return a value can be created.
- PostgreSQL
- Procedures that return a value cannot be created.
Migration procedure
Use the following procedure to perform migration:
- Search for the CREATE and PROCEDURE keywords, and identify where a procedure is defined.
- Confirm that the OUT or INOUT keyword is specified in the arguments.
- Replace the CREATE PROCEDURE statement with the CREATE FUNCTION statement.
- If the IN, OUT, or INOUT keyword is specified in the arguments, move it to the beginning of the arguments.
- Change the AS clause to AS $$. (If the keyword is IS, change it to AS.)
- If a variable is defined, add the DECLARE keyword after $$.
- Delete the final slash (/) and specify $$ and a LANGUAGE clause.
- If calling a function, call it without specifying arguments in the OUT parameter and store the return value in the variable. If there are multiple OUT parameters, use a SELECT INTO statement.
Migration example
The example below shows migration when the OUT parameter of CREATE PROCEDURE is used to define a procedure that returns a value.
Oracle database | PostgreSQL |
---|---|
|
|
See
Refer to "Defining Nested Procedures" for examples of migrating a call portion that uses a SELECT INTO statement.
5.4.4 Defining Nested Procedures
Functional differences
- Oracle database
- Nested procedures can be defined.
- PostgreSQL
- Nested procedures cannot be defined.
Migration procedure
Procedures must be replaced with functions, but functions cannot be nested in PostgreSQL. Therefore, define and call the functions separately. Use the following procedure to perform migration:
- Search for the CREATE and PROCEDURE keywords, and identify where a procedure is defined.
- If a PROCEDURE statement is defined in a DECLARE clause, regard it as a nested procedure.
- Check for variables that are used by both the procedure and the nested procedure.
- Replace a nested procedure (from PROCEDURE procedureName to END procedureName;) with a CREATE FUNCTION statement. Specify the variables you found in step 3 in the INOUT argument of CREATE FUNCTION.
- Replace the portion that calls the nested procedure with a SELECT INTO statement. Specify the common variables you found in step 3 in both the variables used for calling the function and the variables used for accepting the INTO clause.
Migration example
The example below shows migration when nested procedures are used and a variable is shared by a procedure and its call portion.
Oracle database | PostgreSQL |
---|---|
|
|
5.4.5 Defining Anonymous Code Blocks
Description
An anonymous code block generates and executes a temporary function within a procedural language.
Functional differences
- Oracle database
- Anonymous code blocks that are enclosed with (DECLARE) BEGIN to END can be executed.
- PostgreSQL
- PL/pgSQL blocks ((DECLARE) BEGIN to END) that are enclosed with DO $$ to $$ can be executed.
Migration procedure
Use the following procedure to perform migration:
- Search for the keywords DECLARE and BEGIN, and identify where an anonymous code block is defined.
- Specify DO $$ at the beginning of the anonymous code block.
- Delete the final slash (/) and specify $$.
Migration example
The example below shows migration when an anonymous code block is defined.
Oracle database | PostgreSQL |
---|---|
|
|
5.5 Migrating Packages
This section explains how to migrate PL/SQL packages.
Description
A package defines and contains procedures and functions as a single relationship group in the database.
Functional differences
- Oracle database
- Packages can be created.
- PostgreSQL
- Packages cannot be created.
Packages cannot be created in PostgreSQL, so define a schema with the same name as the package and define functions that have a relationship in the schema so that they are treated as a single group. In the following sections, the migration procedure is explained for each feature to be defined in a package.
5.5.1 Defining Functions Within a Package
Functional differences
- Oracle database
- Functions can be created within a package.
- PostgreSQL
- The package itself cannot be created.
Migration procedure
Use the following procedure to perform migration:
- Search for the keywords CREATE and PACKAGE, and identify where they are defined.
- Define a schema with the same name as the package.
- If a FUNCTION statement is specified within a CREATE PACKAGE BODY statement, define, within the schema created in step 2, the functions that were defined within the package.
Migration example
The example below shows migration when a package is defined and functions are created within that package.
Oracle database | PostgreSQL |
---|---|
|
|
See
Refer to "Defining Functions" for information on migrating FUNCTION statements within a package.
5.5.2 Defining Procedures Within a Package
Functional differences
- Oracle database
- Procedures can be created within a package.
- PostgreSQL
- The package itself cannot be created.
Migration procedure
Use the following procedure to perform migration:
- Search for the keywords CREATE and PACKAGE, and identify where they are defined.
- Define a schema with the same name as the package.
- If a PROCEDURE statement is specified within a CREATE PACKAGE BODY statement, migrate the procedures that were defined within the package to functions and define them within the schema created in step 2.
Migration example
The example below shows migration when a package is defined and procedures are created within that package.
Oracle database | PostgreSQL |
---|---|
|
|
See
Refer to "Defining Procedures" for information on migrating PROCEDURE statements within a package.
5.5.3 Sharing Variables Within a Package
Functional differences
- Oracle database
- Variables can be shared within a package.
- PostgreSQL
- A package cannot be created, so variables cannot be shared.
Migration procedure
Use a temporary table instead of variables within a package. Use the following procedure to perform migration:
- Search for the keywords CREATE and PACKAGE, and identify where they are defined.
- Check for variables defined directly in a package.
- Create a temporary table that defines the variables checked in step 2 in a column.
- Insert one record to the temporary table created in step 3. (The set value is the initial value specified within the package.)
- Replace the respective portions that reference a variable and set a variable with SQL statements.
- To reference a variable, use a SELECT INTO statement to store a value in the variable and then reference it. (A variable for referencing a variable must be defined separately.)
- To update a variable, use an UPDATE statement and update the target column.
Migration example
The example below shows migration when a package is defined and variables within the package are shared.
Oracle database | PostgreSQL |
---|---|
|
|