amazon_aws_s3_pkg with SSL Support

CREATE OR REPLACE PACKAGE BODY amazon_aws_s3_pkg
AS

  /*

  Purpose:   PL/SQL wrapper package for Amazon AWS S3 API

  Remarks:   inspired by the whitepaper "Building an Amazon S3 Client with Application Express 4.0" by Jason Straub
             see http://jastraub.blogspot.com/2011/01/building-amazon-s3-client-with.html

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     09.01.2011  Created
  SWS     05.09.2014  Added capabilities to use https and Oracle Wallet

  */

  g_orcl_wallet_path       CONSTANT VARCHAR2(255) := 'file:/app/oracle/product/11.2.0/db_1/wallet';
  g_orcl_wallet_pw         CONSTANT VARCHAR2(255) := 'WALLET_PASSWORD';
  g_aws_url_http           CONSTANT VARCHAR2(255) := 'http://'; -- Set to either http:// or https://
  g_aws_url_s3             CONSTANT VARCHAR2(255) := g_aws_url_http || 's3.amazonaws.com/';
  g_aws_host_s3            CONSTANT VARCHAR2(255) := 's3.amazonaws.com';
  g_aws_namespace_s3       CONSTANT VARCHAR2(255) := 'http://s3.amazonaws.com/doc/2006-03-01/';
  g_aws_namespace_s3_full  CONSTANT VARCHAR2(255) := 'xmlns="' || g_aws_namespace_s3 || '"';
  g_date_format_xml        CONSTANT VARCHAR2(30)  := 'YYYY-MM-DD"T"HH24:MI:SS".000Z"';


PROCEDURE raise_error (p_error_message IN VARCHAR2)
AS
BEGIN

  /*

  Purpose:   raise error

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     15.01.2011  Created

  */

  raise_application_error (-20000, p_error_message);

END raise_error;


PROCEDURE check_for_errors (p_clob IN clob)
AS
  l_xml xmltype;
BEGIN

  /*

  Purpose:   check for errors (clob)

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     15.01.2011  Created

  */

  IF (p_clob IS NOT NULL) AND (LENGTH(p_clob) > 0) THEN

    l_xml := xmltype (p_clob);

    IF l_xml.EXISTSNODE('/Error') = 1 THEN
      debug_pkg.print (l_xml);
      raise_error (l_xml.EXTRACT('/Error/Message/text()').getstringval());
    END IF;

  END IF;

END check_for_errors;


PROCEDURE check_for_errors (p_xml IN xmltype)
AS
BEGIN

  /*

  Purpose:   check for errors (XMLType)

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     15.01.2011  Created

  */

  IF p_xml.EXISTSNODE('/Error') = 1 THEN
    debug_pkg.print (p_xml);
    raise_error (p_xml.EXTRACT('/Error/Message/text()').getstringval());
  END IF;

END check_for_errors;


FUNCTION check_for_redirect (p_clob IN clob) RETURN VARCHAR2
AS
  l_xml                          xmltype;
  l_returnvalue                  VARCHAR2(4000);
BEGIN

  /*

  Purpose:   check for redirect

  Remarks:   Used by the "delete bucket" procedure, by Jeffrey Kemp
             see http://code.google.com/p/plsql-utils/issues/detail?id=14
             "One thing I found when testing was that if the bucket is not in the US standard region,
              Amazon seems to respond with a TemporaryRedirect error.
              If the same request is re-requested to the indicated URL it works."

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     16.02.2013  Created, based on code by Jeffrey Kemp

  */

  IF (p_clob IS NOT NULL) AND (LENGTH(p_clob) > 0) THEN

    l_xml := xmltype (p_clob);

    IF l_xml.EXISTSNODE('/Error') = 1 THEN

      IF l_xml.EXTRACT('/Error/Code/text()').getStringVal = 'TemporaryRedirect' THEN
        l_returnvalue := l_xml.EXTRACT('/Error/Endpoint/text()').getStringVal;
        debug_pkg.printf('Temporary Redirect to %1', l_returnvalue);
      END IF;

    END IF;

  END IF;

  RETURN l_returnvalue;

END check_for_redirect;


FUNCTION make_request (p_url IN VARCHAR2,
                       p_http_method IN VARCHAR2,
                       p_header_names IN t_str_array,
                       p_header_values IN t_str_array,
                       p_request_blob IN blob := NULL,
                       p_request_clob IN clob := NULL) RETURN clob
AS
  l_http_req     UTL_HTTP.req;
  l_http_resp    UTL_HTTP.resp;

  l_amount       BINARY_INTEGER := 32000;
  l_offset       INTEGER := 1;
  l_buffer       VARCHAR2(32000);
  l_buffer_raw   RAW(32000);

  l_response     VARCHAR2(2000);
  l_returnvalue  clob;

