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

[Peter Mroz]

I forgot to mention platform and version stuff...

HP 9000/735 running HP-UX 9.01

Oracle 7.1.6.2.0

PL/SQL Release 2.1.6.2.0

>
> 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
>
> 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
>
> PS (prevent spam): to email me remove the words junk from my address
>
> --
> Peter Mroz Domain Solutions Corporation
> Tel: 610-892-7540 1023 East Baltimore Pike, Suite 205
> Fax: 610-892-7616 Media, PA 19063

[Peter Mroz]

[Ms. D.H. Harvey]

Peter Mroz (junk...@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:

[Peter Mroz]

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

--