Blog: Oracle to SQL Server migration with SSMA: How to convert unsupported table expressions

Oracle’s table functions allow you to define a set of PL/SQL statements that will, when queried, behave just as a regular query to the table. You may use the table function to manipulate the individual elements of a collection (user-defined object types) in your SQL code.

The problem is that SQL Server Migration Assistant (SSMA) for Oracle can not convert table function and generates a set of errors:

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

This blog post covers the reasons why SSMA does not convert the table function and shows some proven workarounds.

SSMA for Oracle convert table function

The following example shows how we use the table operator in Oracle in order to get separate values from the nested table.

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

First, we define the user type TP_OBJECT, and then we define the type TAB_TYPE as a table of previously defined type.

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

And here goes the SQL statement that 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;

When we use SSMA to convert this statement from Oracle to SQL Server, the SSMA generates the error message «Error O2SS0482: table function not converted».

Possible Remedies

You can solve this error, following these 3 sure steps:

1. SSMA converts nested table p_tab_var TEST_FUNCTIONS.TAB_TYPE; to @p_tab_var varchar(8000).

You should declare the table variable with the same structure as the Oracle’s object type.

2. SSMA converts BULK COLLECT to

1
2
3
4
5
6
@p_tab_var.AssignData(ssma_oracle.fn_bulk_collect2CollectionSimple(
(
SELECT
FROM
FOR XML PATH )
)

You should replace SSMA Extension Pack emulation for BULK COLLECT with simple INSERT into TABLE variable.

3. SSMA converts FROM TABLE(p_tab_var) to “FROM AS fci” because TABLE operator couldn’t be converted.

You should replace dummy FROM clause with FROM @p_tab_var.

The updated SQL Server code will look 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

Now you can use this code example to emulate your Oracle’s table function in the SQL Server database environment.

Need help getting started? Check out our Jumpstart for SSMA offer!

Related posts

Be sure to check the regularly updated table of contents for our series of blog posts on typical SSMA conversion errors.

Source of technical information: Oksana Eremenko, Technical Lead at DB Best.