Re: Oracle external view schema discrepancy
Date: Tue, 27 May 2008 13:23:55 -0700 (PDT)
On May 27, 2:38 am, stre..._at_yahoo.co.uk wrote:
> 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
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, ...
and you'll have your NUMBER type displayed for the view when you describe it.
David Fitzjarrell Received on Tue May 27 2008 - 15:23:55 CDT