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.

 

zzz1.png

Figure 1: Notepad++ Example

 

Here are the steps:

  1. Launch Notepad++
  2. Main menu -> Plugins -> Plugin Manager -> Show Plugin Manager
  3. Available Tab, Find and check NppExec plugin (see Figure 2 below)
  4. Press Install button to download & install plugin – restarts Notepad++
  5. Open a SQL script
  6. Press F6 key (NppExec’s default keyboard mapping for “Execute Statement”)
  7. Enter the following macro script into the Execute pop-up (see Figure 3 below)

set ORA_USER=bert

set ORA_PASS=bert1234

set ORA_SID= ORCL

npp_save

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)"

 

  1. Change the first three variables for your database, username and password
  2. Press the OK button

 

zzz2.png

Figure 2: Notepad++ Plugin Manager – Enable NppExec

 

zzz3.png

Figure 3: Save your SQL*Plus execute macro