Migrar variables de OUT enlace de Oracle a una base de datos SQL Postgre

Creado por Bikash Chandra Rout () y Vinay Paladi () AWS AWS

Entorno: PoC o piloto	Origen: base de datos relacional	Objetivo: RDS /Aurora PostgreSQL
Tipo R: redefinir la plataforma	Carga de trabajo: Oracle	Tecnologías: bases de datos; migración
AWSservicios: Amazon Aurora; AmazonRDS; AWS SCT

Resumen

Este patrón muestra cómo migrar las variables de OUT enlace de Oracle Database a uno de los siguientes servicios de bases de datos SQL compatibles con AWS Postgre:

• Amazon Relational Database Service (RDSAmazon) para Postgre SQL

• Edición compatible con Postgre de SQL Amazon Aurora

Postgre SQL no admite variables de enlace. OUT Para obtener la misma funcionalidad en sus sentencias de Python, puede crear una SQL función PL/pg personalizada que utilice en su lugar las variables GET y SETdel paquete. Para aplicar estas variables, el script de función contenedora de ejemplo que se proporciona en este patrón utiliza un paquete de extensiones AWS Schema Conversion Tool (AWSSCT).

Nota: Si la instrucción EXECUTE IMMEDIATE de Oracle es una SELECT instrucción que puede devolver una fila como máximo, se recomienda hacer lo siguiente:

• Coloque las variables de enlace (define) OUT en la cláusula INTO

• Coloque las variables de enlace IN en la cláusula USING

Para obtener más información, consulte la EXECUTEIMMEDIATEdeclaración en la documentación de Oracle.

Requisitos previos y limitaciones

Requisitos previos 

• Una AWS cuenta activa 

• Una base de datos de origen de Oracle Database 10g (o más reciente) en un centro de datos local

• Una instancia de base de datos Amazon RDS for Postgre o una SQL instancia de base de datos Aurora compatible con SQL Postgre

Arquitectura

Pila de tecnología de origen

• Base de datos de Oracle Database 10g (o posterior) local 

Pila de tecnología de destino

• Una instancia de base de datos Amazon RDS for Postgre o una SQL instancia de base de datos Aurora compatible con SQL Postgre

Arquitectura de destino

El siguiente diagrama muestra un ejemplo de flujo de trabajo para migrar variables de enlace de Oracle Database a una base de datos compatible con OUT Postgre. SQL AWS

En el diagrama, se muestra el siguiente flujo de trabajo:

1. AWSSCTconvierte el esquema de la base de datos de origen y la mayoría del código personalizado a un formato compatible con la base de datos de destino compatible con Postgre. SQL AWS

2. La función PL/pg marca todos los objetos de la base de datos que no se puedan convertir automáticamente. SQL A continuación, los objetos marcados se convierten manualmente para completar la migración.

Herramientas

• Amazon Aurora Postgre SQL -Compatible Edition es un motor de base de datos relacional totalmente administrado y ACID compatible que le ayuda a configurar, operar y escalar las implementaciones de Postgre. SQL

• Amazon Relational Database Service (RDSAmazon) para SQL Postgre le ayuda a configurar, operar y escalar una base de datos relacional de SQL Postgre en la nube. AWS

• AWSSchema Conversion Tool (AWSSCT) admite migraciones de bases de datos heterogéneas al convertir automáticamente el esquema de la base de datos de origen y la mayoría del código personalizado a un formato compatible con la base de datos de destino.

• pgAdmines una herramienta de administración de código abierto para Postgre. SQL Proporciona una interfaz gráfica que permite crear, mantener y utilizar objetos de bases de datos.

Epics

Migre las variables OUT de enlace de Oracle mediante una función PL/pg personalizada y SQL AWS SCT

Tarea	Descripción	Habilidades requeridas
Conéctese a su base de datos compatible con PostgreSQL. AWS	Una vez creada la instancia de base de datos, puede utilizar cualquier aplicación SQL cliente estándar para conectarse a una base de datos del clúster de base de datos. Por ejemplo, puede utilizarla pgAdminpara conectarse a su instancia de base de datos. Para obtener más información, consulte cualquiera de los temas siguientes: Conexión a una RDS instancia de base de datos de Amazon en la Guía del RDS usuario de Amazon Conexión a un clúster de base de datos de Amazon Aurora en la Guía del usuario de Amazon Aurora	Ingeniero de migraciones
Añada el ejemplo del script de la función contenedora de este patrón al esquema principal de la base de datos de destino.	Copie el ejemplo del script de la función SQL contenedora PL/pg de la sección de información adicional de este patrón. A continuación, añada la función al esquema principal de la base de datos de destino. Para obtener más información, consulte la documentación de CREATEFUNCTIONPostgre. SQL	Ingeniero de migraciones
(Opcional) Actualice la ruta de búsqueda en el esquema principal de la base de datos de destino para que incluya el esquema Test_PG.	Para mejorar el rendimiento, puede actualizar la variable SQL search_path de Postgre para que incluya el nombre del esquema Test_PG. Si incluye el nombre del esquema en la ruta de búsqueda, no necesitará especificarlo cada vez que llame a la función PL/pg. SQL Para obtener más información, consulte la sección 5.9.3 La ruta de búsqueda del esquema en la documentación de Postgre. SQL	Ingeniero de migraciones

Recursos relacionados

• AWSSchema Conversion Tool

• OUTvincular variables (documentación de Oracle)

• Mejore el rendimiento de las SQL consultas mediante el uso de variables de enlace (Oracle Blog)

Información adicional

Ejemplo de función PL/pg SQL

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