📋Steps in this guide1/4

Oracle REST Data Services (ORDS) : File Upload and Download (mod_plsql Style)

Upload and download files directly from the database using Oracle REST Data Services to replace the mod_plsql style upload/download functionality.

oracle miscconfigurationintermediate

by OracleDba

13 views

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. We can now create the table to hold the documents in the new schema. Create a package with all the necessary code to upload and download files. 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 setting for the session. We need to make sure the login user has access to the API, and it will also need direct access to the documents table.

Code/Command (click line numbers to comment):

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;

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 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

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;
/

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. If it is present, blank or remove the 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. For a Database Access Descriptor (DAD) we could set the following parameters to allow for upload and download of documents. With ORDS we currently only have the ability to identify the documents table using the parameter, not the download path or download procedure. The following command sets the parameter for the pool we just created. We need to restart ORDS for the changes to be visible. The following shows how you might restart ORDS deployed with Tomcat.

Code/Command (click line numbers to comment):

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

# 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

PlsqlDocumentTablename        doc_schema_owner.documents
PlsqlDocumentPath             docs
PlsqlDocumentProcedure        document_api.download

ords --config ${ORDS_CONFIG} config --db-pool doc-pdb1 set owa.docTable doc_schema_owner.documents

$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. 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 user for the connection details, and we ignore APEX RESTful Services configuration. We need to associate a base URL path with the new pool, so we can run the application. For a Database Access Descriptor (DAD) we could set the following parameters to allow for upload and download of documents. With ORDS we currently only have the ability to identify the documents table using the parameter, not the download path or download procedure. The following command sets the parameter for the pool we just created. You need to restart ORDS for the changes to be visible. The following shows how you might restart ORDS deployed with Tomcat.

Code/Command (click line numbers to comment):

<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>

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...

$JAVA_HOME/bin/java -jar ords.war map-url --type base-path /doc-pdb1 doc-pdb1

PlsqlDocumentTablename        doc_schema_owner.documents
PlsqlDocumentPath             docs
PlsqlDocumentProcedure        document_api.download

$JAVA_HOME/bin/java -jar ords.war set-property --conf doc_pdb1 owa.docTable doc_schema_owner.documents

$CATALINA_HOME/bin/shutdown.sh
$CATALINA_HOME/bin/startup.sh

Test It

The upload page is available from the following URL. It calls the first overload of the procedure, to display the upload page. The second overload of the 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. 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...

Code/Command (click line numbers to comment):

# Format
http://<server-name>:<port>/ords/doc-pdb1/document_api.upload

# Example
http://localhost:8080/ords/doc-pdb1/document_api.upload

# 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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!