How to enter newline character in Oracle?

Question

select col1, col2     into name1, name2     from table1     where col1=col;          m_sub := 'Subject ';     m_msg := 'Hello '||name||' ,'||/n||/n||'Your order has been placed.'; 

---

Error(34,33): PLS-00103: Encountered the symbol "/" when expecting one of the following: ( - + case mod new null avg count current max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

Answer 1

Chr(Number) should work for you.

select 'Hello' || chr(10) ||' world' from dual 

Remember different platforms expect different new line characters:

• CHR(10) => LF, line feed (unix)
• CHR(13) => CR, carriage return (windows, together with LF)

Answer 2

According to the Oracle PLSQL language definition, a character literal can contain "any printable character in the character set". https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/02_funds.htm#2876

@Robert Love's answer exhibits a best practice for readable code, but you can also just type in the linefeed character into the code. Here is an example from a Linux terminal using sqlplus:

SQL> set serveroutput on SQL> begin      2  dbms_output.put_line( 'hello' || chr(10) || 'world' );   3  end;   4  / hello world  PL/SQL procedure successfully completed.  SQL> begin   2  dbms_output.put_line( 'hello   3  world' );   4  end;   5  / hello world  PL/SQL procedure successfully completed. 

Instead of the CHR( NN ) function you can also use Unicode literal escape sequences like u'\0085' which I prefer because, well you know we are not living in 1970 anymore. See the equivalent example below:

SQL> begin   2  dbms_output.put_line( 'hello' || u'\000A' || 'world' );   3  end;   4  / hello world  PL/SQL procedure successfully completed. 

For fair coverage I guess it is worth noting that different operating systems use different characters/character sequences for end of line handling. You've got to have a think about the context in which your program output is going to be viewed or printed, in order to determine whether you are using the right technique.

• Microsoft Windows: CR/LF or u'\000D\000A'
• Unix (including Apple MacOS): LF or u'\000A'
• IBM OS390: NEL or u'\0085'
• HTML: '<BR>'
• XHTML: '<br />'
• etc. etc.