Open Source, Digital Asset Management, Media Asset Management

There are some things in Oracle that seam to be odd when you come from another SQL database.

One of these “odd” commands is that there is no automatic incrementation within the SQL syntax. For doing this you will need to create “Sequences”, which after all are more powerful then an automatic incrementation, but that is another topic. And the other “odd” thing, is that you are not able to drop many tables within a single SQL syntax.

But with some PL/SQL knowledge we will overcome this and store the code as a stored procedure.

PL/SQL gives us some ways to retrieve the status, table names, column names and much more from every table and schema. As an example; We can retrieve the column names of a table with:

SELECT COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE TABLE_NAME = UPPER(‘&Table_Name.’)

This would give us all the columns of the table you specify here. There is a nice TechNote by Oracle from 2004 about this. Just remember that Oracle is Case Sensitive and all references to columns and tables MUST be in uppercases!

With this know how armed we are able to retrieve a couple of tables with a simple SQL tag. So, let’s say we have a couple of tables that have the same prefix we could use something like (assuming that your table prefix is “demo_”:

SELECT object_name
FROM user_objects
WHERE object_type=’TABLE’
AND object_name LIKE ‘DEMO_%’

With this we will get a list of all the tables that start with “demo_” which we then can use for any purpose. Of course with only a select statement we are not (yet) able to do anything to the database like Insert, delete or update. The best for this is to put together a small Stored Procedure together with some conditions.

Since this post is all about to drop many tables in one go we can use the following procedure:

create or replace PROCEDURE delete_many_tables
(tablename IN VARCHAR2)
IS
cur integer;
begin
cur:= dbms_sql.OPEN_CURSOR();
for t in (select object_name from user_objects where object_type=’TABLE’ and object_name like tablename) loop
execute immediate
‘drop table ‘ ||t.object_name|| ‘ cascade constraints’;
end loop;
dbms_sql.close_cursor(cur);
end delete_many_tables;

Copy and paste this into your favorite SQL command tool (mine is the free SQLDeveloper from Oracle) and store the procedure.

Since this is a stored procedure we are able to pass in the prefix or the actual table names dynamically. Meaning that you call this stored procedure with the following syntax:

call delete_many_tables(‘DEMO_%’);

This would call the procedure, pass the value and drop all the tables with the given value. Feel free to modify this code or work upon it. I would appreciate any feedback or suggestions on this as well.

As a side note: If you want to call this within a ColdFusion page your code would look like this:

<CFSTOREDPROC PROCEDURE=”yourschemaname.delete_many_tables” DATASOURCE=”yourdatasource”>
<CFPROCPARAM VALUE=”DEMO_%” CFSQLTYPE=”cf_sql_varchar” type=”in”>
</CFSTOREDPROC>