ORA-04028: cannot generate diana for object

Wednesday, 10.06.2009 – Dejan

Zadnjih dana me ba┼í krenulo sa ─Źudnim Oracle gre┼íkama…

Prvo u ponedeljak dobijem jednu gre┼íku, koju do sada u ┼żivotu nisam vidio:

ORA-04028: cannot generate diana for object name

Potra┼żio sam na MetaLinku neko obja┼ínjenje ili rje┼íenje, ali ne na─Ĺoh ni┼íta korisno. Samo definiciju pojma diana (DIANA je skra─çenica za ‘Descriptive Intermediate Attributed Notation for Ada‘) i obavijest, da je ova gre┼íka rije┼íena jo┼í u verziji Oracle 8. Otkud onda odjednom ta gre┼íka kod nas u bazi sa verzijom 10g?

ORA-04028: cannot generate diana for object name
Cause: A lock conflict prevented the generation of diana for an object.
Action: Check the syntax. If no syntax errors are found, report this error to Oracle World Wide Support.

PL/SQL is based on the programming language Ada. As a result, PL/SQL uses a variant of Descriptive Intermediate Attributed Notation for Ada (DIANA), which is a tree-structured intermediate language. It is defined using a meta-notation called Interface Definition Language (IDL). DIANA provides for communication internal to compilers and other tools.

DIANA is an abstract syntax tree for PL/SQL: it’s the output from the “front end” of the PL/SQL compiler (the parser and semantic analyzer). The committees that created Ada also created Diana, and any conforming Ada compiler is supposed to generate Diana. PL/SQL Diana is a modified version of the Ada Diana because PL/SQL includes SQL functionality not present in Ada.
At compile time, PL/SQL source code is translated into machine-readable m-code. Both the DIANA and m-code for a procedure or package are stored in the database. At run time, they are loaded into the shared memory pool. The DIANA is used to compile dependent procedures; the m-code is simply executed.

Gre┼íka se pojavila prilikom rekreiranja nekoliko “materialized views” (dalje u tekstu kao MV)┬ái potom┬árekompilacije nekoliko PL/SQL paketa, koji su koristili te MV. Nakon ┼íto┬ásu MV rekreirani, par PL/SQL paketa su postali invalidni i prilikom ponovne rekompilacije javljali su gorenavedenu gre┼íku ORA-04028. Poku┼íavao sam potom sa rekompilacijom spornih MV (ALTER MATERIALIZED VIEW COMPILE), ali da stvar bude ─Źudnija, svaki od tih┬áMV neposredno nakon rekompilacije postaje invalidan… Wtf!?

Ni Google mi nije pomogao, pa sam se odlu─Źio na radikalan korak – dropnuo sam doti─Źne MV i ponovo ih jednog po jednog kreirao. Nakon toga sam rekompilirao invalidne pakete i sve je radilo kako treba. Pojma nemam za┼íto se ta gre┼íka pojavila, niti kako bih ovaj problem mogao druga─Źije rije┼íiti… Ako se neko susretao sa ovom gre┼íkom i zna ┼íta raditi u tom slu─Źaju, neka napi┼íe komentar i pomogne meni, a vjerujem i drugima. ­čÖé

 

Druga gre┼íka se javlja pri ugnije┼ż─Ĺenom┬ákori┼ítenju analiti─Źkih funkcija (npr. SUM() OVER() i td.)┬ái┬áfunkcije TO_NUMBER(string) kada string sadr┼żi┬ánulu (‘0’) i to u slu─Źaju kada se radi operacija dijeljenja:

create table ora01476(
 num_id      number,
 string_col  varchar2(64)
 );
 
insert into ora01476
select numval, stringval
  from (select column_value as stringval 
          from table (sys.ODCIVarchar2List('0','1','2','3'))
       ) strings,
      (select column_value as numval
        from table(sys.odcinumberlist(0, 1,2,3,4))
      ) numbers;
      
commit;

Nakon ovog upita:

     
select CASE 
         WHEN num_id = 0 OR string_col='0'
         THEN 0
         ELSE sum(num_id / TO_NUMBER(string_col)) over(partition by num_id)
       END as result
  from ora01476;

javlja se greška:

ORA-01476: divisor is equal to zero

Za┬áovu gre┼íku sumnjam da je u pitanju bug, pa sam otvorio jedan Service Request na MetaLinku.┬áUpravo sam┬ánapravio test case, da poka┼żem kako se gre┼íka mo┼że u svako doba reproducirati, pa da vidimo ┼íta ─çe stru─Źnjaci iz Oraclea re─çi po ovom pitanju … Vi┼íe o ovome nakon ┼íto se Service Request zatvori.

  1. One Response to “ORA-04028: cannot generate diana for object”

  2. Baš sam nedavno naišao na tako nešto na Ask Tom, evo linka: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:571023051648

    By Djordje on Jun 10, 2009

Post a Comment