Download/Upload a BLOB via HTTP (mod_plsql)
Here are some simple tips to manage files on your plsql based web applications .
Uploading
Uploading files to the server it's pretty straight forward, as most of the work is already done by the module, you don't need to write any code to get the files into the database.
When you configured the DAD, or even if you are using portal's (/pls/portal), there is a setting called PlsqlDocumentTablename, which in the case of portal's DAD points to the portal.WWDOC_DOCUMENT$ table.
This pretty much indicates that any file send to the DAD is loaded into the table... that's right anything, you submit to your server to http://<your server>/pls/portal will be uploaded, which makes things easy but it's kind of a weak point for hacking and security.
so if you simple write a HTML form with file input and point the action to your server (i.e. a procedure that saves the form in a table), the document will be uploaded and the procedure will receive a varchar2 with the document name.
for example:
<html>
<form name="my_upload" action="http://myserver/pls/portal/sample.save_form" method="POST" enctype="multipart/form-data" >
<input type="file" name="p_file">
<input type="submit" value="upload">
</form>
</html>
It doesn't matter what the sample.save_form procedure does, or if it even exist, the file will be uploaded to the table define in the DAD, from there you can move it to your own table or do whatever you want.
Something to consider is that if you are using portal's DAD, there is a garbage collection job that reguraly checks the documents table and deletes anything that has been uploaded and does not belong to portal. This deletion is based on a session id put to the record to identify where it came from, to avoid portal deleting your document, simply set the session_id to null;
UPDATE portal.WWDOC_DOCUMENT$
SET SESSION_ID = NULL
WHERE NAME = :V_NAME;
where V_NAME is the name of the file, which you should receive in the procedure you submitted the form to.
Downloading
If you used portal's dad, all you need is the name of the file and create a link to:
http://myserver/pls/portal/docs/<file_name>
On the other hand, if you moved the file to your own table, or you loaded the blob a different way into your table, you can write a simple procedure to pass the file to the browser, just keep in mind that you need to record the mime type in your table or know what it is, as this is used when downloading the file.
PROCEDURE download (p_id in number) AS
l_blob_content MY_TABLE.attachement%TYPE;
l_mime_type MY_TABLE.mime%TYPE;
l_filename MY_TABLE.filename%TYPE;
BEGIN
SELECT attachement,
mime,
filename
INTO l_blob_content,
l_mime_type
l_filename
FROM MY_TABLE
WHERE request_id=p_id;
OWA_UTIL.mime_header(l_mime_type, FALSE);
HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_blob_content));
htp.p('Content-disposition: attachment; filename="'||l_filename||'"');
OWA_UTIL.http_header_close;
WPG_DOCLOAD.download_file(l_blob_content);
EXCEPTION
WHEN OTHERS THEN
HTP.htmlopen;
HTP.headopen;
HTP.title('File Downloaded');
HTP.headclose;
HTP.bodyopen;
HTP.header(1, 'Download Status');
HTP.print(SQLERRM);
HTP.bodyclose;
HTP.htmlclose;
END download;
No comments:
Post a Comment