Migrate Oracle functions and procedures that have more than 100 arguments to PostgreSQL

Created by Srinivas Potlachervoo (AWS)

Environment: PoC or pilot	Source: Oracle	Target: PostgreSQL
R Type: Replatform	Workload: Open-source; Oracle	Technologies: Databases; Migration
AWS services: Amazon RDS; Amazon Aurora

Summary

This pattern shows how to migrate Oracle Database functions and procedures that have more than 100 arguments to PostgreSQL. For example, you can use this pattern to migrate Oracle functions and procedures to one of the following PostgreSQL-compatible AWS database services:

• Amazon Relational Database Service (Amazon RDS) for PostgreSQL

• Amazon Aurora PostgreSQL-Compatible Edition

PostgreSQL doesn’t support functions or procedures that have more than 100 arguments. As a workaround, you can define a new data type that has type fields that match the source function’s arguments. Then, you can create and run a PL/pgSQL function that uses the custom data type as an argument.

Prerequisites and limitations

Prerequisites 

• An active AWS account

• An Amazon RDS Oracle database (DB) instance

• An Amazon RDS for PostgreSQL DB instance or an Aurora PostgreSQL-Compatible DB instance

Product versions

• Amazon RDS Oracle DB instance versions 10.2 and later

• Amazon RDS PostgreSQL DB instance versions 9.4 and later, or Aurora PostgreSQL-Compatible DB instance versions 9.4 and later

• Oracle SQL Developer version 18 and later

• pgAdmin version 4 and later

Architecture

Source technology stack  

• Amazon RDS Oracle DB instance versions 10.2 and later

Target technology stack  

• Amazon RDS PostgreSQL DB instance versions 9.4 and later, or Aurora PostgreSQL-Compatible DB instance versions 9.4 and later

Tools

AWS services

• Amazon Relational Database Service (Amazon RDS) for PostgreSQL helps you set up, operate, and scale a PostgreSQL relational database in the AWS Cloud.

• Amazon Aurora PostgreSQL-Compatible Edition is a fully managed, ACID-compliant relational database engine that helps you set up, operate, and scale PostgreSQL deployments.

Other services

• Oracle SQL Developer is an integrated development environment that simplifies the development and management of Oracle databases in both traditional and cloud-based deployments.

• pgAdmin is an open-source management tool for PostgreSQL. It provides a graphical interface that helps you create, maintain, and use database objects.

Best practices

Make sure that the data type that you create matches the type fields that are included in the source Oracle function or procedure.

Epics

Run an Oracle function or procedure that has more than 100 arguments

Task	Description	Skills required
Create or identify an existing Oracle/PLSQL function or procedure that has more than 100 arguments.	Create an Oracle/PLSQL function or procedure that has more than 100 arguments. -or- Identify an existing Oracle/PLSQL function or procedure that has more than 100 arguments. For more information, see sections 14.7 CREATE FUNCTION Statement and 14.11 CREATE PROCEDURE Statement in the Oracle Database documentation.	Oracle/PLSQL knowledge
Compile the Oracle/PLSQL function or procedure.	Compile the Oracle/PLSQL function or procedure. For more information, see Compiling a function in the Oracle Database documentation.	Oracle/PLSQL knowledge
Run the Oracle/PLSQL function.	Run the Oracle/PLSQL function or procedure. Then, save the output.	Oracle/PLSQL knowledge

Define a new data type that matches the source function's or procedure's arguments

Task	Description	Skills required
Define a new data type in PostgreSQL.	Define a new data type in PostgreSQL that includes all of the same fields that appear in the source Oracle function’s or procedure’s arguments. For more information, see CREATE TYPE in the PostgreSQL documentation.	PostgreSQL PL/pgSQL knowledge

Create a PostgreSQL function that includes the new TYPE argument

Task	Description	Skills required
Create a PostgreSQL function that includes the new data type.	Create a PostgreSQL function that includes the new TYPE argument. To review an example function, see the Additional information section of this pattern.	PostgreSQL PL/pgSQL knowledge
Compile the PostgreSQL function.	Compile the function in PostgreSQL. If the new data type fields match the source function’s or procedure’s arguments, then the function successfully compiles.	PostgreSQL PL/pgSQL knowledge
Run the PostgreSQL function.	Run the PostgreSQL function.	PostgreSQL PL/pgSQL knowledge

Troubleshooting

Issue	Solution
The function returns the following error: ERROR: syntax error near “<statement>”	Make sure that all of the function’s statements end with a semicolon (;).
The function returns the following error: ERROR: “<variable>” is not a known variable	Make sure that the variable that’s used in the function body is listed within the function’s DECLARE section.

Related resources

• Working with Amazon Aurora PostgreSQL (Amazon Aurora User Guide for Aurora)

• CREATE TYPE (PostgreSQL documentation)

Additional information

Example PostgreSQL function that includes a TYPE argument

CREATE OR REPLACE FUNCTION test_proc_new
(
    IN p_rec type_test_proc_args
) 
RETURNS void
AS
$BODY$
BEGIN

    /*
    **************
    The body would contain code to process the input values.
    For our testing, we will display couple of values.
    ***************
    */
    RAISE NOTICE USING MESSAGE = CONCAT_WS('', 'p_acct_id: ', p_rec.p_acct_id);
    RAISE NOTICE USING MESSAGE = CONCAT_WS('', 'p_ord_id: ', p_rec.p_ord_id);
    RAISE NOTICE USING MESSAGE = CONCAT_WS('', 'p_ord_date: ', p_rec.p_ord_date);
   
END;
$BODY$
LANGUAGE plpgsql 
COST 100;