8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
File Upload and Download Procedures in Oracle Database 10g
This article is an update of a previous Oracle9i article, which includes some modified configuration steps necessary in Oracle 10g. For this procedure to work the HTTP Server software, available on the Companion CD, must be installed into a separate ORACLE_HOME, typically,
/u01/app/oracle/product/10.1.0/client_1
The example assumes the the ORACLE_HOME environment variable has been set to this path also.
ORACLE_HOME=/u01/app/oracle/product/10.1.0/client_1; export ORACLE_HOME
Oracle provide a mechanism to upload and download files directly from the database using a Database Access Descriptor (DAD). In this article I'll demonstrate how it works with a simple example.
First a Database Access Descriptor (DAD) must be created. Add the following entry to the "$ORACLE_HOME/Apache/modplsql/conf/dads.conf" file.
<Location /pls/UTILS> SetHandler pls_handler Order deny,allow Allow from all AllowOverride None PlsqlDatabaseUsername utils PlsqlDatabasePassword utils PlsqlDatabaseConnectString dev PlsqlAuthenticationMode Basic PlsqlDocumentTablename utils.documents PlsqlDocumentPath docs PlsqlDocumentProcedure document_api.download </Location>
Run the following command to obfuscate the DAD password.
cd $ORACLE_HOME/Apache/modplsql/conf/ perl dadTool.pl -o
Make sure the PlsqlDatabaseConnectString
parameter value has a corresponding entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file for the client ORACLE_HOME. With this in place restart the HTTP server using the following commands.
$ORACLE_HOME/opmn/bin/opmnctl stopall $ORACLE_HOME/opmn/bin/opmnctl startall
For the upload to be successful the documents
table must be created with the following structure.
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 ) /
Next we create a package specification which will contain all the necessary code.
CREATE OR REPLACE PACKAGE document_api AS PROCEDURE upload; PROCEDURE upload (file IN VARCHAR2); PROCEDURE download; PROCEDURE download (file IN VARCHAR2); END; / SHOW ERRORS
Then the package body.
CREATE OR REPLACE PACKAGE BODY document_api AS -- ---------------------------------------------------------------------------- PROCEDURE upload AS -- ---------------------------------------------------------------------------- l_real_name VARCHAR2(1000); BEGIN HTP.htmlopen; HTP.headopen; HTP.title('Test Upload'); HTP.headclose; HTP.bodyopen; HTP.header(1, 'Test Upload'); HTP.print('<form enctype="multipart/form-data" action="document_api.upload" method="post">'); HTP.print(' File to upload: <input type="file" name="file"><br />'); HTP.print(' <input type="submit" value="Upload">'); HTP.print('</form>'); HTP.bodyclose; HTP.htmlclose; END upload; -- ---------------------------------------------------------------------------- -- ---------------------------------------------------------------------------- PROCEDURE upload (file IN VARCHAR2) AS -- ---------------------------------------------------------------------------- l_real_name VARCHAR2(1000); BEGIN HTP.htmlopen; HTP.headopen; HTP.title('File Uploaded'); HTP.headclose; HTP.bodyopen; HTP.header(1, 'Upload Status'); l_real_name := SUBSTR(file, INSTR(file, '/') + 1); BEGIN -- Delete any existing document to allow update. DELETE FROM documents WHERE name = l_real_name; -- Update the prefixed name with the real file name. UPDATE documents SET name = l_real_name WHERE name = file; HTP.print('Uploaded ' || l_real_name || ' successfully.'); EXCEPTION WHEN OTHERS THEN HTP.print('Upload of ' || l_real_name || ' failed.'); HTP.print(SQLERRM); END; HTP.br; -- Create some links to demonstrate URL downloads. HTP.br; HTP.print('URL Downloads:'); HTP.br; FOR cur_rec IN (SELECT name FROM documents) LOOP HTP.anchor('docs/' || cur_rec.name, 'docs/' || cur_rec.name); HTP.br; END LOOP; -- Create some links to demonstrate direct downloads. HTP.br; HTP.print('Direct Downloads:'); HTP.br; FOR cur_rec IN (SELECT name FROM documents) LOOP HTP.anchor('document_api.download?file=' || cur_rec.name, 'document_api.download?file=' || cur_rec.name); HTP.br; END LOOP; HTP.bodyclose; HTP.htmlclose; END upload; -- ---------------------------------------------------------------------------- -- ---------------------------------------------------------------------------- PROCEDURE download IS -- ---------------------------------------------------------------------------- l_filename VARCHAR2(255); BEGIN l_filename := SUBSTR(OWA_UTIL.get_cgi_env('PATH_INFO'), 2); WPG_DOCLOAD.download_file(l_filename); EXCEPTION WHEN OTHERS THEN HTP.htmlopen; HTP.headopen; HTP.title('File Downloaded'); HTP.headclose; HTP.bodyopen; HTP.header(1, 'Download Status'); HTP.print('Download of ' || l_filename || ' failed.'); HTP.print(SQLERRM); HTP.bodyclose; HTP.htmlclose; END download; -- ---------------------------------------------------------------------------- -- ---------------------------------------------------------------------------- PROCEDURE download (file IN VARCHAR2) AS -- ---------------------------------------------------------------------------- l_blob_content documents.blob_content%TYPE; l_mime_type documents.mime_type%TYPE; BEGIN SELECT blob_content, mime_type INTO l_blob_content, l_mime_type FROM documents WHERE name = file; OWA_UTIL.mime_header(l_mime_type, FALSE); HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_blob_content)); HTP.p('Content-Disposition: filename="' || file || '"'); 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; -- ---------------------------------------------------------------------------- END; / SHOW ERRORS
Once this code is compiled a file can be uploaded by using the following URL format.
# Format http://<server-name>:<port>/pls/<DAD>/document_api.upload # Example http://myserver:7777/pls/UTILS/document_api.upload
Alternatively, the upload can be performed by adjusting the form action in the upload.html file.
The second overload of the upload
procedure does not really upload the file as this is done automatically by the DAD. It simply allows you to perform additional processing if necessary. Oracle prefixes the file name with a generated folder name to reduce the possibility of name conflicts. In this example the files are renamed to their original names.
In addition the upload code produces two types of links to test the download code. The first set of links use the "document access path" and the "document access procedure" defined in the DAD. These allow access to the files using a regular URL, masking the procedure call
which actually returns the file. Using the document access path in the URL causes the DAD to call the document access procedure. The
requested file name must be retrieved from the URL and used in the WPG_DOCLOAD.download_file
procedure call.
The second set of links call an alternative download
procedure directly. In this case file name must be specified as a
parameter.
For more information see:
Hope this helps. Regards Tim...