Migrate Oracle OUT bind variables to a PostgreSQL database

Created by Bikash Chandra Rout (AWS) and Vinay Paladi (AWS)

Environment: PoC or pilot	Source: Database Relational	Target: RDS/Aurora Postgresql
R Type: Replatform	Workload: Oracle	Technologies: Databases; Migration
AWS services: Amazon Aurora; Amazon RDS; AWS SCT

Summary

This pattern shows how to migrate Oracle Database OUT bind variables to either one of the following PostgreSQL-compatible AWS database services:

• Amazon Relational Database Service (Amazon RDS) for PostgreSQL

• Amazon Aurora PostgreSQL-Compatible Edition

PostgreSQL doesn’t support OUT bind variables. To get the same functionality in your Python statements, you can create a custom PL/pgSQL function that uses the GET and SET package variables instead. To apply these variables, the example wrapper function script that’s provided in this pattern uses an AWS Schema Conversion Tool (AWS SCT) extension pack.

Note: If the Oracle EXECUTE IMMEDIATE statement is a SELECT statement that can return one row at most, it’s a best practice to do the following:

• Put OUT bind variables (defines) in the INTO clause

• Put IN bind variables in the USING clause

For more information, see EXECUTE IMMEDIATE statement in the Oracle documentation.

Prerequisites and limitations

Prerequisites  

• An active AWS account 

• An Oracle Database 10g (or newer) source database in an on-premises data center

• An Amazon RDS for PostgreSQL DB instance or an Aurora PostgreSQL-Compatible DB instance

Architecture

Source technology stack

• On-premises Oracle Database 10g (or newer) database 

Target technology stack

• An Amazon RDS for PostgreSQL DB instance or an Aurora PostgreSQL-Compatible DB instance

Target architecture

The following diagram shows an example workflow for migrating Oracle Database OUT bind variables to a PostgreSQL-compatible AWS database.

The diagram shows the following workflow:

1. AWS SCT converts the source database schema and a majority of the custom code to a format compatible with the target PostgreSQL-compatible AWS database.

2. Any database objects that can’t be converted automatically are flagged by the PL/pgSQL function. Objects that are flagged are then manually converted to complete the migration.

Tools

• 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.

• 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 compatible with the target database.

• 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 Oracle OUT bind variables by using a custom PL/pgSQL function and AWS SCT

Task	Description	Skills required
Connect to your PostgreSQL-compatible AWS database.	After you’ve created your DB instance, you can use any standard SQL client application to connect to a database in your DB cluster. For example, you can use pgAdmin to connect to your DB instance. For more information, see either of the following: Connecting to an Amazon RDS DB instance in the Amazon RDS User Guide Connecting to an Amazon Aurora DB cluster in the Amazon Aurora User Guide	Migration engineer
Add the example wrapper function script from this pattern to the target database's main schema.	Copy the example PL/pgSQL wrapper function script from the Additional information section of this pattern. Then, add the function to the target database's main schema. For more information, see CREATE FUNCTION in the PostgreSQL documentation.	Migration engineer
(Optional) Update the search path in the target database's main schema so that includes the Test_pg schema.	To improve performance, you can update the PostgreSQL search_path variable so that it includes the Test_pg schema name. If you include the schema name in the search path, you don’t need to specify the name whenever you call the PL/pgSQL function. For more information, see section 5.9.3 The Schema Search Path in the PostgreSQL documentation.	Migration engineer

Related resources

• AWS Schema Conversion Tool

• OUT bind variables (Oracle documentation)

• Improve SQL query performance by using bind variables (Oracle Blog)

Additional information

Example PL/pgSQL function

/* Oracle */

CREATE or replace PROCEDURE test_pg.calc_stats_new1 (
                                              a NUMBER,
                                              b NUMBER,
                                              result out NUMBER
                                            )                                                                                   
IS
BEGIN
result:=a+b;
END;
/
/* Testing */
set serveroutput on 
DECLARE
  a NUMBER := 4;
  b NUMBER := 7;
  plsql_block VARCHAR2(100);
  output number;
BEGIN
  plsql_block := 'BEGIN test_pg.calc_stats_new1(:a, :b,:output); END;';
  EXECUTE IMMEDIATE plsql_block USING a, b,out output;  -- calc_stats(a, a, b, a)
  DBMS_OUTPUT.PUT_LINE('output:'||output);
END;

output:11


PL/SQL procedure successfully completed.

--Postgres--

/* Example : 1 */
CREATE OR REPLACE FUNCTION test_pg.calc_stats_new1(
                                                    w integer,
                                                    x integer
                                                  )
RETURNS integer
AS 
$BODY$
begin
      return w + x ;
end;
$BODY$
LANGUAGE  plpgsql;


CREATE OR REPLACE FUNCTION aws_oracle_ext.set_package_variable(
                                                                package_name name,
                                                                variable_name name,
                                                                variable_value anyelement
                                                              )
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
begin
    perform set_config
      ( format( '%s.%s',package_name, variable_name )
      , variable_value::text
      , false );
  end;
$BODY$;

CREATE OR REPLACE FUNCTION aws_oracle_ext.get_package_variable_record(
                                                                      package_name name,
                                                                      record_name name
                                                                    )
RETURNS text
LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
begin
    execute 'select ' || package_name || '$Init()';

    return aws_oracle_ext.get_package_variable
        ( 
         package_name := package_name
        , variable_name := record_name || '$REC' );
end;
$BODY$;

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

/* callable for 1st Example */

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 ; 
$$

/*In above Postgres example we have set the value of v_output using v_output_l in the dynamic anonymous block to mimic the 
  behaviour of oracle out-bind variable .*/

--Postgres Example : 2 --
CREATE OR REPLACE FUNCTION test_pg.calc_stats_new2(
 w integer,
 x integer,
inout status text,
 out result integer)
AS
$BODY$
DECLARE
begin
result :=  w + x ;
status := 'ok';
end;
$BODY$
LANGUAGE  plpgsql;

/* callable for 2nd Example */
DO $$ 
declare
v_sql text;
v_output_loc int; 
v_staus text:= 'no';
a integer :=1;
b integer :=2;
BEGIN 
perform  test_pg.init();
execute 'do $a$ declare v_output_l int; v_status_l text; begin select * from test_pg.calc_stats_new2('||a||','||b||','''||v_staus||''') into v_status_l,v_output_l;
PERFORM aws_oracle_ext.set_package_variable(''test_pg'', ''v_output'', v_output_l) ; 
PERFORM aws_oracle_ext.set_package_variable(''test_pg'', ''v_status'', v_status_l) ; 
end; $a$'  ; 
v_output_loc := aws_oracle_ext.get_package_variable('test_pg', 'v_output');
v_staus := aws_oracle_ext.get_package_variable('test_pg', 'v_status');
raise notice 'v_output_loc %',v_output_loc; 
raise notice 'v_staus %',v_staus; 
END ; 
$$