Home » SQL & PL/SQL » SQL & PL/SQL » Help in Creating a Text File using UTL_FILE
Help in Creating a Text File using UTL_FILE [message #409083] Fri, 19 June 2009 05:10 Go to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Hi,

I have a table ATTACHMENT where in it has a BLOB datatype column ATTACHMENTFILE...my requirement is to generate a TEXT file with the data present in the table.

But when I tried to execute the below procedure..I get the following error:

ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at line 4


I have created a oracle directory MYDIR as C:\tmp and given READ WRITE permissions TO ALL.

[B]CREATE OR REPLACE PROCEDURE blob2file
IS
 vblob   BLOB;
 vstart  NUMBER := 1;
 bytelen NUMBER := 32000;
 pfname  VARCHAR2(100) := 'Attachment.txt';
 len     NUMBER;
 my_vr   RAW(32000);
 x       NUMBER;
 l_output utl_file.file_type;
BEGIN
  -- define output directory
  l_output := utl_file.fopen('MYDIR', pfname, 'WB', 32760);

  -- get length of blob
  SELECT dbms_lob.getlength(attachmentfile)
  INTO len
  FROM attachment;  

  -- save blob length
  x := len;

  -- select blob into variable
  SELECT attachmentfile
  INTO vblob
  FROM attachment;

  -- 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
    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 blob2file;
/[/B]


Please help me overcomeing this error.

Thanks,
Hemanth
Re: Help in Creating a Text File using UTL_FILE [message #409091 is a reply to message #409083] Fri, 19 June 2009 05:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Does the directory C:/tmp exist on your server (ie not on your local pc)
Does the user under which Oracle was installed have the privileges to write to that directory
Re: Help in Creating a Text File using UTL_FILE [message #409092 is a reply to message #409083] Fri, 19 June 2009 05:57 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
I have the ADMIN permissions...
Actually I don't have much idea but where do you generally check for the directory on the Oracle Server..

Also the error is not sepcifying nothing abt directory but abt UTL_FILE file operation..please clarify me if I am wrong.

Please let me know is it possible to generate a file onto our local PC using the same procedure.

Thanks,
Ashok
Re: Help in Creating a Text File using UTL_FILE [message #409095 is a reply to message #409092] Fri, 19 June 2009 06:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You will get an error from UTL_FILE if you try to open a file in a directory that doesn't exist.

Do you know for certain that the directory c:/tmp exists on your server.
If not, then that is almost certainly the problem.
Re: Help in Creating a Text File using UTL_FILE [message #409096 is a reply to message #409092] Fri, 19 June 2009 06:26 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hemanth123 wrote on Fri, 19 June 2009 11:57

Please let me know is it possible to generate a file onto our local PC using the same procedure.


No, not unless the local PC hosts the Oracle database.
Re: Help in Creating a Text File using UTL_FILE [message #409135 is a reply to message #409096] Fri, 19 June 2009 10:42 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
It should also be possible if you can mount your PC drive as a network device that the Oracle database can see like any other newtwork drive. I do not recall ever actually doing this however. Most companies frown upon the idea.

Good luck, Kevin
Previous Topic: Help with parsing a full name field
Next Topic: Call HOST Command in Pl/Sql (merged)
Goto Forum:
  


Current Time: Sat Dec 03 06:06:31 CST 2016

Total time taken to generate the page: 0.09501 seconds