Home » SQL & PL/SQL » SQL & PL/SQL » sql server procedure into oracle (oracle 10g)
sql server procedure into oracle [message #400637] Wed, 29 April 2009 03:18 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
can anyone help me in giving this sql server stored procedure into oracle

IF EXISTS (SELECT name FROM sysobjects WHERE  name  = 'GetMedian' AND type = 'P')
DROP PROCEDURE GetMedian
GO
Create Procedure GetMedian (@SQLStmt Varchar(8000))  AS  
 Declare   
 @String Varchar(8000),  
 @Value int,  
 @Row1 int,  
 @Row2 int,  
 @Value1 Decimal(20,3),  
 @Value2 Decimal(20,3)  
Begin  
 SET @String = 'Declare CurMedian CURSOR SCROLL FOR ' + @SQLStmt  
 EXEC (@String)  
 OPEN CurMedian  
 SET @Value = @@Cursor_Rows  
 IF (@@Cursor_Rows%2) = 1  
 Begin  
  SET @Row1 = (@Value/2)+1  
  SET @Row2 = 0  
  Fetch Absolute @Row1 From CurMedian INTO @Value1  
  SET @Value = @Value1   
 End  
 Else  
 Begin  
  SET @Row1 = (@Value/2)  
  SET @Row2 = (@Value/2)+1  
  Fetch Absolute @Row1 From CurMedian INTO @Value1  
  Fetch First From CurMedian Into @Value2  
  SET @Value2 = 0  
  Fetch Absolute @Row2 From CurMedian INTO @Value2  
  SET @Value = (@Value1 + @Value2)/2  
 End  
 Close CurMedian  
 Deallocate CurMedian  
 Select @Value  
End  
GO

Re: sql server procedure into oracle [message #400642 is a reply to message #400637] Wed, 29 April 2009 03:28 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What does it do exactly?

Oracle already has a built-in median function.
Previous Topic: Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning?
Next Topic: drop oldest partition
Goto Forum:
  


Current Time: Sun Dec 04 16:33:37 CST 2016

Total time taken to generate the page: 0.14051 seconds