| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> CLOB Store Procedure
List,
Finaly I could insert a record into a CLOB table, but as soon as I change
the INPUT from varchar2 to CLOB the nothing inserted into clob column, any
Idea?
here is the store procedure:
CREATE OR REPLACE PROCEDURE S_Blob(FILE_BLOB IN clob, >>> when this is
varchar data inserted BUT when I change it to CLOB nothing inserted
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; v_size BINARY_INTEGER;
BEGIN SELECT DBMS_LOB.GETLENGTH(FILE_BLOB ) INTO amount FROM dual ;
INSERT INTO FILE_BLOB_TEMP(batch_file_id,file_content,file_size) VALUES(seq_batch_file_id.NEXTVAL,EMPTY_CLOB() ,amount) RETURNING file_content INTO lob_loc ;
DBMS_LOB.OPEN(lob_loc, DBMS_LOB.LOB_READWRITE);
FOR I IN 1 .. 3 LOOP
DBMS_LOB.WRITE (lob_loc,amount,position,FILE_BLOB);
position := position + amount;
DBMS_LOB.CLOSE(lob_loc);
END LOOP;
COMMIT;
END;
/
 |
 |