Sybase VS Oracle

Today in a meeting they were discussing differences between Oracle and Sybase.

1. Values Larger than Column

While inserting a value into a string column, Sybase truncates the value to the length of the database column. Oracle on the other hand, doesn't do such a thing. (To me this sounds like the proper behavior, but then again I've been working with Oracle too long). If you insert a value that's longer than the column, then it will throw the following exception:

9i above below:
ORA-01401: inserted value too large for column

Oracle 10g:
ORA-12899: value too large for column "SCOTT"."EMP"."ENAME" (actual: 12, maximum: 10)



Example:

create table t1(code varchar2(4));

insert into t1 values ('TEST');

insert into foo values ('ATOMS'); <-- Generates the above exception in Oracle, whereas in Sybase this will be inserted as ATOM. (or so I was told)

Note: In Sybase this behavior can be mimicked by using
set string_rtruncation on

The default setting is off. To mimic Sybase behavior in Oracle use the substr function to get the right size of the value.

2. Case sensitivity in WHERE conditions


Oracle comparisons are case sensitive. Depending on the case of the text and how it was input, we may have to bring both sides of = sign to the same case before comparing.

Sybase ignores the case of the actual text while comparing.


Example:

INSERT INTO t1 VALUES('test');

In Sybase the following SQLs return 1 Row.


SELECT FROM t1 WHERE code = 'TEST';

SELECT FROM t1 WHERE code = 'TeSt';

In Oracle both the above SQLs will not return any row.

Note: To mimic Oracle behavior in Sybase, the sorting order needs to be changed. This is done at the server level using utilities like sqlloc. To mimic Sybase behavior use lower or upper function on either side of the equal sign.