Thanks for the question, Gerd.

Asked: February 13, 2007 - 11:39 am UTC

Last updated: May 29, 2008 - 11:32 am UTC

Version: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

hallo tom,

i have a problem with an PL/1 program referencing my views
(especially with the number datatype).

i query my table with a simple select, without any summary or others,
and make this select to a view!

Create view myview as
SELECT CHARGEN_NR, CHARGEN_LAUF_NR, BESTAND_GEW, BESTAND_STK
FROM VMBT040;

SQL> desc myview
Name Null? Typ
----------------------------------------- -------- ----------------------------
CHARGEN_NR NOT NULL CHAR(6)
CHARGEN_LAUF_NR NOT NULL NUMBER(2)
BESTAND_GEW NOT NULL NUMBER(6)
BESTAND_STK NOT NULL NUMBER(6)

and then i want a summary of my number fields

Create view myviewsum
(CHARGEN_NR, CHARGEN_LAUF_NR, BESTAND_GEW, BESTAND_STK) AS
SELECT CHARGEN_NR, CHARGEN_LAUF_NR, sum(BESTAND_GEW) , sum(BESTAND_STK)
FROM VMBT040
GROUP BY CHARGEN_NR, CHARGEN_LAUF_NR;

SQL> DESC myviewsum
Name Null? Typ
----------------------------------------- -------- ----------------------------
CHARGEN_NR NOT NULL CHAR(6)
CHARGEN_LAUF_NR NOT NULL NUMBER(2)
BESTAND_GEW NUMBER
BESTAND_STK NUMBER

and then i lose then information of the NUMBERS data precision!

Is there a way to control the NUMBER data precision i this case?

thanks a lot

and Tom said...

well, I would argue you haven't - because the sum of a bunch of number(6) values is not a number(6) - it is quite possibly much much larger.

ops$tkyte%ORA9IR2> create table t( x varchar2(5), y number(6) );

Table created.

ops$tkyte%ORA9IR2> create or replace view vw
  2  as
  3  select x,
  4         sum(y) y_normal,
  5         cast( sum(y) as number(7)) y_cast
  6    from t
  7   group by x;

View created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> desc vw
 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 X                                             VARCHAR2(5)
 Y_NORMAL                                      NUMBER
 Y_CAST                                        NUMBER(7)

but, beware the size you pick!!!

ops$tkyte%ORA9IR2> insert into t select 'x', 999999  from all_users;

35 rows created.

ops$tkyte%ORA9IR2> select x, y_normal from vw;

X       Y_NORMAL
----- ----------
x       34999965

ops$tkyte%ORA9IR2> select x, y_cast from vw;
select x, y_cast from vw
                       *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The precision is not visible?

Gerd Dröscher, February 15, 2007 - 9:12 am UTC

hallo tom,

this solution is the write thing i need,
but, in my describe output of SQLPLUS, is the precision not visible?

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> CREATE TABLE t( x VARCHAR2(5), y NUMBER(6) );

TABLE created.

CREATE OR REPLACE VIEW vw
AS
SELECT x,
SUM(y) y_normal,
CAST( SUM(y) AS NUMBER(7)) y_cast
FROM t
GROUP BY x;

VIEW created.

DESC vw

SQL> DESC vw
Name Null? Typ
----------------------------------------- -------- ----------------------------
X VARCHAR2(5)
Y_NORMAL NUMBER
Y_CAST NUMBER

I really don't see the precision of Y_CAST?

thank you

February 15, 2007 - 10:31 pm UTC

all i can say is - in 9.2.0.8, it shows the correct information

Joe Bloggs, May 28, 2008 - 7:48 am UTC

Why does this...

CREATE OR REPLACE VIEW ABC AS
SELECT CAST ('19' AS INTEGER)  AS XYZ
FROM   DUAL

yield column XYZ as NUMBER in view ABC and not INTEGER ???

May 28, 2008 - 9:51 am UTC

ops$tkyte%ORA10GR2> create table t ( x integer, y number ) ;

Table created.

ops$tkyte%ORA10GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)
 Y                                                 NUMBER