BEGIN

  /*

  Purpose:   make HTTP request

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     15.01.2011  Created

  */

  debug_pkg.printf('%1 %2', p_http_method, p_url);

  -- Call to Oracle Wallet
  IF g_aws_url_http = 'https://' THEN
    UTL_HTTP.SET_WALLET(g_orcl_wallet_path, g_orcl_wallet_pw);
  END IF;

  l_http_req := UTL_HTTP.begin_request(p_url, p_http_method);

  IF p_header_names.COUNT > 0 THEN
    FOR i IN p_header_names.FIRST .. p_header_names.LAST LOOP
      debug_pkg.printf('%1: %2', p_header_names(i), p_header_values(i));
      UTL_HTTP.set_header(l_http_req, p_header_names(i), p_header_values(i));
    END LOOP;
  END IF;

  IF p_request_blob IS NOT NULL THEN

    BEGIN
      LOOP
        DBMS_LOB.read (p_request_blob, l_amount, l_offset, l_buffer_raw);
        UTL_HTTP.write_raw (l_http_req, l_buffer_raw);
        l_offset := l_offset + l_amount;
        l_amount := 32000;
      END LOOP;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;

  ELSIF p_request_clob IS NOT NULL THEN

    BEGIN
      LOOP
        DBMS_LOB.read (p_request_clob, l_amount, l_offset, l_buffer);
        UTL_HTTP.write_text (l_http_req, l_buffer);
        l_offset := l_offset + l_amount;
        l_amount := 32000;
      END LOOP;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;

  END IF;

  l_http_resp := UTL_HTTP.get_response(l_http_req);

  DBMS_LOB.createtemporary (l_returnvalue, FALSE);
  DBMS_LOB.OPEN (l_returnvalue, DBMS_LOB.lob_readwrite);

  BEGIN
    LOOP
      UTL_HTTP.read_text (l_http_resp, l_buffer);
      DBMS_LOB.writeappend (l_returnvalue, LENGTH(l_buffer), l_buffer);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE <> -29266 THEN
        RAISE;
      END IF;
  END;

  UTL_HTTP.end_response (l_http_resp);

  RETURN l_returnvalue;

END make_request;


FUNCTION get_url (p_bucket_name IN VARCHAR2,
                  p_key IN VARCHAR2 := NULL) RETURN VARCHAR2
AS
  l_returnvalue VARCHAR2(4000);
BEGIN

  /*

  Purpose:   construct a valid URL

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     03.03.2011  Created

  */

  l_returnvalue := g_aws_url_http || p_bucket_name || '.' || g_aws_host_s3 || '/' || p_key;

  RETURN l_returnvalue;

END get_url;


FUNCTION get_host (p_bucket_name IN VARCHAR2) RETURN VARCHAR2
AS
  l_returnvalue VARCHAR2(4000);
BEGIN

  /*

  Purpose:   construct a valid host string

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     03.03.2011  Created

  */

  l_returnvalue := p_bucket_name || '.' || g_aws_host_s3;

  RETURN l_returnvalue;

END get_host;


FUNCTION get_bucket_list RETURN t_bucket_list
AS
  l_clob                         clob;
  l_xml                          xmltype;

  l_date_str                     VARCHAR2(255);
  l_auth_str                     VARCHAR2(255);

  l_header_names                 t_str_array := t_str_array();
  l_header_values                t_str_array := t_str_array();

  l_count                        PLS_INTEGER := 0;
  l_returnvalue                  t_bucket_list;

BEGIN

  /*

  Purpose:   get buckets

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     09.01.2011  Created

  */

  l_date_str := amazon_aws_auth_pkg.get_date_string;
  l_auth_str := amazon_aws_auth_pkg.get_auth_string ('GET' || CHR(10) || CHR(10) || CHR(10) || l_date_str || CHR(10) || '/');

  l_header_names.extend;
  l_header_names(1) := 'Host';
  l_header_values.extend;
  l_header_values(1) := g_aws_host_s3;

  l_header_names.extend;
  l_header_names(2) := 'Date';
  l_header_values.extend;
  l_header_values(2) := l_date_str;

  l_header_names.extend;
  l_header_names(3) := 'Authorization';
  l_header_values.extend;
  l_header_values(3) := l_auth_str;

  l_clob := make_request (g_aws_url_s3, 'GET', l_header_names, l_header_values, NULL);

  IF (l_clob IS NOT NULL) AND (LENGTH(l_clob) > 0) THEN

    l_xml := xmltype (l_clob);

    check_for_errors (l_xml);

    FOR l_rec IN (
      SELECT EXTRACTVALUE(VALUE(t), '*/Name', g_aws_namespace_s3_full) AS bucket_name,
        EXTRACTVALUE(VALUE(t), '*/CreationDate', g_aws_namespace_s3_full) AS creation_date
      FROM TABLE(XMLSEQUENCE(l_xml.EXTRACT('//ListAllMyBucketsResult/Buckets/Bucket', g_aws_namespace_s3_full))) t
      ) LOOP
      l_count := l_count + 1;
      l_returnvalue(l_count).bucket_name := l_rec.bucket_name;
      l_returnvalue(l_count).creation_date := TO_DATE(l_rec.creation_date, g_date_format_xml);
    END LOOP;

  END IF;

  RETURN l_returnvalue;

