How to store excel file in oracle database for display on JSP page?

I'm not fully understanding the question.

You can store the binary worksheet in a BLOB column in a table.

Are you asking this or how to load it in to the BLOB or something else?

If you need to have the whioole file accessible to the users, then
You can store your file in Oracle as BLOB, then in the servlet of JSP select it and write
into some temporary location and provide the link to that location on yor web page, so that the users could click on the link and open this file in Excel.


If the user may be insterested only in some lilmited columns then think about reading the Excel file using POI and storing specifgic content in the normal fields of orcale table
http://poi.apache.org/

Use a PreparedStatement in conjunction with PreparedStatement.setBinaryStream

Thsi is an example of storing BLOB in Oracle
http://www.kodejava.org/examples/279.htm

This is an example how to upload and download images to BLOBs in Oracle

http://srikanthtechnologies.com/blog/java/fileupload.aspx

It of course does not matter of those are images or Excel files

Thanks, I just want to be able to do a one time load of file into the blob in oracle from back end. The aim is to present the user with the excel file from the front end. My question is:

1. how do I load the excel file with multiple work sheets into the blob.
2. how can the user view the same excel file from the java application by clicking on a link. The file is just for viewing only. thanks a lot.

This link shows how to store image file in Oracle:
http://www.kodejava.org/examples/279.htm

However, it makes no difference which file you store in orcale, be it a picture, Excel file , or any other file and it of course does not matter what kind of Excel file it is , how many worksheets. etc.
Contents of any file can be stored in Oracle exactly the same way, as any file is just a collection of binary data.

This link also shows how to retrieve contents of the file from Oracle and write it to the file on your computer - in case of JSP or servlet - on the file on the server:

http://srikanthtechnologies.com/blog/java/fileupload.aspx

Once again - it makes no difference what kind of file you are retrieveing.
The only point is that when you retrieve it in the servlet you need to write it into the server folder under the location which can be accessed by the user through web server running on your server (should be somewhere under document root of the web server).
You also need to give the file the name with extension .xls. Then you
make sure that your sevlet creates and returns to the user the web page which would contain the link pointing to the file which you retrieved from oracle and just written to the server locatiion. Once you provide correct link and the .xls extension- then when the user clicks on the link - the browser will offer to user to open the file in MS Excel and the user will be able to open the file, view  any worksheet in this file or do anything they normally do with any Excel file.

thanks, but I want to do the upload from the back end. I tried the following:

SQL> create table special(a number, b varchar2(20), c blob);

Table created.

SQL>
SQL> insert into special values(1,'abcd',empty_blob());

1 row created.
create or replace procedure load(id number)
is
dest_lob blob;
src_lob bfile:=bfilename('C:\MYIMAGE','test.xlsx');
amt integer:=sys.dbms_lob.getlength(src_lob);
begin
select c into dest_lob from special
where a=id for update;
dbms_lob.fileopen(src_lob);
dbms_lob.loadfromfile(dest_lob,src_lob,amt);
dbms_lob.fileclose(Src_lob);
commit;
end;
/

Procedure created.

SQL> exec load(1);
BEGIN load(1); END;

*
ERROR at line 1:
ORA-22285: non-existent directory or file for GETLENGTH operation
ORA-06512: at "SYS.DBMS_LOB", line 566
ORA-06512: at "SYSTEM.LOAD", line 5
ORA-06512: at line 1

I have the file in place and the path is defined, but why do I get this error...

thanks, but I want to do the upload from the back end. I tried the following:

In that case:

a. why have you posted this question in the Java TA?
b. in what way are you going to invoke this back end code?

I posted my question in Oracle & Java TA. The load of file is a one time thing so, we can run the script from the back end to load the excel file into the table.

The link to access the file will be in jsp code. I hope that makes sense

The load of file is a one time thing so, we can run the script from the back end to load the excel file into the table.

OK. In that case, the question is not relevant to the Java TA. The fact that the link is connected to a JSP is not important.

should I delete this question and create another one in oracle TA?

No - it's ok - it's going to be moved. Just think of additional TAs is could be relevant to

can someone help me?

Do not store the excel itself in oracle. Store the file somewhere in the local disk and only store the path to this file in oracle. It's quicker and you do not load the database with binary objects.

To go along with girionis' post, if the files are going to exist on the database server anyway, store them as a BFILE rather than a BLOB.  That way only a pointer to the file is stored in the database, but it is still accessible via query.

