?
Solved

Replacing text/string in CLOB or LONG field in Oracle

Posted on 2007-10-17
4
Medium Priority
?
9,431 Views
Last Modified: 2013-12-19
How can I replace text/string in CLOB or LONG field in ORACLE 9i
0
Comment
Question by:dnabatra
  • 3
4 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 20092395
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
 
LVL 48

Expert Comment

by:schwertner
ID: 20092444
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
 

Author Comment

by:dnabatra
ID: 20095059
Actually I have to update the clob field with the new text.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 750 total points
ID: 20099128
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question