Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Erase CLOB data

Posted on 2011-02-25
16
2,222 Views
Last Modified: 2012-05-11
Hi,

Is it possible to erase the data from the CLOB? If yes please let me know how?

Thanks
0
Comment
  • 9
  • 6
16 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34980692
Define 'erase'?

Do you want to delete the entire contents or part of the clob?

If part of the lob, to you want the erased part to be removed or just over written?

Check out DBMS_LOB.ERASE
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_lob.htm#i997838
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34980832


update yourtable set your_clob = NULL where....
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34980842
>>update yourtable set your_clob = NULL

or
update yourtable set your_clob = empty_clob() where....

depending on exactly what they want to do.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34980843
No i wanted to erase only some part not the whole data
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34980866
And also how do i come to know the starting point and the ending point to erase. I have only the string which i need to erase.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34980868
>>erase only some part

OK, now what does 'erase'?

for example:
in this clob I want to erase the first word clob

Do you want 'clob' spaced out or 'removed'?

in this      I want to erase the first word clob
or
in this I want to erase the first word clob


0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34980883
If you want it removed, doc link but look for:  FRAGMENT_DELETE
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34980896
>>And also how do i come to know the starting point and the ending point to erase

ALL strings in the clob, just the first, last, ???

same doc link:  dms_lob.instr
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34980945
could you please provide some sample code for my clear understanding
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34980965
As soon as you answer my questions:

ALL strings in the clob, just the first, last, ???
and what you mean by erase, replace with spaces or remove?

In other words:  What if the 'string' appears more than once?

Also post your Oracle version.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34981022
Not all string in the CLOB. only the sting which i am having in some local variable.

the string never appears more than once in CLOB.

I am using Oracle 10g version.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34981070
now.... define 'erase'.

Repeat from when I asked in http:#a34980868


Do you want 'clob' spaced out or 'removed'?

in this      I want to erase the first word clob
or
in this I want to erase the first word clob

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 34982102
Since you didn't respond, here's a 10g example that does both.

One of these should work for you.
drop table tab1 purge;
create table tab1(col1 clob);


insert into tab1 values('Since you did not respond I will have to guess');
insert into tab1 values('Experts-Exchange is easier if you respond to the Experts questions');
commit;

create or replace function eraseCLOB(inClob in clob, replaceString in varchar2) return CLOB
is
	amountToErase number;
	newClob	clob;
begin
	amountToErase := length(replaceString);
	newClob := inClob;
	DBMS_LOB.ERASE(newClob, amountToErase, DBMS_LOB.INSTR(newClob,replaceString));
	return newClob;

end;
/

show errors

create or replace function deleteCLOB(inClob in clob, replaceString in varchar2) return CLOB
is
	amountToErase number;
	newClob	clob;
begin

	dbms_lob.createtemporary(newClob,true);
	dbms_lob.copy(newClob,inClob,dbms_lob.instr(inClob,replaceString)-1,1,1);
	dbms_lob.copy(newClob,inClob,dbms_lob.instr(inClob,replaceString)-1,1,1);

	dbms_lob.copy(newClob,inClob,dbms_lob.getlength(inClob) - dbms_lob.getlength(newClob),dbms_lob.getlength(newClob),dbms_lob.getlength(newClob) + length(replaceString)+1);
	return newClob;

end;
/

show errors

prompt before
select ':' || col1 || ':' from tab1;

update tab1 set col1 = eraseClob(col1,'respond');

prompt after erase
select ':' || col1 || ':' from tab1;
rollback;


update tab1 set col1 = deleteClob(col1,'respond');

prompt after delete
select ':' || col1 || ':' from tab1;
rollback;

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34986323
For my requirement first function will suite. when i call eraseCLOB function i am getting below error.

ORA-21560: argument 3 is null, invalid, or out of range
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34993126
You get that error when you are running my test example as-is or when you try to run it in your system.
0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 34999447
GOOD ONE
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
grant user/role question 11 32
pl/sql - query very slow 26 71
oracle sqlplus query delimiter 8 20
pl/sql parameter is null sometimes 2 14
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

856 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