Home » SQL & PL/SQL » SQL & PL/SQL » unload BLOB to os file (plsql)
unload BLOB to os file (plsql) [message #323275] Wed, 28 May 2008 01:03 Go to next message
ora_baby
Messages: 89
Registered: May 2008
Member
how i can unload Blob to os file? use plsql Smile
Re: unload BLOB to os file (plsql) [message #323283 is a reply to message #323275] Wed, 28 May 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use utl_file.put_raw by chunk of 32K, for instance.
Or write your own program in your favorite language (I'm pretty you can find one on Internet).

Regards
Michel
Re: unload BLOB to os file (plsql) [message #323288 is a reply to message #323283] Wed, 28 May 2008 01:31 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
i finded following code

CREATE OR REPLACE PROCEDURE save_blob_to_file IS
 

 i1    BLOB;
 len   NUMBER;
 my_vr RAW(10000);
 i2    NUMBER;
 i3    NUMBER := 10000;
BEGIN
  -- get the blob locator
  SELECT iblob
  INTO i1
  FROM pdm
  WHERE dname = 'name1';

  -- find the length of the blob column
  len := dbms_lob.getlength(i1);
  dbms_output.put_line('Column Length: ' || TO_CHAR(len));

  -- Read 10000 bytes at a time
  i2 := 1;
  IF len < 10000 THEN
    -- If the col length is < 10000
    dbms_lob.read(i1,len,i2,my_vr);

   outputstring ('c:\See\getImage2.jpeg',rawtohex(my_vr),'wb',2*len);

    -- You have to convert the data to rawtohex format.
    -- Directly sending the buffer
    -- data will not work
    -- That is the reason why we are sending the length as
    -- the double the size of the data read

    dbms_output.put_line('Read ' || to_char(len) || 'Bytes');
  ELSE
    -- If the col length is > 10000
    dbms_lob.read(i1,i3,i2,my_vr);

   outputstring('c:\see\getImage2.jpeg',rawtohex(my_vr),'wb',2*i3);

    dbms_output.put_line('Read ' || TO_CHAR(i3) || ' Bytes ');
  END IF;

  i2 := i2 + 10000;

  WHILE (i2 < len )
  LOOP
    -- loop till entire data is fetched
    dbms_lob.read(i1,i3,i2,my_vr);

    dbms_output.put_line('Read ' || TO_CHAR(i3+i2-1) ||
    ' Bytes ');

    outputstring('c:\see\getImage2.jpeg',rawtohex(my_vr),'ab',2*i3);

    i2 := i2 + 10000 ;
  END LOOP;
END;


but...
what is outputstring()?


Re: unload BLOB to os file (plsql) [message #323295 is a reply to message #323288] Wed, 28 May 2008 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search it where you found the procedure.

Regards
Michel
Re: unload BLOB to os file (plsql) [message #323308 is a reply to message #323295] Wed, 28 May 2008 02:49 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
i do not say Confused
this code is retrived from

http://www.dbforums.com/showthread.php?t=669001

>The outputstring procedure is the oracle procedure interface to the External procedure.

In my system outputstring procedure is error. What is problem?
may be, to add this procedure? but, what is code?

[Updated on: Wed, 28 May 2008 02:50]

Report message to a moderator

Re: unload BLOB to os file (plsql) [message #323309 is a reply to message #323308] Wed, 28 May 2008 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a custom procedure not a standard Oracle one.
But if it an external procedure, it is surely a C (or other language) program that just write into a file.
So it is in the category I named "Or write your own program in your favorite language".

Regards
Michel
Re: unload BLOB to os file (plsql) [message #323316 is a reply to message #323309] Wed, 28 May 2008 03:10 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
may be you say me about external proc
, because i do not what is it

how work with ex. proc.?
Re: unload BLOB to os file (plsql) [message #323340 is a reply to message #323316] Wed, 28 May 2008 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/search?remark=quick_search&word=external+procedure&tab_id=&format=ranked

Regards
Michel
Re: unload BLOB to os file (plsql) [message #323588 is a reply to message #323340] Wed, 28 May 2008 23:13 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
ok, the article is very interesting Smile
but, i didn't find about outputstring() Confused
may be another way is existed to unload BLOB to OS file ??
Re: unload BLOB to os file (plsql) [message #323590 is a reply to message #323588] Wed, 28 May 2008 23:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but, i didn't find about outputstring()

Of course, it is NOT a standard procedure.
Ask the one that posted the code.

Quote:
may be another way is existed to unload BLOB to OS file ??

Yes, search on the web if you don't want to or can't write it.

Regards
Michel
Re: unload BLOB to os file (plsql) [message #323597 is a reply to message #323590] Wed, 28 May 2008 23:54 Go to previous message
ora_baby
Messages: 89
Registered: May 2008
Member
gg Smile
ok
thank you for saying and helping
i'm going to find about BLOB later, when i'm have a litre of coffe Cool Cool Cool
Previous Topic: Can You tell me any option avilable To Search a File?
Next Topic: validation check for numeric and alphabets
Goto Forum:
  


Current Time: Sat Dec 10 04:59:03 CST 2016

Total time taken to generate the page: 0.09940 seconds