Viewing views in editor

[brendank]

We are having a peculiar problem where, if the view source of a view is attempted, the view source is retrieved but then PL/SQL developer attempts to query the view. Normally you wouldn't expect the view to be queried when you only want to view the source code.

Our views return a lot of data, so PL/SQL developer hangs until the session is killed or all the data is returned (~1hr).

This is with Oracle 8i and PL/SQL developer 5.1.6. It only happens with certain views which makes me think it could be the view text tripping up PL/SQL developer in some way.

Has anyone come across this problem before?

Regards,
Brendan

 

[Marco Kalter]

This can only happen if the view is invalid. PL/SQL Developer does not attempt to query the view, but it does attempt to validate it. If many (directly and indirectly) referenced objects are also invalid, they will also be implicitly validated. This can take a long time.

 

[brendank]

I appreciate this, but this doesn't seem to be the case here. The view is fairly straightforward and only references three objects (simple functions). The reason I believe it it querying the view is because the pga memory (v$sessat stat#20) consumed by the session try to view the view's source code ramps up to a level comparable with an attempt to query the view with no filter. Also, recompiling all the objects in the whole database only takes 1-2 minutes.

 

[Marco Kalter]

That's odd. We'll check it out.

 

[brendank]

Thanks Marco,

If its any help here is copy of the session view showing the progress of the session that has right clicked on the view name in the browser, and selected 'View' (nothing more). It shows a query of dba_views is made followed immediately by a query of the view.

4C73EF04 EMIR_MONTHLY_PAL 497F9B44 198154668 select * from EMIR_MONTHLY_PAL.V_KIO_CLAIM_CM_DRILL
4C73EF04 EMIR_MONTHLY_PAL 4F13B9D0 163736876 select * from sys.dba_views where owner = wner and view_na
4C73EF04 EMIR_MONTHLY_PAL 4ABE8EB4 3873071646 ( select get_drills_claims_
4C73EF04 EMIR_MONTHLY_PAL 4B9532E0 678297403 select * from v$version
4C73EF04 EMIR_MONTHLY_PAL 4B8F77E8 4284276465 select name from v$statname order by statistic#

 

[brendank]

As a follow up to the last message, the same effect occurs when trying to export the views in question.

Thanks again,
Brendan