1. Create Table into database
CREATE TABLE MY_BLOBS
(
ID VARCHAR2(255 BYTE),
BLOB_COL BLOB
)
;
2. Upload Procudure
CREATE OR REPLACE PROCEDURE upload_file (file_name IN VARCHAR2)
AS
src_loc BFILE := BFILENAME ('EXT_DIR', file_name);
dest_loc BLOB;
lv_id varchar2(50);
BEGIN
select substr(file_name,1,instr(file_name,'.')-1)
into lv_id
from dual;
INSERT INTO my_blobs
VALUES (lv_id, EMPTY_BLOB ())
RETURNING blob_col
INTO dest_loc;
DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (dest_loc, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (dest_lob => dest_loc,
src_lob => src_loc,
amount => DBMS_LOB.getlength (src_loc)
);
DBMS_LOB.CLOSE (dest_loc);
DBMS_LOB.CLOSE (src_loc);
COMMIT;
END;
/
3. Download Procedure
CREATE OR REPLACE PROCEDURE download_file (file_name IN VARCHAR2)
AS
l_file UTL_FILE.file_type;
l_buffer RAW (32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
BEGIN
-- Get LOB locator
SELECT BLOB_COL
INTO l_blob
FROM my_blobs
WHERE ID = file_name;
l_blob_len := DBMS_LOB.getlength (l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen ('EXT_DIR', file_name, 'WB', 32767);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw (l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose (l_file);
EXCEPTION
WHEN OTHERS
THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open (l_file)
THEN
UTL_FILE.fclose (l_file);
END IF;
RAISE;
END;
/
CREATE TABLE MY_BLOBS
(
ID VARCHAR2(255 BYTE),
BLOB_COL BLOB
)
;
2. Upload Procudure
CREATE OR REPLACE PROCEDURE upload_file (file_name IN VARCHAR2)
AS
src_loc BFILE := BFILENAME ('EXT_DIR', file_name);
dest_loc BLOB;
lv_id varchar2(50);
BEGIN
select substr(file_name,1,instr(file_name,'.')-1)
into lv_id
from dual;
INSERT INTO my_blobs
VALUES (lv_id, EMPTY_BLOB ())
RETURNING blob_col
INTO dest_loc;
DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (dest_loc, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (dest_lob => dest_loc,
src_lob => src_loc,
amount => DBMS_LOB.getlength (src_loc)
);
DBMS_LOB.CLOSE (dest_loc);
DBMS_LOB.CLOSE (src_loc);
COMMIT;
END;
/
3. Download Procedure
CREATE OR REPLACE PROCEDURE download_file (file_name IN VARCHAR2)
AS
l_file UTL_FILE.file_type;
l_buffer RAW (32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
BEGIN
-- Get LOB locator
SELECT BLOB_COL
INTO l_blob
FROM my_blobs
WHERE ID = file_name;
l_blob_len := DBMS_LOB.getlength (l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen ('EXT_DIR', file_name, 'WB', 32767);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw (l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose (l_file);
EXCEPTION
WHEN OTHERS
THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open (l_file)
THEN
UTL_FILE.fclose (l_file);
END IF;
RAISE;
END;
/