END get_bucket_list;


FUNCTION get_bucket_tab RETURN t_bucket_tab pipelined
AS
  l_bucket_list                  t_bucket_list;
BEGIN

  /*

  Purpose:   get buckets

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     19.01.2011  Created

  */

  l_bucket_list := get_bucket_list;

  FOR i IN 1 .. l_bucket_list.COUNT LOOP
    pipe ROW (l_bucket_list(i));
  END LOOP;

  RETURN;

END get_bucket_tab;


PROCEDURE new_bucket (p_bucket_name IN VARCHAR2,
                      p_region IN VARCHAR2 := NULL)
AS

  l_request_body                 clob;
  l_clob                         clob;
  l_xml                          xmltype;

  l_date_str                     VARCHAR2(255);
  l_auth_str                     VARCHAR2(255);

  l_header_names                 t_str_array := t_str_array();
  l_header_values                t_str_array := t_str_array();

BEGIN

  /*

  Purpose:   create bucket

  Remarks:   *** bucket names must be unique across all of Amazon S3 ***

             see http://docs.amazonwebservices.com/AmazonS3/latest/API/RESTBucketPUT.html

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     15.01.2011  Created

  */

  l_date_str := amazon_aws_auth_pkg.get_date_string;

  IF p_region IS NOT NULL THEN
    l_auth_str := amazon_aws_auth_pkg.get_auth_string ('PUT' || CHR(10) || CHR(10) || 'text/plain' || CHR(10) || l_date_str || CHR(10) || '/' || p_bucket_name || '/');
  ELSE
    l_auth_str := amazon_aws_auth_pkg.get_auth_string ('PUT' || CHR(10) || CHR(10) || CHR(10) || l_date_str || CHR(10) || '/' || p_bucket_name || '/');
  END IF;

  l_header_names.extend;
  l_header_names(1) := 'Host';
  l_header_values.extend;
  l_header_values(1) := get_host(p_bucket_name);

  l_header_names.extend;
  l_header_names(2) := 'Date';
  l_header_values.extend;
  l_header_values(2) := l_date_str;

  l_header_names.extend;
  l_header_names(3) := 'Authorization';
  l_header_values.extend;
  l_header_values(3) := l_auth_str;

  IF p_region IS NOT NULL THEN

    l_request_body := '<CreateBucketConfiguration ' || g_aws_namespace_s3_full || '><LocationConstraint>' || p_region || '</LocationConstraint></CreateBucketConfiguration>';

    l_header_names.extend;
    l_header_names(4) := 'Content-Type';
    l_header_values.extend;
    l_header_values(4) := 'text/plain';

    l_header_names.extend;
    l_header_names(5) := 'Content-Length';
    l_header_values.extend;
    l_header_values(5) := LENGTH(l_request_body);

  END IF;

  l_clob := make_request (get_url (p_bucket_name), 'PUT', l_header_names, l_header_values, NULL, l_request_body);

  check_for_errors (l_clob);

END new_bucket;


FUNCTION get_bucket_region (p_bucket_name IN VARCHAR2) RETURN VARCHAR2
AS

  l_clob                         clob;
  l_xml                          xmltype;

  l_date_str                     VARCHAR2(255);
  l_auth_str                     VARCHAR2(255);

  l_header_names                 t_str_array := t_str_array();
  l_header_values                t_str_array := t_str_array();

  l_returnvalue                  VARCHAR2(255);

BEGIN

  /*

  Purpose:   get bucket region

  Remarks:   see http://docs.amazonwebservices.com/AmazonS3/latest/API/RESTBucketGETlocation.html

             note that the region will be NULL for buckets in the default region (US)

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     03.03.2011  Created

  */

  l_date_str := amazon_aws_auth_pkg.get_date_string;
  l_auth_str := amazon_aws_auth_pkg.get_auth_string ('GET' || CHR(10) || CHR(10) || CHR(10) || l_date_str || CHR(10) || '/' || p_bucket_name || '/?location');

  l_header_names.extend;
  l_header_names(1) := 'Host';
  l_header_values.extend;
  l_header_values(1) := get_host(p_bucket_name);

  l_header_names.extend;
  l_header_names(2) := 'Date';
  l_header_values.extend;
  l_header_values(2) := l_date_str;

  l_header_names.extend;
  l_header_names(3) := 'Authorization';
  l_header_values.extend;
  l_header_values(3) := l_auth_str;

  l_clob := make_request (get_url(p_bucket_name) || '?location', 'GET', l_header_names, l_header_values);

  IF (l_clob IS NOT NULL) AND (LENGTH(l_clob) > 0) THEN

    l_xml := xmltype (l_clob);

    check_for_errors (l_xml);

    IF l_xml.EXISTSNODE('/LocationConstraint', g_aws_namespace_s3_full) = 1 THEN
      -- see http://pbarut.blogspot.com/2006/11/ora-30625-and-xmltype.html
      IF l_xml.EXTRACT('/LocationConstraint/text()', g_aws_namespace_s3_full) IS NOT NULL THEN
        l_returnvalue := l_xml.EXTRACT('/LocationConstraint/text()', g_aws_namespace_s3_full).getstringval();
      ELSE
        l_returnvalue := NULL;
      END IF;
    END IF;

  END IF;

  RETURN l_returnvalue;

END get_bucket_region;


PROCEDURE get_object_list (p_bucket_name IN VARCHAR2,
                           p_prefix IN VARCHAR2,
                           p_max_keys IN NUMBER,
                           p_list OUT t_object_list,
                           p_more_marker IN OUT VARCHAR2)
AS
  l_clob                         clob;
  l_xml                          xmltype;

  l_date_str                     VARCHAR2(255);
  l_auth_str                     VARCHAR2(255);

  l_header_names                 t_str_array := t_str_array();
  l_header_values                t_str_array := t_str_array();

  l_count                        PLS_INTEGER := 0;
  l_returnvalue                  t_object_list;

BEGIN

  /*

  Purpose:   get objects

  Remarks:   see http://docs.amazonwebservices.com/AmazonS3/latest/API/index.html?RESTObjectGET.html

             see http://code.google.com/p/plsql-utils/issues/detail?id=16

             "I've rewritten get_object_list as an internal procedure that uses the "marker" parameter,
             so that get_object_tab can now call the Amazon API multiple times to return the complete set of objects.
             The get_object_list function remains functionally unchanged in this version - it just returns one set of objects -
             it could be enhanced to support the marker parameter as well, I guess,
             but I'd rather not expose that sort of thing to the caller personally.
             The nice thing about the pipelined function is that the subsequent calls to Amazon
             will only be executed if the client actually fetches all the rows."

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     15.01.2011  Created
  JKEMP   14.08.2012  Rewritten as private procedure, see remarks above

  */

  l_date_str := amazon_aws_auth_pkg.get_date_string;
  l_auth_str := amazon_aws_auth_pkg.get_auth_string ('GET' || CHR(10) || CHR(10) || CHR(10) || l_date_str || CHR(10) || '/' || p_bucket_name || '/');

  l_header_names.extend;
  l_header_names(1) := 'Host';
  l_header_values.extend;
  l_header_values(1) := get_host (p_bucket_name);

  l_header_names.extend;
  l_header_names(2) := 'Date';
  l_header_values.extend;
  l_header_values(2) := l_date_str;

  l_header_names.extend;
  l_header_names(3) := 'Authorization';
  l_header_values.extend;
  l_header_values(3) := l_auth_str;

  IF p_more_marker IS NOT NULL THEN
    l_clob := make_request (get_url(p_bucket_name) || '?marker=' || p_more_marker || '&max-keys=' || p_max_keys || '&prefix=' || UTL_URL.escape(p_prefix), 'GET', l_header_names, l_header_values, NULL);
  ELSE
    l_clob := make_request (get_url(p_bucket_name) || '?max-keys=' || p_max_keys || '&prefix=' || UTL_URL.escape(p_prefix), 'GET', l_header_names, l_header_values, NULL);
  END IF;

  IF (l_clob IS NOT NULL) AND (LENGTH(l_clob) > 0) THEN

    l_xml := xmltype (l_clob);

    check_for_errors (l_xml);

    FOR l_rec IN (
      SELECT EXTRACTVALUE(VALUE(t), '*/Key', g_aws_namespace_s3_full) AS key,
        EXTRACTVALUE(VALUE(t), '*/Size', g_aws_namespace_s3_full) AS size_bytes,
        EXTRACTVALUE(VALUE(t), '*/LastModified', g_aws_namespace_s3_full) AS last_modified
      FROM TABLE(XMLSEQUENCE(l_xml.EXTRACT('//ListBucketResult/Contents', g_aws_namespace_s3_full))) t
      ) LOOP
      l_count := l_count + 1;
      l_returnvalue(l_count).key := l_rec.key;
      l_returnvalue(l_count).size_bytes := l_rec.size_bytes;
      l_returnvalue(l_count).last_modified := TO_DATE(l_rec.last_modified, g_date_format_xml);
    END LOOP;

    -- check if this is the last set of data or not

    l_xml := l_xml.EXTRACT('//ListBucketResult/IsTruncated/text()', g_aws_namespace_s3_full);

    IF l_xml IS NOT NULL AND l_xml.getStringVal = 'true' THEN
      p_more_marker := l_returnvalue(l_returnvalue.LAST).key;
    END IF;

  END IF;

  p_list := l_returnvalue;

