[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2953
  • Last Modified:

Erase CLOB data

Hi,

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

Thanks
0
Suriyaraj_Sudalaiappan
Asked:
Suriyaraj_Sudalaiappan
  • 9
  • 6
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:


update yourtable set your_clob = NULL where....
0
 
slightwv (䄆 Netminder) Commented:
>>update yourtable set your_clob = NULL

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

depending on exactly what they want to do.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Suriyaraj_SudalaiappanAuthor Commented:
No i wanted to erase only some part not the whole data
0
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
If you want it removed, doc link but look for:  FRAGMENT_DELETE
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
Suriyaraj_SudalaiappanAuthor Commented:
could you please provide some sample code for my clear understanding
0
 
slightwv (䄆 Netminder) Commented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
You get that error when you are running my test example as-is or when you try to run it in your system.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
GOOD ONE
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now