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.

background to my Database posts

I have been working with various databases in different settings. Client Server, backend programming etc. Few times I have even worked in upgrading versions of databases. Though I have had interludes with many different databases including informix, DB2 and esoteric Integra SQL, I've been mostly working with Oracle databases for most part. From Oracle version 6 to Oracle 10g recently. I've seen the database mature so much. In fact, when I got back to Oracle after a little break, I kept coding in old style not using newer features. Only recently when I took up a job to do a conversion of database from Sybase to Oracle, I started realizing the how much Oracle had changed and all the new features in it.

When I did client server programming in PB, C or Java, I didn't have to explore all the features in the database. But when I had to see the insides of the programs in Sybase and try to convert to Oracle the best possible ways, the door opened up. Entire book of Oracle is wide open now and suddenly I am conscious about every little feature or deficiency in Oracle.

This blog is about my conversion project. Rather what I learn from it. I will try to add entries about the challenges in the conversion and any "discoveries".