CODEWITHSUNDEEP
oracle-databasenewline
indolent
indolent

Reputation: 3391

How to enter newline character in Oracle?

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

Upvotes: 87

Views: 336343

Answers (3)

harun ugur
harun ugur

Reputation: 1852

begin   
   dbms_output.put_line( 'hello' ||chr(13) || chr(10) || 'world' );
end;

Upvotes: 6

Douglas Held
Douglas Held

Reputation: 1461

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.

Upvotes: 8

Robert Love
Robert Love

Reputation: 12581

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)

Upvotes: 164

Related Questions