END get_object_list;


FUNCTION get_object_list (p_bucket_name IN VARCHAR2,
                          p_prefix IN VARCHAR2 := NULL,
                          p_max_keys IN NUMBER := NULL) RETURN t_object_list
AS
  l_object_list                  t_object_list;
  l_more_marker                  VARCHAR2(4000);
BEGIN

  /*

  Purpose:   get objects

  Remarks:   see http://docs.amazonwebservices.com/AmazonS3/latest/API/index.html?RESTObjectGET.html

  Who     Date        Description
  ------  ----------  -------------------------------------
  JKEMP   14.08.2012  Created

  */

  get_object_list (
    p_bucket_name => p_bucket_name,
    p_prefix      => p_prefix,
    p_max_keys    => p_max_keys,
    p_list        => l_object_list,
    p_more_marker => l_more_marker --ignored by this function
  );

  RETURN l_object_list;

END get_object_list;


FUNCTION get_object_tab (p_bucket_name IN VARCHAR2,
                         p_prefix IN VARCHAR2 := NULL,
                         p_max_keys IN NUMBER := NULL) RETURN t_object_tab pipelined
AS
  l_object_list                  t_object_list;
  l_more_marker                  VARCHAR2(4000);
BEGIN

  /*

  Purpose:   get objects

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     19.01.2011  Created

  */

  LOOP

    get_object_list (
      p_bucket_name => p_bucket_name,
      p_prefix      => p_prefix,
      p_max_keys    => p_max_keys,
      p_list        => l_object_list,
      p_more_marker => l_more_marker
      );

    FOR i IN 1 .. l_object_list.COUNT LOOP
      pipe ROW (l_object_list(i));
    END LOOP;

    EXIT WHEN l_more_marker IS NULL;

  END LOOP;

  RETURN;

END get_object_tab;


FUNCTION get_download_url (p_bucket_name IN VARCHAR2,
                           p_key IN VARCHAR2,
                           p_expiry_date IN DATE) RETURN VARCHAR2
AS
  l_returnvalue                  VARCHAR2(4000);
  l_key                          VARCHAR2(4000) := UTL_URL.escape (p_key);
  l_epoch                        NUMBER;
  l_signature                    VARCHAR2(4000);
BEGIN

  /*

  Purpose:   get download URL

  Remarks:   see http://s3.amazonaws.com/doc/s3-developer-guide/RESTAuthentication.html

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     15.01.2011  Created

  */

  l_epoch := amazon_aws_auth_pkg.get_epoch (p_expiry_date);
  l_signature := amazon_aws_auth_pkg.get_signature ('GET' || CHR(10) || CHR(10) || CHR(10) || l_epoch || CHR(10) || '/' || p_bucket_name || '/' || l_key);

  l_returnvalue := get_url (p_bucket_name, l_key)
    || '?AWSAccessKeyId=' || amazon_aws_auth_pkg.get_aws_id
    || '&Expires=' || l_epoch
    || '&Signature=' || wwv_flow_utilities.url_encode2 (l_signature);

  RETURN l_returnvalue;

END get_download_url;


PROCEDURE new_object (p_bucket_name IN VARCHAR2,
                      p_key IN VARCHAR2,
                      p_object IN blob,
                      p_content_type IN VARCHAR2,
                      p_acl IN VARCHAR2 := NULL)
AS

  l_key                          VARCHAR2(4000) := UTL_URL.escape (p_key);

  l_clob                         clob;
  l_xml                          xmltype;

  l_date_str                     VARCHAR2(255);
  l_auth_str                     VARCHAR2(255);

  l_header_names                 t_str_array := t_str_array();
  l_header_values                t_str_array := t_str_array();

