9 Replies Latest reply: May 7, 2012 2:25 AM by 716619 RSS

    BFILE: need advice for best practice

    716619
      Hi,

      I'm planning to implement a document management system. These are my requirements:
      (0) Oracle 11gR2 on Windows 2008 server box
      (1) Document can be of type Word, Excel, PDF or plain text file
      (2) Document will get stored in DB as BFILE in a table
      (3) Documents will get stored in a directory structure: action/year/month, i.e. there will be many DB directory objects
      (4) User has read only access to files on DB server that result from BFILE
      (5) User must check out/check in document for updating content

      So my first problem is how to "upload" a user's file into the DB. My idea is:
      - there is a "transfer" directory where the user has read/write access
      - the client program copies the user's file into the transfer directory
      - the client program calls a PL/SQL-procedure to create a new entry in the BFILE table
      - this procedure will run with augmented rights
      - procedure may need to create a new DB directory (depending on action, year and/or month)
      - procedure must copy the file from transfer directory into correct directory (UTL_FILE?)
      - procedure must create new row in BFILE table

      Is this a practicable way? Is there anything that I could do better?

      Thanks in adavance for any hints,
      Stefan

      Edited by: Stefan Misch on 06.05.2012 18:42
        • 1. Re: BFILE: need advice for best practice
          sb92075
          Stefan Misch wrote:
          Hi,

          I'm planning to implement a document management system. These are my requirements:
          (0) Oracle 11gR2 on Windows 2008 server box
          (1) Document can be of type Word, Excel, PDF or plain text file
          (2) Document will get stored in DB as BFILE in a table
          (3) Documents will get stored in a directory structure: action/year/month, i.e. there will be many DB directory objects
          (4) User has read only access to files on DB server that result from BFILE
          (5) User must check out/check in document for updating content

          So my first problem is how to "upload" a user's file into the DB. My idea is:
          - there is a "transfer" directory where the user has read/write access
          - the client program copies the user's file into the transfer directory
          - the client program calls a PL/SQL-procedure to create a new entry in the BFILE table
          what exactly is this mythical "client program" & where does it reside and run?
          • 2. Re: BFILE: need advice for best practice
            716619
            There is nothing mythical about this. This document store will be part of a C/S system that is used to store customer data, orders etc. So it's a windows exe with forms and dialogs as front end UI to the backend database. One of those old fashioned C/S systems.

            BTW, this is the first time I'm working with BFILE data type...
            • 3. Re: BFILE: need advice for best practice
              Tubby
              Any reason you have your heart set on a BFILE?

              Please give this a read
              http://asktom.oracle.com/pls/asktom/f?p=100:11:779974601499601::::P11_QUESTION_ID:5671517437591

              Perhaps a BLOB would better suit your needs.
              • 4. Re: BFILE: need advice for best practice
                sb92075
                Stefan Misch wrote:
                There is nothing mythical about this. This document store will be part of a C/S system that is used to store customer data, orders etc. So it's a windows exe with forms and dialogs as front end UI to the backend database. One of those old fashioned C/S systems.

                BTW, this is the first time I'm working with BFILE data type...
                As far as I know, Oracle no longer supports any C/S software.

                What is name & version for client software.

                post results of following SQL run on DB Server

                SELECT * FROM V$VERSION;
                • 5. Re: BFILE: need advice for best practice
                  716619
                  yes, from a DBA point of view...

                  But what about the posibility for the users to browse their files?. This would mean I had to duplicate the files: one copy that goes into the DB and is stored as BLOB and can be used to search. Another copy will get stored on the file system just to enable the user to browse their files (i.e. what files where created for action "offers" in february 2012. The filenames contain customer id and name as well as user id). In most cases there will be less that 100 files in any of those directories.

                  This is why I thought a BFILE might be the best alternative as I get both: fast index search and browsing capability for users that are used to use windows explorer...
                  • 6. Re: BFILE: need advice for best practice
                    716619
                    what makes you think Oracle does no longer support C/S...
                    Think of my program as some VB6 programming (in fact its SQLWindows from former Gupta, now Unify). This program "talks" to normal Oracle10/11 client. Most clients are WinXP using Oracle 10, some Win7 with Oracle 11 client.

                    Here is the info you asked for:
                    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
                    PL/SQL Release 11.2.0.1.0 - Production
                    CORE     11.2.0.1.0     Production
                    TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
                    NLSRTL Version 11.2.0.1.0 - Production
                    • 7. Re: BFILE: need advice for best practice
                      Tubby
                      Stefan Misch wrote:
                      yes, from a DBA point of view...
                      Not really just from a DBA point of view. If you're a developer and you choose BFILE, and you don't have those BFILE's on the file system being backed up and they subsequently go "missing" i would say you (the developer) are at fault for not understanding the infrastructure you are working within.
                      Stefan Misch wrote:
                      But what about the posibility for the users to browse their files?. This would mean I had to duplicate the files: one copy that goes into the DB and is stored as BLOB and can be used to search. Another copy will get stored on the file system just to enable the user to browse their files (i.e. what files where created for action "offers" in february 2012. The filenames contain customer id and name as well as user id). In most cases there will be less that 100 files in any of those directories.

                      This is why I thought a BFILE might be the best alternative as I get both: fast index search and browsing capability for users that are used to use windows explorer...
                      Sounds like it would be simple enough to add some metadata about the files in a table. So a bunch of columns providing things like "action", "Date", "customer id", etc.... along with the document stored in a BLOB column.

                      As for the users browsing the files, you'd need to build an application to interface with the database ... but i don't see how you're going to get away from building an application to interface with the database for this in any event.

                      I personally wouldn't be a fan of providing users any sort of access to a production servers file system, but that could just be me.
                      • 8. Re: BFILE: need advice for best practice
                        sb92075
                        Stefan Misch wrote:
                        what makes you think Oracle does no longer support C/S...
                        Oracle's FORMS went web-based a decade ago.

                        What prevents client from filling up DB Server disk volume?
                        • 9. Re: BFILE: need advice for best practice
                          716619
                          Tubby,
                          the table with the BFILE (or BLOB) has all the neccessary columns like action, date, checked out flag etc. I will need to build a user interface to this table anyway.

                          I see the advantage of the BLOB (consistency, no need to struggle with file access on the server, no need for upload procedure I mentioned, no need for directory objects to create the structure action/year/month etc.)

                          Thanks for helping,
                          Stefan