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 ASBind 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.
SELECT * FROM dual
WHERE dummy = :1
ORA-01027: bind variables not allowed for data definition operations
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.
CREATE VIEW index_monitoring_ddl ASClearly this statement is going to fail for the reason explained above.
SELECT 'ALTER INDEX ' || index_name || ' '
|| :1 || ' USAGE;' as DDL
WHERE table_name = :2;
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 ASIf you have apex, you can try out this example in seconds from within SQL Workshop.
SELECT a.table_name, b.monitor, 'ALTER INDEX ' || a.index_name || ' '
|| b.monitor || ' USAGE;' as DDL
FROM user_indexes a, (select 'MONITORING' as monitor
select 'NOMONITORING' as monitor
from dual) b
where table_name = :TAB
and monitor = :MON;
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