What is New Line Character in Oracle?
Sometimes, we need to make several lines for each row. For example, we'd like to compose some dialogs into a more readable format.
SQL> select 'What''s your name?' || ' My name is ' || first_name || ' ' || last_name || '.' dialogue from employees order by 1;
DIALOGUE
----------------------------------------------------------------------------
What's your name? My name is Adam Fripp.
What's your name? My name is Alana Walsh.
What's your name? My name is Alberto Errazuriz.
...
In the above sentences, we'd like to break lines for each ones. So we use a character function CHR which takes ASCII code as an input argument to make it.
CHR(10)
According to ASCII code table, we use CHR(10) to represent a line feed (\n).
SQL> select 'What''s your name?' || chr(10) || 'My name is ' || first_name || ' ' || last_name || '.' dialogue from employees order by 1;
DIALOGUE
----------------------------------------------------------------------------
What's your name?
My name is Adam Fripp.
What's your name?
My name is Alana Walsh.
What's your name?
My name is Alberto Errazuriz.
...
As you can see, we make 2 lines for each rows.
For DBA, CHR(10) may have more values than developers. By using it, we can compose some executable SQL statements for our maintenance jobs. A practical example of using CHR(10) can be fount at: How to Migrate 9i to 12c via DB Link.