Hot File

File Upload and Download Procedures in Oracle Database 10g

View: 736    Dowload: 0   Comment: 0   Post by: naruto  
Author: none   Category: Oracle   Fields: Education - Schools

0 point/3 review File has been tested

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,

Introduction

/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));
  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 a the file name with a generated folder name to reduce the posibility 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

File Upload and Download Procedures in Oracle Database 10g

File Upload and Download Procedures in Oracle Database 10g Posted on 20-03-2014  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, 5/10 736

Comment:

To comment you must be logged in members.

Files with category

  • The basic functions in Oracle ( P3 )

    View: 583    Download: 0   Comment: 0   Author: none  

    The basic functions in Oracle ( P3 )

    Category: Oracle
    Fields: Other

    0/2 review
    The basic functions in Oracle ( P3 ) Blog / MSSQL database - oracle - This article is a sequel of the song " The basic functions in Oracle ( P2 ) " . In this section alone will solve all the functions and character strings in group 1 . Are you ready...

  • The basic functions in Oracle (P2)

    View: 634    Download: 0   Comment: 0   Author: none  

    The basic functions in Oracle (P2)

    Category: Oracle
    Fields: Other

    0/4 review
    The basic functions in Oracle ( P2 ) Blog / MSSQL database - oracle - Today 2Cwebvn please write to the jaw in a series on Oracle , the first post we have a vision of all the basic functions in Oracle , today I will go all details about the group is...

  • The basic functions in Oracle (P1)

    View: 507    Download: 0   Comment: 0   Author: none  

    The basic functions in Oracle (P1)

    Category: Oracle
    Fields: Other

    0/2 review
    The basic functions in Oracle ( P1 ) Blog / MSSQL database - oracle - Today 2Cwebvn would like to introduce you to the basic functions of Oracle , said that the basic function but here is a combination of functions in Oracle . This article is quite...

  • Oracle 10g Grid Control Installation On Red Hat Enterprise Linux 3 (RHEL3)

    View: 509    Download: 0   Comment: 0   Author: none  

    Oracle 10g Grid Control Installation On Red Hat Enterprise Linux 3 (RHEL3)

    Category: Oracle
    Fields: Education - Schools

    0/2 review
    In this article I'll describe the installation of Oracle 10g Grid Control on Red Hat Enterprise Linux 3 (RHEL3). The article is based on a server installation with a minimum of 2G swap and the following package groups installed.

  • Oracle Database 10g Release 2 (10.2.0.1) Installation On Fedora Core 4 (FC4)

    View: 509    Download: 0   Comment: 0   Author: none  

    Oracle Database 10g Release 2 (10.2.0.1) Installation On Fedora Core 4 (FC4)

    Category: Oracle
    Fields: Education - Schools

    0/2 review
    In this article I'll describe the installation of Oracle Database 10g Release 2 (10.2.0.1) on Fedora Core 4. The article is based on a Fedora Core 4 Server Installation with a minimum of 2G swap, secure Linux disabled and the following package groups...

  • MERGE Statement Enhancements in Oracle Database 10g

    View: 485    Download: 0   Comment: 0   Author: none  

    MERGE Statement Enhancements in Oracle Database 10g

    Category: Oracle
    Fields: Education - Schools

    0/2 review
    Oracle 10g includes a number of amendments to the MERGE statement making it more flexible.

  • Installation Enhancements in Oracle Database 10g

    View: 560    Download: 0   Comment: 0   Author: none  

    Installation Enhancements in Oracle Database 10g

    Category: Oracle
    Fields: Education - Schools

    0/2 review
    Installation of the Oracle 10g database software has been enhanced in several ways. This articles provides a brief summary of the improvements

  • File Upload and Download Procedures in Oracle Database 10g

    View: 737    Download: 0   Comment: 0   Author: none  

    File Upload and Download Procedures in Oracle Database 10g

    Category: Oracle
    Fields: Education - Schools

    0/3 review
    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...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd