CODEWITHSUNDEEP
oracle-databaseplsql
Patrick J Abare II
Patrick J Abare II

Reputation: 1129

Oracle SQL VARCHAR column with Line break

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?

Upvotes: 4

Views: 18936

Answers (3)

navin_rai
navin_rai

Reputation: 18

Agree with above answers, its a syntax error CHAR(10) is incorrect function, please use CHR(10) instead:

for e.g.

SELECT 'The following ' || CHR(10) || CHR(10)|| 'has been disapproved' SMS_TEXT
FROM dual;

Upvotes: -1

Gary_W
Gary_W

Reputation: 10360

You have a syntax error. It's CHR(10) not CHAR(10).

Upvotes: 3

Patrick Bacon
Patrick Bacon

Reputation: 4640

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;

Upvotes: 8

Related Questions