Data Migration Architecture of SSMA for Oracle

几个通过SSMA 转换到 SQLSERVER中的ORACLE FUNCTION , 发现 SSMA 竟然使用了SP 重载 ！！！

create function [ssma_oracle].[db_error_get_oracle_exception_id]

(

@message nvarchar(4000),

@number int

)

returns nvarchar(4000)

as

begin

if(@number = 2627)

return N'ORA-00001'

if(@number = 8134)

return N'ORA-01476'

if(@number = 16915)

return N'ORA-06511'

if(@number = 16917)

return N'ORA-01001'

if(@number = 512)

return N'ORA-01422'

if(@number = 547)

return N'ORA-02291'

if(@number = 59999)

begin

declare @start int

set @start = CHARINDEX(N'[', @message)

if(@start > 0)

begin

declare @end int

set @end = CHARINDEX(N']', @message, @start)

if(@end > 0)

begin

return SUBSTRING(@message, @start + 1, @end - @start - 1)

end

end

return null

end

if (@number = 59998)

return @message

return null

end

GO

/ Object: UserDefinedFunction [ssma_oracle].[instr4_nvarchar] Script Date: 8/21/2018 3:13:47 PM **/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create FUNCTION [ssma_oracle].instr4_nvarchar, @str2 as nvarchar(max), @pos as int, @occurrence as int)

returns int

begin

if( @str1 is NULL) or (@str2 is NULL) or (@pos is NULL) or (@pos = 0) or (@occurrence is NULL) or ( @occurrence < 1 )

return NULL

if( @pos < 0 )

begin

while( ([ssma_oracle].length_nvarchar-@pos-1) <= [ssma_oracle].length_nvarchar )

begin

if( substring(@str1, [ssma_oracle].length_nvarchar + @pos + 1, [ssma_oracle].length_nvarchar) = @str2 )

begin

SET @occurrence = @occurrence-1

if @occurrence < 1

return [ssma_oracle].length_nvarchar + @pos + 1

end

SET @pos = @pos-1

end

return 0

end

SET @pos = @pos-1

while @occurrence > 0

begin

SET @pos = charindex(@str2, @str1, @pos+1)

if @pos = 0

return 0

SET @occurrence = @occurrence-1

end

return @pos

end

GO

/ Object: UserDefinedFunction [ssma_oracle].[length_nvarchar] Script Date: 8/21/2018 3:13:47 PM **/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create FUNCTION [ssma_oracle].length_nvarchar)

returns int

as

begin

return len(replace(@s, ' ', '.'))

end

GO

/ Object: UserDefinedFunction [ssma_oracle].[substr2_nvarchar] Script Date: 8/21/2018 3:13:47 PM **/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create FUNCTION [ssma_oracle].substr2_nvarchar, @pos as int)

returns nvarchar(max)

begin

declare

@strlen as int

if( @src is NULL) or (@pos is NULL )

return NULL;

SET @strlen=[ssma_oracle].length_nvarchar

if abs(@pos) > @strlen

return NULL;

if @pos = 0

SET @pos = 1

if @pos > 0

begin

return substring(@src, @pos, @strlen-@pos+1)

end

return substring(@src, @strlen + @pos+1, -@pos)

end

GO