asked on

ORA-06512: at "SYS.UTL_FILE", line 536

I am having below set of codes and am planing to extract images from oracle db, kindly assist me on where am doing wrong because am getting this errors
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 21

my directory has permission required.

DECLARE

v_dir varchar2(10):='MY_DIR';
t_blob BLOB;
t_len NUMBER;
t_file_name VARCHAR2(100);
t_output UTL_FILE.file_type;
t_TotalSize number;
t_position number := 1;
t_chucklen NUMBER := 4096;
t_chuck raw(4096);
t_remain number;

BEGIN
FOR i IN 1..100 LOOP

SELECT DBMS_LOB.getlength (IMAGE), brid ||'A.jpg'
INTO t_TotalSize, t_file_name FROM SA.T_CLIENTIDENTIFICATIONS WHERE IMAGEID=i;
t_remain := t_TotalSize;
t_output := UTL_FILE.fopen (v_dir, t_file_name, 'wb', 32760);
SELECT IMAGE INTO t_blob FROM SA.T_CLIENTIDENTIFICATIONS WHERE IMAGEID=i;
WHILE t_position < t_TotalSize
LOOP
DBMS_LOB.READ (t_blob, t_chucklen, t_position, t_chuck);
UTL_FILE.put_raw (t_output, t_chuck);
UTL_FILE.fflush (t_output);
t_position := t_position + t_chucklen;
t_remain := t_remain - t_chucklen;
IF t_remain < 4096
THEN
t_chucklen := t_remain;
END IF;
END LOOP;
END LOOP;
END;

slightwv (䄆 Netminder)

>>my directory has permission required.

If I create MY_DIR pointing to a folder where I know Oracle has access, your code works for me.

Here is my complete tested code (I commented out your code to use my setup):

/*
drop table tab1 purge;
create table tab1(brid number, image blob);
insert into tab1 values(1, HEXTORAW('4041424344'));
commit;

create or replace directory my_dir as 'C:\temp';
*/

DECLARE

v_dir varchar2(10):='MY_DIR';
t_blob BLOB;
t_len NUMBER;
t_file_name VARCHAR2(100);
t_output UTL_FILE.file_type;
t_TotalSize number;
t_position number := 1;
t_chucklen NUMBER := 4096;
t_chuck raw(4096);
t_remain number;

BEGIN
--FOR i IN 1..100 LOOP

SELECT DBMS_LOB.getlength (IMAGE), brid ||'A.jpg'
	INTO t_TotalSize, t_file_name FROM tab1; -- WHERE IMAGEID=i;

dbms_output.put_line('Got: ' || t_file_name);

t_remain := t_TotalSize;
t_output := UTL_FILE.fopen (v_dir, t_file_name, 'WB', 32760);

SELECT IMAGE INTO t_blob FROM tab1; -- WHERE IMAGEID=i;

WHILE t_position < t_TotalSize LOOP
	DBMS_LOB.READ (t_blob, t_chucklen, t_position, t_chuck);
	UTL_FILE.put_raw (t_output, t_chuck);
	UTL_FILE.fflush (t_output);
	t_position := t_position + t_chucklen;
	t_remain := t_remain - t_chucklen;
	IF t_remain < 4096
	THEN
		t_chucklen := t_remain;
	END IF;
END LOOP;
--END LOOP;
UTL_FILE.fclose (t_output);
END; 
/

Open in new window

flow01

mayby the error occurs on the 51e iteration because you opened the 50e file without closing it, thus reaching the maximum open filehandlers of utl_file ?

slightwv (䄆 Netminder)

Excellent catch! Yes, I noticed the missing close and I added it in my code. I didn't even think about too many open files!

Muzurl Francen

ASKER

Thanks for the response above but i have amend the code where am now doing a query that is having more than one records and am getting the following error

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 19

slightwv (䄆 Netminder)

Error should be self explanatory. A select INTO query must return one and only one row.

Muzurl Francen

ASKER

Thanks for the response, i am new to this field and that explain why an having chellenges on this issue. I dint know you cannot fetch more than one record in SELECT INTO query.

I have tried to add a loop through the query but am having a challenge since the images are been populated to the directory but the image file is empty what could be the issue? Kindly assist

[code]
--create or replace directory my_dir as '/export/home/haruser/Desktop/Pic';

DECLARE

v_dir varchar2(10):='MY_DIR';
t_blob BLOB;
t_len NUMBER;
t_file_name VARCHAR2(100);
t_output UTL_FILE.file_type;
t_TotalSize number;
t_position number := 1;
t_chucklen NUMBER := 4096;
t_chuck raw(4096);
t_remain number;

BEGIN
FOR i IN 1..10 LOOP


SELECT DBMS_LOB.getlength (IMAGE), brid ||'P.jpg'
	INTO t_TotalSize, t_file_name FROM Photo WHERE IMAGEID=i;
	
IF (t_file_name IS NOT NULL) THEN


dbms_output.put_line('Got: ' || t_file_name);

t_remain := t_TotalSize;
t_output := UTL_FILE.fopen (v_dir, t_file_name, 'WB', 32760);

SELECT IMAGE INTO t_blob FROM Photo WHERE IMAGEID=i;
	
WHILE t_position < t_TotalSize LOOP
	DBMS_LOB.READ (t_blob, t_chucklen, t_position, t_chuck);
	UTL_FILE.put_raw (t_output, t_chuck);
	UTL_FILE.fflush (t_output);
	t_position := t_position + t_chucklen;
	t_remain := t_remain - t_chucklen;
	IF t_remain < 4096
	THEN
		t_chucklen := t_remain;
	END IF;
END LOOP;
END IF;
END LOOP;
UTL_FILE.fclose (t_output);
END; 
/

Open in new window

[/code]

slightwv (䄆 Netminder)

Move the FCLOSE inside the loop where you are opening it.

You can help keep track of scope if you properly indent individual code blocks.

See if this helps.

[code]
--create or replace directory my_dir as '/export/home/haruser/Desktop/Pic';

DECLARE

v_dir varchar2(10):='MY_DIR';
t_blob BLOB;
t_len NUMBER;
t_file_name VARCHAR2(100);
t_output UTL_FILE.file_type;
t_TotalSize number;
t_position number := 1;
t_chucklen NUMBER := 4096;
t_chuck raw(4096);
t_remain number;

BEGIN
   FOR i IN 1..10 LOOP


      SELECT DBMS_LOB.getlength (IMAGE), brid ||'P.jpg'
	INTO t_TotalSize, t_file_name FROM Photo WHERE IMAGEID=i;
	
      IF (t_file_name IS NOT NULL) THEN


         dbms_output.put_line('Got: ' || t_file_name);

         t_remain := t_TotalSize;
         t_output := UTL_FILE.fopen (v_dir, t_file_name, 'WB', 32760);

         SELECT IMAGE INTO t_blob FROM Photo WHERE IMAGEID=i;
	
         WHILE t_position < t_TotalSize LOOP
            DBMS_LOB.READ (t_blob, t_chucklen, t_position, t_chuck);
            UTL_FILE.put_raw (t_output, t_chuck);
            UTL_FILE.fflush (t_output);
            t_position := t_position + t_chucklen;
            t_remain := t_remain - t_chucklen;
            IF t_remain < 4096 THEN
		t_chucklen := t_remain;
            END IF;
         END LOOP;
      END IF;
      UTL_FILE.fclose (t_output);
   END LOOP;
END; 
/

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

ORA-06512: at &quot;SYS.UTL_FILE&quot;, line 536

ORA-06512: at "SYS.UTL_FILE", line 536