DBMS_LOB
CREATE TABLE LOB_TABLE (Key_Value Integer, B_LOB BLOB, C_LOB Clob, N_LOB NClob, F_LOB BFILE);
INSERT INTO A_TABLE VALUES (EMPTY_BLOB ());
INSERT INTO LOB_TABLE VALUES (21, NULL, NULL, NULL, BFILENAME ('IMG', 'Image1.gif');
INSERT INTO LOB_TABLE VALUES (22, NULL, NULL, NULL, BFILENAME ('IMG', 'Image2.gif');
Update LOB_TABLE SET F_LOB = BFileName ('IMG', 'Image3.gif') Where key_value = 22; Update LOB_TABLE SET F_LOB = (SELECT F_LOB FROM LOB_TABLE WHERE KEY_VALUE = 22) Where key_value = 21;
Create Directory Scott_dir as '/ usr / home / scott'
Maximum Number of Open Bfiles session_max_open_files = 20insert Into Lob_Table Values (1, Null, 'Abcd', NULL, NULL);
COMMIT;
DECLARE num_var INTEGER; clob_selected CLOB; clob_updated CLOB; clob_copied CLOB; read_amount INTEGER; read_offset INTEGER; write_amount INTEGER; write_offset INTEGER; buffer VARCHAR2 (20); BEGIN
SELECT C_LOB INTO Clob_Selected from Lob_Table Where Key_Value = 1;
SELECT C_LOB INTO Clob_updated from lob_table where key_value = 1 for update; clob_copied: = clob_selected;
Read_AMount: = 10; Read_offset: = 1;
DBMS_LOB.READ (Clob_Selected, Read_Amount, Read_offset, Buffer);
- dbms_output.put_line ( 'clob_selected value:' || buffer); insert into temp values (1, buffer); read_amount: = 10; dbms_lob.read (clob_copied, read_amount, read_offset, buffer); - dbms_output.put_line ( 'clob_copied value:' || buffer); insert into temp values (2, buffer); read_amount: = 10; dbms_lob.read (clob_updated, read_amount, read_offset, buffer); - dbms_output.put_line ( 'clob_updated value:' | | buffer; INSERT INTO TEMP VALUES (3, Buffer); - At Time T4: Write_Amount: = 3; Write_offset: = 5; Buffer: = 'EFG'; DBMS_LOB.WRITE (Clob_updated, Write_Amount, Write_offset, Buffer; read_amount: = 10; dbms_lob.read (clob_updated, read_amount, read_offset, buffer); - dbms_output.put_line ( 'clob_updated value:' || buffer); insert into temp values (4, buffer); read_amount: = 10; dbms_lob .read (clob_selected, read_amount, read_offset, buffer); - dbms_output.put_line ( 'clob_selected value:' || buffer); insert into temp values (5, buffer); - Produces the output 'abcd' read_amount: = 10; dbms_lob.read (clob_copied, read_amount, read_offset, buffer); - dbms_output.put_line ( 'clob_copied value:' || buffer); insert into temp values (6, buffer); - Produces the output ' abcd'END; / OCILobGetLength () OCILobWrite () FUNCTION EMPTY_BLOB () RETURN BLOB; FUNCTION EMPTY_CLOB () RETURN CLOB; FUNCTION BFILENAME (directory_alias IN VARCHAR2, filename IN VARCHAR2) RETURN BFILE;
