Dear All,
I have the below function in oracle, which I need to migrate it to SQL Server
create or replace function dynamic_pivot4 return sys_refcursor
as
p_cursor sys_refcursor;
l_query long := 'select year, city, product';
begin
for x in (select distinct month from sample order by 1 )
loop
l_query := l_query ||
replace(
replace( q'|, sum(case month when $1$ then quantity end) $2$|',
'$1$',
dbms_assert.enquote_literal(x.month) ),
'$2$',
dbms_assert.simple_sql_name( '"' || x.month || '"' ) );
end loop;
l_query := l_query || ',sum(quantity) Total from sample group by rollup(year, city, product) order by year';
dbms_output.put_line( l_query );
open p_cursor for l_query;
return p_cursor;
end;
/
The SSMA has reported the below translation for the PLSQL function
CREATE PROCEDURE dbo.DYNAMIC_PIVOT4$IMPL
@return_value_argument varchar(8000) OUTPUT
AS
/*Generated by SQL Server Migration Assistant for Oracle version 5.3.0.*/
BEGIN
DECLARE
@p_cursor CURSOR,
@l_query varchar(max) = 'select year, city, product'
DECLARE
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@x$MONTH float(53)
DECLARE
DB_IMPLICIT_CURSOR_FOR_x CURSOR LOCAL FORWARD_ONLY FOR
SELECT DISTINCT SAMPLE.MONTH
FROM dbo.SAMPLE
ORDER BY 1
OPEN DB_IMPLICIT_CURSOR_FOR_x
WHILE 1 = 1
BEGIN
FETCH DB_IMPLICIT_CURSOR_FOR_x
INTO @x$MONTH
IF @@FETCH_STATUS = -1
BREAK
/*
* SSMA error messages:
* O2SS0004: Unparsed SQL [replace(
* replace( q'|, sum(case month when $1$ then quantity end) $2$|',
* '$1$',
* dbms_assert.enquote_literal(x.month) ),
* '$2$',
* dbms_assert.simple_sql_name( '"' || x.month || '"' ) );] cannot be converted.
*/
END
CLOSE DB_IMPLICIT_CURSOR_FOR_x
DEALLOCATE DB_IMPLICIT_CURSOR_FOR_x
SET @l_query = ISNULL(@l_query, '') + ',sum(quantity) Total from sample group by rollup(year, city, product) order by year'
PRINT @l_query
DECLARE
@auxiliary_cursor_definition_sql nvarchar(max)
DECLARE
@auxiliary_exec_param nvarchar(max)
IF (cursor_status('variable', N'@p_cursor') > -2)
DEALLOCATE @p_cursor
/*
* SSMA error messages:
* O2SS0157: The OPEN...FOR statement will be converted, but the dynamic string must be converted manually.
SET @auxiliary_cursor_definition_sql = 'SET @auxiliary_tmp_cursor = CURSOR LOCAL FOR ' + @l_query + '; OPEN @auxiliary_tmp_cursor'
*/
SET @auxiliary_exec_param = '@auxiliary_tmp_cursor cursor OUTPUT'
EXECUTE sp_executesql @auxiliary_cursor_definition_sql, @auxiliary_exec_param, @p_cursor OUTPUT
/*
* SSMA error messages:
* O2SS0245: The conversion of cursors in return statements is not supported.
RETURN @p_cursor
*/
END
GO
CREATE FUNCTION dbo.DYNAMIC_PIVOT4
(
)
RETURNS varchar(8000)
AS
/*Generated by SQL Server Migration Assistant for Oracle version 5.3.0.*/
BEGIN
DECLARE
@active_spid INT,
@login_time DATETIME104
SET @active_spid = sysdb.ssma_oracle.GET_ACTIVE_SPID()
SET @login_time = sysdb.ssma_oracle.GET_ACTIVE_LOGIN_TIME()
DECLARE
@return_value_argument varchar(8000)
/*
* SSMA warning messages:
* O2SS0452: "xp_ora2ms_exec2_ex" when called from within UDF cannot bind to outer transaction. It can lead to dead locks and losing transaction atomicity. Consider calling $impl procedure directly.
*/
EXECUTE master.dbo.xp_ora2ms_exec2_ex
@active_spid,
@login_time,
N'SAMPLE',
N'DBO',
N'DYNAMIC_PIVOT4$IMPL',
N'true',
@return_value_argument OUTPUT125 RETURN @return_value_argument127
END
GO
But the conversion reported some error (comment part of stored procedure)
Kindly help me in converting the Oracle PLSQL function to SQL Server Stored Procedure