Reading files from directory

First we need to create a directory in oracle that will point to a location on the disk.

CREATE OR REPLACE DIRECTORY ctemp AS 'c:\test';

We can see the directory in the system view dba_directories.

This procedure reads the file and stores it as a blob in a table test.

create or replace procedure read_blob(p_dir varchar2, p_file_name varchar2) is
  v_blob         blob;
  v_bfile        bfile;
  v_dest_offset  pls_integer;
  v_src_offset   pls_integer;
  v_name varchar2(100) := p_file_name;
begin
      dbms_lob.createtemporary(v_blob, true, dbms_lob.session);
      v_bfile := bfilename(p_dir, v_name);
      dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
      v_dest_offset  := 1;
      v_src_offset   := 1;      
      dbms_lob.loadblobfromfile(v_blob, v_bfile, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset);
      
      insert into test
        (name, content)
      values
        (v_name, v_blob);      

      dbms_lob.fileclose(v_bfile);
      dbms_lob.freetemporary(v_blob);
end read_blob;

Oracle BUG when using xmltype() function

I found a bug on Oracle DB when using types. The problem occours when you try to make an XML data using xmltype function from types that have more than 3 subtypes.
Here is an example of the test code:

CREATE OR REPLACE TYPE D_T AS OBJECT(F varchar2(1),G varchar2(2))
/
CREATE OR REPLACE TYPE C_T AS OBJECT (D1 D_T, D2 D_T)
/
CREATE OR REPLACE TYPE B_T AS OBJECT(C C_T)
/
CREATE OR REPLACE TYPE A_T AS OBJECT(B B_T)
/
declare
 v_a a_t;
begin                       
   v_a := a_t(
              b_t(
                  c_t(
                      null,
                      d_t('1','2')
                      )
                  )
              );
   dbms_output.put_line(xmltype(v_a).extract('*').getClobVal);
   v_a := a_t(
              b_t(
                  c_t(
                      d_t(null,null),
                      d_t('1','2')
                      )
                  )
              );
   dbms_output.put_line(xmltype(v_a).extract('*').getClobVal);
end;
/

The first dbms_output prints out:

<A_T>
  <B/>
</A_T>

The second one prints out:

<A_T>
  <B>
    <C>
      <D1/>
      <D2>
        <F>1</F>
        <G>2</G>
      </D2>
    </C>
  </B>
</A_T>

The output on both should be the almost the same with one difference. We can see that in the first output, the data from c_t type gets lost. The data is stored in the v_a variable but xmltype doesn't show it. The correct output from the first dbms_output should be (with C element that also contains D2 and is without D1 element):

<A_T>
  <B>
    <C>
      <D2>
        <F>1</F>
        <G>2</G>
      </D2>
    </C>
  </B>
</A_T>

The problem was tested both on 10g and 11g version of DB.

Simple blob to clob conversion

Function converts blob to clob.

create or replace function blob_to_clob(p_blob blob) return clob is
declare 
 v_blob blob; 
 v_clob clob := 'x'; 
 v_dest_offsset integer := 1; 
 v_src_offsset integer := 1; 
 v_lang_context integer := dbms_lob.default_lang_ctx;
 v_warning integer; 
BEGIN 
 dbms_lob.converttoclob 
 ( dest_lob => v_clob 
 , src_blob => p_blob 
 , amount => dbms_lob.lobmaxsize 
 , dest_offset => v_dest_offsset 
 , src_offset => v_src_offsset 
 , blob_csid => dbms_lob.default_csid 
 , lang_context => v_lang_context 
 , warning => v_warning 
 ); 
 return v_clob;
END;
/

In case of using a different charset change the value of blob_csid to blob_csid => nls_charset_id('X') where X is a for example UTF8.