Home » SQL & PL/SQL » SQL & PL/SQL » How to read the blob column and write into file? (merged)
How to read the blob column and write into file? (merged) [message #362590] Wed, 03 December 2008 02:57 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
Hi,

i wanted to store the zip file in a blob column and i also want to download the file

i have tried in the following manner


-- Creation of the table

create table demo
( ID int,
  theblob blob);

--insert the zip file into the table

declare
    l_blob    blob;
    l_bfile    bfile;
begin
    insert into demo values ( 5, empty_blob() ) 
    returning theBlob into l_blob;

    l_bfile := bfilename( 'MWDIR_TST', 'demo.zip' );
    dbms_lob.fileopen( l_bfile );

    dbms_lob.loadfromfile( l_blob, l_bfile,
                               dbms_lob.getlength( l_bfile ) );

    dbms_lob.fileclose( l_bfile );
end;
/

-- Function to convert the blob into clob

CREATE OR REPLACE FUNCTION XBLOB_To_CLOB(L_BLOB BLOB) RETURN CLOB IS
L_CLOB			CLOB;
L_SRC_OFFSET	NUMBER;
L_DEST_OFFSET	NUMBER;
L_BLOB_CSID		NUMBER := DBMS_LOB.DEFAULT_CSID;
V_LANG_CONTEXT	NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
L_WARNING		NUMBER;
L_AMOUNT		NUMBER;
BEGIN
IF DBMS_LOB.GETLENGTH(L_BLOB) > 0 THEN
DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);
L_SRC_OFFSET := 1;
L_DEST_OFFSET := 1;
L_AMOUNT := DBMS_LOB.GETLENGTH(L_BLOB);
DBMS_LOB.CONVERTTOCLOB(L_CLOB,
L_BLOB,
L_AMOUNT,
L_SRC_OFFSET,
L_DEST_OFFSET,
1,
V_LANG_CONTEXT,
L_WARNING);
RETURN L_CLOB;
ELSE
L_CLOB:= TO_CLOB('');
RETURN L_CLOB;
End IF;
DBMS_LOB.FREETEMPORARY(L_CLOB);
END XBLOB_To_CLOB;
/

-- Procedure to wtire clob into file 
CREATE OR REPLACE PROCEDURE Write_CLOB_To_File ( directory_name varchar2,filename varchar2, clob_loc CLOB )
IS
buffer VARCHAR2(32767);
buffer_size CONSTANT BINARY_INTEGER := 32767;
amount BINARY_INTEGER;
offset NUMBER(38);
file_handle UTL_FILE.FILE_TYPE;
BEGIN
file_handle := UTL_FILE.FOPEN(location => directory_name,filename => filename,open_mode => 'w',max_linesize => buffer_size);
amount := buffer_size;
offset := 1;
-- ----------------------------------------------
-- READ FROM CLOB / WRITE OUT TO DISK
-- ----------------------------------------------
WHILE amount >= buffer_size
LOOP
DBMS_LOB.READ(lob_loc => clob_loc,amount => amount,offset => offset,buffer => buffer);
buffer:=replace(buffer,chr(13),'');
offset := offset + amount;
UTL_FILE.PUT(file => file_handle,buffer => buffer);
UTL_FILE.FFLUSH(file => file_handle);
END LOOP;
UTL_FILE.FCLOSE(file => file_handle);
END Write_CLOB_To_File;
/


-- To execute use the following example
declare TmpClob CLOB;
begin
select XBLOB_TO_CLOB(theblob) into TmpClob from demo where id=5;
Write_Clob_To_File('TEMP','demo.txt',TmpClob);
end;
/


when i am excuting the above code it is not bringing the exact binary values from the database ?

can any one help me on this?



Thanks
Rangan S
Re: How to read the blob column and write into file? [message #362593 is a reply to message #362590] Wed, 03 December 2008 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you (try to) convert binary data to text data if you just want to retrieve the data in a file?

Formatting not just mean use code tags, it also means indent the code.

Regards
Michel
Re: How to read the blob column and write into file? [message #362594 is a reply to message #362590] Wed, 03 December 2008 03:03 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DON'T multipost.

Regards
Michel
Previous Topic: LONG DATA TYPE
Next Topic: how to generate number without exceed 6 digigts
Goto Forum:
  


Current Time: Mon Dec 05 21:05:35 CST 2016

Total time taken to generate the page: 0.07111 seconds