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...

ORA-01720: grant option does not exist for SCHEMA.TABLE

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You are getting ORA-01720: grant option does not exist for SCHEMA.SOME_TABLE while trying to execute CREATE OR REPLACE VIEW V_MY_VIEW because you added the new table SOME_TABLE residing on a different schema and you lack privileges on SOME_TABLE. The you go and ask for SELECT privilege on the table SOME_TABLE with GRANT OPTION.
After obtaining the privilege you retry the operation but you still get:

ORA-01720: grant option does not exist for SCHEMA.SOME_TABLE

This happens because since version 11g you cannot simply replace a view containing a newly added table even if you have the SELECT privilege WITH GRANT OPTION.

You need to DROP VIEW V_MY_VIEW first and then re-create it.

The error message is misleading to say the least, a more specific error should be raised instead in these situations.

See message translations for ORA-01720 and search additional resources.

ORA-01027: bind variables not allowed for data definition operations

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

As I've been summoned up in an OTN forum posting (thanks Andrew!), i need to quickly fill the gap before someone starts thinking that i don't check regularly my statistics to see where the people come from ;-)

It's easy to reproduce this (unlikely?) error:

CREATE VIEW test_view AS
SELECT * FROM dual
WHERE dummy = :1

ORA-01027: bind variables not allowed for data definition operations

Bind variables are allowed in DML statements (SELECT, UPDATE, DELETE) or inside programs, but not inside DDLs (the Data Definition Language statements) and the fact that CREATE VIEW contains a SELECT statement is certainly not enough to make Oracle happy.

Now, the real problem is to understand why you may want to create a view containing bind variables.

Views are meant to hide the complexity of certain queries, especially when you need to calculate values using non-trivial functions or to perform joins, correlated subqueries and so on.
For example:

CREATE VIEW index_monitoring_ddl AS
SELECT 'ALTER INDEX ' || index_name || ' '
     || :1 || ' USAGE;' as DDL
FROM user_indexes
WHERE table_name = :2;

Clearly this statement is going to fail for the reason explained above.
My intention is to create a view returning a string containing an executable statement that will turn on or off index monitoring on all indexes defined against a user-defined table name.

The whole point behind using bind variables is in reducing the presence of one-off SQL statements in the shared pool. As you know SQL queries containing bind variables can be parsed once and executed many times, so you are saving space in the buffer and CPU time by avoiding unnecessary parsing.

Let's put aside the fact that probably we are not going to turn on and off index monitoring so often in a real world scenario, so using bind variables in a situation like this is not worth the effort, but the example i give is just meant to show that even when it seems hard to avoid using bind variables, it could be just a matter of re-designing the query.

So, how can we achieve the same functionality without using bind variables?

Let's begin with the easy part:
the filter predicate "WHERE table_name = :2" is totally useless in this context.
I can define my view without it and apply the filtering directly when i execute the query on the view.
All right, but then, how can i pick the right MONITORING/NOMONITORING keyword if i cannot pass it as a parameter?

CREATE OR REPLACE VIEW index_monitoring_ddl AS
SELECT a.table_name, b.monitor, 'ALTER INDEX ' || a.index_name || ' '
      || b.monitor || ' USAGE;' as DDL
FROM user_indexes a, (select 'MONITORING' as monitor
                        from dual
                      UNION ALL
                      select 'NOMONITORING' as monitor
                        from dual) b
/

select DDL
 from index_monitoring_ddl
where table_name = :TAB
and monitor = :MON;

If you have apex, you can try out this example in seconds from within SQL Workshop.

So, it turns out that i could achieve the same result without putting bind variables inside the view.
I don't say that this technique will work in every case, but when you get certain weird errors you must exercise your fantasy...

See message translations for ORA-01027 and search additional resources