Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement