Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-04028: cannot generate diana for object ACCOUNT.TBL
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 COMPLEXQUERY
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
Abstract: ORA 4028 WHEN SELECT ON VIEW USES A FUNCTION THAT REFERENCES
THE
SAME VI
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 19063Received on Tue Sep 09 1997 - 00:00:00 CDT