Insert Control Characters in vi with Ctrl-V for Oracle Prompts

calendar Jun 20, 2026 / · 7 min read · Oracle DBA Unix Commands vi SQL*Plus Shell Terminal Oracle Administration Text Editing  ·
Share on: linkedin copy

Insert Control Characters in vi with Ctrl-V for Oracle Prompts

Purpose

A custom SQL*Plus prompt that prints the connected instance name in colour, or a login script that clears the screen before the banner, both need one thing the keyboard cannot type directly: a literal control character embedded in the file. Press Esc while editing and vi leaves insert mode. Press Enter and vi opens a new line instead of writing a carriage return into the buffer. The control codes a terminal escape sequence is built from are exactly the keys vi reserves for its own commands, so typing them does the wrong thing.

Ctrl-V is the escape hatch. In vi insert mode, Ctrl-V tells the editor to take the next keystroke literally — to store its raw byte in the file rather than act on it. This is how a DBA embeds an Esc (^[, the start of every ANSI terminal sequence), a carriage return (^M), or a bell (^G) inside a glogin.sql, a .profile, or a shell wrapper without the keystroke being interpreted.

The technique is small but it unlocks a whole class of UNIX edits: coloured SQL*Plus prompts, scripts that reposition the cursor, and the repair of DOS line endings that sneak into files transferred from Windows. It works in classic vi, in vim, and at any shell prompt running in vi editing mode.

Code

 1# In vi, enter insert mode first (press i), then:
 2
 3Ctrl-V  Esc      -> inserts a literal ESC character, shown as ^[
 4Ctrl-V  Enter    -> inserts a literal carriage return, shown as ^M
 5Ctrl-V  Ctrl-G   -> inserts a literal BEL (bell) character, shown as ^G
 6Ctrl-V  Ctrl-I   -> inserts a literal TAB, shown as ^I
 7
 8# Example: a coloured SQL*Plus prompt in glogin.sql.
 9# The ^[ below is a real ESC byte typed with Ctrl-V Esc, not the
10# two printable characters caret and open-bracket.
11
12SET SQLPROMPT "^[[1;33m_USER@_CONNECT_IDENTIFIER>^[[0m "
13
14# Example: strip trailing ^M (carriage returns) from a DOS-tainted
15# script, using a literal CR typed as Ctrl-V Ctrl-M in the search.
16:%s/^V^M//g

Code Breakdown

The mechanism is one keystroke with three roles, depending on what follows it.

Ctrl-V as the literal-next prefix

In insert mode, Ctrl-V (sometimes documented as Ctrl-Q on terminals where Ctrl-V is intercepted for flow control) suspends vi's normal key handling for one keystroke. The very next key is written to the buffer as its raw byte value. vi then displays that byte in caret notation: an Esc byte shows on screen as ^[, a carriage return as ^M, a tab as ^I. The caret form is a display convenience — the file holds a single non-printing byte, not the two visible characters.

The SQL*Plus prompt example

SET SQLPROMPT controls the text SQLPlus prints before every command. The string ^[[1;33m is an ANSI escape sequence: a real Esc byte (typed with Ctrl-V Esc), then [1;33m, which sets bold yellow. _USER and _CONNECT_IDENTIFIER are SQLPlus substitution variables that expand to the login name and the connect string. The trailing ^[[0m resets the colour so query output is not painted yellow too. Put this line in glogin.sql and every SQL*Plus session opens with a coloured, instance-aware prompt — a cheap guard against running a DROP on production when you meant the test database.

The search-and-replace example

:%s/^V^M//g is an ex substitution across the whole file. Inside the search pattern you again press Ctrl-V then Ctrl-M to enter a literal carriage return, so the pattern matches the stray ^M bytes that appear at the end of every line in a file edited on Windows and copied to UNIX. Replacing them with nothing converts DOS line endings to clean UNIX ones — a frequent fix when a SQL script uploaded by FTP in binary mode refuses to run cleanly.

Key Points

  • Ctrl-V means "take the next key literally." It is the only portable way to put a control byte into a file from inside vi.
  • Caret notation is display-only. ^[ on screen is one Esc byte on disk, not a caret followed by a bracket. Counting characters by eye will mislead you.
  • Ctrl-Q is the fallback. On sessions where the terminal eats Ctrl-V for XON/XOFF flow control, vi accepts Ctrl-Q for the same literal-next behaviour.
  • The escape character starts every ANSI sequence. Coloured prompts, cursor moves, and screen clears all begin with a literal Esc (^[), which is why this keystroke is the foundation for terminal-styling an Oracle login script.
  • A literal ^M is a carriage return, not a newline. It is the visible symptom of DOS line endings, and Ctrl-V Ctrl-M is how you match and remove it.

Insights and Best Practices

Prefer named conventions where they exist

Modern vim also accepts Ctrl-V u followed by four hex digits to insert a Unicode code point, and Ctrl-V followed by three decimal digits to insert a byte by its decimal value — for example Ctrl-V 027 for Esc. The decimal form is unambiguous and survives copy-paste better than pressing an unprintable key, so it is the safer choice when you are documenting a procedure for a runbook.

Keep escape sequences out of shared scripts when you can

A glogin.sql that paints the prompt is convenient on your own terminal, but the embedded Esc bytes turn into garbage on a terminal that does not understand ANSI colour, and they can corrupt the output when SQL*Plus is driven by a calling program that captures stdout. Reserve coloured prompts for interactive login.sql files in your own home directory; keep automated and batch scripts free of control bytes.

Verify the byte, do not trust the eye

After inserting a control character, confirm it landed correctly with cat -v file or od -c file. cat -v renders the same caret notation vi shows, and od -c prints the exact byte values. Both let you prove an Esc is really 033 and not the literal text ^[.

Use this to repair, not just to create

The most common real-world use is cleanup: removing the ^M carriage returns that break a SQL script transferred from a Windows workstation, or stripping a stray bell character that makes a terminal beep every time a log is tailed. The Ctrl-V prefix is what lets you type the offending byte into a search pattern so it can be matched and deleted.

When to Use This

  • Building a coloured or instance-aware SQL*Plus prompt in glogin.sql or login.sql.
  • Embedding a terminal escape sequence — clear screen, cursor move, colour — in a UNIX login or banner script.
  • Removing DOS carriage returns (^M) from a SQL or shell script copied from Windows.
  • Inserting a literal tab into a file where the editor would otherwise expand it to spaces.
  • Stripping a stray control byte (bell, form feed) that is corrupting log output or a terminal session.

Troubleshooting Common Issues

If Ctrl-V seems to do nothing, the terminal is probably intercepting it before vi sees it — try Ctrl-Q instead, which most vi builds accept as the same literal-next prefix. If you typed the prompt sequence but the colour does not appear, you most likely typed the two printable characters ^ and [ rather than a real Esc; open the file with cat -v and confirm the ^[ is a single highlighted byte, then re-enter it with Ctrl-V Esc. If a script that ran on Windows now fails on UNIX with odd "command not found" errors at line ends, the cause is almost always trailing ^M bytes — run cat -v script.sql to confirm, then remove them with the :%s/^V^M//g substitution or with dos2unix if it is installed. When a coloured prompt corrupts the output of a batch job, remove the escape sequence from the shared script and keep it only in your interactive login file.

References

Posts in this series