This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Feb 1, 2013 12:46 AM by Marwim RSS

BLOB Datatype

988253 Newbie
Currently Being Moderated
Hi Everyone,

I want to ask one question.

How to read or access the BLOB file directly from database through PL/SQL without downloading it.

Please answer my question.
Thanks,

Sachin
  • 1. Re: BLOB Datatype
    jeneesh Guru
    Currently Being Moderated
    Did you try Googling?

    And what do you mean by NOT downloading?
  • 2. Re: BLOB Datatype
    988253 Newbie
    Currently Being Moderated
    Yes I did that but I have not found that on google


    Whenever I am Reading BLOB File It is downloaded in my operating system but that I dont want
    So how we can read the blob file without downloading it on OS
  • 3. Re: BLOB Datatype
    Karthick_Arp Guru
    Currently Being Moderated
    Whenever I am Reading BLOB File It is downloaded in my operating system
    How are you reading?
  • 4. Re: BLOB Datatype
    988253 Newbie
    Currently Being Moderated
    Hi,

    Whenever I am opening my BLOB File. It is downloaded in my computer C drive and then it has open in particular application for ex .txt file open in Notepad
    So how i can open BLOB File WITHOUT DOWNLOADING IT ON OS
  • 5. Re: BLOB Datatype
    Marwim Expert
    Currently Being Moderated
    Any file you open will be downloaded to a temporary file. How else would a programm have access to it?

    Regards
    Marcus

    Edited by: Marwim on 31.01.2013 11:10

    Exceptions might be music/video streams where you don't download the complete file but only the currently used pieces. Though even then you have to download it to your computer.
  • 6. Re: BLOB Datatype
    988253 Newbie
    Currently Being Moderated
    Hi Marcus,

    Thanks for your reply

    My Image Is downloaded in C drive i.e C:\

    So How i can download that file (Image) in temporary File.

    Thanks
    Sachin
  • 7. Re: BLOB Datatype
    Marwim Expert
    Currently Being Moderated
    How do you download the file?
  • 8. Re: BLOB Datatype
    ascheffer Expert
    Currently Being Moderated
    Whenever you open and read a bfile or blob with plsql it's not downloaded to your C drive or any other OS file
  • 9. Re: BLOB Datatype
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    985250 wrote:

    How to read or access the BLOB file directly from database through PL/SQL without downloading it.
    You do not seem to grasp a basic issue here.

    The database row consists of data. Data is bytes. For text. For dates. For numbers. For BLOBs. For any other data type. That data is stored as bytes.

    The client requests a row's data. The data of that rows is transmitted (using TCP packets usually) from server to client. The client receives these bytes - in a memory area it has reserved for receiving the data from the server.

    Whether that data is a BLOB or a numbers and strings, is not relevant. The bytes do not care.

    There are however other issues that determine how the client goes about requesting a row's data and how the client choose to receive it.

    If a row is a small 1KB of bytes per row - the client can reserve a 100KB area in memory and request a 100 rows at a time from the server.

    If a row has a 1000 columns and is over 1MB of bytes per row. Not a good idea to request a 100 rows at time. As that means a 100MB memory area is needed for that data on the client.

    Similar considerations are relevant for BLOBs. Let's say the BLOB in the row is a PNG image of 500KB. The client can create an image object in memory. This object will have a property (memory area) that contains the "raw" bytes of the image. The client can now request the BLOB from the server and write the 500KB it receives for that BLOB directly into the data property of the that image object.

    That image object can be on a client window - which means that the client window now automatically displays the image. Read directly from the database.

    A BLOB can be an Open Office spreadsheet (do Open Source). This spreadsheet may contain 10 worksheets of 10MB each.

    What is the best way for the client to deal with this and display the results (the spreadsheet) to the end-user? Well, likely that would be the client calling the database, requesting 1MB of data from the BLOB at a time, receiving that chunk of the BLOB into a 1MB memory area, and then writing that memory area to a temporary file. This way, only 1MB of client memory is spend on reading a 100MB data object from the server. When the last chunk is written to file, the client closes it, and informs the operating system to open the file for the end-user to view. The o/s launches Open Office and the user can now view and use the 100MB spreadsheet.

    The client receives data. How it deals with that and what it does with that, is a client s/w decision. Not a database decision. Nothing forces a client to "file download" BLOB data from the database.
  • 10. Re: BLOB Datatype
    988253 Newbie
    Currently Being Moderated
    hi,

    I want to run my program from oracle database itself without saving it on drive.

    Please Reply

    Thanks
  • 11. Re: BLOB Datatype
    ascheffer Expert
    Currently Being Moderated
    I have some questions for you:
    where is this BLOB file located, is it a blob in your database, is it a BFILE on your database, is it a file on the file system of your database, or ....
    what do you mean with reading and acessing a BLOB file (with PLSQL), just read every byte of the BLOB, or .......
  • 12. Re: BLOB Datatype
    988253 Newbie
    Currently Being Moderated
    Hi,

    It is located in my database.
  • 13. Re: BLOB Datatype
    988253 Newbie
    Currently Being Moderated
    Hi,

    It is a BLOB in my database.
  • 14. Re: BLOB Datatype
    Mahmoud_Rabie Journeyer
    Currently Being Moderated
    You can workaround this code to achieve what you want
    CREATE OR REPLACE PROCEDURE extract_file(product_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('DIR_TEMP', 'filename','wb', 32760);
    
    vstart := 1;
    bytelen := 32000;
    
    -- get length of blob
    SELECT dbms_lob.getlength(productblob)
    INTO len
    FROM products
    WHERE id = product_id;
    
    -- save blob length
    x := len;
    
    -- select blob into variable
    SELECT product_blob
    INTO vblob
    FROM products
    WHERE id = product_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 IF;
    utl_file.fclose(l_output);
    end loop;
    Reference
    http://www.dba-oracle.com/t_read_blob.htm

    If this solves your question please mark it as Correct. Otherwise as helpful

    Regards
    Mahmoud
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points