Expert: Edson - 6/29/2009

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);

  -- close the file
  UTL_FILE.fclose(l_infile);

end;

Wiat reply