Migrieren Sie Oracle OUT-Bind-Variablen in eine PostgreSQL-Datenbank

Erstellt von Bikash Chandra Rout (AWS) und Vinay Paladi (AWS)

Umgebung: PoC oder Pilotprojekt	Quelle: Database Relational	Ziel: RDS/Aurora Postgresql
R-Typ: Replatform	Arbeitslast: Oracle	Technologien: Datenbanken; Migration
AWS-Dienste: Amazon Aurora; Amazon RDS; AWS SCT

Übersicht

Dieses Muster zeigt, wie Oracle OUT Database-Bindungsvariablen zu einem der folgenden PostgreSQL-kompatiblen AWS-Datenbankservices migriert werden:

• Amazon Relational Database Service (Amazon RDS) für PostgreSQL

• Amazon Aurora PostgreSQL-Compatible Edition

PostgreSQL unterstützt keine OUT Bind-Variablen. Um dieselbe Funktionalität in Ihren Python-Anweisungen zu erhalten, können Sie eine benutzerdefinierte PL/pgSQL-Funktion erstellen, die stattdessen die Paketvariablen GET und SETverwendet. Um diese Variablen anzuwenden, verwendet das in diesem Muster bereitgestellte Beispiel-Wrapper-Funktionsskript ein AWS Schema Conversion Tool (AWS SCT) -Erweiterungspaket.

Hinweis: Wenn es sich bei der EXECUTE IMMEDIATE SELECT Oracle-Anweisung um eine Anweisung handelt, die höchstens eine Zeile zurückgeben kann, hat es sich bewährt, wie folgt vorzugehen:

• Fügen Sie OUT Bind-Variablen (Defines) in die INTO Klausel ein

• Fügen Sie IN Bind-Variablen in die USING Klausel ein

Weitere Informationen finden Sie in der Oracle-Dokumentation unter EXECUTE IMMEDIATE-Anweisung.

Voraussetzungen und Einschränkungen

Voraussetzungen

• Ein aktives AWS-Konto 

• Eine Oracle Database 10g-Quelldatenbank (oder neuer) in einem lokalen Rechenzentrum

• Eine Amazon RDS for PostgreSQL PostgreSQL-DB-Instance oder eine Aurora PostgreSQL-kompatible DB-Instance

Architektur

Quelltechnologie-Stack

• Lokale Oracle Database 10g-Datenbank (oder neuer) 

Zieltechnologie-Stack

• Eine Amazon RDS for PostgreSQL PostgreSQL-DB-Instance oder eine Aurora PostgreSQL-kompatible DB-Instance

Zielarchitektur

Das folgende Diagramm zeigt einen Beispiel-Workflow für die Migration von Oracle OUT Database-Bindungsvariablen in eine PostgreSQL-kompatible AWS-Datenbank.

Das Diagramm zeigt den folgenden Workflow:

1. AWS SCT konvertiert das Quelldatenbankschema und einen Großteil des benutzerdefinierten Codes in ein Format, das mit der PostgreSQL-kompatiblen AWS-Zieldatenbank kompatibel ist.

2. Alle Datenbankobjekte, die nicht automatisch konvertiert werden können, werden von der PL/pgSQL-Funktion gekennzeichnet. Objekte, die markiert sind, werden dann manuell konvertiert, um die Migration abzuschließen.

Tools

• Amazon Aurora PostgreSQL-Compatible Edition ist eine vollständig verwaltete, ACID-konforme relationale Datenbank-Engine, die Sie bei der Einrichtung, dem Betrieb und der Skalierung von PostgreSQL-Bereitstellungen unterstützt.

• Amazon Relational Database Service (Amazon RDS) für PostgreSQL unterstützt Sie bei der Einrichtung, dem Betrieb und der Skalierung einer relationalen PostgreSQL-Datenbank in der AWS-Cloud.

• Das AWS Schema Conversion Tool (AWS SCT) unterstützt heterogene Datenbankmigrationen, indem das Quelldatenbankschema und ein Großteil des benutzerdefinierten Codes automatisch in ein mit der Zieldatenbank kompatibles Format konvertiert werden.

• pgAdmin ist ein Open-Source-Verwaltungstool für PostgreSQL. Es bietet eine grafische Oberfläche, mit der Sie Datenbankobjekte erstellen, verwalten und verwenden können.

Epen

Migrieren Sie Oracle OUT-Bind-Variablen mithilfe einer benutzerdefinierten PL/pgSQL-Funktion und AWS SCT

Aufgabe	Beschreibung	Erforderliche Fähigkeiten
Connect zu Ihrer PostgreSQL-kompatiblen AWS-Datenbank her.	Nachdem Sie Ihre DB-Instance erstellt haben, können Sie jede Standard-SQL-Client-Anwendung verwenden, um eine Verbindung zu einer Datenbank in Ihrem DB-Cluster herzustellen. Sie können beispielsweise pgAdmin verwenden, um eine Verbindung zu Ihrer DB-Instance herzustellen. Weitere Informationen finden Sie in einer der folgenden Seiten: Herstellen einer Verbindung zu einer Amazon RDS-DB-Instance im Amazon RDS-Benutzerhandbuch Herstellen einer Verbindung zu einem Amazon Aurora Aurora-DB-Cluster im Amazon Aurora Aurora-Benutzerhandbuch	Migrationsingenieur
Fügen Sie das Beispiel-Wrapper-Funktionsskript aus diesem Muster zum Hauptschema der Zieldatenbank hinzu.	Kopieren Sie das Beispiel für ein PL/pgSQL-Wrapper-Funktionsskript aus dem Abschnitt Zusätzliche Informationen dieses Musters. Fügen Sie dann die Funktion zum Hauptschema der Zieldatenbank hinzu. Weitere Informationen finden Sie im Abschnitt CREATE FUNCTION der PostgreSQL-Dokumentation.	Ingenieur für Migration
(Optional) Aktualisieren Sie den Suchpfad im Hauptschema der Zieldatenbank, sodass er das Test_PG-Schema enthält.	Um die Leistung zu verbessern, können Sie die PostgreSQL-Variable search_path so aktualisieren, dass sie den Schemanamen test_PG enthält. Wenn Sie den Schemanamen in den Suchpfad aufnehmen, müssen Sie den Namen nicht jedes Mal angeben, wenn Sie die PL/pgSQL-Funktion aufrufen. Weitere Informationen finden Sie in Abschnitt 5.9.3 Der Schemasuchpfad in der PostgreSQL-Dokumentation.	Migrationsingenieur

Zugehörige Ressourcen

• AWS-Schemakonvertierungstool

• OUT-Bindungsvariablen (Oracle-Dokumentation)

• Verbessern Sie die Leistung von SQL-Abfragen mithilfe von Bind-Variablen (Oracle-Blog)

Zusätzliche Informationen

Beispiel für eine PL/pgSQL-Funktion

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