Use dbms_lob for load clob data from file

  
SQL>
SQL>
SQL> create or replace directory dir1   as 'c:\temp\';

Directory created.

SQL>
SQL> create or replace directory "dir2" as 'c:\temp\';

Directory created.

SQL>
SQL> create table demo
  2  ( id        int primary key,
  3    theClob   clob
  4  )
  5  /

Table created.

SQL>
SQL> declare
  2      l_clob    clob;
  3      l_bfile   bfile;
  4  begin
  5      insert into demo values ( 1, empty_clob() )
  6      returning theclob into l_clob;
  7
  8      l_bfile := bfilename( 'DIR1', 'test.txt' );
  9      dbms_lob.fileopen( l_bfile );
 10
 11      dbms_lob.loadfromfile( l_clob, l_bfile,dbms_lob.getlength( l_bfile ) );
 12
 13      dbms_lob.fileclose( l_bfile );
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the path specified.
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 9


SQL>
SQL> select dbms_lob.getlength(theClob), theClob from demo
  2  /

no rows selected

SQL>
SQL>
SQL> drop table demo;

Table dropped.

SQL>
SQL>
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.	This block demonstrates the use of DBMS_LOB.COPY.
7.	This block demonstrates DBMS_LOB.GETLENGTH.
8.	DBMS_LOB.SUBSTR: Select the first 50 characters of clob_col, and the first 25 bytes of blob_col, for each row.
9.	Use DBMS_LOB.GETLENGTH to get the length of a clob type variable
10.	DBMS_LOB.CREATETEMPORARY
11.	DBMS_LOB.ERASE
12.	DBMS_LOB.FREETEMPORARY
13.	DBMS_LOB.ISTEMPORARY
14.	DBMS_LOB.READ
15.	DBMS_LOB.WRITE
16.	This script tests the DBMS_LOB.LOADFROMFILE procedure

Use dbms_lob for load clob data from file : dbms_lob « System Packages « Oracle PL / SQL

Related examples in the same category