Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Multi-Select Object Actions via XML Extensions

2 Mins Read

You want to drop, purge 10 tables, and you don’t want to select, click, click, click 10x. What’s a SQL Developer user to do?

Well, if you want to live dangerously, you could build a custom extension for SQL Developer to do that for you.

We tweaked the IDE to allow this in version 4.1.2 – if you’re on any version PRIOR to version 4.1.2, this won’t work.

BASICALLY…

You can have an item type of “TABLE” and a selectionMode of “BOTH”.

You can then have more than 1 selected object’s name and type fed to #OBJECT_NAMES# and #OBJECT_OWNERS#.

Pipe that to a pl/sql array and then dynamically generate a script, and voila!

I have 6 tables selected
I have 6 tables selected

And then that will give me…

Like I said, dangerous.
Like I said, dangerous.

Which will run…

 
DECLARE
  TYPE names_type IS TABLE OF VARCHAR2(32); /* +2 for quotes */
  names names_type := names_type('"PEEPS"', '"PRODUCTS"', '"REDACTED"', '"SALES"', '"SALES_UK"', '"SALES_US"');
  owners names_type := names_type('"SQLDEVDEMO"', '"SQLDEVDEMO"', '"SQLDEVDEMO"', '"SQLDEVDEMO"', '"SQLDEVDEMO"', '"SQLDEVDEMO"');
  sqltxt VARCHAR(200);
BEGIN
  FOR i IN names.FIRST .. names.LAST LOOP
    sqltxt := 'DROP TABLE ' || owners(i) || '.' || names(i) || ' cascade constraints PURGE';
    EXECUTE IMMEDIATE sqltxt;
  END LOOP;
END;
/

I recommend you NOT hit OK, and you instead copy it to a SQL Worksheet and run it there – after you have confirmed you’re in the RIGHT database, and have the RIGHT objects selected to be dropped, or stats collected, or renamed, or whatever your extension is going to do to them.

Here’s the code you need to add as an ACTION in the preferences.

Don't get mad after you accidentally do this in PROD.
Don’t get mad after you accidentally do this in PROD.
 
<?xml version="1.0" encoding="UTF-8"?>
 
<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.oracle.com/sqldeveloper/3_1/dialogs
                        http://xmlns.oracle.com/sqldeveloper/3_1/dialogs.xsd"
    xmlns="http://xmlns.oracle.com/sqldeveloper/3_1/dialogs">
 
 
	<item type="TABLE" reloadparent="true" selectionMode="BOTH">
		<title>XML Multi-Selection "Drop Table" Auto Exec Example...</title>
        <prompt type="check">
            <label>Cascade Constraints</label>
            <value>cascade constraints</value>
        </prompt>
        <prompt type="check">
            <label>Purge</label>
            <value>PURGE</value>
        </prompt>
		<prompt type="confirm">
			<label>Drop table(s) #OBJECT_NAMES#?</label>
		</prompt>
		<sql type="SCRIPT">
			<![CDATA[
DECLARE
  TYPE names_type IS TABLE OF VARCHAR2(32); /* +2 for quotes */
  names names_type := names_type(#OBJECT_NAMES#);
  owners names_type := names_type(#OBJECT_OWNERS#);
  sqltxt VARCHAR(200);
BEGIN
  FOR i IN names.FIRST .. names.LAST LOOP
    sqltxt := 'DROP TABLE ' || owners(i) || '.' || names(i) || ' #0# #1#';
    EXECUTE IMMEDIATE sqltxt;
  END LOOP;
END;
/
]]>
		</sql>
        <help>Drops the selected table(s).</help>
        <confirmation>
            <title>Confirmation</title>
            <prompt>Table(s) "#OBJECT_NAMES#" dropped</prompt>
        </confirmation>
	</item>
</items>
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

1 Comment

  1. Michel Ramirez Reply

    Hi,

    Great Post and thanks.

    In first image, i see [ XML Multi-Selection “Copy Table as Table n_” Manual Exec Exmeple…]

    this means taht DLL statement can be display in worksheet?
    If the anwers is Yes, can you shared xml extension 🙂

    Best Regards

Write A Comment