Re: Porting Sybase stored procedures to Oracle

> Sybase --> Oracle stored procedures

I did't see the original post but here is my reply... Did I post to this already? oh well I've been on vacation...

I have recently ported en entire warehouse management system from Sybase to Oracle. This a complex system that actualy directs all of the material movements in the warehouse. It was implemented nearly exclusively in stored procedures. Converting the code, with the help of a text editor, is easy and straight forward.

Rumor, from with-in Oracle Corp, is that the their professional services peole have a set of scripts to quickly convert Sybase Transact SQL to PL/SQL. I here it is *very* good. I had a name a while back but i've misplaced it.

On the issue of procedures returning multiple records, though I did not have to deal with it, there are several other alternatives. If the stored procedures are called from C (or other supported language) Oracle's Pro*C is able to fill and access C arrays very effeciently. This array ability is far supperior in some ways to Sybase's multi-record return ability (FASTER). Oracle also supports procedures using and returning tables as part ot the parameter string of the procedure.

There is another rumor about using Oracle's error handling abilities. When inside SQL*Plus the database passes arras of text back to SQL*Plus. I here it is possible to write your on handling interface...

Oracle implements temporary table but it is week compared to Sybase's #table_name contruct -- O only allows one field indexed by an binary integer.

BTW -- After the conversion the Oracle system performed at a peak transaction rate of about 40,000 warehouse business transactions an hour while the Syybase system can only do about 8,000 transactions an hour. The Oracle system was on a SPARC 1000 6 60MHZ CPU's and only 2.5 disk drives. The Sybase sstem used solid state disk drives, 125 MHZ CPU's and a lot of elbow grease...

Lawrence V. Rohrer
1500 Adams #105
Costa Mesa, California 92626
lrohrer_at_earthlink.net
http://www.earthlink.net/~lrohrer/index.html Received on Mon Aug 28 1995 - 00:00:00 CEST