Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL MySQL
Hi Everyone, I am very new to this.
 
I have been trying to configure a friends stored procedure to work for my environment. The problem however is that he is using mySql ( because he is working with a Sql database) and I need to use Pl/Sql to use this function in an oracle environment.
 
Could someone please convert this function into a Pl-Sql function, or atleast help me to understand what is being done in the code.
 
Thanks in advance for the help
 
 
USE [Box]
GO
/****** Object:  StoredProcedure [idf].[getInfo]    Script Date: 11/03/2011 15:38:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author:        <Author,,Name>
-- ALTER  date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
--  exec [idf].[getInfo] '2011-11-01 00:20', '2011-11-01 13:22'

ALTER PROCEDURE [idf].[getInfo] 
      
      @FromDate datetime,
      @ToDate datetime
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
DECLARE @SQL1 nvarchar(1000)
DECLARE @SQL2 nvarchar(1000)
DECLARE @Val nvarchar(1000)
DECLARE @DateTimeChar nvarchar(1000)
DECLARE @params nvarchar(1000)
DECLARE @Value real  
DECLARE @DateTime datetime 
 
SET @SQL2 = 'SELECT @Val = IP_TREND_TIME FROM OPENQUERY(IP21_BFC, ''SELECT IP_TREND_TIME, IP_TREND_VALUE FROM "PLC_EAST.PV" WHERE IP_TREND_TIME >= ''''' 
+ box.idf.DateTimeToIP21Date(@FromDate) + ''''' AND IP_TREND_TIME <= ''''' + box.idf.DateTimeToIP21Date(@ToDate) 
+ ''''' AND IP_TREND_VALUE > -0.5 ORDER BY IP_TREND_TIME DESC '')'
 
SET @params = '@Val nvarchar(1000) OUTPUT'  
EXEC sp_executesql @SQL2, @params, @Val = @DateTimeChar OUTPUT
 
SET @SQL1 = 'SELECT @Val = MAX(IP_TREND_VALUE) FROM OPENQUERY(IP21_BFC, ''SELECT IP_TREND_TIME, IP_TREND_VALUE FROM "PLC_EAST.PV" WHERE IP_TREND_TIME >= ''''' 
+ Box.idf.DateTimeToIP21Date(@FromDate) + ''''' AND IP_TREND_TIME <= ''''' + @DateTimeChar 
+ ''''' AND IP_TREND_VALUE > 0.3 ORDER BY IP_TREND_TIME DESC '')'
 
EXEC sp_executesql @SQL1, @params, @Val = @Value OUTPUT
 
SET @Value = CONVERT(real, @Value)
SET @DateTime = CONVERT(datetime, @DateTimeChar, 21)
 
IF @Value is null
begin
  SET @Value = 8.0
end
IF @DateTimeChar is null
begin
  SET @DateTime = @ToDate
end
 
SELECT @DateTime, @Value
 
END
 

 

 

 
Posted 14-Feb-12 21:20pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 5 Mar 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100