Home » SQL & PL/SQL » SQL & PL/SQL » how can i read blob data from database into my directory (merged by CM) (Oracle 10g)
how can i read blob data from database into my directory (merged by CM) [message #443083] Fri, 12 February 2010 08:40 Go to next message
gajanan_lad
Messages: 1
Registered: February 2010
Location: Pune
Junior Member
I can store my video in to my database.
but i cannot read such file.....using this procedure

ORA-29285: file write error

CREATE OR REPLACE PROCEDURE Extract_bfile
     (p_id  IN NUMBER)
IS
  vblob     BFILE;
  vstart    NUMBER := 1;
  bytelen   NUMBER := 32000;
  len       NUMBER;
  my_vr     RAW(32767);
  x         NUMBER;
  l_output  utl_file.file_type;
BEGIN
  -- define output directory
  l_output := utl_file.Fopen('LOG_DIR','fil1.flv','wb',32760);
  
  vstart := 1;
  
  bytelen := 32000;
  
  -- get length of blob
  SELECT dbms_lob.Getlength(vdata)
  INTO   len
  FROM   flvtemp
  WHERE  ID = p_id;
  
  -- save blob length
  x := len;
  
  -- select blob into variable
  SELECT vdata
  INTO   vblob
  FROM   flvtemp
  WHERE  ID = p_id;
  
  -- if small enough for a single write
  IF len < 32760 THEN
    utl_raw.Cast_to_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;
      
      utl_file.Fclose(l_output);
    END LOOP;
  END IF;
END; 


formatted by BlackSwan

[Updated on: Fri, 12 February 2010 08:55] by Moderator

Report message to a moderator

Re: how can i read blob data from database into my directory [message #443085 is a reply to message #443083] Fri, 12 February 2010 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
At which line do you get the error?

Use SQL*Plus and copy and paste your session.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Fri, 12 February 2010 08:42]

Report message to a moderator

Re: how can i read blob data from database into my directory (merged by CM) [message #443090 is a reply to message #443083] Fri, 12 February 2010 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
29285, 00000, "file write error"
// *Cause:  Failed to write to, flush, or close a file.
// *Action: Verify that the file exists, that it is accessible, and that 
//          it is open in write or append mode.


Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version
Re: how can i read blob data from database into my directory (merged by CM) [message #443091 is a reply to message #443083] Fri, 12 February 2010 09:26 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
In accordance to the SELECT INTO you should declare vblob AS BLOB:
DECLARE
  vblob   BLOB;

BEGIN

-- select blob into variable
SELECT vdata
  INTO   vblob
  FROM   flvtemp
  WHERE  ID = p_id;


Re: how can i read blob data from database into my directory (merged by CM) [message #443653 is a reply to message #443091] Tue, 16 February 2010 13:52 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://fdegrelle.over-blog.com/article-12589417.html
Previous Topic: Spooling data in pipe delimitted file
Next Topic: Query for scenario
Goto Forum:
  


Current Time: Sun Sep 25 04:24:29 CDT 2016

Total time taken to generate the page: 0.19335 seconds