Home » SQL & PL/SQL » SQL & PL/SQL » insert mp3 file into BLOB
insert mp3 file into BLOB [message #300684] Sun, 17 February 2008 17:23 Go to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
Hi
How can insert mp3 file into BLOB column into tab1
(by PL\SQL insert)?
Thanx
Rafeek
Re: insert mp3 file into BLOB [message #300687 is a reply to message #300684] Sun, 17 February 2008 18:28 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
One option would be Loading a BLOB with Data from a BFILE.

You may also consider External Tables using LOBFILE.

[Updated on: Sun, 17 February 2008 18:33]

Report message to a moderator

Re: insert mp3 file into BLOB [message #300708 is a reply to message #300684] Sun, 17 February 2008 22:34 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

Hope i will helps.


SQL>create table imagetab (imagfile blob)

SQL>create or replace directory 
    imagefiles as 'c:\oracle'

SQL>  declare
  2    v_bfile BFILE;
  3    v_blob  BLOB;
  4  begin
  5    insert into imagetab (imagfile)
  6    values (empty_blob())
  7    return imagfile into v_blob;
  8    v_bfile := BFILENAM('IMAGEFILES', 
       'pic.jpeg');
  9    Dbms_Lob.fileopen (v_bfile, Dbms_Lob.File_Readonly);
 10    Dbms_Lob.Loadfromfile 
       (v_blob, v_bfile,Dbms_Lob.Getlength(v_bfile));
 11    Dbms_Lob.Fileclose(v_bfile);
 12    commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

select count(*) from imagetab;


  COUNT(*)
----------
         1


Quote:

pic.jpeg file should be in c:\oracle
directory also.



Thanks

Mano
Re: insert mp3 file into BLOB [message #300817 is a reply to message #300708] Mon, 18 February 2008 03:16 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
how can i return file form table and save it as
a file into os (BY PL\SQL)?
thax
Rafeek

[Updated on: Mon, 18 February 2008 03:18]

Report message to a moderator

Re: insert mp3 file into BLOB [message #300826 is a reply to message #300684] Mon, 18 February 2008 03:31 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member

By UTL_FILE Built-ins
Re: insert mp3 file into BLOB [message #300830 is a reply to message #300826] Mon, 18 February 2008 03:40 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
i insert a mp3 file into table .
UTL_FILE create text file .
how can i get my mp3 file form BLOB column into table tab1
and save this my mp3 file in my os ?
thax
Rafeek
Re: insert mp3 file into BLOB [message #300839 is a reply to message #300817] Mon, 18 February 2008 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You get it in binary format in your application and your application save it as it wants.
Or you can use utl_file.put_raw.

Regards
Michel


Re: insert mp3 file into BLOB [message #300859 is a reply to message #300839] Mon, 18 February 2008 06:05 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
i use this code to insert image or any file into blob column in database

CREATE TABLE B_FILE
(
  BLOB_COLUMN  BLOB,
  ID           NUMBER
);

declare
a_blob blob;
a_bfile bfile := bfilename('REEDIR','j.jpg');
begin
insert into b_file  values (empty_blob(),2) returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob,a_bfile,dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;


i use this code to return image or mp3 file form table B_FILE
and save it in 'REEDIR' directory
by the image output dont like the source image
declare
    v_out_outdir          VARCHAR2(2000) := 'REEDIR';
    v_out_filename        VARCHAR2(500):='ahmed1';
    v_out_filename_ext    VARCHAR2(4)    := '.zip';
    v_out_filename_full   VARCHAR2(500);
    v_file_count          NUMBER         := 0;
    v_file_handle         UTL_FILE.FILE_TYPE;
   
    TYPE v_lob_cur_typ IS REF CURSOR;
    v_lob_cur             v_lob_cur_typ;
    v_sql_string          VARCHAR2(4000);
    v_blob_loc            BLOB;
    v_buffer              RAW(32767);
    v_buffer_size         CONSTANT BINARY_INTEGER := 32767;
    v_amount              BINARY_INTEGER;
    v_offset              NUMBER(38);
    
BEGIN

        DBMS_OUTPUT.ENABLE(1000000);
    
    v_sql_string := 'SELECT BLOB_COLUMN FROM rafeek.B_FILE where ID=3';
    
    OPEN v_lob_cur FOR v_sql_string;
    
    LOOP
    
        FETCH v_lob_cur INTO  v_blob_loc;
        EXIT WHEN v_lob_cur%NOTFOUND;
        
        v_file_count := v_file_count + 1;
        v_out_filename_full := v_out_filename || v_out_filename_ext;
        
        v_file_handle := UTL_FILE.FOPEN(v_out_outdir, v_out_filename_full, 'w',  32760);
        
        v_amount := v_buffer_size;
        v_offset := 1;
        
        DECLARE
            invalid_LOB_locator EXCEPTION;
          --  PRAGMA EXCEPTION_INIT(invalid_LOB_locator, -06502);
        
        BEGIN
        
            WHILE v_amount >= v_buffer_size
            LOOP
                DBMS_LOB.READ(
                lob_loc    => v_blob_loc,
                amount     => v_amount,
                offset     => v_offset,
                buffer     => v_buffer);
                
                v_offset := v_offset + v_amount;
                
                UTL_FILE.PUT_RAW(
                file      => v_file_handle,
                buffer    => v_buffer,
                autoflush => true);
                
                UTL_FILE.FFLUSH(file => v_file_handle);
            END LOOP;
        
        END;
       
        UTL_FILE.FCLOSE(v_file_handle);

    END LOOP;
    
    CLOSE v_lob_cur;

END;

[Updated on: Mon, 18 February 2008 06:08]

Report message to a moderator

Re: insert mp3 file into BLOB [message #300860 is a reply to message #300859] Mon, 18 February 2008 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why you use dynamic SQL and not a static one?
Why "DBMS_OUTPUT.ENABLE(1000000);"?
Why "invalid_LOB_locator EXCEPTION;"?
...
If you post code then remove garbage.

Regards
Michel
Re: insert mp3 file into BLOB [message #300877 is a reply to message #300860] Mon, 18 February 2008 07:46 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
i think i remove garbage
Quote:
declare
v_out_outdir VARCHAR2(2000) := 'REEDIR';
v_out_filename VARCHAR2(500):='99999';
v_out_filename_ext VARCHAR2(4) := '.jpg';
v_out_filename_full VARCHAR2(500);
v_file_count NUMBER := 0;
v_file_handle UTL_FILE.FILE_TYPE;
TYPE v_lob_cur_typ IS REF CURSOR;
v_lob_cur v_lob_cur_typ;
v_sql_string VARCHAR2(4000);
v_blob_loc BLOB;
v_buffer RAW(32767);
v_buffer_size CONSTANT BINARY_INTEGER := 32767;
v_amount BINARY_INTEGER;
v_offset NUMBER(38);

BEGIN
v_sql_string := 'SELECT BLOB_COLUMN FROM rafeek.B_FILE where ID=4';
OPEN v_lob_cur FOR v_sql_string;
LOOP
FETCH v_lob_cur INTO v_blob_loc;
EXIT WHEN v_lob_cur%NOTFOUND;
v_file_count := v_file_count + 1;
v_out_filename_full := v_out_filename || v_out_filename_ext;
v_file_handle := UTL_FILE.FOPEN(v_out_outdir, v_out_filename_full, 'w', 32760);
v_amount := v_buffer_size;
v_offset := 1;
DECLARE
BEGIN
WHILE v_amount >= v_buffer_size
LOOP
DBMS_LOB.READ(
lob_loc => v_blob_loc,
amount => v_amount,
offset => v_offset,
buffer => v_buffer);

v_offset := v_offset + v_amount;

UTL_FILE.PUT_RAW(
file => v_file_handle,
buffer => v_buffer,
autoflush => true);

UTL_FILE.FFLUSH(file => v_file_handle);
END LOOP;
END;
UTL_FILE.FCLOSE(v_file_handle);
END LOOP;
CLOSE v_lob_cur;
END;

not work correct
  • Attachment: j.zip
    (Size: 38.54KB, Downloaded 205 times)

[Updated on: Mon, 18 February 2008 07:48]

Report message to a moderator

Re: insert mp3 file into BLOB [message #300879 is a reply to message #300877] Mon, 18 February 2008 07:53 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
Quote:
v_sql_string := 'SELECT BLOB_COLUMN FROM rafeek.B_FILE where ID=4';
OPEN v_lob_cur FOR v_sql_string;

What's with the dynamic sql?

Quote:
v_offset := 1;
DECLARE
BEGIN

Declare and begin are totally useless here.

Quote:
not work correct

Not an Oracle error-message
Re: insert mp3 file into BLOB [message #300880 is a reply to message #300877] Mon, 18 February 2008 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Edit your post, use CODE tags and not QUOTE, indent the code.

"not work correct" is not an Oracle message.

Regards
Michel
Re: insert mp3 file into BLOB [message #301071 is a reply to message #300879] Tue, 19 February 2008 02:27 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
BEGIN
-- Get LOB locator
SELECT BLOB_COLUMN
INTO l_blob
FROM B_FILE
WHERE id = 4;
l_blob_len := DBMS_LOB.getlength(l_blob);
l_file := UTL_FILE.fopen('REEDIR','MyImage.jpg','w', 32767);
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILE.fclose(l_file);

EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/
Re: insert mp3 file into BLOB [message #301085 is a reply to message #301071] Tue, 19 February 2008 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 18 February 2008 14:54
Edit your post, use CODE tags and not QUOTE, indent the code.

"not work correct" is not an Oracle message.

Regards
Michel


Re: insert mp3 file into BLOB [message #457404 is a reply to message #300684] Mon, 24 May 2010 05:37 Go to previous messageGo to next message
mageed_ahmed
Messages: 4
Registered: June 2009
Location: KSA
Junior Member
Gentlemen.

I succeeded to insert a file inside the BLOB and retrieve it back to the file system from the BLOLB with different codes

Every thing is working fine but when I open the retrieved file it does not open

for images saying cannot open the file

Thanks and waiting your help

Re: insert mp3 file into BLOB [message #457409 is a reply to message #457404] Mon, 24 May 2010 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your code and use code tags.

Regards
Michel
Re: insert mp3 file into BLOB [message #457421 is a reply to message #457409] Mon, 24 May 2010 06:29 Go to previous messageGo to next message
mageed_ahmed
Messages: 4
Registered: June 2009
Location: KSA
Junior Member
Thanks Michel

Now it opens in file system but with very bad resolution as the file Mr. Rafeek attached j.zip.

How can retrieve it with same specs?

DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
BEGIN
-- Get LOB locator
SELECT BLOB_COLUMN
INTO l_blob
FROM B_FILE
WHERE id = 2;
l_blob_len := DBMS_LOB.getlength(l_blob);
l_file := UTL_FILE.fopen('REEDIR','MyImage.jpg','w', 32767);
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/


Thanks in advance
Ahmed

[Updated on: Mon, 24 May 2010 06:37]

Report message to a moderator

Re: insert mp3 file into BLOB [message #457440 is a reply to message #457421] Mon, 24 May 2010 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now it opens in file system but with very bad resolution

How is this an Oracle problem?
Oracle (rdbms) does not care of what you put inside a BLOB it is just bits and bytes.
It does not open anything and surely not an image.
Oracle returns what you gave it, if you gave it low resolution image it will not return a hihj resolution image.

Regards
Michel

[Updated on: Mon, 24 May 2010 09:31]

Report message to a moderator

Re: insert mp3 file into BLOB [message #457449 is a reply to message #457440] Mon, 24 May 2010 09:01 Go to previous messageGo to next message
mageed_ahmed
Messages: 4
Registered: June 2009
Location: KSA
Junior Member
Thanks for your reply

it is sure inside DB right as inserted if I read using forms or toad

But what is the PL code I can use to export the image to file system correctly?

[Updated on: Mon, 24 May 2010 09:02]

Report message to a moderator

Re: insert mp3 file into BLOB [message #457452 is a reply to message #457449] Mon, 24 May 2010 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 22521
Registered: January 2009
Senior Member
>it is sure inside DB right as inserted if I read using forms or toad

You have no proof of this.

>But what is the PL code I can use to export the image to file system correctly?

You need to write it yourself (or have others to do it for you).

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: insert mp3 file into BLOB [message #457454 is a reply to message #457449] Mon, 24 May 2010 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But what is the PL code I can use to export the image to file system correctly?

The one you posted seems correct.

Regards
Michel
Re: insert mp3 file into BLOB [message #575237 is a reply to message #300708] Mon, 21 January 2013 10:22 Go to previous messageGo to next message
mathiazhagan01
Messages: 5
Registered: January 2013
Location: Coimbatore
Junior Member

Your answer is so useful...! Can you please tell me how to retrieve the image file again from the database...?
Re: insert mp3 file into BLOB [message #575241 is a reply to message #575237] Mon, 21 January 2013 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SELECT.

Regards
Michel
Re: insert mp3 file into BLOB [message #575242 is a reply to message #575241] Mon, 21 January 2013 10:38 Go to previous messageGo to next message
mathiazhagan01
Messages: 5
Registered: January 2013
Location: Coimbatore
Junior Member

Actually I have done for song file...! I tried select...! But it shows error..!

SP2-0678: Column or attribute type can not be displayed by SQL*Plus

I am doing a project in c#,to retrieve song file from database and to play it..! Please help me..!
Re: insert mp3 file into BLOB [message #575243 is a reply to message #575242] Mon, 21 January 2013 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot do it in SQL*Plus as it does not know how to "display" a song (it even does not know your BLOB is a song).
You can do it in C" as YOU know the BLOB is a song and so you know how to "display" it.
As you know how to retrieve the BLOB, the problem is no more an Oracle or SQL one, it is a C# one.

Regards
Michel
Re: insert mp3 file into BLOB [message #575324 is a reply to message #575243] Tue, 22 January 2013 04:47 Go to previous messageGo to next message
mathiazhagan01
Messages: 5
Registered: January 2013
Location: Coimbatore
Junior Member

Thank you for your answers...!

I have tried like this:

create table songdb(songfile blob);

create or replace directory Insert_File as 'D:\Insert_File';

declare
v_bfile BFILE;
v_blob BLOB;
begin
insert into songdb (songfile)
values (empty_blob())
return songfile into v_blob;
v_bfile := BFILENAME('INSERT_FILE','Insert_Song.mp3');
Dbms_Lob.fileopen (v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile
(v_blob, v_bfile,Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);
commit;
end;
/

select count(*) from songdb;

create or replace directory Retrieve_File as 'D:\Retrieve_File';

DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
BEGIN
-- Get LOB locator
SELECT songfile
INTO l_blob
FROM songdb;
l_blob_len := DBMS_LOB.getlength(l_blob);
l_file := UTL_FILE.fopen('RETRIEVE_FILE','Retrieve_Song.mp3','w', 32767);
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/

It retrieved the song,but it not exactly what I inserted.It is noisy and plays too fast.Is it possible to retrieved the song without any changes...?
Re: insert mp3 file into BLOB [message #575325 is a reply to message #575324] Tue, 22 January 2013 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

I don't see any reason why there could be differences (but as the code is not formatted it is hard to tell).
Did you check the differences with a "diff" in binary mode?
What are they?

Regards
Michel
Re: insert mp3 file into BLOB [message #575326 is a reply to message #575325] Tue, 22 January 2013 04:56 Go to previous messageGo to next message
mathiazhagan01
Messages: 5
Registered: January 2013
Location: Coimbatore
Junior Member

Sir, I don't know how to check the differences with a "diff" in binary mode...! Can you please explain that...?
Re: insert mp3 file into BLOB [message #575332 is a reply to message #575325] Tue, 22 January 2013 05:28 Go to previous messageGo to next message
mathiazhagan01
Messages: 5
Registered: January 2013
Location: Coimbatore
Junior Member

Sir, I have tried this in c#:

long SongOneLength = song_one.Length;
            long SongTwoLength = song_two.Length;

            long i = 0, j = 0;

            if (SongOneLength == SongTwoLength)
            {
                MessageBox.Show("Same Length");

                for (i = 0; i < SongOneLength; i++)
                {
                    if (song_one[i] == song_two[i])
                        j++;
                }

                if(i==j)
                    MessageBox.Show("Song Matched");
                else
                    MessageBox.Show("Song Not Matched");

            }
            else
            {
                MessageBox.Show("Not Same Length");
            }


song_one which is before inserted,song_two is after retrieved. It shows "Not Same Length"...!

Can please help me...?
Re: insert mp3 file into BLOB [message #575336 is a reply to message #575332] Tue, 22 January 2013 05:45 Go to previous message
Michel Cadot
Messages: 58610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search on the web for a Windows "diff" tool and use it to see the difference between the 2 files.

Regards
Michel
Previous Topic: RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT
Next Topic: Maintaining log table
Goto Forum:
  


Current Time: Tue Jul 29 10:58:55 CDT 2014

Total time taken to generate the page: 0.08876 seconds