0

I need to retrieve from the database a few large wave files and would like to retrieve divided into wave files smaller (about 5Mb). How can I do? I've seen the procedure dbms_lob.read, but this return maximum file size of 32Kb.

Regards

procedure extract_blob(p_id in number, wrote_length in out number, chunk out blob) is
   len_file binary_integer;
   myblob blob;
   myname varchar2(255);
   buffer_length number := 32760;

   begin    
     select file_wav_data, file_wav_name, dbms_lob.getlength(file_wav_data)
     into myblob, myname, lun_file 
     from t_wav 
     where id = p_id;

     if(len_file > wrote_length) then
         dbms_lob.read(myblob,buffer_length,wrote_length+1,chunk);
         wrote_length := wrote_length + buffer_length;
     else wrote_length := -999; --EOF
     end if;
   end;
Rocco Jr.
  • 77
  • 1
  • 2
  • 10
  • `dbms_lob.read` returns a RAW and RAWs are limited to 32kB, so maybe RAW is not the right format. What do you want to do with this data? Do you want to copy the data somewhere? on the server? on a client?... – Vincent Malgrat Nov 30 '12 at 16:38
  • Sorry no time to prepare an anwser for you but I will give you a tip. Read 32kb, then write it to file (utl_fiile.put_raw), read next 32kb, write it to file.. do that in loop untill you write yours 5mbs or end of blob. But there can be a problem that a wave file has a special file structure (begin and end of file) so it wouldn't be so easy just to split it - you will get a binary split file. – Gumowy Kaczak Dec 01 '12 at 00:37
  • @Vincent. I want to develop a stored procedure that has a BLOB as an output parameter that contains a portion of the wave file. See the edit. – Rocco Jr. Dec 03 '12 at 08:36
  • @MMM I can not use the procedure utl_fiile.put_raw, because I don't want save the wave file on the file system – Rocco Jr. Dec 03 '12 at 08:38
  • @RoccoJr. You're still not explaining what the procedure has to do? Do you want to extract only the first N bytes of the clob? What is the purpose of `wrote_length`? – Vincent Malgrat Dec 03 '12 at 09:08
  • @Vincent Sorry. wrote_lengt and buffer_lenght are the parameters start and end of the portion I want to extract. The buffer_lenght is currently set to 32kb because it's the max dimension that i can extract in a RAW, but i wish it was bigger... – Rocco Jr. Dec 03 '12 at 10:33

1 Answers1

2

You probably want to use temporary LOBs:

procedure extract_blob(
    p_id in number,
    offset in number,
    chunk_length in out number,
    chunk out blob
) is
    chunk blob;
    wav_data blob;
    full_length number;
    chunk_length number;

begin    
    select file_wav_data, dbms_lob.getlength(file_wav_data)
    into wav_data, full_length
    from t_wav 
    where id = p_id;

    chunk_length := greatest(full_length - offset, 0);
    if chunk_length = 0 then
        return;
    end if;

    dbms_lob.createtemporary(chunk, TRUE);

    dbms_lob.copy(chunk, wav_data, chunk_length, 0, offset);

end extract_blob;

If possible, you should free the temporary LOB from the client side after you have processed it (using DBMS_LOB.FREETEMPORARY).

Codo
  • 75,595
  • 17
  • 168
  • 206