Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Reg: Oracle PLSQL function to SQL Server Stored Procedure Migration

Madhu.149Jul 25 2014 — edited Jul 25 2014

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 22 2014
Added on Jul 25 2014
3 comments
1,616 views