Replacing text/string in CLOB or LONG field in Oracle

How can I replace text/string in CLOB or LONG field in ORACLE 9i
dnabatraAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
CLOBs are manipulated with the package DBMS_LOB.
http://www.psoug.org/reference/dbms_lob.html

Erases all or part of a LOB

dbms_lob.erase(
lob_loc IN OUT NOCOPY BLOB,
amount  IN OUT NOCOPY INTEGER,
offset  IN INTEGER := 1);

Writes a buffer to the end of a LOB:

CREATE TABLE book (
bookid NUMBER(5),
title VARCHAR2(50),
description VARCHAR2(100));

INSERT INTO book
VALUES
(1, '11g Inovations', 'New Features in Oracle 11g');

CREATE TABLE author (
authorid NUMBER(5),
author_name VARCHAR2(60));

INSERT INTO author
VALUES
(1, 'Daniel Morgan');

CREATE TABLE book_author_ie (
bookid NUMBER(5),
authorid NUMBER(5));

INSERT INTO book_author_ie
SELECT bookid, authorid
FROM book, author;

CREATE OR REPLACE PROCEDURE xml_gen(cvar IN OUT NOCOPY CLOB) AS
 CURSOR c IS
 SELECT b.title, b.description, a.author_name
 FROM book b, author a, book_author_ie ie
 WHERE b.bookid = ie.bookid
 AND a.authorid = ie.authorid;
BEGIN
  FOR r IN c LOOP
    dbms_lob.writeappend(cvar, 19, '<root><book><title>');
    dbms_lob.writeappend(cvar, length(r.title), r.title);
    dbms_lob.writeappend(cvar, 14, '</title><desc>');
    dbms_lob.writeappend(cvar, length(r.description), r.description);
    dbms_lob.writeappend(cvar, 27, '</desc></book><author_name>');
    dbms_lob.writeappend(cvar, length(r.author_name), r.author_name);
    dbms_lob.writeappend(cvar, 21, '</author_name></root>');
  END LOOP;
END xml_gen;
/

set serveroutput on

DECLARE
 cvar CLOB := ' ';
BEGIN
  xml_gen(cvar);
  dbms_output.put_line(cvar);
END;
/


Loads BFILE data into an internal CLOB:

dbm_lob.loadclobfromfile(
dest_lob     IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_bfile    IN     BFILE,
amount       IN     INTEGER,
dest_offset  IN OUT INTEGER,
src_offset   IN OUT INTEGER,
bfile_csid   IN     NUMBER,
lang_context IN OUT INTEGER,
warning         OUT INTEGER);
0
 
schwertnerCommented:
REPLACE(
srcstr CLOB CHARACTER SET ANY_CS,
oldsub CLOB CHARACTER SET SRCSTR%CHARSET,
newsub CLOB CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN CLOB CHARACTER SET SRCSTR%CHARSET;
0
 
schwertnerCommented:
http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10759/functions119.htm

REPLACE(char, search_string
        [, replacement_string ]
       )


REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned.

Both search_string and replacement_string, as well as char, can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.

REPLACE provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.

0
 
dnabatraAuthor Commented:
Actually I have to update the clob field with the new text.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.