Disable vi Autoindent Before Pasting SQL Scripts on Oracle Hosts

calendar May 23, 2026 / · 7 min read · Oracle DBA Unix Commands vi SQL*Plus Editor Oracle Administration Shell SQL Scripts  ·
Share on: linkedin copy

Disable vi Autoindent Before Pasting SQL Scripts on Oracle Hosts

Purpose

Pasting a SQL script copied from email, a Confluence page, or a ticket comment into a vi buffer over SSH — and watching every line shift one indent right of the previous one — is the most common and most frustrating vi failure mode an Oracle DBA encounters. The script that ran fine when executed directly from the wiki snippet now fails on the host with SP2-0042: unknown command " INSERT" on every line. The cause is autoindent: vi assumed each new line should match the indentation of the previous one, and the terminal cannot distinguish a real keypress from a pasted character, so the indent compounds.

Three vi options together prevent the cascade: :set noai (disable autoindent), :set nosm (disable showmatch — the bracket-bounce highlight that slows large pastes), and :set paste on vim-derived implementations (a single switch that turns off every paste-hostile feature at once). Set them before entering insert mode, paste the script, then restore the defaults so the next file edit retains expected behaviour.

The bug surfaces equally in classic vi on AIX/Solaris/HP-UX and in vim on Oracle Linux and RHEL, because both implement autoindent the same way. The fix is one of the first things to commit to muscle memory for any DBA who edits SQL scripts on remote Oracle hosts.

Code

 1" In vi command mode, BEFORE pressing i to insert and pasting:
 2:set noai
 3:set nosm
 4
 5" On vim (Oracle Linux, RHEL, macOS) the single switch:
 6:set paste
 7
 8" Now press i, paste the SQL, press Esc, then restore:
 9:set ai
10:set sm
11:set nopaste
12
13" Optional — make the defaults paste-safe for all SQL files
14:autocmd BufRead,BufNewFile *.sql setlocal noai nosm

1# Persist for every interactive vi session — add to ~/.vimrc
2cat <<'EOF' >> ~/.vimrc
3" SQL-friendly defaults for the oracle user
4autocmd BufRead,BufNewFile *.sql setlocal noai nosm
5autocmd BufRead,BufNewFile *.pls setlocal noai nosm
6autocmd BufRead,BufNewFile *.pkb setlocal noai nosm
7EOF

Code Breakdown

:set noai

autoindent (abbreviated ai) makes vi copy the leading whitespace of the previous line onto each new line as you type. Useful for hand-editing structured code; catastrophic when the "typing" is actually a paste from the terminal, because every newline triggers another indent and the previous paste's indent is preserved on top of it. :set noai switches the behaviour off; :set ai switches it back on.

:set nosm

showmatch (abbreviated sm) briefly highlights the matching open-paren or open-brace when you type the closing one. During a paste of a multi-thousand-line SQL block with many balanced parentheses, the highlight redraws between every character and can slow the paste enough that the terminal buffer overflows and characters are dropped. :set nosm disables the highlight; the paste runs at terminal speed.

:set paste (vim only)

vim added a single paste mode in version 6 that disables every paste-hostile formatting feature at once: autoindent, smartindent, cindent, textwidth, formatoptions, expandtab, and abbreviation expansion. One switch on, one switch off. Classic vi (nvi, BSD vi, AT&T vi) does not have :set paste and requires the two individual noai/nosm toggles above.

autocmd BufRead,BufNewFile *.sql setlocal noai nosm

The persistent recipe. autocmd registers a callback fired whenever vim opens or creates a file matching the glob; setlocal scopes the option change to that buffer only so it does not leak into the next file edited in the same session. Three globs cover SQL*Plus scripts (*.sql), PL/SQL specifications (*.pls), and PL/SQL bodies (*.pkb).

