Home » Developer & Programmer » Reports & Discoverer » Provide download link to blob in database on Oracle Reports? (Oracle Reports 11g)
Provide download link to blob in database on Oracle Reports? [message #592478] Thu, 08 August 2013 15:01 Go to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
Not sure if this is possible to do, but here is the problem I'm trying to solve.

My user would like to have a way to attach files to database records. For simplicity, we'll say that each employee in the database could have 0-many file attachments associated with them. I know how to make this part work using Oracle Forms.

I have two thoughts on this.. either I'm storing the files to a folder somewhere, or I'm storing it in the database.

So that's the easy part for me. The tough part is that they want to be able to run an Oracle Report on our application server (displays as a PDF, also downloadable as a PDF) and have links on this report to the attachments that they uploaded using the forms process above.

For the idea of storing the files on a folder somewhere, I simply created a text object on the report that had it's hyperlink property pointing to the file location. Done.. opens fine.

However, I want to be able to have the option of storing these files in the database instead, just in case we can't go with the shared folder idea. I'm not sure how to make this work. I can store a blob in the database.. but how do I link to that blob on the report for them to be able to download it? Is this even possible? To me, this doesn't sound like it should be able to work, but I figured I'd ask anyways.

So just to sum, I'd need to know how to create a download link on the Oracle Report that let's them download a file out of the database.

Thanks
Re: Provide download link to blob in database on Oracle Reports? [message #592480 is a reply to message #592478] Thu, 08 August 2013 16:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you could create a (stored) procedure which would utilize UTL_FILE, read BLOB contents and create a file in a directory on a database server. You'd be able to create a hyperlink to that file, then.
Re: Provide download link to blob in database on Oracle Reports? [message #592481 is a reply to message #592480] Thu, 08 August 2013 16:07 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
That is a pretty good idea.. good thinking outside of the box. I'll consider that as an option.. thanks!
Re: Provide download link to blob in database on Oracle Reports? [message #592483 is a reply to message #592481] Thu, 08 August 2013 16:15 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Although that might work, it is kind of a doubtful "solution" because it returns you back to what you already have - files stored in a file system, not within the database. So, why would you store files into the database, then? End users shouldn't care about that anyway; for them, it is important that they can read information written in these files, right?
Re: Provide download link to blob in database on Oracle Reports? [message #592484 is a reply to message #592483] Thu, 08 August 2013 16:22 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
Yup, you're absolutely right. I came back here to mention that, but see you came to that conclusion already. Unfortunately it isn't really buying anything. So I think that really leads me to think that the best bet is to just dump the files to a file system of some sort to begin with, as in my initial plan.

Thanks for your help anyways.
Re: Provide download link to blob in database on Oracle Reports? [message #592485 is a reply to message #592484] Thu, 08 August 2013 16:27 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem. Wait a little bit longer, someone else might get another, better idea.
Re: Provide download link to blob in database on Oracle Reports? [message #605797 is a reply to message #592478] Tue, 14 January 2014 15:49 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
I'd like to re-explore this. I let this issue go because we decided to store these file attachments in Windows, but I've now learned that this isn't going to be possible. My DBA/server guys are leaning heavily towards using the DB to store the files. This is great for everything but this one little issue.

So again, I've come full circle. To compound the problem, one issue is that the PDF report that is generated, with all these links, could be run for a very long period for many many people. In other words, it could link to hundreds of attachments, meaning I wouldn't want them all downloading to a server or to the user's machine every time they run the report. I'd really like the PDF to to have a hyperlink on it that goes and fetches the attachment right from the database, if this is at all possible (I have my doubts).

If anyone has any ideas on this, I'm all ears.. even if it requires some use of Java or other technologies. I'm starting to think that this is the only way.

I guess my question can be summed as so:

Is it possible to access an Oracle database blob via a hyperlink on a PDF in some fashion?

[Updated on: Tue, 14 January 2014 17:20]

Report message to a moderator

Re: Provide download link to blob in database on Oracle Reports? [message #605884 is a reply to message #605797] Wed, 15 January 2014 15:11 Go to previous message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
So for the sake of info for anyone else attempting this.. I think I figured out a way... but I don't really like it, to be honest.. but it's a way...

The URL on the Reports generated PDF would be a link to an Oracle Form call, with a parameter indicating the file ID needing to be uploaded. The Form would just simply pop open the attachment file (via Webutil) for the passed in ID. The thing I don't like about this is the user is presented with an error (invalid password) off the start, and then have to log into Oracle Forms. Not terrible... but not really that "clean" of a solution either. I am leaning more and more towards Windows solution, but at least I have an idea.
Previous Topic: Call a report from a report
Next Topic: Matrix report
Goto Forum:
  


Current Time: Thu Mar 28 18:34:15 CDT 2024