Autonomous transactions

We continued our googling for more info on support for autonomous transactions and handling clob datatypes in procedures in non-oracle databases. For a moment we switched our focus to MS SQL server.

With its support to accept text datatypes as parameters in procedures it looked promising. On further analysis we found it has a feature called table variables to allow 'kind of' autonomous transaction behaviour. The so called table variables does not participate in the transaction!! so one can store data in these variables and can rollback the transaction without any worries. The data in the table variables shall be retained as they are independant of transaction.

Unfortunately even this is not helpful for us as we want to control transactions from our Java program, not in the procedures. Our program would start a transaction and then invoke a procedure so rollback shall be invoked by the application after the procedure invocation is over, based on the output generated by the procedure.

Finally with its support for handling text/clob, MS SQL server seems to be better choice than Sybase.

Converting from Oracle to Sybase

The product I work on is a rules engine where routing logic and validations are configured. We use Oracle database for storing trades and logic for business validations is coded in PL/SQL stored procedures. One fine day our client asked us to start analysis on using Sybase instead of Oracle for backend.

We started to list down the differences in features by comparing against ones we were using on Oracle. After spending a decent amount of time our issues/concerns kept growing, but never did we find something that is available in Sybase but not on Oracle. Below is the list we have compiled.

• A single row on a table in Sybase can not span more than one page. Due to this restriction a row can not occupy more than 2K (default page size).
• Identity is scantily equal to Synonym in Oracle. It can not stand alone, can have only column as identity in a table. No control over where to start and how much to increment.
• There is no concept of grouping stored procedures into packages.
• There are no equivalents available for varray/nested tables/hash maps in Sybase. To analyze further on usage of temp tables as an alternative.
• ROWID and ROWNUM sudo columns are not available in Sybase.
• Sybase supports 'text' as an equivalent of CLOB, but stored procedures can not have IN/OUT parameters of type text.
• Stored procedure arguments of type varchar have a maximum size limit of 255 (300 in 12.5?) in Sybase.
• Sybase does not provide any support for exception handling. One has to check for the global system variable @@error to check for any error or @@rowcount to realize NO_DATA_FOUND/TOO_MANY_ROWS conditions.
• No support for defining variable/column type based on existing columns using %TYPE construct. One has to define the type explicitly.
• No support for controlling the procedure privileges at run time. In Oracle we have support to define a procedure to run with privileges of Invoker/Definer at run time.
• No support for autonomous transactions in Sybase.
• There is no INOUT parameter type, OUT is functionally same as INOUT

Datatype mapping

Oracle	Sybase
number	decimal/numeric
date	date
timestamp	datetime
varchar2	varchar
clob	text

Except for 'autonomous transactions' & 'clob handling' remaining are not show stoppers, we can always find some workarounds. With these many features lacking I wonder how Sybase is a strong contender for Oracle - could someone please help me !!