Tags: AWS Schema Conversion Tool, database conversion errors
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”.
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; |
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; |
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.
