Edit

Share via

O2SS0359: Cannot get description for return type of function call expression (Error)

This article covers the reason why SQL Server Migration Assistant (SSMA) for Oracle isn't able to convert the record set which is returned from the function.

Background

A Function is a block of code that performs a particular task and then returns control to the calling code. When it returns control, it also returns a value to the calling code. When a function returns a value, the value is returned via a return statement to the caller of the function, after being implicitly converted to the return type of the function in which it was defined.

Whenever you call a function that returns a record in another procedure, SSMA isn't able to resolve that data type. Hence, when you try to use this record set in your called procedure, SSMA generates an error message.

Example

In the following example, you create a procedure print_emp, in which you call another function get_employeeTbl that returns a record.

CREATE OR REPLACE PROCEDURE print_emp(p_empl_no in emp.empno%type)
AS
    Proc_Table emp%rowtype;
BEGIN
    Proc_Table := get_employeeTbl(p_empl_no);

    dbms_output.put_line(Proc_Table.empno);
    dbms_output.put_line(Proc_Table.ename);
    dbms_output.put_line(Proc_Table.job);
    dbms_output.put_line(Proc_Table.mgr);
    dbms_output.put_line(Proc_Table.hiredate);
    dbms_output.put_line(Proc_Table.sal);
    dbms_output.put_line(Proc_Table.comm);
    dbms_output.put_line(Proc_Table.deptno);
END;
/

CREATE OR REPLACE FUNCTION get_employeeTbl(p_empl_no in emp.empno%type)
RETURN emp%rowtype
AS
    l_cust_record emp%rowtype;
BEGIN
    SELECT *
    INTO l_cust_record
    FROM emp
    WHERE empno = p_empl_no;

    RETURN (l_cust_record);
END;

When SSMA tries to convert the previous code of main procedure print_emp, it doesn't resolve the operations of called function get_emploreeTbl, which returns a record and hence generates the following error message:

O2SS0359: Cannot get description for return type of function call expression

Possible remedies

The solution of the previous error is to rewrite the code in SQL Server. As SQL Server supports scalar functions, inline table-valued functions and multi-statement table-valued functions, you can declare a temporary table @mytable within the T-SQL code of called function. In the code, you fill this table using the same business logic and then return this table back to the calling environment. In the calling procedure, you also have to use table variable to store the return value (record set in our case) of called function.

Here's the rewritten code for the previous example:

CREATE PROCEDURE [dbo].[PRINT_EMP](@p_empl_no Int)
AS
BEGIN
    DECLARE
        @Proc_table TABLE
        (
            EMPNO int,
            ENAME varchar(max),
            JOB varchar(max),
            MGR float,
            HIREDATE datetime,
            SAL float,
            COMM float,
            DEPTNO float
        );

    INSERT INTO @Proc_table(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    SELECT * FROM dbo.GET_EMPLOYEETBL(@p_empl_no)

    SELECT * FROM @Proc_table
END
GO

CREATE FUNCTION [dbo].[GET_EMPLOYEETBL](@p_empl_no Int)
RETURNS @mytable TABLE
(
    EMPNO int,
    ENAME varchar(max),
    JOB varchar(max),
    MGR float,
    HIREDATE datetime,
    SAL float,
    COMM float,
    DEPTNO float
)
AS
BEGIN
    INSERT INTO @mytable(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT
        EMP.EMPNO,
        EMP.ENAME,
        EMP.JOB,
        EMP.MGR,
        EMP.HIREDATE,
        EMP.SAL,
        EMP.COMM,
        EMP.DEPTNO
    FROM
        dbo.EMP
    WHERE EMP.EMPNO = @p_empl_no

    RETURN
END
  • O2SS0380: Unable to convert function with record return type