This script tests the DBMS_LOB.LOADFROMFILE procedure
SQL>
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
14 DBMS_LOB.OPEN(v_source_locator1, DBMS_LOB.LOB_READONLY);
15 DBMS_LOB.OPEN(v_source_locator2, DBMS_LOB.LOB_READONLY);
16 DBMS_LOB.OPEN(v_dest_blob, DBMS_LOB.LOB_READWRITE);
17 DBMS_LOB.OPEN(v_dest_clob, DBMS_LOB.LOB_READWRITE);
18
19 DBMS_OUTPUT.PUT_LINE('Length of the BLOB file is: '||DBMS_LOB.GETLENGTH(v_source_locator1));
20 DBMS_OUTPUT.PUT_LINE('Length of the CLOB file is: '||DBMS_LOB.GETLENGTH(v_source_locator2));
21 DBMS_OUTPUT.PUT_LINE('Size of BLOB pre-load: '||DBMS_LOB.GETLENGTH(v_dest_blob));
22 DBMS_OUTPUT.PUT_LINE('Size of CLOB pre-load: '||DBMS_LOB.GETLENGTH(v_dest_clob));
23
24
25
26 DBMS_LOB.CLOSE(v_source_locator1);
27 DBMS_LOB.CLOSE(v_source_locator2);
28 DBMS_LOB.CLOSE(v_dest_blob);
29 DBMS_LOB.CLOSE(v_dest_clob);
30
31 EXCEPTION
32 WHEN OTHERS
33 THEN
34 DBMS_OUTPUT.PUT_LINE(SQLERRM);
35
36 DBMS_LOB.CLOSE(v_source_locator1);
37 DBMS_LOB.CLOSE(v_source_locator2);
38 DBMS_LOB.CLOSE(v_dest_blob);
39 DBMS_LOB.CLOSE(v_dest_clob);
40
41 END;
42 /
SQL>
SQL> PROMPT ** SELECT of the description column
SQL>
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.
SQL>
SQL>
Related examples in the same category