Load a file : DBMS_LOB « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. DBMS_LOB

SQL>
SQL> set echo on
SQL>
SQL> create table demo
  2  ( id           int primary key,
  3    theBlob      blob
  4  )
  5  /

Table created.

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

Directory created.

SQL>
SQL> create sequence blob_seq;

Sequence created.

SQL>
SQL>
SQL> create or replace procedure load_a_file( p_dir_name in varchar2,p_file_name in varchar2) as
  2      l_blob    blob;
  3      l_bfile   bfile;
  4  begin
  5      insert into demo values ( blob_seq.nextval, empty_blob() )
  6      returning theBlob into l_Blob;
  7
  8      l_bfile := bfilename( p_dir_name, p_file_name );
  9      dbms_lob.fileopen( l_bfile );
 10
 11      dbms_lob.loadfromfile( l_blob, l_bfile,dbms_lob.getlength( l_bfile ) );
 12      dbms_lob.fileclose( l_bfile );
 13  end;
 14  /

Procedure created.

SQL>
SQL> exec load_a_file( 'MY_FILES', 'clean.sql' );
BEGIN load_a_file( 'MY_FILES', 'clean.sql' ); END;

*
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 "JAVA2S.LOAD_A_FILE", line 9
ORA-06512: at line 1


SQL>
SQL> drop table demo;

Table dropped.

SQL> drop sequence blob_seq;

Sequence dropped.

SQL>