replace text within Oracle variable that is a CLOB

Question

I have an Oracle procedure that accepts a parameter (v_MessageText) to update a column called message_text on a table. The column message_text is a CLOB. I would like to be able to replace text within the input so if a user inputs testABC it will output testXYZ into the column message_text. At first, it seemed simple. I tried running the code below within my procedure but I am getting an error:

    SELECT REPLACE (v_MessageText, 'ABC', 'XYZ') from dual;

The error Oracle gives me is: "PLS-00428: an INTO clause is expected in this select statement". Why? What am I doing wrong here?

Answer 1

Never mind - very simple! Issue is fixed by using this SQL instead:

    SELECT REPLACE (v_MessageText, 'ABC', 'XYZ') into v_MessageText from dual;