AWS prescriptive-guidance documentation change
Summary
Updated migration pattern documentation for Oracle SERIALLY_REUSABLE Pragma packages to PostgreSQL. Changes include: updated title specifying Amazon Aurora/RDS targets; replaced AWS SCT references with AWS DMS Schema Conversion; added 'Best practices' and 'Troubleshooting' sections; revised architecture diagram; updated prerequisites; restructured code examples with new implementation approach using 'reset_vars' parameter; modified related resources.
Security assessment
The changes focus on migration methodology updates and tooling changes without addressing security vulnerabilities, weaknesses, or incidents. The content discusses package state management and memory optimization during database migration, with no mention of security controls, vulnerabilities, or security-related configurations. The added 'Best practices' and 'Troubleshooting' sections cover functional behavior preservation, not security aspects.
Diff
diff --git a/prescriptive-guidance/latest/patterns/migrate-oracle-serially-reusable-pragma-packages-into-postgresql.md b/prescriptive-guidance/latest/patterns/migrate-oracle-serially-reusable-pragma-packages-into-postgresql.md index 3feca21d2..ce1ea6618 100644 --- a//prescriptive-guidance/latest/patterns/migrate-oracle-serially-reusable-pragma-packages-into-postgresql.md +++ b//prescriptive-guidance/latest/patterns/migrate-oracle-serially-reusable-pragma-packages-into-postgresql.md @@ -7 +7 @@ -SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resourcesAdditional information +SummaryPrerequisites and limitationsArchitectureToolsBest practicesEpicsTroubleshootingRelated resourcesAdditional information @@ -9 +9 @@ SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resourcesAddit -# Migrate Oracle SERIALLY_REUSABLE pragma packages into PostgreSQL +# Migrate Oracle SERIALLY_REUSABLE Pragma packages into Amazon Aurora or Amazon RDS for PostgreSQL @@ -15 +15 @@ SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resourcesAddit -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. +This pattern provides a step-by-step approach for migrating Oracle packages that use the SERIALLY_REUSABLE Pragma to Amazon Aurora PostgreSQL-Compatible Edition or Amazon RDS for PostgreSQL on AWS, while maintaining the original functionality. @@ -17 +17 @@ This pattern provides a step-by-step approach for migrating Oracle packages that -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](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_ExtensionPack.html). +The PRAGMA SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server (for example, a PL/SQL anonymous block or a stored procedure call through a database link). After this call, the storage for the package variables can be reused, reducing memory consumption. @@ -19 +19 @@ PostgreSQL doesn’t support the concept of packages and the SERIALLY_REUSABLE p -For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems046.htm) in the Oracle documentation. +PostgreSQL doesn't natively support the concept of SERIALLY_REUSABLE Pragma. To achieve equivalent functionality, this pattern uses a wrapper function approach combined with the AWS Database Migration Service (AWS DMS) [Schema Conversion (AWS DMS SC)](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SchemaConversion.html) feature to migrate the package structure. The example script provided demonstrates how to preserve the reset-on-each-call behavior in PostgreSQL. @@ -21 +21 @@ For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/ -## Prerequisites and limitations +For more information, see [SERIALLY_REUSABLE](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/SERIALLY_REUSABLE-pragma.html) Pragma in the Oracle documentation. @@ -23 +23 @@ For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/ -**Prerequisites** +## Prerequisites and limitations @@ -27,5 +27 @@ For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/ - * 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 - - + * Access to the AWS DMS Schema Conversion service @@ -32,0 +29 @@ For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/ + * An Amazon Aurora PostgreSQL-Compatible Edition database or an Amazon RDS for PostgreSQL database @@ -34,3 +31 @@ For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/ -**Product versions** - - * Oracle Database version 10g and later + * Oracle database version 10g or later @@ -45 +40 @@ For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/ - * Oracle Database on premises + * On-premises Oracle database @@ -54 +49 @@ For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/ - * AWS SCT + * AWS DMS Schema Conversion @@ -61 +56 @@ For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/ - + @@ -67 +62 @@ For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/ - * [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. + * [AWS Database Migration Service (AWS DMS) Schema Conversion](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SchemaConversion.html) makes database migrations between different types of databases more predictable. Use it to assess the complexity of your migration for your source data provider and to convert database schemas and code objects. You can then apply the converted code to your target database. @@ -82,0 +78,15 @@ For more information, see [SERIALLY_REUSABLE Pragma](https://docs.oracle.com/cd/ +## Best practices + + * Always use `reset_vars = 1` for top-level calls and `reset_vars = 0` for internal sub-calls. + + * Keep all default values in the `$init` function as a single source of truth. + + * Match Oracle variable names in PostgreSQL for traceability. + + * Validate by comparing Oracle `DBMS_OUTPUT` with PostgreSQL `RAISE NOTICE` output. + + * Test in separate PostgreSQL sessions to confirm variables reset between calls. + + + + @@ -87,4 +97,4 @@ 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 +Set up AWS DMS SC.| Configure AWS DMS connectivity to the source database. For more information, see [Converting database schemas using DMS Schema Conversion](https://docs.aws.amazon.com/dms/latest/userguide/getting-started.html).| DBA, Developer +Convert the script.| Use AWS DMS SC 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 DMS SC to **Single file per stage**. This configures AWS DMS to separate the .sql file into multiple .sql files based on object type.| DBA, Developer +Change the code.| Open the `init` function generated by AWS DMS SC, and change it as shown in the example in the _Additional information_ section. It will add a variable to achieve the functionality `reset_vars = 0`.| DBA, Developer @@ -92,0 +103,7 @@ Test the conversion.| Deploy the `init` function to the Aurora PostgreSQL-Compat +## Troubleshooting + +Issue| Solution +---|--- +Field does not exist error when accessing package variables.| Ensure that the `$init` function condition uses `IF v_need_init` or `reset_vars = 1` so that variables are always created on first access in a session. +Variables not resetting between top-level calls or resetting unexpectedly during internal sub-calls.| Use `reset_vars = 1` for direct top-level calls and pass `reset_vars = 0` for internal function-to-function calls. + @@ -95 +112 @@ Test the conversion.| Deploy the `init` function to the Aurora PostgreSQL-Compat - * [AWS Schema Conversion Tool](https://aws.amazon.com/dms/schema-conversion-tool/) + * [AWS DMS Schema Conversion](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SchemaConversion.html) @@ -109,4 +126,2 @@ Test the conversion.| Deploy the `init` function to the Aurora PostgreSQL-Compat - Source Oracle Code: - - CREATE OR REPLACE PACKAGE test_pkg_var - IS + -------Source Oracle Code: + CREATE OR REPLACE PACKAGE test_pkg_var IS @@ -114,5 +129,2 @@ Test the conversion.| Deploy the `init` function to the Aurora PostgreSQL-Compat - PROCEDURE function_1 - (test_id number); - PROCEDURE function_2 - (test_id number - ); + PROCEDURE function_1(test_id NUMBER); + PROCEDURE function_2(test_id NUMBER); @@ -120,3 +132,2 @@ Test the conversion.| Deploy the `init` function to the Aurora PostgreSQL-Compat - - CREATE OR REPLACE PACKAGE BODY test_pkg_var - IS + / + CREATE OR REPLACE PACKAGE BODY test_pkg_var IS @@ -124,9 +135,9 @@ Test the conversion.| Deploy the `init` function to the Aurora PostgreSQL-Compat - 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'; + v_char VARCHAR2(20) := 'DEFAULT_VALUE'; + v_num NUMBER := 123; + PROCEDURE function_2(test_id NUMBER) IS + BEGIN dbms_output.put_line('function_2 => v_char=' || v_char || ', v_num=' || v_num); END; + PROCEDURE function_1(test_id NUMBER) IS + BEGIN + dbms_output.put_line('function_1 => v_char=' || v_char || ', v_num=' || v_num); + v_char := 'MODIFIED_VALUE'; + dbms_output.put_line('function_1 => v_char after update=' || v_char); @@ -135,7 +145,0 @@ Test the conversion.| Deploy the `init` function to the Aurora PostgreSQL-Compat - - 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; @@ -143,6 +147,2 @@ Test the conversion.| Deploy the `init` function to the Aurora PostgreSQL-Compat - - Calling the above functions - - set serveroutput on - - + / + SET SERVEROUTPUT ON @@ -153,15 +151,4 @@ Test the conversion.| Deploy the `init` function to the Aurora PostgreSQL-Compat - - - 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 - + ------Target PostgreSQL Code: + CREATE SCHEMA IF NOT EXISTS testoracle; + CREATE OR REPLACE FUNCTION testoracle.test_pkg_var$init(reset_vars IN INTEGER DEFAULT 0) RETURNS void AS $BODY$ + DECLARE v_need_init BOOLEAN; @@ -169,59 +156,7 @@ Test the conversion.| Deploy the `init` function to the Aurora PostgreSQL-Compat - - 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);