Oracle Stored Procedure to Ingres

[dbczak]

Hi,

I'm going to be migrating an oracle database to Ingres, and I'm trying to find a conversion tool that will convert the stored procedures.

I've found a conversion tool that will convert the PL/SQL stored procs to T-SQL for SQL Server because I think that T-SQL is closer to the Ingres Syntax, but I have not been able to find any tool nor documentation on converting stored procs from T-SQL to Ingres or PL/SQL to Ingres.

Any help would be greatly appreciated.

Thanks,
zak

[nick.bolton]

There was shift2ingres, the winner of CA's "million dollar challenge" - but it doesn't look like that project has been maintained for a few years now: SourceForge.net: Shift2Ingres

PL/SQL and T-SQL are very complex languages, that can run things like Java or VBScript natively. Ingres procedures offer relatively limited "database" functionality, so trying to convert an procedures in this way may not be an option.

[Bodo]

As Nick said PL/SQL and T-SQL are very complex, but often you can convert a complex language construct in a much less complex one in Ingres.
E.g. in SQLServer you can use cursors within a stored procedure:

DECLARE my_cursor CURSOR FOR SELECT ... FOR UPDATE;
OPEN my_cursor;
FETCH NEXT FROM my_cursor ...;
WHILE ::FETCH_STATUS = 0
BEGIN
    <statements>
    FETCH NEXT FROM my_cursor;
END;
CLOSE my_cursor;
DEALLOCATE my_cursor;

You can't declare a cursor in Ingres DB Procedures, but there is much easier way to get the same:

FOR SELECT ...
DO
    <statements>
ENFDOR;

An conversion tool will often just try a 1:1 translation, which often also means bad performance, etc.
Using your gray cells is a viable alternative