insert mp3 file into BLOB [message #300684] |
Sun, 17 February 2008 17:23  |
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 #300708 is a reply to message #300684] |
Sun, 17 February 2008 22:34   |
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 #300830 is a reply to message #300826] |
Mon, 18 February 2008 03:40   |
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 #300859 is a reply to message #300839] |
Mon, 18 February 2008 06:05   |
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 #300877 is a reply to message #300860] |
Mon, 18 February 2008 07:46   |
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 1215 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   |
Frank
Messages: 7901 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.
Not an Oracle error-message
|
|
|
|
Re: insert mp3 file into BLOB [message #301071 is a reply to message #300879] |
Tue, 19 February 2008 02:27   |
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 #457404 is a reply to message #300684] |
Mon, 24 May 2010 05:37   |
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 #457421 is a reply to message #457409] |
Mon, 24 May 2010 06:29   |
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   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #575324 is a reply to message #575243] |
Tue, 22 January 2013 04:47   |
 |
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...?
|
|
|
|
|
|
|