Oracle OUT 바인딩 변수를 PostgreSQL 데이터베이스로 마이그레이션

작성자: Bikash Chandra Rout(AWS) 및 Vinay Paladi(AWS)

환경: PoC 또는 파일럿	소스: 관계형 데이터베이스	대상: RDS/Aurora Postgresql
R 타입: 리플랫포밍	워크로드: Oracle	기술: 데이터베이스, 마이그레이션
AWS 서비스: Amazon Aurora, Amazon RDS, AWS SCT

요약

이 패턴은 Oracle Database OUT 바인드 변수를 다음 Postgre SQL호환 AWS 데이터베이스 서비스 중 하나로 마이그레이션하는 방법을 보여줍니다.

• Postgre용 Amazon Relational Database Service(AmazonRDS)SQL

• Amazon Aurora Postgre SQL- 호환 버전

PostgreSQL는 OUT 바인딩 변수를 지원하지 않습니다. Python 문에서 동일한 기능을 가져오려면 대신 GET 및 SET 패키지 변수를 사용하는 사용자 지정 PL/pgSQL 함수를 생성할 수 있습니다. 이러한 변수를 적용하기 위해 이 패턴에 제공된 예제 래퍼 함수 스크립트는 AWS Schema Conversion Tool(AWS SCT) 확장 팩 을 사용합니다.

참고: Oracle EXECUTE IMMEDIATE 명령문이 최대 하나의 행을 반환할 수 있는 SELECT 명령문인 경우 다음을 수행하는 것이 가장 좋습니다.

• INTO 절에 OUT 바인드 변수(정의) 넣기

• USING 절에 IN 바인드 변수 넣기

자세한 내용은 Oracle 설명서의 EXECUTE IMMEDIATE 문을 참조하세요.

사전 조건 및 제한 사항

사전 조건 

• 활성 AWS 계정 

• 온프레미스 데이터 센터의 Oracle Database 10g (or(또는 그 이상) 소스 데이터베이스

• Amazon RDS for PostgreSQL DB 인스턴스 또는 Aurora Postgre SQL호환 DB 인스턴스

아키텍처

소스 기술 스택

• 온프레미스 Oracle Database 10g(또는 그 이상) 데이터베이스 

대상 기술 스택

• Amazon RDS for PostgreSQL DB 인스턴스 또는 Aurora Postgre SQL호환 DB 인스턴스

대상 아키텍처 

다음 다이어그램은 Oracle Database OUT 바인드 변수를 Postgre SQL호환 AWS 데이터베이스로 마이그레이션하기 위한 워크플로의 예를 보여줍니다.

이 다이어그램은 다음 워크플로를 보여줍니다.

1. AWS SCT 는 소스 데이터베이스 스키마와 사용자 지정 코드의 대부분을 대상 Postgre SQL호환 AWS 데이터베이스와 호환되는 형식으로 변환합니다.

2. 자동으로 변환할 수 없는 모든 데이터베이스 객체는 PL/pgSQL 함수에 의해 플래그가 지정됩니다. 그런 다음 플래그가 지정된 객체를 수동으로 변환하여 마이그레이션을 완료합니다.

도구

• Amazon Aurora Postgre SQL-Compatible Edition은 PostgreSQL 배포를 설정, 운영 및 확장하는 데 도움이 되는 완전 관리형 ACID규정 준수 관계형 데이터베이스 엔진입니다.

• Amazon Relational Database Service(Amazon RDS) for PostgreSQL는 AWS 클라우드에서 PostgreSQL 관계형 데이터베이스를 설정, 운영 및 확장하는 데 도움이 됩니다.

• AWS Schema Conversion Tool(AWS SCT)는 소스 데이터베이스 스키마와 사용자 지정 코드의 대부분을 대상 데이터베이스와 호환되는 형식으로 자동 변환하여 이기종 데이터베이스 마이그레이션을 지원합니다.

• pgAdmin 는 Postgre용 오픈 소스 관리 도구입니다SQL. 데이터베이스 객체를 생성, 유지 관리 및 사용하는 데 도움이 되는 그래픽 인터페이스를 제공합니다.

에픽

사용자 지정 PL/pgSQL 함수 및 를 사용하여 Oracle OUT 바인딩 변수 마이그레이션 AWS SCT

작업	설명	필요한 기술
Postgre SQL호환 AWS 데이터베이스에 연결합니다.	DB 인스턴스를 생성한 후 표준 SQL 클라이언트 애플리케이션을 사용하여 DB 클러스터의 데이터베이스에 연결할 수 있습니다. 예를 들어 pgAdmin를 사용하여 DB 인스턴스에 연결할 수 있습니다. 자세한 내용은 다음 중 하나를 참조하십시오. Amazon RDS 사용 설명서의 Amazon RDS DB 인스턴스에 연결 Amazon Aurora 사용 설명서의 Amazon Aurora DB 클러스터에 연결	마이그레이션 엔지니어
이 패턴의 예제 래퍼 함수 스크립트를 대상 데이터베이스의 기본 스키마에 추가합니다.	이 패턴의 추가 정보 섹션에서 예제 PL/pgSQL 래퍼 함수 스크립트를 복사합니다. 그런 다음 대상 데이터베이스의 기본 스키마에 함수를 추가합니다. 자세한 내용은 PostgreSQL 설명서CREATEFUNCTION의 섹션을 참조하세요.	마이그레이션 엔지니어
(선택 사항) Test_pg 스키마를 포함하도록 대상 데이터베이스의 기본 스키마에서 검색 경로를 업데이트합니다.	성능을 개선하기 위해 PostgreSQL search_path 변수를 업데이트하여 Test_pg 스키마 이름을 포함할 수 있습니다. 검색 경로에 스키마 이름을 포함하는 경우 PL/pgSQL 함수를 호출할 때마다 이름을 지정할 필요가 없습니다. 자세한 내용은 PostgreSQL 설명서의 섹션 5.9.3 스키마 검색 경로를 참조하세요.	마이그레이션 엔지니어

관련 리소스

• AWS Schema Conversion Tool

• 바OUT인드 변수(Oracle 설명서)

• 바인드 변수(Oracle 블로그)를 사용하여 SQL 쿼리 성능 개선

추가 정보

PL/pgSQL 함수 예제

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