# Migrate Oracle SERIALLY\$1REUSABLE pragma packages into PostgreSQL
<a name="migrate-oracle-serially-reusable-pragma-packages-into-postgresql"></a>

*Vinay Paladi, Amazon Web Services*

## Summary
<a name="migrate-oracle-serially-reusable-pragma-packages-into-postgresql-summary"></a>

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

PostgreSQL doesn’t support the concept of packages and the SERIALLY\$1REUSABLE 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\$1REUSABLE pragma, the example wrapper function script that’s provided in this pattern uses an [AWS Schema Conversion Tool (AWS SCT) extension pack](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_ExtensionPack.html).

For more information, see [SERIALLY\$1REUSABLE Pragma](https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems046.htm) in the Oracle documentation.

## Prerequisites and limitations
<a name="migrate-oracle-serially-reusable-pragma-packages-into-postgresql-prereqs"></a>

**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
<a name="migrate-oracle-serially-reusable-pragma-packages-into-postgresql-architecture"></a>

**Source technology stack**
+ Oracle Database on premises

**Target technology stack**
+ [Aurora PostgreSQL-Compatible](https://aws.amazon.com/rds/aurora/details/postgresql-details/) or Amazon RDS for PostgreSQL
+ AWS SCT

**Migration architecture**

![\[On-premises Oracle DB data going to AWS using AWS SCT, .sql files, manual conversion, to PostgreSQL.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/fe3c45d2-6ea4-43b5-adb1-18f068f126b9/images/2dc90708-e300-4251-9d12-de97b6588b72.png)


## Tools
<a name="migrate-oracle-serially-reusable-pragma-packages-into-postgresql-tools"></a>

**AWS services**
+ [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) 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](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html) 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) helps you set up, operate, and scale a PostgreSQL relational database in the AWS Cloud.

**Other tools**
+ [pgAdmin](https://www.pgadmin.org/) is an open-source management tool for PostgreSQL. It provides a graphical interface that helps you create, maintain, and use database objects.

## Epics
<a name="migrate-oracle-serially-reusable-pragma-packages-into-postgresql-epics"></a>

### Migrate the Oracle package by using AWS SCT
<a name="migrate-the-oracle-package-by-using-aws-sct"></a>


| 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](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Oracle.html). | 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
<a name="migrate-oracle-serially-reusable-pragma-packages-into-postgresql-resources"></a>
+ [AWS Schema Conversion Tool](https://aws.amazon.com/dms/schema-conversion-tool/)
+ [Amazon RDS](https://aws.amazon.com/rds/)
+ [Amazon Aurora features](https://aws.amazon.com/rds/aurora/postgresql-features/)
+ [SERIALLY\$1REUSABLE Pragma](https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/seriallyreusable_pragma.htm#LNPLS01346)

## Additional information
<a name="migrate-oracle-serially-reusable-pragma-packages-into-postgresql-additional"></a>

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