Key Points

  • The pasted indentation is real characters. Once the paste is in the buffer with cascading indent, :set noai alone will not fix the existing damage — only the next paste. To repair the current buffer, use :%s/^ *//g to strip leading whitespace from every line, or paste again into a fresh buffer after toggling noai.
  • Terminal emulator bracketed-paste mode helps modern setups. Recent iTerm2, GNOME Terminal, and PuTTY versions send "bracketed paste" escape sequences that vim 8+ detects and automatically enters paste mode for. The noai/nosm recipe is still the right default for older terminals and classic vi.
  • expandtab is a separate fix for a different bug. If pasted code contains literal \t tabs and the destination requires spaces (Oracle PL/SQL style guides typically mandate spaces), use :set expandtab and :retab after paste. This is independent of the autoindent problem.
  • SP2-0042 from SQL*Plus is the smoke alarm. When SQL*Plus prints SP2-0042: unknown command on every line of a pasted script, the script almost certainly has leading whitespace where SQL*Plus expects none. Re-open in vi with noai and re-paste from the source.
  • view is read-only vi and immune. If the only goal is to look at the pasted script without editing, view <file> opens read-only and avoids the entire autoindent/insert-mode question.

Insights and Best Practices

Build a SQL-safe .exrc or .vimrc for every Oracle host

The persistent fix belongs in the oracle user's ~/.exrc (classic vi) or ~/.vimrc (vim). A minimal SQL-safe profile:

1set noai
2set nosm
3set showmode
4set ruler
5set number
6set ts=4
7set sw=4
8autocmd BufRead,BufNewFile *.sql,*.pls,*.pkb,*.pks setlocal noai nosm

Source-control this alongside the rest of the DBA team's profile assets. Every new oracle user on every host gets the same paste-safe vi from day one.

Use a SQL*Plus heredoc when the script is short

For one-off paste-and-run that does not need to be saved to a file, skip vi entirely. Paste the SQL into a heredoc directly at the shell:

1sqlplus -s / as sysdba <<'EOF'
2SELECT tablespace_name, bytes/1024/1024 mb
3FROM   dba_data_files
4ORDER  BY 2 DESC;
5EOF

The 'EOF' quote disables shell variable expansion inside the heredoc, so a script with & substitution variables pastes verbatim. No vi, no autoindent, no SP2-0042.

Pair with dos2unix for cross-platform pastes

A SQL script copied from a Windows-authored ticket frequently arrives with \r\n line endings. SQL*Plus accepts them but Oracle's parser sometimes complains, and git diff always does. Run pasted scripts through dos2unix (or tr -d '\r' < file.sql > clean.sql) before commit.

Prefer :r !cat over hand-paste for very large scripts

For a multi-thousand-line paste, terminal buffer overflow becomes likely even with nosm. Stage the script as a temp file by scp (or vim - reading from stdin), then read it into the buffer:

1:r !cat /tmp/script.sql

Zero typing, zero autoindent risk, and the paste is one atomic operation rather than a stream of keystrokes.

When to Run This

  • Before editing any SQL script copied from a wiki, email, or ticket comment over SSH
  • When SQL*Plus emits SP2-0042: unknown command on every line of a freshly pasted script
  • When inheriting an Oracle host whose .exrc/.vimrc is missing or unconfigured
  • During training of any DBA new to vi-on-remote-host workflows
  • Whenever pasting into a terminal with bracketed-paste detection unavailable (legacy PuTTY, raw telnet, IBM xterm)

Troubleshooting Common Issues

If :set noai does not stop the cascade, autoindent is being re-enabled by a filetype-detection plugin. Check :set ai? after the paste; if it returns autoindent, run :filetype off before pasting and :filetype on after.

If :set paste is rejected with "unknown option," the editor is classic vi rather than vim. Fall back to the two-option :set noai plus :set nosm recipe; both work on every vi-family editor.

If the paste appears truncated, the terminal buffer overflowed. Reduce paste size by splitting the script in halves, or use :r !cat <file> to read from a staged file rather than typing through the terminal.

If SP2-0042 persists after a clean noai paste, inspect the file with cat -A <file> (or od -c) to see hidden control characters and non-breaking spaces. Confluence and some email clients silently substitute non-breaking spaces (U+00A0) for ASCII spaces; SQL*Plus rejects them. Strip with sed -i 's/\xc2\xa0/ /g' <file>.

References

Posts in this series