8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

Related articles.

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:

Hope this helps. Regards Tim...

Back to the Top.