Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-04028: cannot generate diana for object ACCOUNT.TBL

Re: ORA-04028: cannot generate diana for object ACCOUNT.TBL

From: Peter Mroz <junkpmroz_at_junkdomaincorp.com>
Date: 1997/09/09
Message-ID: <34154F0D.8E6@junkdomaincorp.com>#1/1

I'm sure most of you will be relieved to hear that this problem is solved. I spoke to Oracle technical support and they determined that the problem is due to a bug in Oracle. It's there in 7.3 and 8; to fix it would require lots of internal rewrites.

The bug occurs when you try and create a view from another view using a SELECT statement. The SELECT statement contains a PL/SQL function that also does a select against the original view. Are you with me so far? The solution is to have the PL/SQL function look at the original database tables instead of the view. I tried that and my program works.

Peter (having fun with PL/SQL) Mroz

Here's info from Oracle on the bug:

To:	Peter Mroz
cc:	us.oracle.com 
Subject:	Bug 516023



Peter,

        Here is that information I found in our bug database. I believe it is exactly the problem you are seeing and hope that the work around works for you.
If not, please call me on this tar number and you should get directly to me or
my voicemail. If I haven't heard from you within two weeks, I will assume the
the workaround worked for you.

***************************( Source: UK-TECREP 
)*****************************
Article-ID:         <Bug:516023>
Related-Bugs:       <BugMatrix:516023>
Subject:            ORA-4028 WHEN QUERYING A SNAPSHOT WITH A COMPLEX
QUERY

*
    RPTNO: 516023                   Base RPTNO: 422823
 Customer:                          Created: 16-JUL-97
Component: RDBMS        Comp Ver: 7.3.2.3         Rel St: P Updated:
29-JUL-97
 Sub Comp: DICTIONARY  RDBMS Ver: 7.3.2.3                        By:
   Status:
  Sub Pri: 2   Severe Loss of Service                  Fixed In Ver:
      O/S: 116 Pyramid DC/OSx MIPS Unix

 PL Group: UNIX Gen/Port: G Port Excep: N Error #: ORA 4028 Pub: Y

 Abstract: ORA 4028 WHEN SELECT ON VIEW USES A FUNCTION THAT REFERENCES THE
SAME VI



*
*PROBLEM: They get the ora 4028 error periodically when running a query against a read only snapshot. The same query works find against the base table. The query contains an IN clause with another select statement that uses decode.
If we use distinct values in the IN clause the query succeeds. The problem had actually gone away for a while but then it started occurring
again and can be reproduced everytime now. WORKAROUND: THE workaround is to use the base table instead of the snapshot.

RELATED BUGS: 441813 Ms. D.H. Harvey wrote:
>
> Peter Mroz (junkpmroz_at_junkdomaincorp.com) wrote:
> : I'm getting the following error message when I try to create a view with
> : an embedded PL/SQL function:
 

> : ORA-04028: cannot generate diana for object L1234.XY_BBB
>
> 04028, 00000, "cannot generate diana for object %s%s%s%s%s"
> // *Cause: Cannot generate diana for an object because of lock conflict.
> // *Action: Please report this error to your support representative.
>
> : Here's the simplified sequence of events:
 

> : 1. Data is stored in the account L1234 in three tables that have
> : identical columns:
 

> : L1234.XY_AAA
> : L1234.XY_BBB
> : L1234.XY_CCC
 
> : 2. I create a new account called L1234_VIEW, whose purpose is to contain
> : views onto L1234
 

> : 3. I create a view that contains the union of all three tables
 

> : create or replace view L1234_VIEW.XY_ALL as
> : select a,b,c,d,e, 'A' source from L1234.XY_AAA union
> : select a,b,c,d,e, 'B' source from L1234.XY_BBB union
> : select a,b,c,d,e, 'C' source from L1234.XY_CCC;
 

> : 4. I then create a view onto L1234_VIEW.XY_ALL that filters stuff out
> : between AAA, BBB, and CCC, using a PL/SQL function. This is the step
> : that fails, giving me the above-mentioned error message.
 

> : create or replace view L1234_VIEW.XY_FILTER as
> : select * from L1234_VIEW.XY_ALL
> : where
> : source = L1234_VIEW.filter_source(a, b, c);
 

> : ORA-04028: cannot generate diana for object L1234.XY_BBB
 

> : I must have outdated Oracle 7 manuals because error message ORA-04028 is
> : not listed.
 

> : If you have any ideas email me or post.
 

> : Thanks a bunch,
> : Peter
>
 

-- 
Peter Mroz				Domain Solutions Corporation
Tel: 610-892-7540			1023 East Baltimore Pike, Suite 205
Fax: 610-892-7616			Media, PA 19063
Received on Tue Sep 09 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US