In this post we explore the technical aspects of Oracle to PostgreSQL migration using AWS SCT. We’ll look into conversion of Oracle “aliases”. We were using AWS SCT build #660 to convert one of our Oracle test databases.

The challenge

Oracle’s subtype declaration allows creating an “alias” for some datatype declaration, which can be re-used later as many times as needed.

PostgreSQL domains entirely differ from Oracle’s subtypes. Thus, whenever you try to convert PL/SQL code containing variables that are declared with defined subtype, SCT generates the message: “Issue 5028 — Unable to convert definition of object with unsupported datatype”.

To substitute subtype, SCT uses a corresponding datatype declaration.

Let’s take a look at the example below:

Oracle code:

CREATE PROCEDURE sct_demo.p_subtype_001
AS
subtype MyType IS varchar2(40);
v_str_1 MyType;
v_str_2 MyType;
BEGIN
NULL;
END;

Converted PostgreSQL code:

CREATE OR REPLACE PROCEDURE sct_demo.p_subtype_001()
AS
$BODY$
DECLARE
v_str_1 CHARACTER VARYING(40);
v_str_2 CHARACTER VARYING(40);
BEGIN
NULL;
END;
$BODY$
LANGUAGE plpgsql;

Let’s look at the alternativesapproaches that can help you handle unsupported data types:

Use the datatype directly without the subtype

If you choose this way, you will lose the subtype name. In many cases, it may be unacceptable because of the strict code standards adopted. However, SCT can convert the code using exactly this way.

Oracle code:

CREATE PROCEDURE sct_demo.p_subtype_002
AS
v_str_1 varchar2(40);
v_str_2 varchar2(40);
BEGIN
NULL;
END;
Converted PostgreSQL code:
CREATE OR REPLACE PROCEDURE sct_demo.p_subtype_002()
AS
$BODY$
DECLARE
v_str_1 CHARACTER VARYING(40);
v_str_2 CHARACTER VARYING(40);
BEGIN
NULL;
END;
$BODY$
LANGUAGE plpgsql;

Declare a local variable with the subtype’s name and use %type

If you go this way, you get the results that are outlined below.

Oracle code:

CREATE PROCEDURE sct_demo.p_subtype_003
AS
MyType varchar2(40);
v_str_1 MyType%TYPE;
v_str_2 MyType%TYPE;
BEGIN
NULL;
END;

Converted PostgreSQL code:

CREATE OR REPLACE PROCEDURE sct_demo.p_subtype_003()
AS
$BODY$
DECLARE
MyType CHARACTER VARYING(40);
v_str_1 MyType%TYPE;
v_str_2 MyType%TYPE;
BEGIN
NULL;
END;
$BODY$
LANGUAGE plpgsql;

If your goal is to share the altered subtype declaration across the database schema, there is a tried and trusted solution. You should create an Oracle Package that includes the variable schema-level Object Type or the table with a column of proper datatype and %TYPE as you may see in the example below.

Make sure to read even more of our posts for efficient database conversion solutions. Also, check out our new AWS Database Migration Support offer to get top-quality help with your database migration projects.

Ready to start the conversation?