BEGIN

  /*

  Purpose:   upload new object

  Remarks:   see  http://docs.amazonwebservices.com/AmazonS3/latest/API/RESTObjectPUT.html

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     16.01.2011  Created

  */

  l_date_str := amazon_aws_auth_pkg.get_date_string;

  IF p_acl IS NOT NULL THEN
    l_auth_str := amazon_aws_auth_pkg.get_auth_string ('PUT' || CHR(10) || CHR(10) || p_content_type || CHR(10) || l_date_str || CHR(10) || 'x-amz-acl:' || p_acl || CHR(10) || '/' || p_bucket_name || '/' || l_key);
  ELSE
    l_auth_str := amazon_aws_auth_pkg.get_auth_string ('PUT' || CHR(10) || CHR(10) || p_content_type || CHR(10) || l_date_str || CHR(10) || '/' || p_bucket_name || '/' || l_key);
  END IF;

  l_header_names.extend;
  l_header_names(1) := 'Host';
  l_header_values.extend;
  l_header_values(1) := get_host(p_bucket_name);

  l_header_names.extend;
  l_header_names(2) := 'Date';
  l_header_values.extend;
  l_header_values(2) := l_date_str;

  l_header_names.extend;
  l_header_names(3) := 'Authorization';
  l_header_values.extend;
  l_header_values(3) := l_auth_str;

  l_header_names.extend;
  l_header_names(4) := 'Content-Type';
  l_header_values.extend;
  l_header_values(4) := NVL(p_content_type, 'application/octet-stream');

  l_header_names.extend;
  l_header_names(5) := 'Content-Length';
  l_header_values.extend;
  l_header_values(5) := DBMS_LOB.getlength(p_object);

  IF p_acl IS NOT NULL THEN
    l_header_names.extend;
    l_header_names(6) := 'x-amz-acl';
    l_header_values.extend;
    l_header_values(6) := p_acl;
  END IF;

  l_clob := make_request (get_url (p_bucket_name, l_key), 'PUT', l_header_names, l_header_values, p_object);

  check_for_errors (l_clob);

END new_object;


PROCEDURE delete_object (p_bucket_name IN VARCHAR2,
                         p_key IN VARCHAR2)
AS

  l_key                          VARCHAR2(4000) := UTL_URL.escape (p_key);

  l_clob                         clob;
  l_xml                          xmltype;

  l_date_str                     VARCHAR2(255);
  l_auth_str                     VARCHAR2(255);

  l_header_names                 t_str_array := t_str_array();
  l_header_values                t_str_array := t_str_array();

BEGIN

  /*

  Purpose:   delete object

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     18.01.2011  Created

  */

  l_date_str := amazon_aws_auth_pkg.get_date_string;
  l_auth_str := amazon_aws_auth_pkg.get_auth_string ('DELETE' || CHR(10) || CHR(10) || CHR(10) || l_date_str || CHR(10) || '/' || p_bucket_name || '/' || l_key);

  l_header_names.extend;
  l_header_names(1) := 'Host';
  l_header_values.extend;
  l_header_values(1) := get_host(p_bucket_name);

  l_header_names.extend;
  l_header_names(2) := 'Date';
  l_header_values.extend;
  l_header_values(2) := l_date_str;

  l_header_names.extend;
  l_header_names(3) := 'Authorization';
  l_header_values.extend;
  l_header_values(3) := l_auth_str;

  l_clob := make_request (get_url(p_bucket_name, l_key), 'DELETE', l_header_names, l_header_values);

  check_for_errors (l_clob);

END delete_object;


FUNCTION get_object (p_bucket_name IN VARCHAR2,
                     p_key IN VARCHAR2) RETURN blob
AS
  l_returnvalue blob;
BEGIN

  /*

  Purpose:   get object

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     20.01.2011  Created

  */

  l_returnvalue := http_util_pkg.get_blob_from_url (get_download_url (p_bucket_name, p_key, SYSDATE + 1));

  RETURN l_returnvalue;

END get_object;


PROCEDURE delete_bucket (p_bucket_name IN VARCHAR2)
AS
  l_clob                         clob;
  l_date_str                     VARCHAR2(255);
  l_auth_str                     VARCHAR2(255);
  l_header_names                 t_str_array := t_str_array();
  l_header_values                t_str_array := t_str_array();
  l_endpoint                     VARCHAR2(255);
