Handle anonymous blocks in Dynamic SQL statements in Aurora PostgreSQL

Created by anuradha chintha (AWS)

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

Summary

This pattern shows you how to avoid the error that you get when handling anonymous blocks in Dynamic SQL statements. You receive an error message when you use the AWS Schema Conversion Tool to convert an Oracle database to an Aurora PostgreSQL-Compatible Edition database. To avoid the error, you must know the value of an OUT bind variable, but you can’t know the value of an OUT bind variable until after you run the SQL statement. The error results from the AWS Schema Conversion Tool (AWS SCT) not understanding the logic inside the Dynamic SQL statement. AWS SCT can’t convert the dynamic SQL statement in PL/SQL code (that is, functions, procedures, and packages).

Prerequisites and limitations

Prerequisites

• Active AWS account

• Aurora PostgreSQL database (DB) instance

• Amazon Relational Database Service (Amazon RDS) for Oracle DB instance

• PostgreSQL interactive terminal (psql)

• SQL *Plus

• AWS_ORACLE_EXT schema (part of the AWS SCT extension pack) in your target database

• Latest version of AWS Schema Conversion Tool (AWS SCT) and its required drivers

Architecture

Source technology stack

• On-premises Oracle Database 10g and later version

Target technology stack

• Amazon Aurora PostgreSQL

• Amazon RDS for PostgreSQL

• AWS Schema Conversion Tool (AWS SCT)

Migration architecture

The following diagram shows how to use AWS SCT and Oracle OUT bind variables to scan your application code for embedded SQL statements and convert the code to a compatible format that an Aurora database can use.

The diagram shows the following workflow:

1. Generate an AWS SCT report for the source database by using Aurora PostgreSQL as the target database.

2. Identify the anonymous block in the Dynamic SQL code block (for which AWS SCT raised the error).

3. Convert the code block manually and deploy the code on a target database.

Tools

AWS services

• 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 Oracle helps you set up, operate, and scale an Oracle relational database in the AWS Cloud.

• AWS Schema Conversion Tool (AWS SCT) helps you make heterogeneous database migrations predictable by automatically converting the source database schema and a majority of the database code objects to a format compatible with the target database.

Other tools

• pgAdmin enables you to connect to and interact with your database server.

• Oracle SQL Developer is an integrated development environment that you can use to develop and manage databases in Oracle Database. You can use either SQL *Plus or Oracle SQL Developer for this pattern.

Epics

Configure the Oracle source database

Task	Description	Skills required
Create an Oracle instance on Amazon RDS or Amazon EC2.	To create an Oracle DB instance on Amazon RDS, see Creating an Oracle DB instance and connecting to a database on an Oracle DB instance in the Amazon RDS documentation. To create an Oracle DB instance on Amazon Elastic Compute Cloud (Amazon EC2), see Amazon EC2 for Oracle in the AWS Prescriptive Guidance documentation.	DBA
Create a database schema and objects for migration.	You can use Amazon Cloud Directory to create a database schema. For more information, see Create a Schema in the Cloud Directory documentation.	DBA
Configure inbound and outbound security groups.	To create and configure security groups, see Controlling access with security groups in the Amazon RDS documentation.	DBA
Confirm that the database is running.	To check the status of your database, see Viewing Amazon RDS events in the Amazon RDS documentation.	DBA

Configure the target Aurora PostgreSQL database

Task	Description	Skills required
Create an Aurora PostgreSQL instance in Amazon RDS.	To create an Aurora PostgreSQL instance, see Creating a DB cluster and connecting to a database on an Aurora PostgreSQL DB cluster in the Amazon RDS documentation.	DBA
Configure an inbound and outbound security group.	To create and configure security groups, see Provide access to the DB cluster in the VPC by creating a security group in the Aurora documentation.	DBA
Confirm that the Aurora PostgreSQL database is running.	To check the status of your database, see Viewing Amazon RDS events in the Aurora documentation.	DBA

Set up AWS SCT

Task	Description	Skills required
Connect AWS SCT to the source database.	To connect AWS SCT to your source database, see Connecting to PostgreSQL as a source in the AWS SCT documentation.	DBA
Connect AWS SCT to the target database.	To connect AWS SCT to your target database, see the What is the AWS Schema Conversion Tool? in the AWS Schema Conversion Tool User Guide.	DBA
Convert the database schema in AWS SCT and save the automated converted code as a SQL file.	To save AWS SCT converted files, see Saving and applying your converted schema in AWS SCT in the AWS Schema Conversion Tool User Guide.	DBA

Migrate the code

Task	Description	Skills required
Get the SQL file for manual conversion.	In the AWS SCT converted file, pull the SQL file that requires manual conversion.	DBA
Update the script.	Manually update the SQL file.	DBA

Related resources

• Amazon RDS

• Amazon Aurora Features

Additional information

The following example code shows how to configure the Oracle source database:

CREATE or replace PROCEDURE calc_stats_new1 (
  a NUMBER,
  b NUMBER,
  result out NUMBER)
IS
BEGIN
result:=a+b;
END;
/

set serveroutput on ;
 
DECLARE
  a NUMBER := 4;
  b NUMBER := 7;
  plsql_block VARCHAR2(100);
  output number;
BEGIN
  plsql_block := 'BEGIN calc_stats_new1(:a, :b,:output); END;';
  EXECUTE IMMEDIATE plsql_block USING a, b,out output;  
  DBMS_OUTPUT.PUT_LINE('output:'||output);
 
END;

The following example code shows how to configure the target Aurora PostgreSQL database:

 w integer,
 x integer)
RETURNS integer
AS
$BODY$
DECLARE
begin
return w + x ;
end;
$BODY$
LANGUAGE  plpgsql;
 
 
CREATE OR REPLACE FUNCTION test_pg.init()
RETURNS void
AS
$BODY$
BEGIN
if aws_oracle_ext.is_package_initialized
      ('test_pg' ) then
      return;
    end if;
    perform aws_oracle_ext.set_package_initialized
      ('test_pg' );
 
PERFORM aws_oracle_ext.set_package_variable('test_pg', 'v_output', NULL::INTEGER);
PERFORM aws_oracle_ext.set_package_variable('test_pg', 'v_status', NULL::text);
END;
$BODY$
LANGUAGE  plpgsql;
 

DO $$ 
declare
v_sql text;
v_output_loc int; 
a integer :=1;
b integer :=2;
BEGIN 
perform  test_pg.init();
--raise notice 'v_sql %',v_sql;
execute 'do $a$ declare v_output_l int; begin select * from test_pg.calc_stats_new1('||a||','||b||') into v_output_l;
PERFORM aws_oracle_ext.set_package_variable(''test_pg'', ''v_output'', v_output_l) ; end; $a$'  ; 
v_output_loc := aws_oracle_ext.get_package_variable('test_pg', 'v_output');
raise notice 'v_output_loc %',v_output_loc; 
END ; 
$$