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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [idf].[getInfo]
@FromDate datetime,
@ToDate datetime
AS
BEGIN
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