In article <7hpvah$t6$1_at_nnrp1.deja.com>,
david617_at_my-dejanews.com wrote:
> My company has created a client-server application which makes
> extensive use of Transact-SQL stored procedures in Microsoft SQL
Server
> 7.0. We are looking to port the server application to Oracle using
the
> Oracle Migration Workbench. This would be implemented through Java
> stored procedures.
>
> Does anyone have experience in migrating from SQL Server to Oracle
with
> 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:
- There are no temp tables in Oracle like you have in SQL Server.
Furthermore, you can't create & drop tables on the fly in your stored
procedures either, so you'll have to change any logic which uses temp
tables to logic which uses a permanent table with a "SessionId"
column that separates the data used by other connections.
- 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.
- Errors are handled differently. Oracle uses throw & catch logic
like C++, as opposed to checking @@error value. An automated migration
utility may be able to handle this issue adequately.
- (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 Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---
Received on Tue May 18 1999 - 17:20:52 CDT
