Home » SQL & PL/SQL » SQL & PL/SQL » please convert this procedure to oracle. (Oracle 11g)
please convert this procedure to oracle. [message #594940] Wed, 04 September 2013 05:48 Go to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Hi Experts,

PLease convert this SQL SERVER procedure to Oracle.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [WED].[sdc_proc] 
@p_id AS int,
@lin AS nvarchar(100) = '',
@lin AS nvarchar(100) = ''
AS
BEGIN
	DECLARE @stm as nvarchar(1000)
	DECLARE @seam as nvarchar(1000)
	SET @stm = ''
	SET @seam = ''
	DECLARE @usl as int
	DECLARE @str as nvarchar(1000)
	SET @str = 'SELECT @intusl = [wb_Access_ID]  FROM '+@lin+'.abc.accesspoint where nlin = ''' +  @lin + ''''
	EXEC sp_executesql @str,N'@intusl int output',@usl output
	IF ISNULL(@usl,0) = 0
	BEGIN
		RETURN
	END 
	ELSE
	BEGIN
		IF @usl = 4
		BEGIN
			SET @stm = ''
			SET @seam = ''
		END
		ELSE IF @usl = 3
		BEGIN
			IF object_id('tempdb..#tmpabcTSeg') IS NOT NULL
			BEGIN
			   DROP TABLE #tmpabcTSeg
			END
				
			CREATE TABLE #tmpabcTSeg (stm varchar(4000))
		
			DECLARE @STRSegm VARCHAR(4000)
			--SET @STRSegm = 'INSERT INTO #tmpabcTSeg 	
			--SELECT distinct c.stm FROM 
			--' + @lin + '.abc.[user_seam] ut,
			--' + @lin + '.abc.[seam] t,
			--' + @lin + '.abc.[user] u,
			--' + @lin + '.abc.[seam_stm] b,
			--' + @lin + '.abc.[stm] c 
			--with(nolock) 
			--WHERE 
			--ut.[user_id] = u.[user_id]
			--AND ut.user_seam = t.seam_id
			--AND u.nlin = '''+ convert(nvarchar(50),@lin)+'''		
			--AND t.seam_ID = b.seam_ID 
			--AND b.stm_ID = c.stm_ID 
			--AND t.seam_is_disabled = 0
			--AND u.disabled = 0'
			SET @STRSegm = 'INSERT INTO #tmpabcTSeg
			SELECT stm FROM ' + @lin + '.abc.vwWBstm 
			WHERE nlin = '''+ convert(nvarchar(50),@lin)+''' '
			
			EXEC(@STRSegm)
			SET @stm = ' AND (stm in (SELECT stm FROM #tmpabcTSeg)) '
								
			--SET @stm = ' AND (stm in (
			--SELECT distinct c.stm FROM 
			--' + @lin + '.abc.[user_seam] ut,
			--' + @lin + '.abc.[seam] t,
			--' + @lin + '.abc.[user] u,
			--' + @lin + '.abc.[seam_stm] b,
			--' + @lin + '.abc.[stm] c 
			--with(nolock) 
			--WHERE 
			--ut.[user_id] = u.[user_id]
			--AND ut.user_seam = t.seam_id
			--AND u.nlin = '''+ convert(nvarchar(50),@lin)+'''		
			--AND t.seam_ID = b.seam_ID 
			--AND b.stm_ID = c.stm_ID 
			--AND t.seam_is_disabled = 0
			--AND u.disabled = 0)) '

		END
		ELSE	
		BEGIN	
			IF object_id('tempdb..#tmpabcTseam') IS NOT NULL
			BEGIN
			   DROP TABLE #tmpabcTseam
			END 
				
			CREATE TABLE #tmpabcTseam (seam varchar(4000))
		
			DECLARE @STRseam VARCHAR(4000)
			--SET @STRseam = 'INSERT INTO #tmpabcTseam SELECT distinct(t.[description]) seam 			
			--FROM 
			--' + @lin + '.abc.[user_seam] ut,
			--' + @lin + '.abc.[seam] t,
			--' + @lin + '.abc.[user] u 
			--with(nolock) 
			--WHERE 
			--ut.[user_id] = u.[user_id]
			--AND ut.user_seam = t.seam_id
			--AND u.nlin = ''' + convert(nvarchar(50),@lin)+'''
			--AND u.disabled = 0 '
			SET @STRseam = 'INSERT INTO #tmpabcTseam
			SELECT description FROM ' + @lin + '.abc.vwWBseamDesc 
			WHERE nlin = ''' + convert(nvarchar(50),@lin)+''' '
			
			exec(@STRseam)
			SET @seam = ' AND (seam in (SELECT seam FROM #tmpabcTseam)) '
				
			--SET @seam = ' AND (seam in (
			--SELECT distinct t.[description] FROM 
			--' + @lin + '.abc.[user_seam] ut,
			--' + @lin + '.abc.[seam] t,
			--' + @lin + '.abc.[user] u 
			--with(nolock) 
			--WHERE 
			--ut.[user_id] = u.[user_id]
			--AND ut.user_seam = t.seam_id
			--AND u.nlin = ''' + convert(nvarchar(50),@lin)+'''
			--AND u.disabled = 0))'
			
		END			
		DECLARE @strTmp as nvarchar(4000)
		SET @strTmp = '' 
		SET @strTmp =  @strTmp + 'SUM(today_ib_call) as today_ib_call,SUM(today_ib_time) as today_ib_time,SUM(today_ob_call) as today_ob_call,SUM(today_ob_time) as today_ob_time,  '
		SET @strTmp =  @strTmp + 'SUM(today_svr_quote) as today_svr_quote,SUM(today_lat_quote) as today_lat_quote,SUM(today_ins_quote) as today_ins_quote,SUM(today_dim_quote) as today_dim_quote ,SUM(today_total_quote) as today_total_quote , '
		SET @strTmp =  @strTmp + 'SUM(week_ib_call) as week_ib_call,SUM(week_ib_time) as week_ib_time,SUM(week_ob_call) as week_ob_call,SUM(week_ob_time) as week_ob_time,  '
		SET @strTmp =  @strTmp + 'SUM(week_svr_quote) as week_svr_quote,SUM(week_lat_quote) as week_lat_quote,SUM(week_ins_quote) as week_ins_quote,SUM(week_dim_quote) as week_dim_quote ,SUM(week_total_quote) as week_total_quote  '
		SET @strTmp =  @strTmp + ' FROM [dbo].[SALES_Report_Creating] with(nolock)  WHERE p_id = ' + convert(varchar(5), @p_id)  + @stm + @seam 
		DECLARE @strSumTotal as varchar(4000)
		DECLARE @strSumstm as varchar(4000)
		DECLARE @strSumRim as varchar(4000)
		DECLARE @strSumseam as varchar(4000)
		DECLARE @strSumseam1 as varchar(4000)
	        SET @strSumTotal =	' SELECT ''-1'' as tid,''ZZZZZ'' as Parent_ID, ''Total'' as Category,''ZZZZZZ''                                 as list,' + @strTmp
		SET @strSumstm =	' SELECT ''0''  as tid,''0''     as Parent_ID, stm   as Category,stm                                    as list,' + @strTmp + '                     GROUP BY stm '
		SET @strSumRim =	' SELECT ''1''  as tid,stm   as Parent_ID, region    as Category,stm + ''  '' + Region                  as list,' + @strTmp + ' AND (Region <>'''') GROUP BY stm, region '
		SET @strSumseam1 =	' SELECT ''2''  as tid,stm   as Parent_ID, seam      as Category,stm + ''  ''                    + seam as list,' + @strTmp + ' AND (Region = '''') GROUP BY stm, seam '
		SET @strSumseam =	' SELECT ''3''  as tid,Region    as Parent_ID, seam      as Category,stm + ''  '' + Region +  ''  '' + seam as list,' + @strTmp + ' AND (Region <>'''') GROUP BY stm, region, seam '

		--EXEC  (@strSumstm + ' UNION all ' + @strSumRim + ' UNION all ' + @strSumseam1 + ' UNION all ' + @strSumseam + ' ORDER BY list')		
        --PRINT(@strSumstm + ' UNION all ' + @strSumRim + ' UNION all ' + @strSumseam1 + ' UNION all ' + @strSumseam + ' ORDER BY list')
		EXEC  (@strSumTotal + ' UNION all ' + @strSumstm + ' UNION all ' + @strSumRim + ' UNION all ' + @strSumseam1 + ' UNION all ' + @strSumseam + ' ORDER BY list')
		
		IF object_id('tempdb..#tmpabcTseam') IS NOT NULL
		BEGIN
		   DROP TABLE #tmpabcTseam
		END	
		IF object_id('tempdb..#tmpabcTSeg') IS NOT NULL
		BEGIN
		   DROP TABLE #tmpabcTSeg
		END	
	END
END

GO

Please help me.

Thanks.
Re: please convert this procedure to oracle. [message #594943 is a reply to message #594940] Wed, 04 September 2013 06:07 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
Sure, just detail the exact requirements that that procedure answers.
Then raise a purchase order with your company to pay me for the job that you appear to have been asked to do. This is not meant to be a place where people come to do your job for you. If they know, people will help guide you, but they won't do your job for you.

[Updated on: Wed, 04 September 2013 06:10]

Report message to a moderator

Re: please convert this procedure to oracle. [message #594944 is a reply to message #594943] Wed, 04 September 2013 06:10 Go to previous messageGo to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
I want to convert this code from SQL server to oracle using oracle syntax.

Thanks.
Re: please convert this procedure to oracle. [message #594945 is a reply to message #594944] Wed, 04 September 2013 06:13 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
You have been told this on more than one occasion mate, it's not a simple case of converting the code to Oracle syntax. You are WAY over your head on whatever project you are involved in and if you continue down this path you are likely to wind up causing some serious damage to whatever db system you have been asked to work on. Believe me when I tell you that this will follow you for a good part of your career. Stop now, tell the people who have asked you to work on this that you do not currently have the skillset to achieve the job correctly and they should either get someone who IS qualified, or they should train you up to do the job.
Re: please convert this procedure to oracle. [message #594951 is a reply to message #594945] Wed, 04 September 2013 07:28 Go to previous messageGo to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Hi All,

Can anybody please provide the equivalent Oracle code for the below piece of code.

	CREATE PROCEDURE [MNR].[wed_manage_proc]
	@utu as nvarchar(100),
	@ABCDBLINK as nvarchar(100)
        AS
	SET @str = 'SELECT 
			@intusr = a.[ID]  
		    FROM 
			' + @ABCDBLINK + '.MNR.[ust_table] a,
			' + @ABCDBLINK + '.MNR.[lgn_table] ab 
		    WHERE 
			ab.manage = 0 
			AND a.ntu = ''' +  @utu + ''''

	EXECUTE exesql @str, N'@intusr int output', @userLevel output
	END

Please help me.

Thanks.
Re: please convert this procedure to oracle. [message #594957 is a reply to message #594951] Wed, 04 September 2013 07:43 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
Do you understand what the SQL server code is doing? Do you understand what i said in my previous post to you?
Re: please convert this procedure to oracle. [message #594961 is a reply to message #594957] Wed, 04 September 2013 07:56 Go to previous messageGo to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Hi ,

I don't have knowledge in SQL server.
So that I am not able to understand some code in this.

Please help me.

Thanks.
Re: please convert this procedure to oracle. [message #594962 is a reply to message #594961] Wed, 04 September 2013 08:05 Go to previous message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
Then you should not be in a job that requires you to convert the code.
You need to go and get the requirements (this has been said to you before) that led to the procedure being built, then go and build the procedure in Oracle. That's it. You don't actually need the SQL Server code... at all. Maybe use the procedure names as a guide.
Previous Topic: Getting error when record type is used.
Next Topic: WILD CARD search
Goto Forum:
  


Current Time: Fri Oct 24 12:13:44 CDT 2014

Total time taken to generate the page: 0.10351 seconds