Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Question Unanswered: Store word documents in SQL

    Hi,

    I'm looking for a method to store word documents in a Oracle SQL-server. I'm using sqlplus.

    I have a usertable where different users are stored, within this table i want to store a word doc for each row.

    The problem is following, i have several users who are stored with their personal information in a table, in addition to this i need a posibility to store a document containing their previous work experience.

    Is there maybe a way to store the documents on the server and let a oracleTable manage them?

    Thanks in advance
    --
    Bigshow

  2. #2
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    I think using blob datatype or bfile datatype, ur requirement should be fulfilled
    Thanks and Regards,

    Praveen Pulikunnu

  3. #3
    Join Date
    Apr 2004
    Posts
    7
    Thanks for the quick reply:-D

    Hope i'm not asking too much, but do you have some example on how this is done?

    if i have a table looking like this;

    CREATE TABLE USERTABLE (
    USER_ID NUMBER(4),
    DOC BLOB));

    How do i send and retrieve files from this table?

    Thanks again
    --
    Bigshow



    Originally posted by praveenpr
    I think using blob datatype or bfile datatype, ur requirement should be fulfilled

  4. #4
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    it depends what you are using as a front end

    have a look at the DBMS_LOB package with specific reference to DBMS_LOB.LOADFROMFILE which alows you to load files from the servers filesystem

    We are writting a web based application and the Apache server has some nice easy to use inbuilt functionality for this sort of thing!

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

  6. #6
    Join Date
    Apr 2004
    Posts
    7
    Hmm,

    We're also creating a web based application, to be more spesific its a system to administrate different employees.

    I'm not sure what you mean by front end, but we're using Java and ApacheTopcat as a testplatform

    --
    Bigshow

    Originally posted by robert xr4x4
    dbms_lob description .....
    http://download-west.oracle.com/docs...b.htm#ARPLS020

  7. #7
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    the apache mod_plsql gateway has the facility to upload and extract files into a table you specify in the config without needing any code behind it.

    when you have an input type="file" it automatically puts the file submitted into the table and it can be retrieved by http://server/pls/dad_name/docs/file.doc

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >We're also creating a web based application
    The statement above implies a THREE tier architecture.
    Tier-1 - The client interface is a braindead piece of s/w called a web browser which only knows how to transform a text string, typically in HTML format, into pixels on the display.
    Tier-3 - Backend database server where data is stored.
    Tier-2 - "Application Server" which know how to exchange packets with both Tier-1 & Tier-3
    It is a non-trival matter to "transport" a file which is "accessible" to web browser and get it stored into the database.
    For your consideration....
    There is no actual "requirement" that the Word document file be stored in the database itself. Alternatively only the complete pathname where the file resides in the OS filesystem needs to be stored in the database.

    HTH & YMMV
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Apr 2004
    Posts
    7
    That sounds like a excellent solution anacedent!:-D

    One of the problems i've been thinking about is namely that there will be a lot of documents stored in the table. A solution where i can store the path to the files in the db.table would be nice.

    But will this cut away the posibilites of sharing documents to certain users? I guess i store the path in a char or varchar, but how do i send the LINK-value of the file to the table? And how do i retreive the file?

    Thanks again people!
    --
    Bigshow

    Originally posted by anacedent
    >We're also creating a web based application
    The statement above implies a THREE tier architecture.
    Tier-1 - The client interface is a braindead piece of s/w called a web browser which only knows how to transform a text string, typically in HTML format, into pixels on the display.
    Tier-3 - Backend database server where data is stored.
    Tier-2 - "Application Server" which know how to exchange packets with both Tier-1 & Tier-3
    It is a non-trival matter to "transport" a file which is "accessible" to web browser and get it stored into the database.
    For your consideration....
    There is no actual "requirement" that the Word document file be stored in the database itself. Alternatively only the complete pathname where the file resides in the OS filesystem needs to be stored in the database.

    HTH & YMMV

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But will this cut away the posibilites of sharing documents to certain users?
    Only if you want to restrict sharing.
    Do you want to allow the janitor to access the files created by the CEO?

    >How do i send the LINK-value of the file to the table?
    Small Matter Of Programming (SMOP).

    >And how do i retreive the file?
    One way is via....
    http://otn.oracle.com/products/forms...il/webutil.htm

    What happens when a user accesses your application via a PDA, MAC or Unix system?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Apr 2004
    Posts
    7
    Fast reply again, fantastic!

    Ok the situation is the following;

    There are COSTUMERS and EMPLOYEES, a customer gets access to its list of employees on thet net (jsp/servlet-login), then, among other things, the customer can see all of its employees with their corresponding resume(cv). The resume is is a *.doc file.

    Now one customer can ONLY see its employees and their CVs, in other words i need some sort of restriction.

    Implementation on other platforms, UNIX, MAC etc is not looked into yet, first priority is to get the system up and running. Tweeking will be done in the next iteration (starts in June).

    So, can this problem be resolved by storing links to the files or do i have to store the files themselves in the db? Could i pretty-please-with-sugar-on-top get some examples also?

    Thanks
    --
    Bigshow

    Originally posted by anacedent
    >But will this cut away the posibilites of sharing documents to certain users?
    Only if you want to restrict sharing.
    Do you want to allow the janitor to access the files created by the CEO?

    >How do i send the LINK-value of the file to the table?
    Small Matter Of Programming (SMOP).

    >And how do i retreive the file?
    One way is via....
    http://otn.oracle.com/products/forms...il/webutil.htm

    What happens when a user accesses your application via a PDA, MAC or Unix system?

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >So, can this problem be resolved by storing links to the files or do i have to store the files themselves in the db?
    I see nothing of significance is gained by storing the files in the DB.
    I would have the files reside in the OS file system on the DB server.
    Problems remain for you to solve.
    1) How to get the file from the browser to Tier-2
    2) How to get the file from Tier-2 to Tier-3, the DB server.
    3) How to control access to the files.

    >Could i pretty-please-with-sugar-on-top get some examples also?
    My consultation fee is $150/hour (US) w/a 20 hour minimum; otherwise
    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Apr 2004
    Posts
    7
    Thanks for the info anacedent, i will look into the possibilites.

    I don't think i will sign up on any of your consultations, i joined this free forum to get some free help by fellow db_users.

    Does anyone else have some ideas on this matter?

    Thanks anyway
    --
    Bigshow

    Originally posted by anacedent
    >So, can this problem be resolved by storing links to the files or do i have to store the files themselves in the db?
    I see nothing of significance is gained by storing the files in the DB.
    I would have the files reside in the OS file system on the DB server.
    Problems remain for you to solve.
    1) How to get the file from the browser to Tier-2
    2) How to get the file from Tier-2 to Tier-3, the DB server.
    3) How to control access to the files.

    >Could i pretty-please-with-sugar-on-top get some examples also?
    My consultation fee is $150/hour (US) w/a 20 hour minimum; otherwise
    You're On Your Own (YOYO)!

  14. #14
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Hey Big...

    May I suggest a detour... look into Oracle's implementation of interMedia -- their document storage and retrieval system. The LOB packages are nice and effective for simple storage, but if you are wanting to offer an application that is useful to present CV's and other documents, I imagine you are looking ahead to allowing users to search, etc through the web app.

    A starting point http://download-west.oracle.com/docs...ses.htm#610581 might give you some help. Also, look into a product called UltraSearch, an Oracle product. Actually UltraSearch uses interMedia, so start with iM first.

    As to examples, the link to the Oracle docs does have some examples. However, if you have loaded Oracle Enterprise Edition, go to the $O_H/ultrasearch/sample/query. This is how Oracle has implemented a JSP solution to what you are trying to do...

    Happy to help, and, by the way, as long as there are sites like this, you are never "on your own".

    JoeB
    Last edited by joebednarz; 04-06-04 at 12:19.

  15. #15
    Join Date
    Apr 2004
    Posts
    7
    Thanks for the constructiv reply joebednarz.

    I've already started looking into it, hope i find something usefull, i probably will.

    By the way, i only need to restrict certain documents to certain users for display on the net.

    --
    Bigshow

    Originally posted by joebednarz
    Hey Big...

    May I suggest a detour... look into Oracle's implementation of interMedia -- their document storage and retrieval system. The LOB packages are nice and effective for simple storage, but if you are wanting to offer an application that is useful to present CV's and other documents, I imagine you are looking ahead to allowing users to search, etc through the web app.

    A starting point http://download-west.oracle.com/docs...ses.htm#610581 might give you some help. Also, look into a product called UltraSearch, an Oracle product. Actually UltraSearch uses interMedia, so start with iM first.

    As to examples, the link to the Oracle docs does have some examples. However, if you have loaded Oracle Enterprise Edition, go to the $O_H/ultrasearch/sample/query. This is how Oracle has implemented a JSP solution to what you are trying to do...

    Happy to help, and, by the way, as long as there are sites like this, you are never "on your own".

    JoeB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •