While there are many different graphical user interface tools for running SQL statements, sometimes all you really want and need is a smaller, focused and thus simpler tool for modest tasks – such as opening and running a basic SQL script. For those specific, lesser needs I often utilize the popular freeware editor Notepad++. However it requires manual tweaking in order to have Notepad++ execute SQL statements via Oracle’s SQL*Plus. In this blog I’ll show you how. The goal will be to have Notepad++ look, feel and behave as shown in the Figure 1 below – where the top half of the screen displays the SQL statement, the bottom half of the screen shows the command’s output, and there is a key mapped to execute the SQL statement.
Figure 1: Notepad++ Example
Here are the steps:
- Launch Notepad++
- Main menu -> Plugins -> Plugin Manager -> Show Plugin Manager
- Available Tab, Find and check NppExec plugin (see Figure 2 below)
- Press Install button to download & install plugin – restarts Notepad++
- Open a SQL script
- Press F6 key (NppExec’s default keyboard mapping for “Execute Statement”)
- Enter the following macro script into the Execute pop-up (see Figure 3 below)
set ORA_SID= ORCL
cmd /c copy /y "$(CURRENT_DIRECTORY)\$(FILE_NAME)" "$(SYS.TEMP)\$(FILE_NAME)" >nul 2>&1
cmd /c echo. >> "$(SYS.TEMP)\$(FILE_NAME)"
cmd /c echo exit >> "$(SYS.TEMP)\$(FILE_NAME)"
sqlplus -l $(ORA_USER)/$(ORA_PASS)@$(ORA_SID) @"$(SYS.TEMP)\$(FILE_NAME)"
- Change the first three variables for your database, username and password
- Press the OK button
Figure 2: Notepad++ Plugin Manager – Enable NppExec
Figure 3: Save your SQL*Plus execute macro