Re: Oracle external view schema discrepancy

On May 27, 2:38 am, stre..._at_yahoo.co.uk wrote:
> hi,
>
> one of our customers has created an external view (view on external
> base tables in a different schema / database type). we do a DESC on a
> table or view to find out what the datatype is for a field using the
> oracle account for our application.
> - if it is a VARCHAR2 we treat it as a string
> - if it is a NUMBER(x) we treat it as an integer
> - if it is a NUMBER(x,y) we treat it as a float
> we have noticed for external views the DESC can say that the field is
> an integer but when we do a SELECT it produces float values for the
> field.

That cannot happen.

> e.g. DESC on EXT_TBL_1 says FIELD01 is VARCHAR(5) but a SELECT
> FIELD01 FROM EXT_TBL_1 gives values of 2,4,3.5,2.1.
>

Which does not prove the values are numbers, only that they LOOK like numbers, and a VARCHAR2(5) is definitely NOT an integer. I expect they allign to the left, as varchar2 strings behave, not to the right, as numbers would display. So your terminology is incorrect for what you've described.

> my questions are:
> - is this a bug or expected behaviour?

They're really varchar2 values. Simply because the result isn't as you'd expect doesn't make it a bug. And it doesn't make VARCHAR2 values integers, either.

> - is there anyway to 'force' the view to give a particular DESC for
> fields at creation time?
>

Certainly, you can cast values to a particular type (well, within reason). Presuming this VARCHAR2(5) field contains nothing but values which to_number() can successfully convert you could:

create view ....
as select ..., ..., ..., ..., cast(field01 as number(9,4)) field01, ...
from ....

and you'll have your NUMBER type displayed for the view when you describe it.

> thanks,
>
> g

David Fitzjarrell Received on Tue May 27 2008 - 15:23:55 CDT