LOB Append
- From: hamid alavi <alavihamid@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 23 Dec 2004 08:22:04 -0800
List,
I have a store procedure for append LOB, when I pass data as varchar2
is working fine but whaen I pass data as CLOB it's failed any idea??
Appreciate.
Here is my Store Procedure:
** I get the following error when run it:
ORA-22297: warning: Open LOBs exist at transaction commit time
CREATE OR REPLACE PROCEDURE Sp_Append_Blob(FILE_BLOB IN CLOB ,
batch_id IN FILE_BLOB.BATCH_FILE_ID%TYPE)
AS
lob_loc CLOB;
v_curr_val INTEGER;
buffer VARCHAR2(32000);
amount BINARY_INTEGER :=4000;
position INTEGER:=1;
I INTEGER;
v_size BINARY_INTEGER;
v_loop INTEGER;
BEGIN
SELECT LENGTH(FILE_BLOB ) INTO amount FROM dual ;
SELECT file_size INTO v_size FROM FILE_BLOB
WHERE batch_file_id = batch_id ;
SELECT file_content INTO lob_loc FROM FILE_BLOB
WHERE batch_file_id = batch_id FOR UPDATE;
DBMS_LOB.OPEN(lob_loc, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.WRITEAPPEND (lob_loc,amount,FILE_BLOB);
DBMS_LOB.CLOSE(lob_loc);
COMMIT;
v_size := v_size + amount;
UPDATE FILE_BLOB SET file_size = v_size
WHERE batch_file_id = batch_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Operation failed End of Data');
END;
/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
