Oracle OCI - Sum() a zjisteni poctu desetinych mist secteneho sloupce

[Jan Serak]

Novák Jiří wrote:
> Dobry den preji !

Zdravicko!

> 
> narazil jsem na problem s kodem  ktery zjistuje pocet desetinych mist  
> numerickeho sloupce
> 
> result = OCIAttrGet (
>                param_handle,
>                OCI_DTYPE_PARAM,
>                &scale,
>                &scale_size,
>                OCI_ATTR_SCALE,
>                m_pSession->m_error_handle);

Tesi me, ze v Cesku taky nekdo pouziva OCI8 ;-)

> 
> Uvedena vec nefunguje pokud sloupec vznikl funkci sum()
> Ocekaval bych ze funkce vrati stejny pocet desetinych mist jako ma 
> puvodni secitany sloupec.
> Lec bohuzel  vraci 0 (puvodni sloupec je NUMBER(15,2))

Sum urcite nemuze mit vysledek stejneho typu jako jsou scitance z 
naprost jednoducheho duvodu: predstavte si tabulku T s milionem zaznamu, 
jejiz atribut A je typu number(1). Co by si chudak Oracle pocal se:

SELECT sum(a) FROM t;

;-)

Datovy typ v Oracle ma (na rozdil od klasickych programovacich jazyku) 
dve charakteristiky. Jedna urcuje, jakym zpusobem se muze s hodnotou 
daneho typu manipulovat (shodna s programovacimi jazyky se silnou 
typovou kontrolou), druha charakteristika je zpusob ukladani do databaze.

Druha charakteristika se uplatnuje vlastne pouze u definice tabulek, 
protoze pro typovou kontrolu podprogramu, views apod. neni relevantni.
Proto podprogramy nemaji parametry (resp. navratovou hodnotu) otypovany 
NUMBER(15,2), ale pouze NUMBER. To je i pripad funkce sum(), ktera vraci 
vzdy NUMBER. Analogicky, navratova hodnota funkce(-i) to_char je 
VARCHAR2 a nikoli VARCHAR2(<maximalni_delka>).

Jak to tedy je?

Oracle fyzicky uklada hodnoty datoveho typu NUMBER nikoli binarne, nybrz 
pomerne zbesilou variantou Binary-Coded-Decimal (BCD). Duvodem neni nic 
jineho nez rychlost zpracovani, coz v tento moment muze znit podivne (s 
binarnimi cisly muze pocitac pracovat nativne, kdezto tento paskvil...).
Zkuste si vsak spocitat, kolik bajtu zabere cislo 10^{120}, pripadne 
10^{-120}, coz jsou priblizne limity datoveho typu NUMBER, ktery zabere 
nejvyse 21 bajtu, ovsem v pripade 1^{120} by zabral 2 bajty(!).

Proto Oracle s datovym typem NUMBER pracuje jako s celociselnou mantisou 
a exponentem dekadickeho zakladu. Vice je to popsano jako nepovinne 
cteni v OCI Developers Guide.

Oracle rozlisuje mezi NUMBER(x), resp NUMBER(x,y), coz povazuje za cisla 
s pevnou desetinnou teckou, a NUMBER, coz povazuje za cisla s plovouci 
desetinnou teckou. V prvnim pripade tedy poskytuje jakousi dalsi 
kontrolu hodnoty pred jejim ulozenim do databaze (u NUMBER ulozi cokoli, 
co se vejde do limitu definovaneho pro NUMBER, tj. delka celociselne 
mantisy do 38, exponent dekadickeho zakladu v nejakem "hodne velkem" 
intervalu).

/* off-topic:
Limit 38 je IMHO zvolen velmi dobre vzhledem k praktickemu pouziti takto 
vysoce presnych cisel, staci si jen uvedomit, ze "nasobkove predpony" 
(nejsem si jist, jestli je to fyzikalne spravny termin) definovane SI 
jsou od 10^{18} do 10^{-18} a ze je potreba jedno "misto" na jednotky 
(10^0) a jedno na znamenko. Vetsi presnost opravdu neni prakticky potreba.
*/

Co s tim v OCIAttrGet?

Problem neni ve funkci samotne, ale v tom, jak chapete atribut 
OCI_ATTR_SCALE. Nyni cituji z dokumentace Oracle:

OCI_ATTR_PRECISION: The precision of numeric type attributes. If the 
precision is nonzero and scale is -127, then it is a FLOAT, else it is a 
NUMBER(precision, scale). For the case when precision is 0, 
NUMBER(precision, scale) can be represented simply as NUMBER.

OCI_ATTR_SCALE: ... (presne tyz text jako u OCI_ATTR_PRECISION)

Vas pripad, kdy OCI_ATTR_SCALE je 0, se rozpada na 2 moznosti v 
zavislosti na hodnote OCI_ATTR_PRECISION. Je-li ruzna od 0, pak se jedna 
o datovy typ NUMBER(x) (totez co NUMBER(x,0)), tj. pevna desetinna 
tecka, nejnizsi ukladany rad jsou jednotky. Pokud je vsak rovna 0, pak 
se jedna o datovy typ NUMBER, tj. plovouci desetinna tecka a pocet 
desetinnych mist je tedy irelevantni.

OCIAttrGet Vam skutecne rika pravdu. Zkuste v SQL*Plus tuhle vec:

CREATE TABLE zkusto AS (SELECT sum(1) suma FROM dual);
describe zkusto

Uvidite neco takoveho:

  Name                  Null?    Type
  --------------------- -------- ----------------------------
  SUMA                           NUMBER

Pak zkuste funkci OCIAttrGet zjistit hodnoty atributu OCI_ATTR_PRESISION 
a OCI_ATTR_SCALE nad sloupcem SUMA tabulky ZKUSTO. Uvidite, ze dostanete 
v obou pripadech 0.

> 
> a) existuje jina moznost jak se zeptat ?

Musite si uvedomit, co chcete zjistovat. Funkci OCIAttrGet zjistujete 
atribut datoveho typu, NIKOLI VLASTNOST KONKRETNI HODNOTY.

> b) lze presnost souctovaneho sloupce vynutit v dotazu na server ?

Obavam se, ze nikoli. Co zajima uzivatele, ktery vznesl dotaz z uvodu 
tohoto prispevku. Vysledek, nebo informace o tom, ze "se soucet neda 
nacpat do datoveho typu scitancu"? IMHO to prvni.

Obavam se, ze nechcete vynucovat presnost nejakeho sloupce, ale typ 
funkce sum().

Tedy, jestli presne chapu, co myslite tim "presnost souctovaneho sloupce".

> c) muze mit vliv jestli se ptam po Prepare nebo po Execute ? (pri 
> zjistovani velikosti by mohlo ....)

Nikoli. Uz po Prepare je jasny typ vyrazu, ktery se pri Execute nemeni 
na zaklade konkretnich hodnot.

> 
> Uvitam  figl v OCI, i obecne info tykajici se treba i jineho rozhrani k 
> oracle.

Nativnejsi rozhrani k Oracle, nez je OCI, najdete tezko ;-)
To, co zjevne ocekavate je, ze funkce sum() ma vracet hodnoty v pevne 
desetinne tecce. Ale v jake desetinne tecce? NUMBER(15,2)? Proc zrovna 
tuhle a ne treba NUMBER(18,8)? Nebo NUMBER(12,-23)?

Preji mnoho zdaru s OCI8!

Jan Serak