Dropping table using stored procedure

You'll need to loop through them.  You will also need to excape the '_' since that is a wildcard character.

Something like this:

begin
	for i in (select table_name from user_tables where table_name like 'UD\_%' escape '\') loop
		execute immediate('drop table ' || i.table_name);
	end loop;
end;
/

Select allOpen in new window

It looks to me like slightwv gave you a suggestion for this part of your question: "How can we drop a table if table name start with UD_%?".

But, how do you want this second part of your question: "but not any table passed as a parameter" to be included?  Do you want to be able to pass in a single table_name that starts with 'UD_' that should be excluded?  Or do you want to be able to pass in a comma-separated list of tables that start with 'UD_' that should be excluded?

Then, even though this action (dropping multiple tables with a PL\SQL procedure) is possible in Oracle, are you sure that is a good idea, or the best and safest way to solve a particular business problem?  I don't usually recommend doing DDL commands (create, drop alter, etc.) in PL\SQL procedures.  Can you tell us a bit more about the business problem that this may help with?