Re: Oracle vs. Sybase troubles

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 2000/03/21
Message-ID: <4gagds80dqhqr355i3vosvmmafdd0eoks6@4ax.com>#1/1

A copy of this was sent to mike_legs <mike_legs_at_my-deja.com> (if that email address didn't require changing) On Tue, 21 Mar 2000 21:15:49 GMT, you wrote:

> I've run into a number of problems trying to write SQL code that is
>insensitive to Oracle (8i) or Sybase (System 11) peculiarities. (I
>realize that in itself may be my problem). Any comments, suggestions,
>or input will be greatly appreciated...
>First is the issue over stored procedures. Is there a way to return
>rows of data from an Oracle stored procedure? (I've read a number of
>correspondences supporting the yes and the no answer, and so yet remain
>somewhat confused.) If so, are there any mechanical differences
>between the datasets... ie will one look different than the other to my
>application program that calls the stored procedure?

see http://osi.oracle.com/~tkyte/ResultSets/index.html -- there are examples there.

the mechanics are different between the 2 databases. We do cursors -- they do not (for result sets from sp's).

>Second are the dates and date differences. Is there a way
>to "SELECT.." the difference between two dates and return a value of an
>arbitrary unit such that the same "SELECT.." query will work against
>Sybase and Oracle? (I know how to 'select datediff(units,date1,date2)'
>in Sybase and how to 'select date1-date2*units_multiplier' in ORACLE,
>and am guessing this is the only way to go about it)

see http://osi.oracle.com/~tkyte/Misc/DateDiff.html -- there are examples there as well for this.

>Lastly in my wordy cry for help is the UNION. Why does a union in
>Sybase preserve the size and type of its respective columns, but Oracle
>promotes 4byte (integers) to 8byte (doubles) and sizes varchar2()'s to
>accomodate the largest member of the union? Most importantly to me, is
>there a way to preserve 4byte (integers) in unions in Oracle?

Oracle does not have a 4byte integer -- there are NUMBERS and that is it. (there are no 8byte doubles either -- only numbers). There is nothing to preserve -- as they weren't there in the first place.

In Oracle8i -- the scale and precision are visible via the desc command:

ops$tkyte_at_8i> create table t1 ( x int ); Table created.

ops$tkyte_at_8i> create table t2 ( x int ); Table created.

ops$tkyte_at_8i> create view v as select x from t1 union select x from t2; View created.

ops$tkyte_at_8i> desc t1
 Name Null? Type

• -------- ------------ X NUMBER(38)

ops$tkyte_at_8i> desc t2
 Name Null? Type

• -------- ------------ X NUMBER(38)

ops$tkyte_at_8i> desc v
 Name Null? Type

• -------- ------------ X NUMBER(38)

they are all INTs with 38 digits of precision.

If we didn't size the varchar2's to support the largest member of the union -- what would it do? you can use substr( CNAME, 1, <MAXLEN> ) on each column (but then predicates that use these would not use indexes on them)...

>I know this is a long one, but any help on any of these issues will
>go a long way. Thanks in advance.
>Mike
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

-- 
http://osi.oracle.com/~tkyte/
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Tue Mar 21 2000 - 00:00:00 CST