Home / Questions / 323311
Accepted
nachfren
Asked: 2020-01-22 01:11:33 +0800 CST 2020-01-22 01:11:33 +0800 CST

Download pdf (BLOB) with select oracle

  • 772

I have the following table:

CREATE TABLE PRODUCTO
( 
"clave" NUMBER NOT NULL ENABLE,
"precio" NUMBER(10,2),
"descripcion_prodcto" BLOB
);

The field descripcion_prodctocontains a pdf with a description of the product, I need from SQL Developer (or any sql editor) to download those pdf in a path, with the file name containing clave+ precio.pdf

There are about 500 records.

I've tried with SQL Developer, but it only lets me download one at a time. Does anyone know a way to be able to download several at once?

edit:

With the following procedure, remove the pdf file, but when opening it, it tells me that it is an erroneous file:

DECLARE
l_file      UTL_FILE.FILE_TYPE;
l_buffer    RAW(32767);
l_amount    BINARY_INTEGER := 32767;
l_pos       NUMBER := 1;
l_blob      BLOB;
l_blob_len  NUMBER;
BEGIN

SELECT descripcion_prodcto
    INTO   l_blob
    FROM   PRODUCTO
WHERE  clave='001';

l_blob_len := DBMS_LOB.getlength(l_blob);

-- Open the destination file.
l_file := UTL_FILE.fopen('RUTA','PRUEBA.pdf','wb',32767);


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;

-- Close the file.
UTL_FILE.fclose(l_file);

END;

Opening the pdf with Sql Developer and Adobe Acrobat the pdf is seen correctly.

Could it be because the pdf contains accent marks and is badly encoded?

pdf

2 Answers

  1. Best Answer

    The problem is that you are only copying the first 32767 bytes of the file. You must use a loop to do the full copy:

    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;
    • 2

  2. check this article

    http://www.dba-oracle.com/t_read_pdf_file_with_oracle.htm

    remember that the PDF must be readable (Different from an image)

    • 0