Oracle to SQL Server migration with SSMA: Converting unsupported table expressions

15 Oct 2021 Andrey Khudyakov (DB architect), Roman Muzyka (writer)

With Oracle table functions, you can define a set of PL/SQL statements in such a way that, when queried, should behave just as a regular query to the table. The table function enables you to manipulate individual collection elements (user-defined object types) in your SQL database code.

However, this may create a database migration challenge because  SQL Server Migration Assistant (SSMA) cannot convert Oracle table functions. Instead, it generates a set of the following errors:

O2SS0474: Conversion of the user-defined type variable is not supported and is converted to the VARCHAR(8000) type.
O2SS0339: Cannot convert usage of a standalone user-defined type.
O2SS0482: Conversion of following TABLE expression is not supported: TABLE().

In this blog post, we will explain why SSMA fails to convert the Oracle table function and give you some tips on how to solve this problem.

The essence of the problem

The example below shows how you can use the table operator to get separate values from the nested table in Oracle.

1
2
3

CREATE OR REPLACE TYPE TEST_FUNCTIONS.TP_OBJECT AS OBJECT (
FIRST_NAME VARCHAR (30),
LAST_NAME VARCHAR (30));

First, you should define the user type TP_OBJECT. Next, you need to define the type TAB_TYPE as a table of a previously defined type.

1	CREATE OR REPLACE TYPE TEST_FUNCTIONS.TAB_TYPE IS TABLE OF TEST_FUNCTIONS.TP_OBJECT;

Continue with running the following SQL statement which uses this table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DECLARE
p_tab_var TEST_FUNCTIONS.TAB_TYPE;
p_FIRST_NAME VARCHAR(30);
p_LAST_NAME VARCHAR(30);
BEGIN
SELECT TEST_FUNCTIONS.TP_OBJECT (FIRST_NAME, LAST_NAME)
BULK COLLECT INTO p_tab_var
FROM TEST_FUNCTIONS.TAB_FOR_TYPES;
SELECT FIRST_NAME, LAST_NAME
INTO p_FIRST_NAME, p_LAST_NAME
FROM TABLE(p_tab_var)
WHERE FIRST_NAME = 'SAM';
DBMS_OUTPUT.PUT_LINE(p_FIRST_NAME || ' ' || p_LAST_NAME);
END;

During an attempt to convert Oracle statement to SQL Server with SSMA, the following error message appears: «Error O2SS0482: Conversion of following TABLE expression is not supported: TABLE(…).

Possible solutions

There are 3 definite steps that will help you solve this error:

1. Use SSMA to convert the nested tablep_tab_varTEST_FUNCTIONS.TAB_TYPE; to @p_tab_var varchar(8000).

It is important to declare the table variable that has the same structure ast the Oracle object type.

2. SSMA uses the following emulation to convert BULK COLLECT to:

1
2
3
4
5
6

@p_tab_var.AssignData(ssma_oracle.fn_bulk_collect2CollectionSimple(
(
SELECT …
FROM …
FOR XML PATH )
)

It is important to replace SSMA Extension Pack emulation for BULK COLLECT with a simple INSERT into TABLE variable.  

3. Use SSMA to convert FROM_TABLE p_tab_var) to “FROM AS fci” since it is not possible to convert the TABLE operator.

Ensure replacing the FROM clause with FROM @p_tab_var. 

As a result, you will get the SQL Server code which looks as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

BEGIN
DECLARE
@p_tab_var AS TABLE
(FIRST_NAME VARCHAR (30),
LAST_NAME VARCHAR (30))
DECLARE
@p_FIRST_NAME VARCHAR(30),
@p_LAST_NAME VARCHAR(30)
INSERT INTO @p_tab_var
SELECT FIRST_NAME, LAST_NAME
FROM dbo.TAB_FOR_TYPES
SELECT @p_FIRST_NAME = FIRST_NAME, @p_LAST_NAME = LAST_NAME
FROM @p_tab_var
WHERE FIRST_NAME = 'SAM'
PRINT ISNULL(@p_FIRST_NAME, '') + ' ' + ISNULL(@p_LAST_NAME, '')
END
GO

This code will enable you to emulate Oracle’s table function in the database environment of SQL Server.

Final thoughts

Did you find this article useful? Make sure to check our Jumpstart for SSMA offer and find out more about opportunities of applying SSMA for database migration. Also, contact ABCloudz to find out more about we can help you with modernizing your database.

Also, please review our Blog section for the series of blog posts on typical errors of SSMA conversions.