Could anyone shed some light on the following, im not very experienced with oracle and not too sure of the purpose of the data dictionary, so any usefull links would be much appreciated.
The ORACLE data dictionary view USER_OBJECTS includes the columns
OBJECT_TYPE may be ‘TABLE’,’ VIEW’, ‘INDEX’, ‘PROCEDURE’, ‘SYNONYM’ etc.
(i)firstly i need to use the technique of generating SQL from SQL, how do I write code that would drop all the views whose names begin with the string ‘TMP’, do you have to refer to a view differently to a table ?
(ii) secondly, amend this code so that all tables and views are dropped whose name begins with the string ‘TMP’. Bearing in mind that it is not possible to drop a table containing a primary key that is referred to by a foreign key in another table, would i just use cascase constraints here?