[Tip] Finally how to take a blob file and play on the disc

[meguelito]

Good afternoon guys after much demand and very wipor I managed to take a file from a blob field and play for the disc in Oracle 9i, which has a limitation of 32k, but with the routine that I command below you can have the file that is the size that for.

Select all

CREATE OR REPLACE PROCEDURE RETRIEVE_LOB IS 
 
  TEMP_BLOB     BLOB; 
  DATA_BUFFER   RAW(32767); 
  TEMP_BUFFER   VARCHAR2(1); 
  AMOUNT        BINARY_INTEGER := 1; 
  POSITION      INTEGER := 1; 
  FILEHANDLE    UTL_FILE.FILE_TYPE; 
  ERROR_NUMBER  NUMBER; 
  ERROR_MESSAGE VARCHAR2(100); 
  LENGTH_COUNT  INTEGER; 
 
BEGIN 
 
  SELECT TEXTO INTO TEMP_BLOB FROM TESTE WHERE ROWNUM = 1; 
  LENGTH_COUNT := DBMS_LOB.GETLENGTH(TEMP_BLOB); 
  DBMS_OUTPUT.PUT_LINE('INTERNAL LOB SIZE IS: ' || LENGTH_COUNT); 
  FILEHANDLE := UTL_FILE.FOPEN('DIRLOB', 'ARQUIVO.PDF', 'W', 32760); 
  WHILE LENGTH_COUNT <> 0 LOOP 
   
    DBMS_LOB.READ(TEMP_BLOB, AMOUNT, POSITION, DATA_BUFFER); 
    UTL_FILE.PUT_RAW(FILEHANDLE, DATA_BUFFER, TRUE); 
    POSITION     := POSITION + 1; 
    LENGTH_COUNT := LENGTH_COUNT - 1; 
    DATA_BUFFER  := NULL; 
   
  END LOOP; 
  DBMS_OUTPUT.PUT_LINE('EXIT THE LOOP'); 
  UTL_FILE.FCLOSE(FILEHANDLE); 
  DBMS_OUTPUT.PUT_LINE('CLOSE THE FILE'); 
 
EXCEPTION 
  WHEN OTHERS THEN 
    BEGIN 
      ERROR_NUMBER  := SQLCODE; 
      ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 100); 
      DBMS_OUTPUT.PUT_LINE('ERROR #: ' || ERROR_NUMBER); 
      DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE: ' || ERROR_MESSAGE); 
      UTL_FILE.FCLOSE_ALL; 
    END; 
 
END;

[alef]

Meguelito

I tested his procedure unfortunately with JPG it does not work completely distorts the colors of the image. Do you know why? Would it be error of the bank version?
I use 9.2.0.1
At 10g XE worked 100% but at 9 does not even go to the Tussa cow.

Thanks,

And I hope you can help me

[meguelito]

And the guy beleza? Because I have no 9.2.0.1 but I tested with 9.2.0.7 and worked 100% with a figure of 500KB, and opened right, with no problem.

This can be a bug of 9.2.0.1.

I used the following command to add the figure:

Select all

declare 
 
   l_blob    blob; 
   l_bfile   bfile; 
 
begin 
   
  insert into LOB_ALAN values ( empty_blob() )  returning CD_LOB into l_blob; 
  l_bfile := bfilename( 'DIR_ARQ', 'Slack.jpg' ); 
  dbms_lob.fileopen( l_bfile ); 
  dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) ); 
  dbms_lob.fileclose( l_bfile ); 
   
end;

and then I used the procedure above to remove and place in the disco.

Att.:
Alan Juliano Metzger
DBA / Oracle Support
MSN: Alanjuliano@yahoo.com.br

[alef]

You just clarified my doubts, it's just a bug of the version, as I had suspected.

Thank you ^^

[alef]

I could swear it was bug of the version, but the error still continues I'll put the pictures aqui before and then to see what happens

before [url=http://www.postimage.org/image.php?v=aV2IgWJA]

After [url=http://www.postimage.org/image.php?v=aV2IiVrJ]


if I use the PLSQL Developer to extract the image from Inside the bench, it gets perfect, but using the codes you passed me, and get out the way I put it up.
In addition to taking too long to run the procedure and increase the file size.

If you can help me. I'm grateful.