Migrate Oracle SERIALLY_REUSABLE pragma packages into PostgreSQL - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resourcesAdditional information

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

Migration architecture

On-premises Oracle DB data going to AWS using AWS SCT, .sql files, manual conversion, to PostgreSQL.

Tools

AWS services

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

TaskDescriptionSkills 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

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()