Oracle Spatial, dynamic SQL, a view and the strange problem of a SDO_GEOMETRY bind variable

The title sound like an old italian joke and indeed I wasted 4 hours on this joke before having the epiphany.

Scenario:

A set of functions accepting a few parameters, in some cases one of the parameters can be a geometry object, while in other cases it's a CLOB containing a geometry in WKT format.

These functions must execute dynamic SQL queries built on some of these parameters.

The data suitable for testing lies in a different schema and the table's columns would need to be renamed, so I decided instead to create a view in that schema with the expected column names, then I granted SELECT to the schema containing the functions.

I ran the first test with functions working on CLOBs and they worked fine.

Then I ran a test on a function taking the SDO_GEOMETRY parameter whose result should be identical to the result of the previous test.

It wasn't, it returned nothing.

Among the dozens of experiments I made to sort out what was wrong, I altered the function such that it converts the SDO_GEOMETRY object back and forth within the OPEN-FOR-USING statement and to my surprise the function worked correctly.

But even if it worked well, I didn't like the fact I had to do this unnecessary step, so I continued investigating.

At a certain point, I decided to try bypassing the view, so I copied some data into a test table and repeated the test.

Now it worked

Then came the epiphany.

I granted SELECT on the base table of the view and repeated the test using the view.

It worked.

I revoked the SELECT  from the base table and, again, it stopped working.

Bingo.

Is it some sort of bug or is it expected behavior?
I am still undecided.

Certainly an error message would have helped to point me in the right direction much earlier.

At any rate the problem affects only the dynamic SQL containing a view in a different schema when passing a SDO_GEOMETRY value to the bind variable which is later used as argument to SDO_FILTER or any other spatial operator in the WHERE clause. If that bind variable is used as argument elsewhere, it seems to work regardless of the privilege on the base table.

I think I'll take a day off tomorrow, I have had enough for this week...