SQL Developer: Execute current worksheet file via SQL*Plus (external tools)

TOAD for Oracle offers a nice feature called “Execute via SQL*Plus”, which executes your current TOAD file in a newly opened SQL*Plus session. It’s a great feature to execute SQL scripts in a controlled and fresh environment without the hassle of opening SQL*Plus, connecting to the database and executing your script manually.
Sad but true, SQL Developer doesn’t offer such a feature out of the box.

So here is how you can set it up yourself:

1. Select “Tools/External Tools” from the main menu.

2. Press the “New…” button in the “External tools” dialog window.

3. Program and arguments:

  • Program Executable:
    C:\WINDOWS\system32\cmd.exe  (May vary at your system!)
  • Arguments:
    /K start cmd /k sqlplus.exe ${sqldev.dbuser}@${sqldev.conn} @${file.path}
  • Run directory:
    ${file.dir}


4. Caption and icon
I have used the icon of the SQLDeveloper.exe here.

5. Menus and toolbars

6. Choose when the tool is enabled

7. Press the “Finish” button.

Now you can execute any file in SQL*Plus with a single button. Remember to assign a connection to your worksheet and to save your file before you start SQL*Plus.
When you start your new external tool, SQL*Plus will open a new database connection with the connection assigned to your worksheet and ask you for the password – that’s it.

Advertisements

5 thoughts on “SQL Developer: Execute current worksheet file via SQL*Plus (external tools)

  1. Hello Matthias,

    it came just in time. I had to give an internal trainig at our company to users who switch from Toad to SQL Developer. Executing existing files in SQL*PLUS has been asked for because SQL Developer for example does not support COLUMN

    Best Regards
    Marcus

    Like

  2. Hello Matthias,
    Thanks for presenting this solution, it’s very usefull.
    I have one question for you: is it possible to pass the password within the commandline
    like ${sqldev.conn} ? Is there a ${sqldev.pwd} ? 😉

    With regards,
    Mark

    Like

    • Hello Mark!

      I’m afraid this is not supported. But maybe you add a prompt “${prompt}” or labled prompt “${promptl:=}” to ask for the password upfront.
      I have added a new post with all the officially supported macros you can use.
      Best regards,
      Matt

      Like

      • Hi Matthias,

        Wow, quick response! Great!
        I’m familiar with the prompt solution. Unfortunately i’m looking for a way to use the SQLDeveloper password of the current worksheet. Next release perhaps?! 😉
        Thanks anyway.

        Greetz,
        Mark

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s