BFILENAME demo
SQL> CREATE TABLE book (
2 id NUMBER (10) PRIMARY KEY,
3 isbn CHAR(10 CHAR),
4 description CLOB,
5 nls_description NCLOB,
6 misc BLOB,
7 chapter_title VARCHAR2(30 CHAR),
8 bfile_description BFILE
9 );
Table created.
SQL>
SQL>
SQL> INSERT INTO book (id,isbn,description,nls_description,misc,bfile_description)VALUES (1,'3', EMPTY_CLOB(),EMPTY_CLOB(),EMPTY_BLOB(),BFILENAME('book_LOC', 'b.pdf'));
1 row created.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_dest_blob BLOB;
3 v_dest_clob CLOB;
4 v_source_locator1 BFILE := BFILENAME('book_LOC', 'bfile_example.pdf');
5 v_source_locator2 BFILE := BFILENAME('book_LOC', 'bfile_example.txt');
6
7 BEGIN
8
9 UPDATE book SET description = EMPTY_CLOB(),misc = EMPTY_BLOB();
10
11 SELECT description, misc INTO v_dest_clob, v_dest_blob FROM book WHERE id = 1 FOR UPDATE;
12
13 DBMS_LOB.LOADFROMFILE(v_dest_blob, v_source_locator1, DBMS_LOB.LOBMAXSIZE, 1, 1);
14 DBMS_LOB.LOADFROMFILE(v_dest_clob, v_source_locator2, DBMS_LOB.LOBMAXSIZE, 1, 1);
15
16 DBMS_OUTPUT.PUT_LINE('Size of BLOB post-load: '||DBMS_LOB.GETLENGTH(v_dest_blob));
17 DBMS_OUTPUT.PUT_LINE('Size of CLOB post-load: '||DBMS_LOB.GETLENGTH(v_dest_clob));
18
19
20 DBMS_LOB.CLOSE(v_source_locator1);
21 DBMS_LOB.CLOSE(v_source_locator2);
22 DBMS_LOB.CLOSE(v_dest_blob);
23 DBMS_LOB.CLOSE(v_dest_clob);
24
25 EXCEPTION
26 WHEN OTHERS
27 THEN
28 DBMS_OUTPUT.PUT_LINE(SQLERRM);
29
30 DBMS_LOB.CLOSE(v_source_locator1);
31 DBMS_LOB.CLOSE(v_source_locator2);
32 DBMS_LOB.CLOSE(v_dest_blob);
33 DBMS_LOB.CLOSE(v_dest_clob);
34
35 END;
36 /
SQL> SET LONG 64000
SQL> SELECT description
2 FROM book
3 WHERE id = 1;
DESCRIPTION
--------------------------------------------------------------------------------
1 row selected.
SQL>
SQL>
SQL> drop table book;
Table dropped.
Related examples in the same category