• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1855
  • Last Modified:

write blob to file in oracle

I have written this procedure which writesblob to a file

Have error when I execure this procedure
--------------------------------

CREATE OR REPLACE PROCEDURE Write_BLOB_To_File(p_id in number)
AS
    v_lob_loc      BLOB;
    v_buffer       RAW(32767);
    v_buffer_size  BINARY_INTEGER;
    v_amount       BINARY_INTEGER;
    v_offset       NUMBER(38) := 1;
    v_chunksize    INTEGER;
    v_out_file     UTL_FILE.FILE_TYPE;

BEGIN

    -- +-------------------------------------------------------------+
    -- | SELECT THE LOB LOCATOR                                      |
    -- +-------------------------------------------------------------+
    SELECT  image
    INTO    v_lob_loc
    FROM    test_blob
    WHERE   id = p_id;

    -- +-------------------------------------------------------------+
    -- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN                  |
    -- +-------------------------------------------------------------+
    v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc);

    IF (v_chunksize < 32767) THEN
        v_buffer_size := v_chunksize;
    ELSE
        v_buffer_size := 32767;
    END IF;

    v_amount := v_buffer_size;

    -- +-------------------------------------------------------------+
    -- | OPENING THE LOB IS OPTIONAL                                 |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);

    -- +-------------------------------------------------------------+
    -- | WRITE CONTENTS OF THE LOB TO A FILE                         |
    -- +-------------------------------------------------------------+
    v_out_file := UTL_FILE.FOPEN(
        location      => 'CONTRACTS',
        filename      => '2011 HeartlandEmployeeReferralCard.pdf',
        open_mode     => 'w',
        max_linesize  => 32767);

    WHILE v_amount >= v_buffer_size
    LOOP

      DBMS_LOB.READ(
          lob_loc    => v_lob_loc,
          amount     => v_amount,
          offset     => v_offset,
          buffer     => v_buffer);

      v_offset := v_offset + v_amount;

      UTL_FILE.PUT_RAW (
          file      => v_out_file,
          buffer    => v_buffer,
          autoflush => true);

      UTL_FILE.FFLUSH(file => v_out_file);

    END LOOP;

    UTL_FILE.FFLUSH(file => v_out_file);

    UTL_FILE.FCLOSE(v_out_file);

    -- +-------------------------------------------------------------+
    -- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT          |
    -- +-------------------------------------------------------------+
    DBMS_LOB.CLOSE(v_lob_loc);
End;

begin

Write_BLOB_To_File(1002);

End;

ORA-22293: LOB already opened in the same transaction
ORA-06512: at "SYS.DBMS_LOB", line 660
ORA-06512: at "HBC_DATA.WRITE_BLOB_TO_FILE", line 37
ORA-06512: at line 3

Any help is appreciated.
0
anumoses
Asked:
anumoses
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
I believe it is because you select a BLOB into v_lob_loc then try to open it.

Comment out the dbms_lob.open and dbms_lob.close and see if it runs.
0
 
anumosesAuthor Commented:
Now I get this error after commenting the lines and executing

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at "HBC_DATA.WRITE_BLOB_TO_FILE", line 42
ORA-06512: at line 3
0
 
slightwv (䄆 Netminder) Commented:
Guess you did need to open.

I set up a simple test case using most of your code (I changed the file name and table names) and it runs for me using 10.2.0.3.

I'll work on a test to go over 32K.  I'll post my results.

I believe you are still on 9i.  Sorry but I don't have any 9i left around to test against.

Here is my test case:
drop table tab1 purge;
create table tab1(id number, image blob);

insert into tab1 values(1,utl_raw.cast_to_raw('Hello'));

create or replace directory contracts as 'C:\';



CREATE OR REPLACE PROCEDURE myproc(p_id in number)
AS
    v_lob_loc      BLOB;
    v_buffer       RAW(32767);
    v_buffer_size  BINARY_INTEGER;
    v_amount       BINARY_INTEGER;
    v_offset       NUMBER(38) := 1;
    v_chunksize    INTEGER;
    v_out_file     UTL_FILE.FILE_TYPE;

