Here is a simple example of loading BLOB content from disk. First, the operating user will need a grant to enable the creation of an Oracle DIRECTORY object: GRANT CREATE ANY DIRECTORY TO testuser;
Next, the user will need to create the DIRECTORY object that points to the location of the source file on disk (in this case, C:\dbload\): CREATE OR REPLACE DIRECTORY test_dir AS 'C:\dbload';
Finally, below is an example of loading test_blob.dat from disk and inserting it into the test_blob_tab table:
SQL> DESC test_blob_tab; Name Null? Type ----------------------------------------- -------- ---------------------------- DATA BLOB SQL> DECLARE 2 3 insert_blob BLOB; 4 5 FUNCTION load_blob( filename VARCHAR2 ) 6 RETURN BLOB 7 AS 8 temp_blob BLOB; 9 file BFILE; 10 src_offset NUMBER DEFAULT 1; 11 dest_offset NUMBER DEFAULT 1; 12 BEGIN 13 14 DBMS_LOB.CREATETEMPORARY( temp_blob, FALSE, DBMS_LOB.SESSION ); 15 16 file := BFILENAME( 'TEST_DIR', filename ); 17 DBMS_LOB.OPEN( file, DBMS_LOB.LOB_READONLY ); 18 19 DBMS_LOB.LOADBLOBFROMFILE( temp_blob, file, 20 DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset ); 21 22 DBMS_LOB.CLOSE( file ); 23 24 25 RETURN temp_blob; 26 27 END; 28 29 BEGIN 30 31 insert_blob := load_blob( 'test_blob.dat' ); 32 INSERT INTO test_blob_tab ( data ) VALUES ( insert_blob ); 33 34 END; 35 / PL/SQL procedure successfully completed.
Advertisements
Pingback: Oracle – Writing a BLOB to an operating system file | brainFizzle