Just be aware that the file on the file system that is pointed to with a BFILE is not backed up as part of a database backup and if security is an issue, users can access the file through the OS assuming they have the correct OS permissions.

I too was going to make similar remarks about file system vs. db storage earlier but thought that there were special reasons in this case to prefer db storage

@slightwv thanks. I am thinking of having a link which just opens the excel file

now I am confused, what's the best way to load the file into the database so that it can be accessed later

@slightwv thanks. I am thinking of having a link which just opens the excel file

Then it has to be in the file system unless you want to have to right reasonably complex front end code

Placing files in a database or file system is a theoretical debate with no 'right' answer.  As you can see, everyone has their own opinion.

Since every system is different, every solution is different.  Personally, for reasons mention above, I prefer storing files in a database if I already need to access information inside a database.  If you are talking about a huge document management system, then probably not.

My reason for this: Data Integrity when it comes to backup and recovery:  I backup my database, I have everything I need.

>>now I am confused, what's the best way to load the file into the database so that it can be accessed later

I think you are confusing the posts:  If you are already convinced that you want to load the file into the database, use the code I posted.

If you are asking "IF" you should load it into the database, we really cannot answer what is 'best' for you.  Only you know your system/application and how it will be used.

>>right reasonably complex front end code

Agree to a point.  If your site does not currently retrieve information from a database, I'm not sure you would want to write all the connectivity code just for this one file.

If you are already connecting and retrieving data throughout the app, setting a Mime-Type and streaming a BLOB will likely not complicate matters that much.

As mentioned, there is no "right" answer.  It is what works best for your application.

My opinion, is that if you are just storing the file and not indexing it with something like Oracle Text, then a BFILE is the way to go.  Just be aware of the caveats.  If you are going to index them, then the performance gain of having them in BLOBs is definitely worth it.

We had also found that with the size of the documents we were handling at the time that BLOBs were wasting too much space in the database.  I believe they are still stored the same way, so just be aware that each BLOB that is not stored in line will be stored in full database blocks only.  There will never be more than one record in a block.  So, if you have a block size of 8K and your documents average 2K (or 10K), then you are wasting an average of 6K per document.  Not a lot of space if yo have a small number of documents but we had millions and it added up really fast.

>The load of file is a one time thing

Are you talking about just one file or you want to store many files corresponding to different id's - in each row of the table ?

If you are talking about one file, then you probably do not need the databases at all, juts place the file in the accessible place in some server directory and put a link to it in the HTML code


If you want to store different files in each row in a table as BLOB and then want to retrieve file back depending on the user request, then you should have some way to handle it from your
web server. Once you use jsp, you have java engine running there, so you can do it with the java servlet or jsp, then some  code which retrives file from the blob in database can be used. Soemthing like the code below:

        
        try {
 Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr"); //modify this according to your DB location and user/password
PreparedStatement pstmt = connection.prepareStatement("select C from SPECIAL where A = ?");
String id = request.getParameter("id");
          
pstmt.set(1,id);
ResultSet rs = pstmt.executeQuery();
while( rs.next() ) {
        Blob blob = rs.getBlob("C");
        System.out.println("Read "+ blob.length() + " bytes ");
        byte [] array = blob.getBytes( 1, ( int ) blob.length() );

        File file = new File("myFile.xls");;
        FileOutputStream out = new FileOutputStream( file );
        out.write( array );
        out.close();
}
// provide the link to the file "myFile.xls" and print the link to your HTML code which goes back to the client; make sure that link is correctly accessing the directory of where you were writing the file as it should be realtive to the document root of your web server

    
        }
        catch(Exception ex) {
             System.out.println(ex.getMessage());
        }
    } 

Select allOpen in new window

for_yan,

Taking a file from the database, writing it to the file system so a web page can access it is more than a little inefficient and not the proper way to do this.

The web page should be able to request it from the database and stream it directly to the client.  I can do it with ASP.Net so I'm sure JSP can handle it as well.

You are right, maybe it is a little inefficient, but worked for me quite fine many times.
I believe, that unless file is very big,
 the main time will still be retrieval from the database.

Don't know either, if JSP can stream it directly.

Quick Google:

The following example is a common practice of file downloading in a JSP page.

http://msdn.microsoft.com/en-us/library/aa478985.aspx

Just be careful of the typos in the Microsoft attempt to write Java!