Solved

write blob to file in oracle

Posted on 2013-01-29
5
1,525 Views
Last Modified: 2013-01-29
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
Comment
Question by:anumoses
  • 3
  • 2
5 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
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
 
LVL 6

Author Closing Comment

by:anumoses
Comment Utility
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now