Oracle SQL VARCHAR column with Line break

Question

I tried this:

update MESSAGE set TEXT = 'The following ' || CHAR(13) || CHAR(10) || 'has been disapproved'
        where TITLE = 'REQUEST_DISAPPROVED';commit;

And:

DECLARE
   msg VARCHAR2 := 'The following ' || CHAR(13) || CHAR(10) || 'has been disapproved';
BEGIN
   update MESSAGE set TEXT = :msg
            where TITLE = 'REQUEST_DISAPPROVED';
END;

And:

var this_is_a_variable varchar2(3000); 

exec :this_is_a_variable := 'The following ' || CHAR(13) || CHAR(10) || 'has been disapproved';
update MESSAGE set TEXT = :this_is_a_variable where TITLE = 'REQUEST_DISAPPROVED';

Each gives a variety of errors, I believe this is merely a syntax problem. The ultimate goal is when a plain text e-mail generated from this message, it will have appropriately placed line breaks.

Is this possible?

Answer 1

You need to use the CHR function and the new line character results in the desired outcome.

UPDATE MESSAGE
SET TEXT = 'The following '
  || CHR(10)
  || CHR(10)
  || 'has been disapproved'
WHERE TITLE = 'REQUEST_DISAPPROVED';
COMMIT;