oracle-databaseplsqlreplaceclob

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.