The "Compile with PL/Scope" action affects only the session user

[rvo-cs]

If using an account with DBA privileges, I'd expect to be able to use the "Compile with PL/Scope" action in order to recompile any schema of my own choosing¹. This is implied by the context menu entry being available on every PL/Scope node in the object tree².

As of v1.0.0 that doesn't work, however, because the target of the action is—obviously, in the PL/SQL code—the value of the user function, which always equates to the session user.

Solution: per the documentation (link) the target schema of an action is identified by the #OBJECT_OWNER# substitution value, so that's what should be used in place of user, in order to enable the action to act on any schema, not just that of the session user.

Additionally, the execute immediate statement in the body of the <<synonyms>> for-loop specifies the synonym to be recompiled without using a schema-qualified name, hence it implicitly targets a synonym in the current schema, whatever it may be—and in general it may be different from #OBJECT_OWNER#.

Solution: obvious.

Footnotes

1. With some safety restrictions, maybe, regarding SYS and other Oracle-maintained schemas. ↩

2. The action is enabled on all nodes of type CONNECTION and plscope-utils-root. ↩

[PhilippSalvisberg]

The Compile with PL/Scope... context menu is available on the connection node

and the PL/Scope node

The idea is to provide a PL/Scope quick start for the current schema. You compile the current schema with PL/Scope and then you are able to explore the additional metadata (e.g. under the PL/Scope node). If you decide to use PL/Scope on a regular basis in your development environment you should adjust your deployment process to ensure the deployed code is compiled with PL/Scope.

It's certainly not the idea to compile the whole database with PL/Scope or several chosen schemas on a regular bases via this action.

If you want to compile code by default with PL/Scope you can configure SQL Developer accordingly as the following screenshot shows:

From that point on every compile operation issued from SQL Developer will include by default PL/Scope. In the editor or the oder in other actions such as Compile All... on the Packages node. So there is no need to add the action Compile with PL/Scope on additional nodes.

Of course one could use #OBJECT_NAME# instead of the pseudo column USER in the code of the action. However, with the current scope of the action this has no impact. The USER pseudo columns works also with proxy connections. So, I do not see a reason to change that.

Regarding the scope of the synonym recompilation in the action. It is limited to the current schema. For private synonyms with a predicate on owner and for public synonyms with a predicate on table_owner. That's intended. Especially for public synonyms. The current schema is responsible for those public synonyms and if you want to get the full PL/Scope picture you have to compile them.

To be clear. I do not plan to extend the scope of this extension to cover DBA functionalities or extend the scope to multiple schemas. The scope is just the current connect. Nothing more and nothing less. Therefore I flagged this enhancement request as "wontfix".

[rvo-cs]

So there is no need to add the action Compile with PL/Scope on additional nodes.

The problem is, as of v1.0.0 the action is also made available on PL/Scope nodes of other schemas, by having it on plscope-utils-root nodes in addition to CONNECTION nodes.

	<!-- first item based on title is considered independent of minversion/maxversion -->
	<item connType="Oracle" type="CONNECTION" reload="true" minversion="11.1">
		<title>Compile with PL/Scope...</title>

And:

	<!-- Copy of the previous item. Changed type only. A menu cannot be assigned to multiple nodes. -->
	<item connType="Oracle" type="plscope-utils-root" reload="true" minversion="11.1">
		<title>Compile with PL/Scope...</title>

And that is misleading,

If the intent is to have the scope of the "Compile with PL/Scope" action limited to the schema of the session user ¹, then it should not be available on plscope-utils-root nodes, as those nodes are also present in the subtrees of other schemas.

Footnotes

1. The user function (that's how it's named in the SQL Reference manual) is equal to sys_context('USERENV', 'SESSION_USER'); this is not to be confused with sys_context('USERENV', 'CURRENT_SCHEMA'). ↩

[PhilippSalvisberg]

You are right. Under the Other Users node the PL/Scope node is visible for every other user.

This was not my intention, I think.

I see basically two options:

a) Amend the extension to ensure it is not shown under the Other Users node
b) Ensure the the editors and actions work also under Other Users, e.g. by using #OBJECT_NAME# as you suggested

[rvo-cs]

Enabling it for other users (except Oracle-maintained users, see issue #60) would be convenient for users working with DBA rights and tasked with maintaining multi-schemas applications. That's why I could never imagine that this was not the original intent.

[PhilippSalvisberg]

I think implementing b) would be sensible.