there is no such thing as an 'integer' really, just a synonym for a NUMBER.

<quote src = Expert Oracle Database Architecture>

Non-native Number Types

In addition to the NUMBER, BINARY_FLOAT, and BINARY_DOUBLE types, Oracle syntactically supports the following numeric datatypes:

-----------------------------------------------------------------------
Note When I say 'syntactically supports,' I mean is that a CREATE statement may use these datatypes, but under the covers they are all really the NUMBER type. There are precisely three native numeric formats in Oracle 10g Release 1 and above and only one native numeric format in Oracle9i Release 2 and earlier. The use of any other numeric datatype was always mapped to the native Oracle NUMBER type.
-----------------------------------------------------------------------

*NUMERIC(p,s): Maps exactly to a NUMBER(p,s). If p is not specified, it defaults to 38.
*DECIMAL(p,s) or DEC(p,s): Maps exactly to a NUMBER(p,s). If p is not specified, it defaults to 38.
*INTEGER or INT: Maps exactly to the NUMBER(38) type.
*SMALLINT: Maps exactly to the NUMBER(38) type.
*FLOAT(b): Maps to the NUMBER type.
*DOUBLE PRECISION: Maps to the NUMBER type.
*REAL: Maps to the NUMBER type.
</quote>

Joe Bloggs, May 29, 2008 - 4:39 am UTC

Yesss, but... TOAD differentiates between INTEGER and NUMBER(38), so why can't basic Oracle?

Look at your example table thru TOAD. It displays INTEGER as an INTEGER, and not NUMBER(38). (Equally, if you add a column of NUMBER(38), it also differentiates between INTEGER and NUMBER(38)) !!!

It seems to me that there is some kind of translation layer missing when it comes to describing column datatypes.

May 29, 2008 - 8:09 am UTC

an integer is a number(38), a number(38) is an integer - they are synonymous, the same, no difference, syntatic sugar.

In Oracle there is no integer datatype really, they are all just NUMBER types

Joe Bloggs, May 29, 2008 - 8:27 am UTC

Then why is TOAD capable of displaying a column created as INTEGER, as INTEGER. But SQL*Plus it seems, is not???

There clearly is a record of the fact the column was created as INTEGER and not NUMBER(38), or TOAD would not be able to pick up this, hence my premise that some layer or something is missing when displayed by Oracle tools.

May 29, 2008 - 8:33 am UTC

no, we just query the dictionary and show you in Oracle terms what you have. What you have - when you say "integer" is a number with 38 digits of precision - sort of like a long long long type.

Nothing is missing.

We've been displaying "number" as the type for some 30 years....

TOAD manipulates data type from user_tab_columns

Maverick, May 29, 2008 - 9:16 am UTC

Joe,
TOAD looks at DATA_LENGTH,DATA_SCALE and DATA_PRECISION and changes the display from NUMBER to INTEGER Data type. As far as ORACLE is concerned, they are all NUMBER Data types.
SQL*PLUS strictly goes by DATA TYPE , I guess.

Joe Bloggs, May 29, 2008 - 11:04 am UTC

CREATE TABLE ABC (A INTEGER, B NUMBER(38))
/

USER_TAB_COLUMNS says,

COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE

A           NUMBER         22                        0
B           NUMBER         22           38           0

so Oracle clearly sees INTEGER different to NUMBER(38). INTEGER has no data precision recorded.

So... back to my original question. Why can't a view definition report an INTEGER as an INTEGER?

Okay, so what's the big deal, u may be asking.

a). it's logically correct to do so,
b). I want my column to be seen as an INTEGER because it's more descriptive that way.
c). data hiding - if I want future programmers to not know whether they are coding off a table or a view, I would want INTEGERs reported back (to be consistent). Eg; surrogate PKs are always INTEGERs, but when VIEWed they get translated to NUMBER, which is err, misleading & confusing.

create or replace view xyz as 
select a,
       B, 
       CAST (b AS INTEGER)  AS B_INTEGER
from   abc
/

It is a bizarre state of affairs to have CAST (B AS INTEGER), not actually do what you'd logically expect it to.

