Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to Convert the content in BLOB field into a PDF file...

jampala - oracleDec 5 2010 — edited Dec 6 2010
Hi,
I am having PDF files stored in BLOB column of a table in Oracle Database (11G R2).
I want to retrieve the files back and store them in hard disk. I am successful in storing the content as a file with '.doc' but if I store the file as '.pdf', adobe fails to open the file with error
Adobe Reader could not open file 'xxx.pdf' because it is either not a supported file type or because the file has been damaged (for example it was sent as an email attachment and wasn't correctly decoded)

I am using following example code to achieve my goal...

Declare
b blob;
c clob;
buffer VARCHAR2(32767);
buffer_size CONSTANT BINARY_INTEGER := 32767;
amount BINARY_INTEGER;
offset NUMBER(38);
file_handle UTL_FILE.FILE_TYPE;
begin
select blob_data into b from blobdata where id=1;
c := blob2clob(b);
file_handle := UTL_FILE.FOPEN('BLOB2FILE','my_file.pdf','w',buffer_size);
amount := buffer_size;
offset := 1;
WHILE amount >= buffer_size
LOOP
DBMS_LOB.READ(c,amount,offset,buffer);
-- buffer:=replace(buffer,chr(13),'');
offset := offset + amount;
UTL_FILE.PUT(file_handle,buffer);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
end;

create or replace FUNCTION BLOB2CLOB ( p_blob IN BLOB ) RETURN CLOB
-- typecasts BLOB to CLOB (binary conversion)
IS
/*****************************************************************************************
|| Purpose : To Convert a BLOB File to CLOB File
|| INPUT : BLOB File
|| OUTPUT : CLOB File
|| History: MB V5.0 24.09.2007 RCMS00318572 Initial version
*****************************************************************************************/
ln_file_check NUMBER;
ln_file_size NUMBER;
v_text_file CLOB;
v_binary_file BLOB;
v_dest_offset INTEGER := 1;
v_src_offset INTEGER := 1;
v_warning INTEGER;
lv_data CLOB;
ln_length NUMBER;
csid VARCHAR2(100) := DBMS_LOB.DEFAULT_CSID;
V_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
BEGIN
DBMS_LOB.createtemporary (v_text_file, TRUE);
SELECT dbms_lob.getlength(p_blob) INTO ln_file_size FROM DUAL;
DBMS_LOB.converttoclob (v_text_file, p_blob, ln_file_size, v_dest_offset, v_src_offset, 0, v_lang_context, v_warning);
SELECT dbms_lob.getlength(v_text_file) INTO ln_length FROM DUAL;
RETURN v_text_file;
END;

Comments

624104
You have got to be kidding me, he's kidding right? Come on man... sample data or something I mean jeez all day with this


Ok maybe a bit harsh... but we really need to see some sample data and desired output to help you faster to get your file to your boss...

TimS

Edited by: TimS on Apr 15, 2009 3:36 PM
APC
Bear in mind that the COLUMN commands only apply to columns in the SELECT statement which match, Because you're applying format masks to the columns they have new column names and so don't match. This means you'll need to add aliases to everything in your query...
select
    RPAD(NVL(RZFIDOC_SCHOOL_YEAR,' '),1) as school_year,
    ....
Alternatively, extend those COLUMN columns to include JUSTIFY LEFT, JUSTIFY CENTER or JUSTIFY RIGHT instead of padding.

Cheers, APC

blog: http://radiofreetooting.blogspot.com
6363
Please help my boss is waiting for the file
Seems to be a lot of urgent help needed for output from tables based on student, assignment, exam scores and lecturer data right now.

Are you sure you mean boss and not teacher?
651180
You don't have to make fun of me sr, maybe you are a genious
I am trying to create a file like this one, but I need to put the file in the right positions
like 0 start in position one and end in pos1...
if you see the preivious email you will know what I mean...
Please don't answer, but just don't make fun..ok

0Kinder John 2231 Delamere Drive Cleveland OH44106 112419863028497742 3 3526 jkinder@connelite.edu 00256696 \
APC
I don't know why you replied to my post as I'm the only person who has offered you helpful advice.

Regards, APC

blog: http://radiofreetooting.blogspot.com
624104
Heh poor APC caught in the crossfire... maybe it was to me, sorry if you took offense. I see a LOT of people come in here just throwing queries out and expecting us to know what the heck they want from it.

Either way read APC's example that is the most obvious problem, try that and show us your output and we'll go from there.

TimS
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 3 2011
Added on Dec 5 2010
5 comments
22,722 views