SQLPlus Newline Character

Not exactly one character will produce a new line in Oracle’s SQLPlus.  By piping the ASCII CHR(13) then piping the ASCII CHR(10) in that order (  || CHR(13) || CHR(10) || ) gives the query its desired result.  This works on the Unix, Linux, and the Windows operating systems.   ASCII CHR(13) is the carriage return character.  ASCII CHR(10) is the line feed character.

Wikipedia provides a nice explanation on both the carriage return and the line feed.

Shown in the example below are what the two characters together look like, and how each act when used independently.

A simple query for SQLPlus:

select ‘A system view is: ‘ || view_name || ‘  ‘ || CHR(13) || CHR(10) || ‘– Line for Comments or Commit –‘ as COMMAND from all_views where owner = ‘SYS’ and view_name like ‘ALL%’;

Simple Query

ASCII CHR(13) ||  ASCII CHR(10) together:

Below is what the top three lines of the result set looks like on Unix/Linux system:

Unix - Linux Both

By using the ASCII characters 13 & 10 in that order, the result data can be moved between files among different operating systems and the appearance will not change.

Notepad Pasted Results

ASCII CHR(10) alone:

Now if only the Line Feed character ASC(10) is used , it will display normally in SQLPlus on a Unix/Linux system (example shown below).

ascii ten only

However, when the result data is opened in a Windows operating system program, like notepad, the data will still be all on the same line.

Windows Ten Only

ASCII CHR(13) alone:

If only the Line Feed (ASC(13)) is used , the results on a Unix/Linux system overwrite the beginning of the line.

thriteen_only

The result set also has the same results as using the ASC (10) character when view on the Windows operating system.

thirteen_notepad

Please note that the results may vary using different programs.

3 Comments

Leave a Comment