Compiling PL/SQL within Vim

Vim is a great Vi clone, which also works on Windows. It supports using external programs to compile the code you are writing. The following will show how to use this to compile PL/SQL code. Cygwin is also used.

The command ‘:make’ in Vim will call an external program on your current document. This external program is setup using the makeprg variable in Vim. Here we will set this to be a shell script, which will be run using Cygwin’s bash shell. The following has to be added to your vimrc file.

autocmd BufRead *.sql set makeprg=c:\\cygwin\\bin\\bash\ c:\\apps\\bin\sql_compile.sh\ \"%\"
autocmd BufRead *.sql set efm=%E%l/%c%m,%C%m,%Z

This sets up the make program and the error format for .sql files. Note that space, slash and double quotes are escaped. The second line sets up the error format. This catches the PL/SQL error messages. The Vim help has information regarding this format, type ‘:help efm’ to get there. The error format used here is not perfect, the last error will have too much data. But it’s useful as is.

The script called is as follows :

<pre>sqlplus user/password@sid &lt;&lt; END
   @$1
   show errors;
   exit
   END
   exit

We call Sql/Plus and execute the file given as first parameter to the script by passing the commands for Sql/Plus using a here document. The errors are then shown using the Sql/Plus command ‘show errors’. After that, we exit both Sql/Plus and the shell script.

Calling ‘:make’ while editing a .sql file will then execute the file and show the errors. Additionally, the Vim functionality regarding error list (using ‘:clist’) can be used then as well.

UPDATE : after this article was written, I bought a new PC. The network at work doesn’t allow me to install Cygwin, the port used by the installation program is blocked. So the compile didn’t work anymore. As is happening more and more, Python came to the rescue. The following script does the same as the one shown above :

import os, popen2, sys

(to_sqlplus,from_sqlplus) = os.popen2('SQLPLUS.EXE sysadm/sysadm@tbp2bi01')
to_sqlplus.write('''
    @%s
    show errors;
    exit;
''' % (sys.argv[1]))
for l in from_sqlplus.readlines():
    print l,