SQL-Developer: Create External Tool dialog – Macros (“variables”)

You can use the folloeing commands in “Arguments” and “Run Directory” sections of the “Create External Tool” dialog:

Macro Meaning
${sqldev.conn} DB Connection String
${sqldev.dbuser} DB UserName
${env:var=} Environment Variable
${file.dir} File Directory
${file.ext} File Extension
${file.name} File Name
${file.name.no.ext} File Name Without Extension
${file.path} File Path
${file.url} File URL
${ide.name} IDE Application Name
${ide.classpath} IDE Classpath
${ide.dir} IDE Install Directory
${ide.oracle.dir} IDE Oracle Home Directory
${ide.user.dir} IDE User Directory
${prompt} Prompt
${promptl:=} Prompt with Label
${prop:name=} System Property
Advertisements

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.