[SOLVED] replace text within Oracle variable that is a CLOB

replace text within Oracle variable that is a CLOB

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?

Solution

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

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

Since Oracle 11.2

Many SQL operators and functions that take VARCHAR2 columns as arguments also accept LOB columns.

See docs

Usually documentation has a chapter «SQL Semantics and LOBs». You can search it easily. Not only replace function supports this. In newer versions more functions support LOBs.