Hi Connor.
Hope everything is fine with you. :-)
Yes, I was hoping to get around it by creating the view "with read only"
then I get NO för inserts, updates and deletes in
user_updatable_columns. I even joined the base table with dual. But I
buy it.
The strange thing is that I have a anonymous pl/sql block that is
looping a number of calls to procedure inside a package, the procedure
reads from this view.
I start the loop. Drop the index. I see that the view, the package body
and the sql inside the package body become invalid, on the next
iteration, the sql gets a new plan_hash_value (fts instead of index
unique scan) and becomes valid, and also the view, but not the package
body, the loop calls the procedure inside the invalid package body, some
more times without problems.
If I let the loop finish, the package body is still invalid and will be
valid on the next call. But if the loop is running and I start the same
loop from another session, it hangs on a library cache pin (on the
package body) because it wants to revalidate it, and it hangs there
until the first loop finish.
Maybe I am just curious why the view can become valid but not the
package body. There are no global variables, use of database types or
anything inside the package.
Take care!
/jocke
2021-01-10 23:54 skrev Connor McDonald:
From a colleague with code access
The invalidation is a catch all to ensure that updatable join views are
re-examined, because the existence/dropping of the unique index may alter its
"updateable-ness"
Cheers,
Connor
Connor McDonald
===========================
email: connor_mcdonald@xxxxxxxxx
blog: connor-mcdonald.com
twitter: connor_mc_d
On Thursday, 7 January 2021, 09:23:48 pm AWST, Jocke Treugut
<jocke@xxxxxxxxxxxxxxxxxx> wrote:
Hi Table,
for many many years ago, I read all the important Oracle manuals every year
(it was probably around six-eight on that reading list). I have probably not
done that so much since Oracle 11.
In the excellent Development Guide is the answers to many of my questions.
Yes, dropping a unique index makes views invalid if they reference a column
that existed in that index (is that necessary when the view is created with
read only, so no dml can happen through the view) and after that chapter
comes Using Edition-Based Redefinition.
The answers are out there :-)
Stay safe everyone and be happy!
/Jocke
