|
Linux
Vimming with SQL*Plus
by David Kalosi
Using Vim in an Oracle environment? Here are some tips and tricks to get the most out of that popular text editor (plus, some advice for running it on Windows 2000)
The Vi tool, which is included in nearly every UNIX-like OS and runs on nearly 20 different platforms, is the easily the world's most popular text editor, and Vim (or Vi iMproved), its enhanced, extended clone, is the one of the most popular text editors among Linux sysadmins. Given the growing popularity of Linux as a deployment platform for the Oracle Database, Vi/Vim is increasingly used in conjunction with SQL*Plus.
In this article, I explain how to get the maximum out of Vim when you use it with SQL*Plus and also how to run the editor in a Windows 2000 environment. (I don't know about you, but Vim is the first thing I install on Windows boxes.) The article assumes that you are using version 6.2 of the Vim editor, which can be downloaded from http://www.vim.org/download.php. I am nearly certain that everything works on 5.X versions too, but many examples and features do not work with the classic Vi editor.
Vi Modes
The Vim editor operates in the following modes:
- Insert mode: This mode lets you insert/append text into the edited file.
- Normal mode: Most commands (delete/yank/search/replace/navigation) work in this mode.
- Visual mode: This mode, which is unavailable in Vi, lets you visually select a portion of text for editing/indenting/formatting.
A more detailed overview of these modes and their options follows later.
Moving Around
The basic commands for moving the cursor (depending on your compatibility settings) are:
k — Moves the cursor UP (optionally the UP arrow)
h — Moves the cursor LEFT (optionally the LEFT arrow)
l — Moves the cursor RIGHT (optionally the RIGHT arrow)
j — Moves the cursor DOWN (optionally the DOWN arrow).
These commands move the cursor one increment in the desired direction. There are also more advanced ways of moving around in the file quickly and efficiently.
Moving by Words
w — Moves the cursor forward one word
b — Moves the cursor backward one word.
Moving to the Start/End of a Line
$ — Moves the cursor to the end of the line
^ — Moves the cursor to the first nonblank character of the line
0 — Moves the cursor to the first character of the line.
You may have noted that the characters $ and ^ represent the same functions in POSIX regular expressions. Later, you'll see that Vim has good support for regular expressions in general.
Moving to a Specified Line or File Position
gg — Moves the cursor to the beginning of the file
G — Moves the cursor to the end of the file
:[0-9] — A colon followed by any number moves the cursor to the specified line number
CTRL-f — Moves the cursor one page down
CTRL-b — Moves the cursor one page up.
Moving to the Matching Bracket
Move your cursor to the starting bracket, and press the % key. The cursor jumps to the closing bracket. This feature is a great help, especially for navigating within a large source file or a SQL statement with many brackets.
These are the basics of moving around in your Vim editor. All the foregoing commands, except using arrows to move around, require you to be in Normal mode. The navigation with the arrow keys is enabled only in the Vim version of the editor, and you must set the "nocompatible feature" (:set nocompatible).
In addition, to make movement even faster, you can specify "counts" with nearly every Vim command. The syntax is [number][command]; for example, the 3w command moves the cursor three words forward. Similarly, 5j moves it five lines down.
Editing Text
Now that you know how to move around in the editor, the next step is to learn about inserting text, editing text, doing pattern matching, and replacing. To insert text, simply press the I key and start typing. Now the editor is in Editing mode. After you have finished editing, you can jump back to Normal mode by pressing the ESC (escape) key once. The situation is the same when you want to append some text. Press the A key, and you can start inserting text after the cursor position.
Some more complex editing options follow:
Deleting a Text Segment
x — Deletes a single character below the cursor
dd — Deletes a single line below the cursor
dw — Deletes a single word below the cursor
d$ — Deletes everything until the end of the line
d% — Deletes everything until the matching bracket
dG — Deletes everything from the current cursor position until the end of the file
dgg — Deletes everything from the current cursor position to the beginning of the file.
You will notice that there are several known characters (w, $, %) from the "Moving Around" section among the Delete commands. You can use previously discussed movements in combination with d—so, for example, 4d deletes four lines of text.
Searching for a Text Segment
To search for a string or a pattern, simply enter the /pattern command in the editor's Normal mode, which initiates a search for occurrences of the pattern in the forward direction. Similarly, the ?pattern command searches the file backward. To navigate through the search results, you can press the n key to jump to the next occurrence of the pattern; typing N does the same in the opposite direction.
The following characters need to be escaped if they are used in search patterns, due to their special meaning:
[]
.
*
^
%
\
/
?
~
$
Certain options make searches behave a bit differently. For example, you can tell Vim to ignore the case of the words, by using the :set ignorecase option. Using :set noignorecase turns this option off.
For better visual navigation within search results, you can use the :set hlsearch option to tell Vim to highlight the occurrences of search pattern matches. To switch highlighting off, use the :set nohlsearch option. You can use any valid regular expression pattern as your search criterion. For example, /^This searches for lines that begin with "This."
Changing and Replacing Text
The change operator, c, works almost identically like the d (Delete) operator. The only difference is that c leaves you in Insert mode. Here are some examples of using the change operator:
cw — Changes the current word
cc — Changes the whole line
c$ — Changes everything until the end of the line.
To replace a text segment, use the r command, which replaces the character under the cursor. For example, using rH on hello turns it to Hello. Similarly, 5rH turns Hello to HHHHH. To change a character to a line break, type r<Enter>.
The R command tells Vim to enter Replace mode. In this mode, Vim replaces the character under the cursor with your input. You exit Replace mode in the same way you exit Insert mode: by pressing the ESC key.
The Substitute Command
The substitute command—%s—is one of the most powerful commands of the Vim editor. It behaves exactly like a global search and replace. If you have ever used the Linux sed editor, you will be familiar with the syntax of this command. Basically, the substitute works as follows:
:<scope>s/pattern/replacement/flags
For example, to substitute Good morning for every occurrence of Hello, type
:%s/Hello/Good morning/g
The % character replicates the change to all the lines in the file, and the g flag causes all the words in the line to be replaced. If you want to do the same substitution, but only on lines 4 to 9, the command will look like this:
:4,9s/Hello/Good morning/g
The two numbers represent the starting and ending lines for the substitution. You can go even further to get a prompt for each replacement and specify an additional c flag to the command.
Advanced Editing
You can speed up the whole editing process by automating some of it. Vim has several features you can use to do some tasks automatically. One of them enables you to repeat a certain commands.
Imagine that you have a few occurrences of "today" in your file and you want to change it in, let's say, three places, so you can't do a global search and replace. Change the word "today" to "yesterday" by issuing the cw (change word) command. Press the ESC key to leave Insert mode. Now navigate to the next word you want to replace and put the cursor at the beginning of the word. Press the period (.) key. The previous action repeats.
Let's say you have a file that consists of one word on each line. You would like to convert each line into insert strings. For example, the Oracle line should look like this:
INSERT INTO companies VALUES ('Oracle')";
Do a search and replace using the method I described above and type the following commands:
:%s/^/INSERT INTO companies VALUES('/g
:%s/$/');/g
The Global Command
This feature of the Vim editor allows you to find a pattern and execute a command there. Say you want to substitute "RDBMS" with "database" if the preceding word is "Oracle." You can use the following command:
:g/Oracle/s/RDBMS/database/g
which changes Oracle RDBMS to Oracle database but leaves MySQL RDBMS as it is.
Abbreviations
You have learned to do a lot of tricky stuff by using some of the advanced Vim features. Now we look into the ability of the Vim editor to do abbreviations.
Imagine you are a PL/SQL programmer or an Oracle DBA and you are using the same code segments over and over when writing your programs or queries. Unfortunately, you have to write them each time. This process will be much easier if you create your own set of abbreviations.
Abbreviations are stored like an ordered map structure, and each abbreviation is identified by its "key" and holds the specific text as its "value." When you type down a key, it is expanded to the value of the abbreviation. You define abbreviations by using the following command:
:ab key value
For example:
:ab hh Hello World !!!
Define the abbreviation, and then change to Insert mode. Type hh, and press the space bar. As you can see, hh turns into Hello World!!!. This abbreviation is, of course, meaningless, but it demonstrates the principle. You can create your own abbreviations and save them in a file called .exrc on UNIX-like systems or _exrc on Windows platforms. Similarly, you can put these abbreviations into.vimrc(UNIX) or _vimrc(Windows) files. (I prefer the second option, because I like to keep all my settings in one place.) Here are some abbreviations that might inspire people to create their own or modify these:
"creates an empty anonymous block
abbr PANBL DECLARE<CR><CR>BEGIN<CR><CR>END;<CR>/
"creates an empty anonymous block and catches the exceptions
abbr PANBLEX DECLARE<CR><CR>BEGIN<CR><CR>EXCEPTION WHEN others<CR>THEN<CR><CR>END;<CR>/
"creates a template for a FOR LOOP
abbr PFL FOR cc IN col.FIRST..col.LAST<CR>LOOP<CR><CR>END LOOP;<CR>
"creates an empty FUNCTION
abbr PCRFN CREATE OR REPLACE FUNCTION <name><CR>AS<CR><CR>BEGIN<CR><CR>END;<CR>/
Referring to the examples, you can define your own abbreviations. Note that the <CR> character sequence indicates a new line. Vim will interpret this sequence as a carriage return character and will create a new line. To achieve this behavior, Vim is using key notations to refer to function keys; for example:
<BS> — backspace
<Tab> — tab
<CR> — carriage return
<Esc> — escape
<EOL> — end-of-line
You can find the remaining key notations in the Vim documentation.
Key Mappings
Another powerful feature of the Vim editor is key mappings, which change the meaning of typed keys. Using key mappings, you can customize the behavior of the editor to fit your needs. A common practice is to map command sequences to function keys. There is a set of commands with which you can define, modify, delete, and list key mappings.
To list all defined key mappings, type :map; to define a new mapping, type :map <key> <command sequence>; and to remove a mapping, type :unmap <key>. (These are just the fundamentals; you can learn more in the Vim documentation.)
For example, you can define the following mapping to append the current UNIX time stamp after the cursor position. The mapping uses the Vim built-in function localtime().
:map <F2> a<CR>=localtime()<CR><Esc>
Using key mappings you can even call an external program, as you can see in the example below.
:map <F3> :!ls -lFa <CR>
Because you can map practically any sequence of commands to a key, you can regard key mappings as a sort of mini macros. You should concentrate on sequences of commands you use frequently.
As with abbreviations, you can store your key mappings in the vimrc or exrc file.
Visual Mode, Indenting, Syntax Highlighting
The Vim editor has another editing mode, called Visual mode (which is unavailable in Vi). You start this mode by pressing the v key in the editor's Normal mode; VISUAL will appear in the ruler. From now on any selection you make using the movement commands will appear highlighted in the editor. You can use every move command to extend a selection. (For example, pressing v and G selects the entire file.)
Code Indenting
You can indent your code in Visual mode by pressing the = key. This action autoindents the selected code segment. Some special options are available in the Vim editor for setting up indenting:
- :set autoindent: Uses the indent from the previous line
- :set smartindent: A "smart" version of the autoindent option very useful for file types that have no indent file available
- :set cindent: Sets C-style indenting.
Syntax Highlighting
One of the most powerful options of the Vim editor is syntax highlighting. A great help when you have to work with large code segments, it significantly increases the readability of the code. Syntax highlighting works with the so-called Vim syntax files. For each edited file extension (.sql, .java), the appropriate syntax file is loaded.
For example,
- shell$ vim TestClass.java loads the java.vim syntax file
- shell$ vim main.c loads the c.vim syntax file
- shell$ vim repquery.sql loads the sql.vim syntax file.
The syntax files are located in the $VIMRUNTIME/syntax directory.
For example, in the sql.vim file, the first lines in the header are author comments. Line comments in Vim files are defined with the " character.
" Vim syntax file
" Language: SQL, PL/SQL (Oracle 8i/9i)
" Maintainer: Paul Moore <gustav@morpheus.demon.co.uk>
" Modified By: David Kalosi <david.kalosi@spordat.sk>
" Last Change: 2004 Feb 10
" Description: Added the missing 9i built-in functions and SQL keywords
" For version 5.x: Clear all syntax items
" For version 6.x: Quit when a syntax file was already loaded
The following code clears any previous syntax definitions, to prevent collisions.
if version < 600
syntax clear
elseif exists("b:current_syntax")
finish
endif
Because SQL and PL/SQL are not case-sensitive languages, the following line tells Vim to ignore case.
syn case ignore
For case-sensitive languages, set syn case match.
The definitions of the language elements follow. The keywords to be highlighted are defined with the syn keyword <group> <keyword> command. This syntax file defines the groups sqlSpecial, sqlKeyword, sqlOperator, sqlStatement, sqlFunction, and sqlType.
" The SQL reserved words, defined as keywords.
syn keyword sqlSpecial false null true
syn keyword sqlKeyword access add as asc begin by check cluster column
syn keyword sqlKeyword nocompress nowait of offline on online start
syn keyword sqlKeyword successful synonym table then to trigger uid
syn keyword sqlKeyword unique user validate values view whenever
.....................
syn keyword sqlOperator not and or
syn keyword sqlOperator in any some all between exists
syn keyword sqlOperator like escape
.....................
syn keyword sqlStatement alter analyze audit comment commit create
syn keyword sqlStatement delete drop execute explain grant insert lock
syn keyword sqlStatement truncate update
.....................
syn keyword sqlType boolean char character date float integer long
syn keyword sqlType mlslabel number raw rowid varchar varchar2 varray
The following lines define how strings look in SQL, including the start and the end of the string as the characters that should be skipped in searches for the end-of-the-string mark, such as when the string contains the escaped character \".
" Strings and characters:
syn region sqlString start=+"+ skip=+\\\\\|\\"+ end=+"+
syn region sqlString start=+'+ skip=+\\\\\|\\'+ end=+'+
You define numbers similarly to strings. The regular expression defines a SQL number.
" Numbers:
syn match sqlNumber "-\=\<\d*\.\=[0-9_]\>"
The SQL and PL/SQL languages also contain comments. The following two lines define the SQL single-line and /**/ C/C++-style multiline comments.
" Comments:
syn region sqlComment start="/technology/\*" end="\*/" contains=sqlTodo
syn match sqlComment "--.*$" contains=sqlTodo
The following command tells Vim to search backward for the beginning or the end of a C-style comment and begin syntax coloring from that point.
syn sync ccomment sqlComment
You can also define keywords to remind you about some problems with the code.
" Todo.
syn keyword sqlTodo TODO FIXME XXX DEBUG NOTE
The following if-else block is used for backward compatibility with Vim version 5.8. The interesting parts in this context are the HiLink commands—they link the specified keywords in the current group to one of the standard colors. The settings themselves are dependent on the terminal type and environment.
As you'll see, some groups, such as Comment and Number, are predefined. The convention when writing Vim syntax files is to prefix the standard group name with the language name. ("Comment" becomes "sqlComment," and so on.)
Here is a list of some preferred groups; there are actually many more of them:
- Comment
- Constant
- Identifier
- Statement
- PreProc
- Type
- Special
- Ignore
- Error
- Todo
if version >= 508 || !exists("did_sql_syn_inits")
if version < 508
let did_sql_syn_inits = 1
command -nargs=+ HiLink hi link <args>
else
command -nargs=+ HiLink hi def link <args>
endif
HiLink sqlComment Comment
HiLink sqlKeyword sqlSpecial
HiLink sqlNumber Number
HiLink sqlOperator sqlStatement
HiLink sqlSpecial Special
HiLink sqlStatement Statement
HiLink sqlString String
HiLink sqlType Type
HiLink sqlTodo Todo
HiLink sqlFunction Statement
delcommand HiLink
endif
You can turn on syntax highlighting with the :syntax on command and override the file extension with the :set filetype= command. To enable SQL syntax for the SQL*Plus-generated afiedt.buf file, type :set filetype=sql.
To see the syntax colors correctly, ensure that your terminal is capable of displaying color characters and optionally set up color-terminal emulation. Use the following commands to display and set terminals on UNIX systems.
For bash and korn shells:
echo $TERM # displays the current terminal settings
export TERM=vt102c # set the terminal to vt102c
For C shells:
echo $TERM # displays the current terminal settings
setenv TERM vt102c # set the terminal to vt102c
You can put the following lines into your .vimrc file to have syntax highlighting automatically enabled with terminals that support color:
if &t_Co > 1
syntax enable
endif
More Oracle and Vim
Many people use built-in SQL*Plus editing commands, such as APPEND, CHANGE, and DEL, for editing the contents of the SQL*Plus buffer. I do not find these commands particularly helpful because of their limited application. A more convenient method is to do the editing work with Vim. All you need to do is set Vim as the OS default editor. On UNIX systems, you can do that by setting the EDITOR environment variable to vim.
For bash and korn shells:
echo $EDITOR # displays the current OS editor
export EDITOR=/usr/bin/vim # sets the OS editor to vim
For C shells:
echo $EDITOR # displays the current OS editor
setenv EDITOR /usr/bin/vim # sets the OS editor to vim
In Windows you have to modify the contents of the %ORACLE_HOME%\sqlplus\admin\glogin.sql file and add the following line:
DEFINE _EDITOR='gvim -c "set filetype=sql"'
You can do this in UNIX too; it will launch Vim with syntax highlighting for SQL files.
So from now on when you need to edit the contents of the SQL*Plus buffer, simply type EDIT, and the Vim editor will start with syntax settings for the SQL files. When you have finished editing, simply exit Vim (:wq). You will be back at the SQL*Plus command prompt, and you can run the contents of the buffer as usual with the slash command, /.
But what if, instead of editing the SQL*Plus buffer, you want to edit a saved SQL script called report.sql? The answer is simple: All you need to do is type EDIT report.sql in the SQL*Plus prompt, and the Vim editor starts. Make the desired changes, quit the editor, and run your script in SQL*Plus, using the @ command:
SQL> @report
Summary
This article described the features of the popular Vim text editor. It also demonstrated some scenarios of using Vim in the Oracle environment and presented some ideas on how to improve your everyday work.
Because the original sql.vim file is outdated, I have made some modifications to the sql.vim syntax file for recognition of Oracle-specific SQL extensions and keywords. You can replace your current sql.vim file with the modified version (download here), which simply adds new keywords to the original file.
David Kalosi works as a database specialist at SporDat spol. s r.o. in Bratislava, Slovak Republic. He has been working with Oracle software and UNIX/Linux systems for more than four years. David is also an OCA for Oracle Application Server.
|
Bookmark
