Home » SQL & PL/SQL » SQL & PL/SQL » Utl_File to export blob problem
Utl_File to export blob problem [message #238389] Thu, 17 May 2007 15:01 Go to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Hi, i am using oracle10g and i executed this procedure to extract the pdf's from the table to the hard disk.
The problem is this: There are more than 3k of pdf's inside the table and oracle only extract maybe 10 files and the rest of the files have only 0k.
i dont know where is the error, can someone help me?
the script is this




DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
nombre VARCHAR2(350);
l_blob_len INTEGER;
cursor pruebacursor is select content, name from edu.docspdf where name like '%.PDF';

BEGIN
open pruebacursor;
loop
FETCH pruebacursor into l_blob, nombre;
-- Get LOB locator
l_blob_len := DBMS_LOB.getlength(l_blob);

-- Open the destination file.
l_file := UTL_FILE.fopen('BLOBS',nombre,'w', 32767);

-- Read chunks of the BLOB and write them to the file
-- until complete.
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;

-- Close the file.
UTL_FILE.fclose(l_file);
exit when pruebacursor%notfound;
end loop;
close pruebacursor;

EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/
Re: Utl_File to export blob problem [message #238427 is a reply to message #238389] Fri, 18 May 2007 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and apply How to format your posts.

Are you sure your other files are named '*.PDF' and not '*.pdf'?

Btw, NAME is a reserved word don't use it as a column name.

Regards
Michel
Re: Utl_File to export blob problem [message #238487 is a reply to message #238389] Fri, 18 May 2007 05:47 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Sorry michael there is the code with format.
btw name is the name of the column that someone build (didn't mine)
and yes, all files are PDF and not pdf.

My question is WHY oracle write in the disk maybe 5 or 6 files with all the information (i opened the files) and the other files have only 0k (but oracle do a "touch file.pdf"

DECLARE
   l_file UTL_FILE.FILE_TYPE;
   l_buffer RAW(32767);
   l_amount BINARY_INTEGER := 32767;
   l_pos INTEGER := 1;
   l_blob BLOB;
   nombre VARCHAR2(350);
   l_blob_len INTEGER;
   cursor pruebacursor 
     is 
   select content, name 
     from edu.docspdf 
     where name like '%.PDF';

BEGIN
open pruebacursor;
  loop
    FETCH pruebacursor into l_blob, nombre;
    l_blob_len := DBMS_LOB.getlength(l_blob);
    l_file := UTL_FILE.fopen('BLOBS',nombre,'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);
    exit when pruebacursor%notfound;
  end loop;
close pruebacursor;

EXCEPTION
  WHEN OTHERS THEN
   IF UTL_FILE.is_open(l_file) THEN
     UTL_FILE.fclose(l_file);
   END IF;
RAISE;
END;
/

[Updated on: Fri, 18 May 2007 05:50]

Report message to a moderator

Re: Utl_File to export blob problem [message #238494 is a reply to message #238487] Fri, 18 May 2007 06:01 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I can say is put debugging lines (for instance dbms_output.put_line) along the code and execute it to see if it does what you think it should.

Btw, the remark on name was not against you, it is just a common advice.

Regards
Michel
Previous Topic: DML stmt inside Functions
Next Topic: Foreignkey
Goto Forum:
  


Current Time: Sun Dec 04 20:32:46 CST 2016

Total time taken to generate the page: 0.03936 seconds