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 LazyDBA home page