My Oracle Support Banner

How to Perform Search and Replace in a CLOB using DBMS_LOB package (Doc ID 122745.1)

Last updated on OCTOBER 09, 2019

Applies to:

PL/SQL - Version and later
Information in this document applies to any platform.
Checked for relevance on 16-Sep-2010


This sample illustrates how to do search and replace in a CLOB column or a CLOB variable using the DBMS_LOB package.

A table is created with two rows of data. Both rows contain the Clob data "aaa#~#bbb#~#ccc", but only one row will be modified. This sample will replace the "#~#" within the Clob with the value " Replaced ".

The procedure created is one that can be referenced by other procedures as this example will illustrate.

This sample program uses following DBMS_LOB package routines :
- COPY()
- TRIM()


The provided sample uses the following parameters:

Input parameters :
dest_lob - LOB locator of the CLOB to be updated.
search_str - search string.
replace_str - replace string.

Output parameters :
dest_lob - modified LOB


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.