load file into blob column

create table test
(
id number primary key,
docs BLOB
);

create or replace directory doc_loc
as 'c:\test';

CREATE OR REPLACE PROCEDURE Load_BLOB_From_File (file_name in varchar2)
AS
src_loc bfile:= bfilename('DOC_LOC',Load_BLOB_From_File.file_name);
dest_loc BLOB;
begin
insert into test values(1,empty_blob()) returning docs
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;
/

show errors;
exec Load_BLOB_From_File('test.pdf');

dump_csv

CREATE OR REPLACE FUNCTION RMVER721.dump_csv (
p_query IN VARCHAR2,
p_filename IN VARCHAR2 DEFAULT 'TEMP',
p_separator IN VARCHAR2 DEFAULT ',',
p_dir IN VARCHAR2 DEFAULT 'XLS_DIR'
)
RETURN NUMBER
IS
l_output UTL_FILE.file_type;
l_thecursor PLS_INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (4000);
l_status PLS_INTEGER;
l_colcnt NUMBER (10) DEFAULT 0;
l_separator VARCHAR2 (10) DEFAULT '';
l_cnt NUMBER (10) DEFAULT 0;
l_sht NUMBER (5) DEFAULT 0;
l_tot NUMBER (10) DEFAULT 0;
rec_tab DBMS_SQL.desc_tab;
BEGIN
l_output := UTL_FILE.fopen (p_dir, p_filename || l_sht || '.xls', 'w');
DBMS_SQL.parse (l_thecursor, p_query, DBMS_SQL.native);
DBMS_SQL.describe_columns (l_thecursor, l_colcnt, rec_tab);

FOR i IN rec_tab.FIRST .. rec_tab.LAST
LOOP
UTL_FILE.put (l_output, l_separator || rec_tab (i).col_name);
l_separator := CHR (9);
END LOOP;

UTL_FILE.new_line (l_output);

FOR i IN 1 .. 255
LOOP
BEGIN
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 2000);
l_colcnt := i;
EXCEPTION
WHEN OTHERS
THEN
IF (SQLCODE = -1007)
THEN
EXIT;
ELSE
RAISE;
END IF;
END;
END LOOP;

DBMS_SQL.define_column (l_thecursor, 1, l_columnvalue, 2000);
l_status := DBMS_SQL.EXECUTE (l_thecursor);

LOOP
EXIT WHEN (DBMS_SQL.fetch_rows (l_thecursor) <= 0);
l_separator := '';

FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue);
UTL_FILE.put (l_output, l_separator || l_columnvalue);
l_separator := CHR (9);
END LOOP;

UTL_FILE.new_line (l_output);
l_cnt := l_cnt + 1;
l_tot := l_tot + 1;

IF l_cnt >= 65000
THEN
l_cnt := 1;
l_sht := l_sht + 1;
UTL_FILE.fclose (l_output);
l_output :=
UTL_FILE.fopen (p_dir, p_filename || l_sht || '.xls', 'w');
l_separator := '';

FOR i IN rec_tab.FIRST .. rec_tab.LAST
LOOP
UTL_FILE.put (l_output, l_separator || rec_tab (i).col_name);
l_separator := CHR (9);
END LOOP;

UTL_FILE.new_line (l_output);
END IF;
END LOOP;

DBMS_SQL.close_cursor (l_thecursor);
UTL_FILE.fclose (l_output);
RETURN l_tot;
END dump_csv;
/

proc_dlmt

PROCEDURE proc_dlmt (qry VARCHAR2, filename VARCHAR2, nr OUT NUMBER)
IS
cursor_number exec_sql.curstype;
ln$empno NUMBER;
lc$name VARCHAR2 (30);
ln$count NUMBER;
BEGIN
cursor_number := exec_sql.open_cursor;
exec_sql.parse (cursor_number, qry);
ln$count := exec_sql.EXECUTE (cursor_number);
exec_sql.close_cursor (cursor_number);

SELECT dump_csv (qry, filename)
INTO nr
FROM DUAL;

FOR fcnt IN 0 .. TRUNC (nr / 65000)
LOOP
web.show_document ('/forms/webutil/' || filename || fcnt || '.xls',
'_blank'
);
END LOOP;
EXCEPTION
WHEN exec_sql.invalid_connection
THEN
alert_msg ('Invalid Connection error.', 'S');
WHEN exec_sql.package_error
THEN
alert_msg (SUBSTR (exec_sql.last_error_mesg,
INSTR (exec_sql.last_error_mesg, ':') + 2
),
'S'
);

IF exec_sql.is_open (cursor_number)
THEN
exec_sql.close_cursor (cursor_number);
alert_msg ('Exception - Cleaned up Cursor', 'N');
END IF;
END;