Thank a lot Regis.

I was worrying a lot b'coz when I ran this procedure this was taking hours.
I watched the procedure more than an hour then I scared and I thought its
better to confirm it from some experienced one.

Thanks a lot.
Ritesh Jain


-----Original Message-----
From: Regis Biassala [mailto:Regis.[Email Address Removed] Tuesday, April 30, 2002 3:40 PM
To: Ritesh Jain; LazyDBA.com Discussion
Subject: RE: Updation of BLOB column


Hi Jain,
Yes this will work.
before update always create a BLOB or CLOB LOCATOR by using EMPTY_BLOB() or
EMPTY_CLOB().
You do not need to use DBMS_LOB in order to update a BLOB or CLOB.

Regis

-----Original Message-----
From: Ritesh Jain [mailto:[Email Address Removed] Tuesday, April 30, 2002 8:59 AM
To: LazyDBA.com Discussion
Subject: Updation of BLOB column



Hi Gurus,

I have to update a BLOB column, can I do it within a procedure. I'm not
using DBMS_LOB package, will it work without that.
I'm giving here the code its small and simple procedure, plz tell me can I
update a BLOB column in this way ?
This procedure has been created successfully.

this is the code :-
===============

CREATE OR REPLACE PROCEDURE update_table_RJ AS

ERR VARCHAR2(200) := NULL;
compound_id number;
commit_count number(9) := 0;

cursor c_compound IS
SELECT * from R5000.T_ALL_Compounds;

rt_compound c_compound%RowType;


begin

-- loop for all the compoundIds first
--check if the cursor is already open
if c_compound%ISOPEN then
close c_compound;
end if;

commit_count := 0;

open c_compound;

dbms_output.put_line('compounds cursor open');

loop
commit_count := commit_count + 1;
if (commit_count mod 10) = 0 then

COMMIT WORK;

if (commit_count mod 500 ) = 0 then
dbms_output.put_line('compounds=
'||to_char(commit_count));
end if;

end if;

fetch c_compound into rt_compound;

exit when c_compound%notfound;

compound_id := rt_compound.AC_Compound_Id;


UPDATE T_KEY_TABLE SET KT_ALL_ATOM_CHEMISTRY = EMPTY_BLOB()
WHERE KT_COMPOUND_ID = compound_id;
UPDATE T_KEY_TABLE SET KT_ALL_ATOM_CHEMISTRY =
rt_compound.AC_ALL_ATOM_CHEMISTRY
WHERE KT_COMPOUND_ID = compound_id;


end loop;
close c_compound;

exception
when others then
ERR := SQLERRM;

dbms_output.put_line('Exception at COMPOUNDS' || compound_id);
dbms_output.put_line(ERR);

end update_table_rj;
=======================

Regards
Ritesh Jain

--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.
**********************************************************************
Oracle LazyDBA home page