8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle REST Data Services (ORDS) : File Upload and Download (mod_plsql Style)
The owa.docTable
parameter has been added in Oracle REST Data Services (ORDS) 18.3, allowing it to process uploads of BLOBs in a similar manner to the way mod_plsq
worked. This article is an update of a previous article, which used mod_plsql
to allow PL/SQL Web Toolkit applications to upload and download files. Oracle REST Data Services (ORDS) can upload and download BLOBs without this functionality (see here), but it helps support people converting existing PL/SQL Web Toolkit applications from mod_plsql
to ORDS.
- Create Test Schemas
- ORDS Setup (ORDS Version 22.1 Onward)
- ORDS Setup (ORDS Versions 18.3 to 21.4)
- Test It
Related articles.
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : RESTful Web Services Handling Media (Documents and Images)
- Oracle REST Data Services (ORDS) : Presenting PL/SQL Web Toolkit Applications
- Oracle REST Data Services (ORDS) : Database Authentication
Create Test Schemas
The example in this article relies on two schemas. The first is the schema owner, which will hold the document table and a PL/SQL API to manage it.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER=pdb1; --DROP USER doc_schema_owner CASCADE; CREATE USER doc_schema_owner IDENTIFIED BY doc_schema_owner QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO doc_schema_owner;
We can now create the table to hold the documents in the new schema.
CONN doc_schema_owner/doc_schema_owner@pdb1 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 );
Create a package with all the necessary code to upload and download files.
CREATE OR REPLACE PACKAGE document_api AS PROCEDURE upload; PROCEDURE upload (file IN VARCHAR2); PROCEDURE download; PROCEDURE download (file IN VARCHAR2); END; / SHOW ERRORS 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 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 -- ---------------------------------------------------------------------------- -- Unused in this example. -- ---------------------------------------------------------------------------- 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
Create a schema which ORDS can use to access the API. Since all the work happens in the schema owner, we use a login trigger to set the CURRENT_SCHEMA
setting for the session.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER=pdb1; --DROP USER doc_login CASCADE; CREATE USER doc_login IDENTIFIED BY doc_login QUOTA UNLIMITED ON users; GRANT CREATE SESSION TO doc_login; CREATE OR REPLACE TRIGGER doc_login.after_logon_trg AFTER LOGON ON doc_login.SCHEMA BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=doc_schema_owner'; END; /
We need to make sure the login user has access to the API, and it will also need direct access to the documents table.
GRANT EXECUTE ON doc_schema_owner.document_api TO doc_login; GRANT SELECT, INSERT ON doc_schema_owner.documents TO doc_login;
ORDS Setup (ORDS Version 22.1 Onward)
There is a more detailed explanation of this setup here.
Setup a new database connection.
export ORDS_HOME=/u01/ords export ORDS_CONFIG=/u01/config/ords export PATH=${ORDS_HOME}/bin:${PATH} export ORDS_LOGS=${ORDS_CONFIG}/logs export DB_PORT=1521 export DB_SERVICE=pdb1 export SYSDBA_USER=SYS export SYSDBA_PASSWORD=SysPassword1 export ORDS_PASSWORD=OraPassword1 ords --config ${ORDS_CONFIG} install \ --log-folder ${ORDS_LOGS} \ --db-pool doc-pdb1 \ --admin-user ${SYSDBA_USER} \ --db-hostname ${HOSTNAME} \ --db-port ${DB_PORT} \ --db-servicename ${DB_SERVICE} \ --gateway-mode proxied \ --gateway-user DOC_LOGIN \ --proxy-user \ --password-stdin <<EOF ${SYSDBA_PASSWORD} ${ORDS_PASSWORD} EOF
If it is present, blank or remove the security.requestValidationFunction
attribute. It controls the PL/SQL allowlist which limits which procedures can be called. That works fine for APEX, but it's going to stop this example running.
# Set to blank value. ords --config ${ORDS_CONFIG} config --db-pool doc-pdb1 set security.requestValidationFunction "" # Remove the setting. ords --config ${ORDS_CONFIG} config --db-pool doc-pdb1 delete security.requestValidationFunction
For a Database Access Descriptor (DAD) we could set the following parameters to allow for upload and download of documents.
PlsqlDocumentTablename doc_schema_owner.documents PlsqlDocumentPath docs PlsqlDocumentProcedure document_api.download
With ORDS we currently only have the ability to identify the documents table using the owa.docTable
parameter, not the download path or download procedure. The following command sets the owa.docTable
parameter for the pool we just created.
ords --config ${ORDS_CONFIG} config --db-pool doc-pdb1 set owa.docTable doc_schema_owner.documents
We need to restart ORDS for the changes to be visible. The following shows how you might restart ORDS deployed with Tomcat.
$CATALINA_HOME/bin/shutdown.sh $CATALINA_HOME/bin/startup.sh
ORDS Setup (ORDS Versions 18.3 to 21.4)
If it is present, remove the following from the "/u01/ords/conf/ords/defaults.xml" file. It controls the PL/SQL allowlist which limits which procedures can be called. That works fine for APEX, but it's going to stop this example running.
<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
Setup a new database connection, so we can connect to the login user and change the parameters without affecting the main APEX pool. Notice how we use the DOC_LOGIN
user for the connection details, and we ignore APEX RESTful Services configuration.
cd /u01/ords $JAVA_HOME/bin/java -jar ords.war setup --database doc-pdb1 Enter the name of the database server [localhost.localdomain]: Enter the database listen port [1521]: Enter 1 to specify the database service name, or 2 to specify the database SID [1]: Enter the database service name [pdb1]: Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:2 Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step. If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]: Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:doc_login Enter the database password for doc_login: Confirm password: Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:2 Dec 08, 2018 5:36:05 PM ... ... Additional output removed for brevity...
We need to associate a base URL path with the new pool, so we can run the application.
$JAVA_HOME/bin/java -jar ords.war map-url --type base-path /doc-pdb1 doc-pdb1
For a Database Access Descriptor (DAD) we could set the following parameters to allow for upload and download of documents.
PlsqlDocumentTablename doc_schema_owner.documents PlsqlDocumentPath docs PlsqlDocumentProcedure document_api.download
With ORDS we currently only have the ability to identify the documents table using the owa.docTable
parameter, not the download path or download procedure. The following command sets the owa.docTable
parameter for the pool we just created.
$JAVA_HOME/bin/java -jar ords.war set-property --conf doc_pdb1 owa.docTable doc_schema_owner.documents
You need to restart ORDS for the changes to be visible. The following shows how you might restart ORDS deployed with Tomcat.
$CATALINA_HOME/bin/shutdown.sh $CATALINA_HOME/bin/startup.sh
Test It
The upload page is available from the following URL.
# Format http://<server-name>:<port>/ords/doc-pdb1/document_api.upload # Example http://localhost:8080/ords/doc-pdb1/document_api.upload
It calls the first overload of the upload
procedure, to display the upload page.
The second overload of the upload
procedure doesn't really upload the file as this is done automatically by ORDS. 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 links to test the download code. You can manually download a file using the following type of URL.
# Format http://<server-name>:<port>/ords/doc-pdb1/document_api.download?file=<file-name> # Example http://localhost:8080/ords/doc-pdb1/document_api.download?file=my_image.jpg
For more information see:
- Understanding Configurable Parameters
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : RESTful Web Services Handling Media (Documents and Images)
- Oracle REST Data Services (ORDS) : Presenting PL/SQL Web Toolkit Applications
- Oracle REST Data Services (ORDS) : Database Authentication
Hope this helps. Regards Tim...