Re: Converting SQL Server Stored Procedures to Oracle using Migration Workbench

From: <>
Date: Tue, 18 May 1999 22:20:52 GMT
Message-ID: <7hsp42$som$>

[Quoted] In article <7hpvah$t6$>, wrote:
> My company has created a client-server application which makes
> extensive use of Transact-SQL stored procedures in Microsoft SQL
> 7.0. We are looking to port the server application to Oracle using
> Oracle Migration Workbench. This would be implemented through Java
> stored procedures.
> Does anyone have experience in migrating from SQL Server to Oracle
> this tool, especially in regards to stored procedures? What
> difficulties have you experienced? Are there any types of client-side
> code that had to be modified to accomodate these changes? How about
> performance issues on Oracle versus SQL Server on the same NT box?

I haven't used the Oracle Migration Workbench, and I don't know what you mean by "Java stored procedures" in Oracle. However, I've done a fair amount of work in moving from Sybase/MS SQL Server to Oracle, and here are the big stumbling blocks you're going to hit:

  1. There are no temp tables in Oracle like you have in SQL Server. [Quoted] [Quoted] Furthermore, you can't create & drop tables on the fly in your stored [Quoted] procedures either, so you'll have to change any logic which uses temp [Quoted] tables to logic which uses a permanent table with a "SessionId" column that separates the data used by other connections. [Quoted]
  2. Stored procedures in Oracle cannot return result sets. Any procedures you use to return sets of data will probably have to be re-thought. Oracle passes all data into & and out of it through its parameters. Your client code will have to be changed to get procedure output from the parameters. [Quoted]
  3. Errors are handled differently. Oracle uses throw & catch logic like C++, as opposed to checking _at__at_error value. An automated migration utility may be able to handle this issue adequately.
  4. (Not a stored procedure issue) Oracle triggers are for the most part more versatile than Sybase/SQL Server triggers, since they have both statement level (Sybase-style) and row-level triggers. However, the statement level triggers in Oracle have no concept of the "inserted" and "deleted" tables - they're just "dumb" triggers that fire with no information about the nature of the action. Standard operating procedure in Oracle is to put all your interesting logic in the row-level triggers. However, the problem with row-level triggers is that the cannot affect other data in the same table, only the row in question. This means that any triggers that do things like updating sibling rows, or inserting backup/audit rows into the same table will not work. The best way to get around this problem is to change these triggers to stored procedures & alter the dependent code accordingly.

--== Sent via ==-- ---Share what you know. Learn what you don't.--- Received on Wed May 19 1999 - 00:20:52 CEST

Original text of this message