Tags: database conversion errors
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.
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
- How to convert foreign keys with different types of columns and referenced columns
- How to convert cursor or cursor variable as a function or procedure call parameter
- How to convert Unparsed SQL — Pivot Operator
- How to convert materialized view with float type
- How to convert interval expressions
- How to convert interval literals
- How to convert database links
- How to convert aggregate functions referring to remote table
- How to convert unsupported SQL clause
- How to convert virtual columns
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.
