migration from SQL server

From: Peter Wilk <PeterWilk_at_compuserve.com>
Date: Thu, 8 Apr 1999 22:08:20 +0200
Message-ID: <7ej28h$ed1$1@orudios.magnet.at>

how to migrate the following stored procedure from MS SQL Server to ORACLE

CREATE PROCEDURE usp_monthlyupdatesummaryhits AS DECLARE @rowsupdated int
BEGIN TRANSACTION
INSERT INTO MonthSummary
SELECT TSumDate=MAX(TSumDate),

       TSiteIP=MAX(TSiteIP),
       TTarget=MAX(TTarget),
       TKBytes=SUM(TKBytes),
       THitCount=SUM(THitCount)

FROM DaySummary
WHERE DATEDIFF(month, TSumDate, GETDATE()) > 3 GROUP BY TSiteIP,

         TTarget,
         DATEPART(month, TSumDate)

SELECT @rowsupdated = @@ROWCOUNT
IF (@@ERROR <> 0) GOTO on_error
IF (@rowsupdated = 0) GOTO on_norows
DELETE
FROM HourSummary
WHERE DATEDIFF(month, TSumDate, GETDATE()) > 3 IF (@@ERROR <> 0) GOTO on_error
DELETE
FROM DaySummary
WHERE DATEDIFF(month, TSumDate, GETDATE()) > 3 IF (@@ERROR <> 0) GOTO on_error
DELETE
FROM CountrySummary
WHERE (TYearNumber = DATEPART(year, GETDATE()) AND TWeekNumber <
(DATEPART(week, GETDATE()) - 25))

   OR (TYearNumber < DATEPART(year, GETDATE()) AND TWeekNumber <
(DATEPART(week, GETDATE()) + 26))

IF (@@ERROR <> 0) GOTO on_error
DELETE
FROM RefererSummary
WHERE (TYearNumber = DATEPART(year, GETDATE()) AND TWeekNumber <
(DATEPART(week, GETDATE()) - 25))

   OR (TYearNumber < DATEPART(year, GETDATE()) AND TWeekNumber <
(DATEPART(week, GETDATE()) + 26))

IF (@@ERROR <> 0) GOTO on_error
DELETE
FROM SessionTargetSummary
WHERE (TYearNumber = DATEPART(year, GETDATE()) AND TWeekNumber <
(DATEPART(week, GETDATE()) - 25))

   OR (TYearNumber < DATEPART(year, GETDATE()) AND TWeekNumber <
(DATEPART(week, GETDATE()) + 26))

IF (@@ERROR <> 0) GOTO on_error
DELETE
FROM UserAgentSummary
WHERE (TYearNumber = DATEPART(year, GETDATE()) AND TWeekNumber <
(DATEPART(week, GETDATE()) - 25))

   OR (TYearNumber < DATEPART(year, GETDATE()) AND TWeekNumber <
(DATEPART(week, GETDATE()) + 26))

IF (@@ERROR <> 0) GOTO on_error
SELECT 'Archived ' + CONVERT(varchar(8), @rowsupdated) + ' records.' COMMIT TRANSACTION
RETURN (0)
on_error:
SELECT 'Error - archive aborted.'
ROLLBACK TRANSACTION
RETURN(1)
on_norows:SELECT 'No records archived.' ROLLBACK TRANSACTION
RETURN(1)
GO

tia for any help

--
Peter Wilk
Assistance and Insurance Consulting
http://ourworld.compuserve.com/homepages/PeterWilk Received on Thu Apr 08 1999 - 15:08:20 CDT