Dropping multiple columns: PLSQL and user_tab_cols

I have a table TABLE_X and开发者_开发知识库 it has multiple columns beginning with M_ characters which needs to be dropped. I decided to use the following PLSQL code to drop almost 100 columns beginning with M_ characters. Is it a good employment of dynamic sql and cursors? Can it be better? I didn't know more simple way since ALTER TABLE ... DROP COLUMN doesn't allow subquery to specify multiple column names.

declare
rcur sys_refcursor;
cn user_tab_cols.column_name%type;
begin
open rcur for select column_name from user_tab_cols where table_name='TABLE_X' and column_name LIKE 'M_%';
loop
fetch rcur into cn;
exit when rcur%NOTFOUND;
execute immediate 'alter table TABLE_X drop column '||cn;--works great
execute immediate 'alter table TABLE_X drop column :col'using cn;--error
end loop;
close rcur;
end;

Also. Why is it impossible to use 'using cn'?

This is a reasonable use of dynamic SQL. I would seriously question an underlying data model that has hundreds of columns in a single table that start with the same prefix and all need to be dropped. That implies to me that the data model itself is likely to be highly problematic.

Even using dynamic SQL, you cannot use bind variables for column names, table names, schema names, etc. Oracle needs to know at parse time what objects and columns are involved in a SQL statement. Since bind variables are supplied after the parse phase, however, you cannot specify a bind variable that changes what objects and/or columns a SQL statement is affecting.

The syntax for dropping multiple columns in a single alter statement is this:

SQL> desc t42
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 COL1                                               NUMBER
 COL2                                               DATE
 COL3                                               VARCHAR2(30)
 COL4                                               NUMBER

SQL> alter table t42 drop (col2, col3)
  2  /

Table altered.

SQL> desc t42
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 COL1                                               NUMBER
 COL4                                               NUMBER

SQL>

So, if you really need to optimize the operation, you'll need to build up the statement incrementally - or use a string aggregation technique.

However, I would question whether you ought to be running a statement like this often enough to need to optimize it.