8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Import BLOB
Related articles.
- Export BLOB Contents (8i)
- Export BLOB Contents Using UTL_FILE
- Import CLOB Contents
- Export CLOB Contents
The following article presents a simple methods for importing a file into a BLOB datatype. First a directory object is created to point to the relevant filesystem directory.
CREATE OR REPLACE DIRECTORY BLOB_DIR AS 'C:\';
Next we create a table to hold the BLOB.
CREATE TABLE tab1 ( id NUMBER, blob_data BLOB );
We import the file into a BLOB datatype and insert it into the table.
DECLARE l_bfile BFILE; l_blob BLOB; l_dest_offset INTEGER := 1; l_src_offset INTEGER := 1; BEGIN INSERT INTO tab1 (id, blob_data) VALUES (1, empty_blob()) RETURN blob_data INTO l_blob; l_bfile := BFILENAME('BLOB_DIR', 'MyImage.gif'); DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); -- loadfromfile deprecated. -- DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile)); DBMS_LOB.loadblobfromfile ( dest_lob => l_blob, src_bfile => l_bfile, amount => DBMS_LOB.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset); DBMS_LOB.fileclose(l_bfile); COMMIT; END; /
To update an existing BLOB do the following.
DECLARE l_bfile BFILE; l_blob BLOB; l_dest_offset INTEGER := 1; l_src_offset INTEGER := 1; BEGIN SELECT blob_data INTO l_blob FROM tab1 WHERE id = 1 FOR UPDATE; l_bfile := BFILENAME('BLOB_DIR', 'MyImage.gif'); DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); DBMS_LOB.trim(l_blob, 0); -- loadfromfile deprecated. -- DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile)); DBMS_LOB.loadblobfromfile ( dest_lob => l_blob, src_bfile => l_bfile, amount => DBMS_LOB.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset); DBMS_LOB.fileclose(l_bfile); END; /
In reality you would probably put this code into a stored procedure, or packaged procedure. This example is available here.
CREATE OR REPLACE PROCEDURE file_to_blob (p_blob IN OUT NOCOPY BLOB, p_dir IN VARCHAR2, p_filename IN VARCHAR2) AS l_bfile BFILE; l_dest_offset INTEGER := 1; l_src_offset INTEGER := 1; BEGIN l_bfile := BFILENAME(p_dir, p_filename); DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); DBMS_LOB.trim(p_blob, 0); IF DBMS_LOB.getlength(l_bfile) > 0 THEN DBMS_LOB.loadblobfromfile ( dest_lob => p_blob, src_bfile => l_bfile, amount => DBMS_LOB.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset); END IF; DBMS_LOB.fileclose(l_bfile); END file_to_blob; /
You might call this like the following.
DECLARE l_blob BLOB; BEGIN SELECT blob_data INTO l_blob FROM tab1 WHERE id = 1 FOR UPDATE; file_to_blob (p_blob => l_blob, p_dir => 'BLOB_DIR', p_filename => 'MyImage.gif'); END; /
For more information see:
- Export BLOB Contents (8i)
- Export BLOB Contents Using UTL_FILE
- Import CLOB Contents
- Export CLOB Contents
- DBMS_LOB
- UTL_FILE
Hope this helps. Regards Tim...