Storing string containing characters like single, ampersand qoutes in a variable

Question

My procedure will be receiving string (email body message) as a input parameter which will be assigned to a variable. it can contain characters like single quotes and ampersand. How can I handle this situation and assign the string as is to a clob variable.

SET SERVEROUTPUT ON

DECLARE

email_body CLOB;

BEGIN

email_body:=to_clob('Hel's Message');

DBMS_OUTPUT.PUT_LINE(length(email_body));

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUt_LINE(SQLCODE||' '||SQLERRM);

END;

Error:

ORA-06550: line 7, column 22: PLS-00103: Encountered the symbol "S" when expecting one of the following:

Answer 1

You can try the following:

SET SERVEROUTPUT ON
/* disable variable substitution */
SET DEFINE OFF 

DECLARE
email_body CLOB;
BEGIN
email_body:=to_clob(q'[Hel's M&ssage]'); /* Q operator to avoid issues with quotes */

    DBMS_OUTPUT.PUT_LINE(length(email_body));

EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUt_LINE(SQLCODE||' '||SQLERRM);
END;
/

Here I use SET DEFINE OFF to prevent variable substitution and the Oracle Q quote operator to handle strings with quotes.