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