FreeLists / oracle-l / RE: CLOB Store procedure

["Looney, Jason" <Jason.Looney@xxxxxxxxxxxx>]

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@xxxxxxxxxxx] 
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;
/

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------