Campo Blob

Aca le envio tres ejemplos diferentes para insert, select y update
Un ejemplo de insert:
DECLARE
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO sca VALUES ('10077703T',empty_blob(),null,null)
RETURN imanif INTO v_blob;
v_bfile := BFILENAME('IMAGES', '10077703Td.gif');
Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);
COMMIT;
END;
Un ejemplo para seleccionar:
PROCEDURE WRITE_ECIT (p_id number, p_blob blob)
IS
v_signature BLOB;
v_sql varchar2(200);
v_size binary_integer := 0;
v_buf raw(32767);
v_amt binary_integer := 30000;
v_pos binary_integer := 1;
BEGIN
select c_blob into v_signature
from t_blob
where c1=p_id
for update;
v_size := dbms_lob.getlength(p_blob);
WHILE v_size > v_amt LOOP
DBMS_LOB.READ(p_blob, v_amt, v_pos, v_buf);
DBMS_LOB.WRITE(v_signature, v_amt, v_pos, v_buf);
v_size := v_size - v_amt;
v_pos := v_pos + v_amt;
END LOOP;
DBMS_LOB.READ(p_blob, v_size, v_pos, v_buf);
DBMS_LOB.WRITE(v_signature, v_size, v_pos, v_buf);
dbms_output.put_line('length of blob is: '||dbms_lob.getlength(v_signature));
end;
Un ejemplo para actualizar:
SQL> create or replace procedure UpdateClob (nid number, contents clob) is
2 clob$ CLOB;
3 begin
4 select
5 c_blob into clob$
6 from t_blob
7 where c1 = nid
8 for update;
9
10 DBMS_LOB.write( clob$, DBMS_LOB.getlength(contents), 1, contents );
11
12 commit;
13 end;
14 /
Procedure created.
SQL> select * from t_blob;
C1 C_BLOB
-- --------------------------------------------------------------------------------
1 this is the original contents of the blob
SQL> begin
2 UpdateClob( 1, 'new blob contents' );
3 end;
4 /
ARA