BEGIN

  /*

  Purpose:   delete bucket

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  JKEMP   09.08.2012  Created

  */

  l_date_str := amazon_aws_auth_pkg.get_date_string;
  l_auth_str := amazon_aws_auth_pkg.get_auth_string ('DELETE' || CHR(10) || CHR(10) || CHR(10) || l_date_str || CHR(10) || '/' || p_bucket_name || '/');

  l_header_names.extend;
  l_header_names(1) := 'Host';
  l_header_values.extend;
  l_header_values(1) := get_host(p_bucket_name);

  l_header_names.extend;
  l_header_names(2) := 'Date';
  l_header_values.extend;
  l_header_values(2) := l_date_str;

  l_header_names.extend;
  l_header_names(3) := 'Authorization';
  l_header_values.extend;
  l_header_values(3) := l_auth_str;

  l_clob := make_request (get_url(p_bucket_name), 'DELETE', l_header_names, l_header_values);

  l_endpoint := check_for_redirect (l_clob);

  IF l_endpoint IS NOT NULL THEN
    l_clob := make_request (g_aws_url_http || l_endpoint || '/', 'DELETE', l_header_names, l_header_values);
  END IF;

  check_for_errors (l_clob);

END delete_bucket;


FUNCTION get_object_acl (p_bucket_name IN VARCHAR2,
                         p_key IN VARCHAR2) RETURN xmltype
AS

  l_clob                         clob;
  l_xml                          xmltype;

  l_date_str                     VARCHAR2(255);
  l_auth_str                     VARCHAR2(255);

  l_header_names                 t_str_array := t_str_array();
  l_header_values                t_str_array := t_str_array();

  l_returnvalue                  xmltype;

BEGIN

  /*

  Purpose:   get object ACL

  Remarks:  get the ACL for an object (private - used by get_object_owner, get_object_grantee_list, get_object_grantee_tab)

  Example return value:

  <AccessControlPolicy xmlns="http://s3.amazonaws.com/doc/2006-03-01/">
    <Owner>
      <ID>c244a7539c1fc912a06691246c90cb93629690ee4703efac8f08e6ff4cb48ef1</ID>
      <DisplayName>jeffreykemp</DisplayName>
    </Owner>
    <AccessControlList>
      <Grant>
        <Grantee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="CanonicalUser">
          <ID>c244a7539c1fc912a06691246c90cb93629690ee4703efac8f08e6ff4cb48ef1</ID>
          <DisplayName>jeffreykemp</DisplayName>
        </Grantee>
        <Permission>FULL_CONTROL</Permission>
      </Grant>
      <Grant>
        <Grantee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Group">
          <URI>http://acs.amazonaws.com/groups/global/AllUsers</URI>
        </Grantee>
        <Permission>READ</Permission>
      </Grant>
    </AccessControlList>
  </AccessControlPolicy>

  Who     Date        Description
  ------  ----------  -------------------------------------
  JKEMP   10.08.2012  Created

  */

  l_date_str := amazon_aws_auth_pkg.get_date_string;
  l_auth_str := amazon_aws_auth_pkg.get_auth_string ('GET' || CHR(10) || CHR(10) || CHR(10) || l_date_str || CHR(10) || '/' || p_bucket_name || '/' || p_key || '?acl');

  l_header_names.extend;
  l_header_names(1) := 'Host';
  l_header_values.extend;
  l_header_values(1) := g_aws_host_s3;

  l_header_names.extend;
  l_header_names(2) := 'Date';
  l_header_values.extend;
  l_header_values(2) := l_date_str;

  l_header_names.extend;
  l_header_names(3) := 'Authorization';
  l_header_values.extend;
  l_header_values(3) := l_auth_str;

  l_clob := make_request (get_url(p_bucket_name, p_key) || '?acl', 'GET', l_header_names, l_header_values, NULL);

  IF (l_clob IS NOT NULL) AND (LENGTH(l_clob) > 0) THEN

    l_xml := xmltype (l_clob);
    check_for_errors (l_xml);
    l_returnvalue := l_xml;

  END IF;

  RETURN l_returnvalue;

END get_object_acl;


FUNCTION get_object_owner (p_bucket_name IN VARCHAR2,
                           p_key IN VARCHAR2) RETURN t_owner
AS
  l_xml                          xmltype;
  l_returnvalue                  t_owner;
BEGIN

  /*

  Purpose:   get owner for an object

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  JKEMP   14.08.2012  Created

  */

  l_xml := get_object_acl (p_bucket_name, p_key);

  l_returnvalue.user_id := l_xml.EXTRACT('//AccessControlPolicy/Owner/ID/text()', g_aws_namespace_s3_full).getStringVal;
  l_returnvalue.user_name := l_xml.EXTRACT('//AccessControlPolicy/Owner/DisplayName/text()', g_aws_namespace_s3_full).getStringVal;

  RETURN l_returnvalue;

END get_object_owner;


FUNCTION get_object_grantee_list (p_bucket_name IN VARCHAR2,
                                  p_key IN VARCHAR2) RETURN t_grantee_list
