Re: How to embed a newline in a PLSQL VARCHAR2

Jomarlen <jomarlen_at_aol.com> wrote in article <19971001160301.MAA12303_at_ladder01.news.aol.com>...
> Try
> v_string := 'This string should split '||chr(10)||'into two lines';
> John

You can always split a string onto more than on line, using the implicit operating-specific newline character sequence. This works: SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2 v_string VARCHAR2(200) := 'This string should split   3 into two lines';
  4 BEGIN
  5 DBMS_OUTPUT.enable;
  6 DBMS_OUTPUT.put_line(v_string);
  7 END;
  8 /
This string should split
into two lines
PL/SQL procedure successfully completed.

If you want to use CHR(10) you should create a constant variable to hold it and use it all over the place:
DECLARE
  CRLF CONSTANT VARCHAR2(2) := CHR(10);
  ...
BEGIN
  ...
  some_var := 'Hello,' || CRLF || 'World!';

This is more maintainable, and you're not doing function calls all over the place.

I was kind of surprised to see under 7.3 on NT using SQLPlus, CHR(10) gives a proper CRLF break; when I checked v_string above there was no CR character (13), just the linefeed character. Yet, NT uses the two-character CRLF sequence. I suspect this is a SQLPlus thing. I wonder if Oracle's native driver and ODBC for 95/NT handle the CRLF correctly?

• Dan Clamage