Object Views with REF columns cause disconnection when I try to DEREF them

From: Carlo Sirna <carlo.sirna_at_digisoft.it>
Date: Thu, 7 Mar 2002 19:17:35 +0100
Message-ID: <a68as1$cf84b$1@ID-135096.news.dfncis.de>

Hi, I think I met an oracle bug...
Currently I am using oracle 8.1.6 under Red Hat linux 6.1

I wrote a tool (for our internal use) that generates automatically object views from traditional tables, adding REF columns that map foreign keys...

This is an example of the generated sql script:

---

CREATE OR REPLACE TYPE TZonaCommerciale AS OBJECT (

   Codice                         VARCHAR2(6),
   Descrizione                    VARCHAR2(40)

)
/

CREATE OR REPLACE force VIEW v_ZonaCommerciale of TZonaCommerciale WITH OBJECT IDENTIFIER (Codice)
AS SELECT

     ZCO_COD,
     ZCO_DESC

  FROM ZONCOM
/

CREATE OR REPLACE TYPE TTavolaGeografica AS OBJECT (

   CodiceAgenteKIT                VARCHAR2(4),
   CodiceAgenteTradizionale       VARCHAR2(4),
   MediaMensileSu12Mesi           NUMBER(22,6),
   Codice                         VARCHAR2(6),
   CodiceTecnicoKIT               VARCHAR2(4),
   CodiceTecnicoTradizionale      VARCHAR2(4),
   CodiceASL                      VARCHAR2(4),
   CodiceZona                     VARCHAR2(6),
   AgenteKIT                      REF TAgente,
   AgenteTradizionale             REF TAgente,
   TecnicoKIT                     REF TAgente,
   TecnicoTradizionale            REF TAgente,
   ASL                            REF TSanitaLocale,
   Zona                           REF TZona

)
/

CREATE OR REPLACE force VIEW v_TavolaGeografica of TTavolaGeografica WITH OBJECT IDENTIFIER (Codice)
AS SELECT

     TAV_AGE,
     TAV_AGET,
     TAV_MESA,
     TAV_TAV,
     TAV_TEC,
     TAV_TECT,
     TAV_USSL,
     TAV_ZON,
   MAKE_REF(v_Agente,TAV_AGE)  ,
   MAKE_REF(v_Agente,TAV_AGET)  ,
   MAKE_REF(v_Agente,TAV_TEC)  ,
   MAKE_REF(v_Agente,TAV_TECT)  ,

   MAKE_REF(v_SanitaLocale,TAV_USSL) ,
   MAKE_REF(v_Zona,TAV_ZON)
  FROM TAVOLE
/

---

Now, thanks to the REF columns I can write sql commands like these:

  select t.Zona.Descrizione from v_TavolaGeografica t   select t.AgenteKit.NumeroFax from v_TavolaGeografica t

commands like these above do work perfectly for any view I created.

 The bug pops up only when I try to get the whole referenced object using the DEREF operator, but ONLY if also the pointed object contains REF columns.

in other words:

    select deref(t.Zona) from v_TavolaGeografica t

executes without any problem and returns the whole TZona object, but this one:

    select deref(t.AgenteKit) from v_TavolaGeografica t

causes an "ORA-03113: not connected to oracle" error:the server process crashes.

this happens for any REF column that references an object having REF columns.

another example is this pl/SQL piece of code:

  declare

     a TAgente;
  begin
    select deref(t.AgenteKit) into a
    from v_TavolaGeografica t
    where codice='374C';
  end;

This code does cause an ORA-03113, while the following one does its job without any problem:

declare
  pa REF TAgente;
  a TAgente;
begin
  select t.AgenteKit into pa
  from v_TavolaGeografica t
  where codice='374C';
  select deref(pa) into a from dual;
end;

Where is the problem? initialization parameters? oracle 8i 8.1.6 known bugs? Red Hat 6.2 issues? Does this work on Oracle 9i?

Thanks,

 Carlo Sirna
 DigiSoft srl Received on Thu Mar 07 2002 - 12:17:35 CST