SQLcl Alias Example For Dropping Multiple Objects.

SQLcl allows you to define "named" scripts or statements using the ALIAS command. 

Jeff has a neat example on his blog.

I created the following alias to make it really easy to destroy a database! drop objects.

RM Alias

Dropping tables, views, procedures is a daily occurrence for database developers.

The following SQLcl ALIAS may come in handy when you want to drop lots of objects at the same time. Just specify the

• object_type : TABLE, VIEW, PROCEDURE, ....
• object_name: Using a  LIKE expression.  Example: MY% 
• created: Number of days. Example: 1

A script is written with all the objects listed. The list of objects can be reviewed and an option to execute the script or cancel without dropping anything is provided.

Needless to say you can do a lot of damage dropping database objects and this makes it very easy to do so. So use at your own risk! Best to grab the alias code below and see if you can tweak it to suit yourself.

Test Env
create table app1_emp(col1 int);
create table app1_users(col1 int);
create table app1_books(col1 int);
create view app1_sales_view as select 1 as col1 from dual;
create procedure app1_proc_orders as begin null; end;
/

Examples

drop all the tables created in the last ~ 2 hours (0.1 of a day)
rm TABLE % 0.1

drop all tables starting with APP1_E created in the last day
rm TABLE APP1_E% 1

drop all views that end in SALES_VIEW created in the last 1000 days
rm VIEW %SALES_VIEW 1000

drop all objects that have E in their name created in the last ~ 2 hours
rm ALL %E% 0.1

drop all the tables created in the last day
rm TABLE % 1

MOD Alias

I expanded on this idea to make a more generic alias.
The mod alias allows you to specify the what kind command is defined around the chosen objects.

Example
CTAS on a bunch of tables you created in the last hour?
No problem

mod TABLE % 0.1 'CTAS :object_name :object_name_new;\n/'

Note

The ALIAS rm automatically creates a script with the DROP statements called sqlcl_rm_script.sql.

The ALIAS mod automatically creates a script with the defined commands called sqlcl_mod_script.sql

Its reasonable to invoke the alias, then not run commands directly. Instead cancel and edit the sqlcl_rm_script.sql or sqlcl_mod_script.sql.

Install

alias load rm.xml

rm.xml 

	<?xml version = '1.0' encoding = 'UTF-8'?>
	<aliases>
	<alias name="rm">
	<description><![CDATA[ DROP MULTIPLE OBJECTS. rm <object_type> <object_name_like_pattern> <since>. EXAMPLE : rm TABLE % 1; rm TABLE MY_ABLE 1; rm TABLE MY%ABL_ 10000 ; rm ALL % 0.3; rm VIEW MYV% 1
	]]></description>
	<queries>
	<query>
	<sql><![CDATA[SET TERMOUT OFF
	STORE SET sqlcl_pre_env
	SET HEADING OFF ECHO OFF FEEDBACK OFF PAGESIZE 0 VERIFY OFF
	SPOOL sqlcl_rm_script.sql
	PROMPT SET TERMOUT ON
	PROMPT SET ECHO ON FEEDBACK ON
	SET TERMOUT ON
	WITH selected_objects as (SELECT object_name, object_type, created, status, generated, oracle_maintained
	FROM user_objects
	WHERE ORACLE_MAINTAINED <> 'Y'
	AND UPPER(object_type) = CASE WHEN UPPER(:object_type) = 'ALL' THEN UPPER(object_type) ELSE UPPER(:object_type) END
	AND object_name LIKE :name
	AND created >(SYSDATE -:since )
	AND object_type IN ('TABLE','VIEW','PROCEDURE','TRIGGER')
	)
	select 'DROP ' || object_type || ' ' || object_name ||';' as cmd FROM selected_objects;
	SET TERMOUT OFF
	PROMPT SET TERMOUT OFF
	PROMPT SET ECHO OFF FEEDBACK OFF
	SPOOL OFF
	SPOOL sqlcl_rm_donothing.sql
	PROMPT SET TERMOUT ON
	PROMPT "PROMPT RM CANCELED"
	PROMPT SET TERMOUT OFF
	SPOOL OFF
	ACCEPT sqlcl_rm_continue CHAR PROMPT 'Type "YES" IF YOU WANT TO EXECUTE THE DROP STATEMENTS>' ;
	col command_choice new_val sqlcl_rm_choice
	SELECT CASE WHEN 'YES'=UPPER('&&sqlcl_rm_continue') THEN 'sqlcl_rm_script.sql' ELSE 'sqlcl_rm_donothing.sql' END AS command_choice FROM DUAL;
	@&&sqlcl_rm_choice
	@sqlcl_pre_env.sql
	SET TERMOUT ON
	]]></sql>
	</query>
	</queries>
	</alias>
	</aliases>

view raw rm.xml hosted with ❤ by GitHub

alias load mod.xml

mod.xml

	<?xml version = '1.0' encoding = 'UTF-8'?>
	<aliases>
	<alias name="mod">
	<description><![CDATA[MODIFY MULTIPLE OBJECTS. mod <object_type> <object_name_like_pattern> <since> <command>. :object_name and \n are replaced with appropriate values . EXAMPLE : rm TABLE % 1 'CTAS :object_name :object_name_NEW;\n/';
	]]></description>
	<queries>
	<query>
	<sql><![CDATA[SET TERMOUT OFF
	STORE SET sqlcl_pre_env
	SET HEADING OFF ECHO OFF FEEDBACK OFF PAGESIZE 0 VERIFY OFF
	SPOOL sqlcl_mod_script.sql
	PROMPT SET TERMOUT ON
	PROMPT SET ECHO ON FEEDBACK ON
	SET TERMOUT ON
	WITH selected_objects as (SELECT object_name, object_type, created, status, generated, oracle_maintained
	FROM user_objects
	WHERE ORACLE_MAINTAINED <> 'Y'
	AND UPPER(object_type) = UPPER(:object_type)
	AND object_name LIKE :name
	AND created > (SYSDATE - :since))
	select regexp_replace(regexp_replace(:script,':object_name',object_name),'\\n',CHR(10)) as cmd FROM selected_objects;
	SET TERMOUT OFF
	PROMPT SET TERMOUT OFF
	PROMPT SET ECHO OFF FEEDBACK OFF
	SPOOL OFF
	SPOOL sqlcl_mod_donothing.sql
	PROMPT SET TERMOUT ON
	PROMPT "PROMPT MOD CANCELED"
	PROMPT SET TERMOUT OFF
	SPOOL OFF
	ACCEPT sqlcl_mod_continue CHAR PROMPT 'Type "YES" IF YOU WANT TO EXECUTE THE STATEMENTS>' ;
	col command_choice new_val sqlcl_mod_choice
	SELECT CASE WHEN 'YES'=UPPER('&&sqlcl_mod_continue') THEN 'sqlcl_mod_script.sql' ELSE 'sqlcl_mod_donothing.sql' END AS command_choice FROM DUAL;
	@&&sqlcl_mod_choice
	@sqlcl_pre_env.sql
	SET TERMOUT ON
	]]></sql>
	</query>
	</queries>
	</alias>
	</aliases>

view raw mod.xml hosted with ❤ by GitHub