-
Retriving binary file from blob column
Hi
I have inserted a BLOB object(a movie file) into a database column (datatype-BLOB) using the function bfilename and dbms_lob package. It was done successfully without any problems. But unfortunately I lost the physical file (.dat file) and now want to retrive that file from the database column. Is there any way using PL/SQL to read that BLOB column and write into the OS file. I am using oracle 9i release 2 on windows 2000 professional.
Thanks
Burzin
-
May be utl_file.put_raw will help you.
I have not done it so far.
Tamil
-
Thanks
Hi
Thanks a lot. I atleast got a hint. I was viewing the 9.0 documentation and there is no function as "put_raw" but I found it in the 9.2 documentation (Release 2). There is "fflush" also which may be useful. I shall try using them and get back in a day or two.
Thanks again
Burzin
-
Hope this helps:
v_pos := 1;
WHILE v_pos < v_blob_len LOOP
DBMS_LOB.READ (v_blob, 32767, v_pos, v_buffer);
UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
v_pos := v_pos + 32767;
END LOOP;
UTL_FILE.FCLOSE(v_file);
Cheers!
OraKid.
-
It Does not work
v_pos := 1;
WHILE v_pos < v_blob_len LOOP
DBMS_LOB.READ (v_blob, 32767, v_pos, v_buffer);
UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
v_pos := v_pos + 32767;
END LOOP;
UTL_FILE.FCLOSE(v_file);
The code given above gives me the error
ORA-29285: file write error
at line 4 i.e., UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
I tried writing a test string into the same file using utl_file.put to check if it writes and if all permissions are given. It wrote the test string successfully. Only raw data is not being written. Here is the procedure that I have written.
CREATE OR REPLACE PROCEDURE SaveBLOB IS
lobd BLOB;
fils utl_file.file_type;
rbuf raw(32767);
amt INTEGER := 32767;
pos integer:=1;
BEGIN
fils:=utl_file.fopen('DIR1','avseq02.dat','w');
SELECT blob_col INTO lobd FROM cust1 WHERE cust_id=0;
dbms_lob.open(lobd,dbms_lob.lob_readonly);
loop
dbms_lob.read(lobd,amt,pos,rbuf);
utl_file.put_raw(fils,rbuf,true);
pos:=pos+32767;
/*Write part of the lob since the length of the lob is 400m*/
if pos>400000 then
exit;
end if;
end loop;
dbms_lob.close(lobd);
utl_file.fclose(fils);
dbms_output.put_line('file successfully created');
END;
/
I get the same error when writing raw data. Is there something that is being missed out ?
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|