AS
  l_xml_namespace_s3_full        CONSTANT VARCHAR2(255) := 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"';
  l_xml                          xmltype;
  l_count                        PLS_INTEGER := 0;
  l_returnvalue                  t_grantee_list;
BEGIN

  /*

  Purpose:   get grantees for an object

  Remarks:   Each grantee will either be a Canonical User or a Group.
             A Canonical User has an ID and a DisplayName.
             A Group has a URI.
             Permission will be FULL_CONTROL, WRITE, or READ_ACP.

  Who     Date        Description
  ------  ----------  -------------------------------------
  JKEMP   14.08.2012  Created

  */

  l_xml := get_object_acl (p_bucket_name, p_key);

  FOR l_rec IN (
    SELECT EXTRACTVALUE(VALUE(t), '*/Grantee/@xsi:type', g_aws_namespace_s3_full || ' ' || l_xml_namespace_s3_full) AS grantee_type,
      EXTRACTVALUE(VALUE(t), '*/Grantee/ID', g_aws_namespace_s3_full) AS user_id,
      EXTRACTVALUE(VALUE(t), '*/Grantee/DisplayName', g_aws_namespace_s3_full) AS user_name,
      EXTRACTVALUE(VALUE(t), '*/Grantee/URI', g_aws_namespace_s3_full) AS group_uri,
      EXTRACTVALUE(VALUE(t), '*/Permission', g_aws_namespace_s3_full) AS permission
    FROM TABLE(XMLSEQUENCE(l_xml.EXTRACT('//AccessControlPolicy/AccessControlList/Grant', g_aws_namespace_s3_full))) t
    ) LOOP
    l_count := l_count + 1;
    l_returnvalue(l_count).grantee_type := l_rec.grantee_type;
    l_returnvalue(l_count).user_id := l_rec.user_id;
    l_returnvalue(l_count).user_name := l_rec.user_name;
    l_returnvalue(l_count).group_uri := l_rec.group_uri;
    l_returnvalue(l_count).permission := l_rec.permission;
  END LOOP;

  RETURN l_returnvalue;

END get_object_grantee_list;


FUNCTION get_object_grantee_tab (p_bucket_name IN VARCHAR2,
                                 p_key IN VARCHAR2) RETURN t_grantee_tab pipelined
AS
  l_grantee_list  t_grantee_list;
BEGIN

  /*

  Purpose:   get grantees for an object

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  JKEMP   14.08.2012  Created

  */

  l_grantee_list := get_object_grantee_list (p_bucket_name, p_key);

  FOR i IN 1 .. l_grantee_list.COUNT LOOP
    pipe ROW (l_grantee_list(i));
  END LOOP;

  RETURN;

END get_object_grantee_tab;


PROCEDURE set_object_acl (p_bucket_name IN VARCHAR2,
                          p_key IN VARCHAR2,
                          p_acl IN VARCHAR2)
AS
  l_key                          VARCHAR2(4000) := UTL_URL.escape (p_key);
  l_clob                         clob;
  l_xml                          xmltype;
  l_date_str                     VARCHAR2(255);
  l_auth_str                     VARCHAR2(255);
  l_header_names                 t_str_array := t_str_array();
  l_header_values                t_str_array := t_str_array();
BEGIN

  /*

  Purpose:   modify the access control list (owner and grantees) for an object

  Remarks:   see http://code.google.com/p/plsql-utils/issues/detail?id=17

  Who     Date        Description
  ------  ----------  -------------------------------------
  JKEMP   22.09.2012  Created

  */

  l_date_str := amazon_aws_auth_pkg.get_date_string;
  l_auth_str := amazon_aws_auth_pkg.get_auth_string ('PUT' || CHR(10) || CHR(10) || CHR(10) || l_date_str || CHR(10) || 'x-amz-acl:' || p_acl || CHR(10) || '/' || p_bucket_name || '/' || l_key || '?acl');

  l_header_names.extend;
  l_header_names(1) := 'Host';
  l_header_values.extend;
  l_header_values(1) := get_host(p_bucket_name);

  l_header_names.extend;
  l_header_names(2) := 'Date';
  l_header_values.extend;
  l_header_values(2) := l_date_str;

  l_header_names.extend;
  l_header_names(3) := 'Authorization';
  l_header_values.extend;
  l_header_values(3) := l_auth_str;

  l_header_names.extend;
  l_header_names(4) := 'x-amz-acl';
  l_header_values.extend;
  l_header_values(4) := p_acl;

  l_clob := make_request (get_url(p_bucket_name, l_key) || '?acl', 'PUT', l_header_names, l_header_values);

  check_for_errors (l_clob);

END set_object_acl;


END amazon_aws_s3_pkg;
/