FreeLists / oracle-l / RE: CLOB Store procedure

[Hamid Alavi <hamid.alavi@xxxxxxxxxxx>]

Jason,

I have change it as follows, but when I run it doesn't insert the CLOB value
into the table:
Any Idea???


CREATE OR REPLACE PROCEDURE S_Blob(FILE_BLOB IN VARCHAR2 ,
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() )
RETURNING file_content INTO lob_loc;

COMMIT;

FOR I IN 1 .. 3 LOOP
 DBMS_LOB.WRITE (lob_loc,amount,position,buffer);  this line doesn't work
properly!!
 position := position + amount;
COMMIT;
END LOOP;

END;
/

-----Original Message-----
From: Looney, Jason [mailto:Jason.Looney@xxxxxxxxxxxx]
Sent: Thursday, July 01, 2004 1:27 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: CLOB Store procedure


Something like this eliminates the overhead of the additional queries.

INSERT INTO table_1 (col1, col2, clob_col)
VALUES (col1_seq.nextval, p_col2, EMPTY_CLOB ())
RETURNING col1, clob_col INTO v_col1, v_clob_col;

Jason.

-----Original Message-----
From: Hamid Alavi [mailto:hamid.alavi@xxxxxxxxxxx] 
Sent: Thursday, July 01, 2004 2:23 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: CLOB Store procedure

RETURNING ? what do you mean is it insert the clob for you?

-----Original Message-----
From: Looney, Jason [mailto:Jason.Looney@xxxxxxxxxxxx]
Sent: Thursday, July 01, 2004 1:10 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: 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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------