Re: migration from SQL server

I recently have had to also convert some MS SQL 6.5 stored procedures to Oracle.

The following summarizes my conclusions as they apply to your situation.

First comment... where are the comments? If this procedure had been properly commented in the first place, it might be easier to just write an equivalent Oracle procedure instead of trying to reverse engineer/ convert this one.

Kevin Little
Oracle DBA, Blue Shield of California

In article <7ej28h$ed1$1_at_orudios.magnet.at>,   "Peter Wilk" <PeterWilk_at_compuserve.com> wrote:
> how to migrate the following stored procedure from MS SQL Server to ORACLE
>
> CREATE PROCEDURE usp_monthlyupdatesummaryhits AS
> DECLARE @rowsupdated int

Global change all '@' (illegal in Oracle identifier, commonly used with MS & Sybase) to 'v_'.

> BEGIN TRANSACTION
> INSERT INTO MonthSummary
> SELECT TSumDate=MAX(TSumDate),
> TSiteIP=MAX(TSiteIP),
> TTarget=MAX(TTarget),
> TKBytes=SUM(TKBytes),
> THitCount=SUM(THitCount)
> FROM DaySummary

This type of 'SELECT X=func(Y),X2=func2(Y2) FROM T' needs to be replaced with 'SELECT func(Y),func2(Y2) INTO X,X2 FROM T'.

> WHERE DATEDIFF(month, TSumDate, GETDATE()) > 3

Global change GETDATE() to SYSDATE. Review Oracle functions in documentation... Oracle doesn't use DATEDIFF, but has MONTHS_BETWEEN function... e.g. select months_between(sysdate,'01-JAN-99') 2* from dual SQL> / MONTHS_BETWEEN(SYSDATE,'01-JAN-99')

                         3.92099425

Up to you to figure out how to use available Oracle functions. By the way, you can add and subtract DATE values and the result is given in days, so you can get the days between by...  1* select sysdate-to_date('01-JAN-99') from dual SQL> / SYSDATE-TO_DATE('01-JAN-99')

                  118.552245



> GROUP BY TSiteIP,


>          TTarget,


>          DATEPART(month, TSumDate)

Oracle requires SQL (or any statement) to end with ';'!

> SELECT @rowsupdated = @@ROWCOUNT
> IF (@@ERROR <> 0) GOTO on_error

Oracle has 'EXCEPTION' handler, such that you can trap an error without having these IF error GOTO statements all over the place. I don't have a good example handy, see the docs.

> 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
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 29 1999 - 15:30:21 CDT