Question How to insert a jpeg file into an oracle table?
How to print the same column into data report in VB?
Answer Hi there.
You must create a column with the type LOB (BLOB or CLOB).
After you must create a procedure to read this file from a directory, you will have to use utl_file and utl_raw.
This procedure will transform the file in a binary row.
This procedure is just a example:
declare
lt_report_clob CLOB;
l_max_line_length integer := 1024; -- set as high as the longest line in your file
l_infile UTL_FILE.file_type;
l_buffer varchar2(1024);
l_emp_id report_table.emp_id%type := 123; -- not clear where emp_id comes from
l_filename varchar2(200) := 'my_file_name.csv'; -- get this from somewhere
begin
-- open the file; we assume an Oracle directory has already been created
l_infile := utl_file.fopen('CSV_DIRECTORY', l_filename, 'r', l_max_line_length);
-- initialise the empty clob
dbms_lob.createtemporary(lt_report_clob, TRUE, DBMS_LOB.session);
loop
begin
utl_file.get_line(l_infile, l_buffer);
dbms_lob.append(lt_report_clob, l_buffer);
exception
when no_data_found then
exit;
end;
end loop;
insert into report_table (emp_id, report)
values (l_emp_id, lt_report_clob);
-- free the temporary lob
dbms_lob.freetemporary(lt_report_clob);
