By Bill Ramos and Badal Bordia, Advaiya Inc.

This blog post covers the reason why SQL Server Migration Assistant (SSMA) for Oracle is not able to convert the record set which is returned as the return type of the function. 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.

Error O2SS0359 cannot get description for return type of function call expression

Background

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

 

Possible Remedies

Consider the below example in which we have created a procedure (print_emp), in which we are calling another function (get_employeeTbl) that is returning a record.

Main Procedure “Print_emp” code

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;

Called Function “Get_employeeTbl”

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 above code of main procedure (print_emp), it does not resolve the operations of called function (get_emploreeTbl) which is returning a record and hence generates error “Error O2SS0359 cannot get description for return type of function call expression”.

clip_image001

 

The solution of the above 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 (@my table) within the T_SQL Code of called function. In the code you fill this table using your same business Logic and then return this table back to the calling environment.  In the calling function, you also have to use table variable to store the return value (record set in our case) of called function.

Below is the rewritten code of above example

Main Procedure “Print_emp” code

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

Called Function “Get_employeeTbl”

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

Go    

Related Errors

There are several other errors related to “Collection and Records” that you may encounter. These include the following:

·         Error O2SS0380 Unable to convert function with record return type

References

For more information, check out the following references:

·         Migrating Oracle to SQL Server 2008 White Paper