Solved

write blob to file in oracle

Posted on 2013-01-29
5
1,653 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38831176
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
ID: 38831196
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38831274
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 38831452
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
ID: 38831633
thanks
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Require data to appear on a single line 2 80
Create Index on a Materialized View 5 47
Oracle 12c Default Isolation Level 17 56
pl/sql parameter is null sometimes 2 25
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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

733 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