Oracle® mod_plsql Upload and Download Procedures Example
This site describes a complete example for uploading and downloading documents directly from the database using mod_plsql.
It asumes, your Oracle® Database is up and running and you've an user with name Scott which is identified by tiger.
First a Database Access Descriptor (DAD) must be created. Add the following entry to your $ORACLE_HOME/Apache/modplsql/conf/dads.conf file:
<Location /pls/scott>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername scott
PlsqlDatabasePassword tiger
PlsqlDatabaseConnectString tns_name
PlsqlAuthenticationMode Basic
PlsqlDocumentTablename scott.documents
PlsqlDocumentPath docs
PlsqlDocumentProcedure docs_api.download_doc
</Location<>Take care that the value of PlsqlDatabaseConnectString points to an existing entry in the %ORACLE_HOME%/network/admin/tnsnames.ora file.
Having made changes to $ORACLE_HOME/Apache/modplsql/conf/dads.conf the OHS has to be restarted with the following command:
%ORACLE_HOME%/opmn/bin/opmnctl restartproc type=ohs
Create a document table with the following structure in Scott's schema (we are going to use a BLOB column only):
create table documents (
name varchar2(256) unique not null,
mime_type varchar2(128),
doc_size number,
dad_charset varchar2(128),
last_updated date,
content_type varchar2(128),
blob_content blob )
/An example record after a successful upload would look like this:
| Column | Contain | Value |
| name | file name | F18645/pets.gif |
| mime_type | mime type | image/gif |
| doc_size | size in bytes | 13542 |
| dad_charset | DAD charset | ascii |
| last_updated | last update | 26.02.2014 |
| content_type | type of content | BLOB |
| blob_content | content |  |
As we've specified the PlsqlDocumentProcedure poiting to docs_api.download_doc, we now need a package called docs_api
with the upload and download procedures:
create or replace package docs_api as
procedure upload_doc;
procedure document_uti ( insert_doc in varchar2 default null, delete_doc in varchar2 default null );
procedure download_doc;
procedure download_doc ( doc in varchar2 );
end;
/
create or replace package body docs_api as
-- PAGEHEAD --
procedure pageHead( p_title in varchar2 default 'mod_plsql Document Management' ) is
begin
htp.p ( '<!DOCTYPE HTML>
<html lang="en">
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
body { font-family: Verdana, Arial, Helvetice, "Sans Serif" }
table { border-collapse: collapse; }
thead { background-color: #ededed; }
th, td { border-bottom: 1px solid #777; padding: 12px; }
img { max-width: 139px; }
.btn-link { padding: 7px 10px; background: #4479cb; color: #ededed;
-webkit-border-radius: 3px; -moz-border-radius: 3px; border-radius: 3px; }
</style>
<title>' || p_title || '</title>
</head>' );
end pagehead;
-- UPLOAD_DOC --
procedure upload_doc as
begin
pagehead( 'Document Upload via mod_plsql' );
htp.p( '<body>
<h1>Document Upload via MOD_PLSQL</h1>
<div>
<form enctype="multipart/form-data" action="docs_api.document_uti" method="post">
<p>File to upload: <input type="file" name="insert_doc"></p>
<p><input type="submit" value="start upload"></p>
</form>
</div>
</body>
</html>' );
end upload_doc;
-- DOCUMENT_UTI --
procedure document_uti ( insert_doc in varchar2 default null, delete_doc in varchar2 default null ) is
l_doc_size varchar2(20);
begin
pagehead();
htp.p ( '<body>
<h1>Oracle® mod_plsql Document Utility</h1>
<p><a class="btn-link" href="docs_api.upload_doc">back to Upload page</a></p>' );
if
insert_doc is not null
then
begin
for l_rec in ( select to_char ( doc_size ) doc_size from documents where name = insert_doc ) loop
l_doc_size := l_rec.doc_size;
end loop;
if
l_doc_size is not null
then
htp.print( '<p>Document upload of <strong>' || insert_doc || '</strong> was successful. Filesize is ' || l_doc_size || ' bytes.</p>' );
else
htp.print( '<p>Document upload of <strong>' || insert_doc || '</strong> was NOT successful. The document could <strong>NOT</strong> be found.</p>' );
end if;
exception
when others
then htp.print( 'Document Upload of ' || insert_doc || ' failed.' );
htp.print( sqlerrm );
end;
elsif
delete_doc is not null
then
begin
delete from documents
where name = delete_doc;
if
sql%rowcount > 0
then
htp.print( '<p>Document <strong>' || delete_doc || '</strong> was successfully deleted.</p>' );
else
htp.print( '<p>Document <strong>' || delete_doc || '</strong> could not be deleted.</p><p>Either it was not found or there was another issue.</p>' );
end if;
exception
when others
then htp.print( 'Delete of document ' || delete_doc || ' failed.');
htp.print( sqlerrm );
end;
end if;
htp.p ( '<h3>Contents of Document table:</h3>
<table>
<thead>
<tr>
<th>name</th>
<th>mime type</th>
<th>size</th>
<th>charset</th>
<th>updated</th>
<th>content type</th>
<th>content</th>
<th>delete ?</th>
</tr>
</thead>' );
for cur_rec in ( select doc.name, doc.mime_type, doc.doc_size, doc.dad_charset,
to_char ( doc.last_updated, 'dd.mm.yyyy hh24:mi' ) last_updated, doc.content_type
from documents doc
order by doc.last_updated ) loop
htp.p ( '<tr>' );
htp.p ( '<td><a href="docs/' || cur_rec.name || '" title="open with document access path">' || cur_rec.name || '</a></td>' );
htp.p ( '<td>' || cur_rec.mime_type || '</td>' );
htp.p ( '<td>' || cur_rec.doc_size || '</td>' );
htp.p ( '<td>' || cur_rec.dad_charset || '</td>' );
htp.p ( '<td>' || cur_rec.last_updated || '</td>' );
htp.p ( '<td>' || cur_rec.content_type || '</td>' );
if
lower ( cur_rec.mime_type ) like 'image%'
then
htp.p ( '<td><a href="docs_api.download_doc?doc=' || cur_rec.name || '" title="open with document access procedure"><img src="docs/' || cur_rec.name || '" /></a></td>' );
else
htp.p ( '<td><a href="docs_api.download_doc?doc=' || cur_rec.name || '" title="open with document access procedure">click to open</a></td>' );
end if;
htp.p ( '<td><a class="btn-link" href="docs_api.document_uti?insert_doc=&delete_doc=' || cur_rec.name || '">delete</a></td>' );
htp.p ( '</tr>' );
end loop;
htp.p ( '</table>
</body>
</html>' );
end document_uti;
-- DOWNLOAD_DOC --
procedure download_doc is
l_doc_name constant varchar2(255) := substr( owa_util.get_cgi_env( 'path_info' ), 2 );
begin
wpg_docload.download_file( l_doc_name );
exception when others then
pagehead( 'mod_plsql Download failure' );
htp.p( '<body>
<h1>mod_plsql Download failure</h1>' );
htp.p( sqlerrm );
htp.p ( '</body></html>' );
end download_doc;
-- DOWNLOAD_DOC overload --
procedure download_doc ( doc in varchar2 ) is
cursor get_document is
select d.blob_content, lower ( d.mime_type ) mime_type
from documents d
where d.name = doc;
l_document get_document%rowtype;
begin
open get_document;
fetch get_document into l_document;
if
get_document%notfound
then
close get_document;
raise_application_error( -20000, 'Document ' || doc || ' not found in documents table.' );
end if;
close get_document;
owa_util.mime_header( l_document.mime_type, false );
htp.print ( 'Content-Length: ' || to_char ( dbms_lob.getlength( l_document.blob_content ) ) );
if
l_document.mime_type not like 'image%'
then
htp.print ( 'Content-Disposition: attachment; filename=' || doc );
end if;
owa_util.http_header_close;
wpg_docload.download_file( l_document.blob_content );
exception when others then
pagehead( 'mod_plsql Download failure' );
htp.p( '<body>
<h1>mod_plsql Download failure</h1>' );
htp.p( sqlerrm );
htp.p ( '</body></html>' );
end download_doc;
end;
/To be continued ...