call us toll-free +1 855 855 3600
 
Home > Blog > Oracle to PostgreSQL migration with AWS SCT: How to handle unsupported data types

Oracle to PostgreSQL migration with AWS SCT: How to handle unsupported data types

Posted by | On | In Amazon Web Services, AWS Database Migration Service, Database Migration, Oracle Database, PostgreSQL | Tags:
 

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 do not match Oracle’s subtypes completely. Thus, whenever you try to convert PL/SQL code containing variables that are declared with defined subtype, SCT will generate the following message: “Issue 5028 — Unable to convert definition of object with unsupported datatype”.

AWS SCT unable to convert unsupported datatypes

 

Continue reading to learn more about the possible remedies of this issue.

Possible Remedies

Consider the following example:

1
2
3
4
5
6
7
8
CREATE PROCEDURE p_subtype_001
AS
subtype MyType IS varchar2(40);
v_str_1 MyType;
v_str_2 MyType;
BEGIN
NULL;
END;

You can make changes to the source code using one of the following options.

1. Use the datatype directly without the subtype.
Using this approach means that you will lose the subtype name, that may be unacceptable for various reasons, for example, due to strict code standards adopted.

1
2
3
4
5
6
7
CREATE PROCEDURE p_subtype_001
AS
v_str_1 varchar2(40);
v_str_2 varchar2(40);
BEGIN
NULL;
END;

This procedure will be successfully converted by SCT to the following PostgreSQL code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION test_suptype()
RETURNS void
AS
$BODY$
DECLARE
v_str_1 CHARACTER VARYING(40);
v_str_2 CHARACTER VARYING(40);
BEGIN
NULL;
END;
$BODY$
LANGUAGE plpgsql;

AWS SCT converts code to PostgreSQL

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

1
2
3
4
5
6
7
8
CREATE PROCEDURE p_subtype_001
AS
MyType varchar2(40);
v_str_1 MyType%TYPE;
v_str_2 MyType%TYPE;
BEGIN
NULL;
END;

This procedure will be converted by SCT to the following PostgreSQL code:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE FUNCTION sct_demo.p_subtype_001()
RETURNS void
AS
$BODY$
DECLARE
MyType CHARACTER VARYING(40);
v_str_1 SCT_DEMO.TEST_SUPTYPE%TYPE;
v_str_2 SCT_DEMO.TEST_SUPTYPE%TYPE;
BEGIN
NULL;
END;
$BODY$
LANGUAGE plpgsql;

Concerting Oracle to PostgreSQL with AWS SCT

If you want to share the altered subtype declaration across the database schema, you should create an Oracle Package with the variable, schema-level Object Type or the table with a column of proper datatype and use %TYPE in the way shown above.

Make sure to check out our new AWS Schema Conversion Tool Jumpstart offer to get you up and running fast for your database migration projects.

Related posts

Be sure to check the regularly updated table of contents for our series of blog posts on typical AWS SCT conversion issues.

 
       
 

Leave a Reply

  • CAPTCHA Image
    Play CAPTCHA Audio
    Reload Image