May 29, 2008 - 11:32 am UTC

but, it won't happen, consistency - backwards compatibility - etc - it isn't a change worth making. The benefit - negligible, the possible negative side effects - huge.


ops$tkyte%ORA9IR2> CREATE TABLE ABC (A INTEGER, B NUMBER(38));

Table created.

ops$tkyte%ORA9IR2> desc abc
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 A                                                 NUMBER(38)
 B                                                 NUMBER(38)

that tells your programmers "there ain't no scale", it is therefore an integer in both cases.

as for the view, I guess I would suggest using the number(38) so as to get that point (for now, it was corrected in 11g - they missed the scale altogether - it was null)

ops$tkyte%ORA11GR1> CREATE TABLE ABC (A INTEGER, B NUMBER(38));

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create or replace view xyz as
  2  select a,
  3         B,
  4         CAST (b AS number(38))  AS B_number,
  5         CAST (b AS integer)  AS B_INTEGER
  6  from   abc
  7  /

View created.

ops$tkyte%ORA11GR1> select table_name, column_name, data_type, data_length, data_precision, data_scale
  2   from user_tab_columns
  3  where table_name in ('ABC','XYZ')
  4  order by table_name, column_name;

TABLE_NAME                     COLUMN_NAME                    DATA_TYPE  DATA_LENGTH DATA_PRECISION DATA_SCALE
------------------------------ ------------------------------ ---------- ----------- -------------- ----------
ABC                            A                              NUMBER              22             38          0
ABC                            B                              NUMBER              22             38          0
XYZ                            A                              NUMBER              22             38          0
XYZ                            B                              NUMBER              22             38          0
XYZ                            B_INTEGER                      NUMBER              22             38          0
XYZ                            B_NUMBER                       NUMBER              22             38          0

6 rows selected.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> set linesize 70
ops$tkyte%ORA11GR1> desc abc
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 A                                            NUMBER(38)
 B                                            NUMBER(38)

ops$tkyte%ORA11GR1> desc xyz
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 A                                            NUMBER(38)
 B                                            NUMBER(38)
 B_NUMBER                                     NUMBER(38)
 B_INTEGER                                    NUMBER(38)

some more strange behaviour

berny, November 17, 2008 - 1:31 pm UTC

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as scott

create table ta_cast (a number(10));
Table created

desc ta_cast
Name Type Nullable Default Comments
---- ---------- -------- ------- --------
A NUMBER(10) Y
create or replace view v_cast as
select a from ta_cast;
View created

desc v_cast
Name Type Nullable Default Comments
---- ---------- -------- ------- --------
A NUMBER(10) Y

create or replace view v_cast as
select cast(a as number(10)) a from ta_cast;
View created
desc v_cast
Name Type Nullable Default Comments
---- ---------- -------- ------- --------
A NUMBER(10) Y

create or replace view v_cast as
select a from ta_cast
union
select a from ta_cast;
View created

desc v_cast
Name Type Nullable Default Comments
---- ---------- -------- ------- --------
A NUMBER(10) Y

-------------------------------------------------
--all fine so far, but in the following view
--Oracle behaves like a teenager really:

create or replace view v_cast as
select cast(a as number(10)) a from ta_cast
union
select a from ta_cast;
View created

desc v_cast
Name Type Nullable Default Comments
---- ------ -------- ------- --------
A NUMBER Y

--that would be a workaround, to really get the cast
--you want:

create or replace view v_cast as
select cast(a as number(10)) a from
(
select cast(a as number(10)) a from ta_cast
union
select a from ta_cast
);
View created

desc v_cast
Name Type Nullable Default Comments
---- ---------- -------- ------- --------
A NUMBER(10) Y

Regards,
Berny

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

The precision is not visible?

TOAD manipulates data type from user_tab_columns

some more strange behaviour

More to Explore

PL/SQL demos

PL/SQL docs

Is it possible to control the view columns NUMBER data precision

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

The precision is not visible?

TOAD manipulates data type from user_tab_columns

some more strange behaviour

More to Explore

PL/SQL demos

PL/SQL docs