1 2 Previous Next 29 Replies Latest reply on Feb 1, 2013 8:46 AM by Marwim

    BLOB Datatype

      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.

        • 1. Re: BLOB Datatype
          Did you try Googling?

          And what do you mean by NOT downloading?
          • 2. Re: BLOB Datatype
            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
              Whenever I am Reading BLOB File It is downloaded in my operating system
              How are you reading?
              • 4. Re: BLOB Datatype

                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
                  Any file you open will be downloaded to a temporary file. How else would a programm have access to it?


                  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
                    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.

                    • 7. Re: BLOB Datatype
                      How do you download the file?
                      • 8. Re: BLOB Datatype
                        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
                          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

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

                            Please Reply

                            • 11. Re: BLOB Datatype
                              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

                                It is located in my database.
                                • 13. Re: BLOB Datatype

                                  It is a BLOB in my database.
                                  • 14. Re: BLOB Datatype
                                    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;
                                    -- 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
                                    ELSE -- write in pieces
                                    vstart := 1;
                                    WHILE vstart < len and bytelen > 0
                                       -- 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;
                                    end loop;

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

                                    1 2 Previous Next