Migrate Oracle SERIALLY_REUSABLE pragma packages into PostgreSQL

Created by Vinay Paladi (AWS)

Environment: PoC or pilot	Source: Oracle Database	Target: PostgreSQL
R Type: Re-architect	Workload: Oracle; Open-source	Technologies: Migration; Databases
AWS services: AWS SCT; Amazon Aurora

Summary

This pattern provides a step-by-step approach for migrating Oracle packages that are defined as SERIALLY_REUSABLE pragma to PostgreSQL on Amazon Web Services (AWS). This approach maintains the functionality of the SERIALLY_REUSABLE pragma.

PostgreSQL doesn’t support the concept of packages and the SERIALLY_REUSABLE pragma. To get similar functionality in PostgreSQL, you can create schemas for packages and deploy all the related objects (such as functions, procedures, and types) inside the schemas. To achieve the functionality of the SERIALLY_REUSABLE pragma, the example wrapper function script that’s provided in this pattern uses an AWS Schema Conversion Tool (AWS SCT) extension pack.

For more information, see SERIALLY_REUSABLE Pragma in the Oracle documentation.

Prerequisites and limitations

Prerequisites

• An active AWS account

• The latest version of AWS SCT and the required drivers

• An Amazon Aurora PostgreSQL-Compatible Edition database or an Amazon Relational Database Service (Amazon RDS) for PostgreSQL database

Product versions

• Oracle Database version 10g and later

Architecture

Source technology stack

• Oracle Database on premises

Target technology stack

• Aurora PostgreSQL-Compatible or Amazon RDS for PostgreSQL

• AWS SCT

Migration architecture

Tools

AWS services

• AWS Schema Conversion Tool (AWS SCT) supports heterogeneous database migrations by automatically converting the source database schema and a majority of the custom code to a format that’s compatible with the target database.

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

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

Other tools

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

Epics

Migrate the Oracle package by using AWS SCT

Task	Description	Skills required
Set up AWS SCT.	Configure AWS SCT connectivity to the source database. For more information, see Using Oracle Database as a source for AWS SCT.	DBA, Developer
Convert the script.	Use AWS SCT to convert the Oracle package by selecting the target database as Aurora PostgreSQL-Compatible.	DBA, Developer
Save the .sql files.	Before you save the .sql file, modify the Project Settings option in AWS SCT to Single file per stage. AWS SCT will separate the .sql file into multiple .sql files based on object type.	DBA, Developer
Change the code.	Open the init function generated by AWS SCT, and change it as shown in the example in the Additional information section. It will add a variable to achieve the functionality pg_serialize = 0.	DBA, Developer
Test the conversion.	Deploy the init function to the Aurora PostgreSQL-Compatible database, and test the results.	DBA, Developer

Related resources

• AWS Schema Conversion Tool

• Amazon RDS

• Amazon Aurora features

• SERIALLY_REUSABLE Pragma

Additional information

Source Oracle Code:

CREATE OR REPLACE PACKAGE test_pkg_var
IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE function_1
 (test_id number);
PROCEDURE function_2
 (test_id number
 );
END;

CREATE OR REPLACE PACKAGE BODY test_pkg_var
IS
PRAGMA SERIALLY_REUSABLE;
v_char VARCHAR2(20) := 'shared.airline';
v_num number := 123;

PROCEDURE function_1(test_id number)
IS
begin
dbms_output.put_line( 'v_char-'|| v_char);
dbms_output.put_line( 'v_num-'||v_num);
v_char:='test1';
function_2(0);
END;

PROCEDURE function_2(test_id number)
is
begin
dbms_output.put_line( 'v_char-'|| v_char);
dbms_output.put_line( 'v_num-'||v_num);
END;
END test_pkg_var;

Calling the above functions

set serveroutput on


EXEC test_pkg_var.function_1(1);


EXEC test_pkg_var.function_2(1);


Target Postgresql Code:


CREATE SCHEMA test_pkg_var;

CREATE OR REPLACE FUNCTION test_pkg_var.init(pg_serialize IN INTEGER DEFAULT 0)

RETURNS void
AS
$BODY$

DECLARE

BEGIN

if aws_oracle_ext.is_package_initialized( 'test_pkg_var' ) AND pg_serialize = 0

then

return;

end if;

PERFORM aws_oracle_ext.set_package_initialized( 'test_pkg_var' );

PERFORM aws_oracle_ext.set_package_variable( 'test_pkg_var', 'v_char', 'shared.airline.basecurrency'::CHARACTER

VARYING(100));

PERFORM aws_oracle_ext.set_package_variable('test_pkg_var', 'v_num', 123::integer);

END;

$BODY$

LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION test_pkg_var.function_1(pg_serialize int default 1)

RETURNS void
AS

$BODY$
DECLARE

BEGIN

PERFORM test_pkg_var.init(pg_serialize);

raise notice 'v_char%',aws_oracle_ext.get_package_variable( 'test_pkg_var', 'v_char');

raise notice 'v_num%',aws_oracle_ext.get_package_variable( 'test_pkg_var', 'v_num');

PERFORM aws_oracle_ext.set_package_variable( 'test_pkg_var', 'v_char', 'test1'::varchar);

PERFORM test_pkg_var.function_2(0);
END;

$BODY$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION test_pkg_var.function_2(IN pg_serialize integer default 1)

RETURNS void

AS

$BODY$

DECLARE

BEGIN

PERFORM test_pkg_var.init(pg_serialize);

raise notice 'v_char%',aws_oracle_ext.get_package_variable( 'test_pkg_var', 'v_char');

raise notice 'v_num%',aws_oracle_ext.get_package_variable( 'test_pkg_var', 'v_num');

END;
$BODY$
LANGUAGE plpgsql;


Calling the above functions

select test_pkg_var.function_1()

 select test_pkg_var.function_2()