BEGIN

    -- +-------------------------------------------------------------+
    -- | SELECT THE LOB LOCATOR                                      |
    -- +-------------------------------------------------------------+
    SELECT  image
    INTO    v_lob_loc
    FROM    tab1
    WHERE   id = p_id;

    -- +-------------------------------------------------------------+
    -- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN                  |
    -- +-------------------------------------------------------------+
    v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc);

    IF (v_chunksize < 32767) THEN
        v_buffer_size := v_chunksize;
    ELSE
        v_buffer_size := 32767;
    END IF;

    v_amount := v_buffer_size;

    -- +-------------------------------------------------------------+
    -- | OPENING THE LOB IS OPTIONAL                                 |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);

    -- +-------------------------------------------------------------+
    -- | WRITE CONTENTS OF THE LOB TO A FILE                         |
    -- +-------------------------------------------------------------+
    v_out_file := UTL_FILE.FOPEN(
        location      => 'CONTRACTS', 
        filename      => '2011 HeartlandEmployeeReferralCard.txt', 
        open_mode     => 'w',
        max_linesize  => 32767);

    WHILE v_amount >= v_buffer_size
    LOOP

      DBMS_LOB.READ(
          lob_loc    => v_lob_loc,
          amount     => v_amount,
          offset     => v_offset,
          buffer     => v_buffer);

      v_offset := v_offset + v_amount;

      UTL_FILE.PUT_RAW (
          file      => v_out_file,
          buffer    => v_buffer,
          autoflush => true);

      UTL_FILE.FFLUSH(file => v_out_file);

    END LOOP;

    UTL_FILE.FFLUSH(file => v_out_file);

    UTL_FILE.FCLOSE(v_out_file);

    -- +-------------------------------------------------------------+
    -- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT          |
    -- +-------------------------------------------------------------+
    DBMS_LOB.CLOSE(v_lob_loc);
End;
/

show errors

begin

myproc(1);

End;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
There was a bug somewhere in your proc.  I was able to reproduce your error with your procedure with a larger BLOB.

I didn't take the time to find out where the bug was.

I took code from the link below and tweaked it to match your setup (and fixed a small syntax issue with it):
www.dba-oracle.com/t_writing_blob_clob_os_file.htm

Here is an updated test case for a large BLOB.

drop table tab1 purge;
create table tab1(id number, image blob);

insert into tab1 values(1,null);

--create a large BLOB
declare
	mylob blob;
begin
	dbms_lob.createtemporary(mylob,TRUE);
	dbms_lob.open(mylob,dbms_lob.lob_readwrite);

	for i in 1..30000 loop
		dbms_lob.writeappend(mylob, utl_raw.length(utl_raw.cast_to_raw('Hello')), utl_raw.cast_to_raw('Hello'));
	end loop;

	dbms_lob.close(mylob);

	update tab1 set image = mylob;

	dbms_lob.freetemporary(mylob);
	commit;
end;
/

create or replace directory contracts as 'C:\';

CREATE OR REPLACE PROCEDURE myproc(p_id in number) IS

	vblob BLOB;
	vstart NUMBER := 1;
	bytelen NUMBER := 32000;
	len NUMBER;
	my_vr RAW(32000);
	x NUMBER;

	l_output utl_file.file_type;

BEGIN

-- define output directory
l_output := utl_file.fopen('CONTRACTS', '2011 HeartlandEmployeeReferralCard.txt','wb', 32760);
        

vstart := 1;
bytelen := 32000;

-- get length of blob
SELECT dbms_lob.getlength(image)
INTO len
FROM tab1
WHERE id = p_id;

-- save blob length
x := len;

-- select blob into variable
SELECT image
INTO vblob
FROM tab1
WHERE id = p_id;

-- if small enough for a single write
IF len < 32760 THEN
utl_file.put_raw(l_output,vblob);
utl_file.fflush(l_output);
ELSE -- write in pieces
vstart := 1;
WHILE vstart < len and bytelen > 0
LOOP
   dbms_lob.read(vblob,bytelen,vstart,my_vr);

   utl_file.put_raw(l_output,my_vr);
   utl_file.fflush(l_output);

   -- set the start position for the next cut
   vstart := vstart + bytelen;

   -- set the end position if less than 32000 bytes
   x := x - bytelen;
   IF x < 32000 THEN
      bytelen := x;
   END IF;
end loop;
END IF;
utl_file.fclose(l_output);

end;
/

show errors

begin

myproc(1);

End;
/

Open in new window

0
 
anumosesAuthor Commented:
thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now