| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: CLOB Store procedure
You could use the RETURNING clause so you don't have to get CURRVAL or your
CLOB handle.
Jason.
-----Original Message-----
From: Hamid Alavi [mailto:hamid.alavi_at_quovadx.com]
Sent: Thursday, July 01, 2004 1:22 PM
To: 'Oracle-L (E-mail)
Subject: CLOB Store procedure
List,
I have written this store procedure to store a CLOB Object into a table,
Just want you guys look at it & if any thing wrong respond to it, appreciate
yur help & time.
Thanks,
CREATE OR REPLACE PROCEDURE S_Blob(FILE_BLOB IN CLOB, batch_file_id IN FILE_BLOB_TEMP.BATCH_FILE_ID%TYPE DEFAULT NULL) AS
lob_loc CLOB; v_curr_val INTEGER; buffer VARCHAR2(32000); amount BINARY_INTEGER :=32000; position INTEGER:=1; I INTEGER;
BEGIN INSERT INTO FILE_BLOB_TEMP(batch_file_id,file_content) VALUES(seq_batch_file_id.NEXTVAL,EMPTY_CLOB());
COMMIT; SELECT seq_batch_file_id.CURRVAL INTO v_curr_val FROM dual;
SELECT file_content INTO lob_loc FROM FILE_BLOB_TEMP WHERE batch_file_id = v_curr_val FOR UPDATE;
FOR I IN 1 .. 3 LOOP
DBMS_LOB.WRITE (lob_loc,amount,position,buffer);
position := position + amount;
COMMIT;
END LOOP;
END;
/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |