This script tests the DBMS_LOB.LOADFROMFILE procedure : dbms_lob « System Packages « Oracle PL / SQL






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

1.Use dbms_lob.getchunksize to get the clob column size
2.Call dbms_lob.write to write value to clob type value
3.Use dbms_lob.writeappend to append value to clob type value
4.Use dbms_lob.compare to compare
5.Use dbms_lob.compare to compare clob type value with offset
6.Use dbms_lob for load clob data from file
7.This block demonstrates the use of DBMS_LOB.COPY.
8.This block demonstrates DBMS_LOB.GETLENGTH.
9.DBMS_LOB.SUBSTR: Select the first 50 characters of clob_col, and the first 25 bytes of blob_col, for each row.
10.Use DBMS_LOB.GETLENGTH to get the length of a clob type variable
11.DBMS_LOB.CREATETEMPORARY
12.DBMS_LOB.ERASE
13.DBMS_LOB.FREETEMPORARY
14.DBMS_LOB.ISTEMPORARY
15.DBMS_LOB.READ
16.DBMS_LOB.WRITE