Amazon RDS for Oracle における UTL_FILE 問題の解決

$ openssl s_client -connect s3.eu-west-2.amazonaws.com:443

CONNECTED(00000003)
depth=2 C = IE, O = Baltimore, OU = CyberTrust, CN = Baltimore CyberTrust Root
verify return:1
depth=1 C = US, O = DigiCert Inc, OU = www.digicert.com, CN = DigiCert Baltimore CA-2 G2
verify return:1
depth=0 C = US, ST = Washington, L = Seattle, O = Amazon.com Inc., CN = *.s3.eu-west-2.amazonaws.com
verify return:1
---
Certificate chain
 0 s:/C=US/ST=Washington/L=Seattle/O=Amazon.com Inc./CN=*.s3.eu-west-2.amazonaws.com
   i:/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert Baltimore CA-2 G2
 1 s:/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert Baltimore CA-2 G2
   i:/C=IE/O=Baltimore/OU=CyberTrust/CN=Baltimore CyberTrust Root
---

$ orapki wallet create -wallet -pwd S3-Oracle -auto_login

$ orapki wallet add    -wallet -trusted_cert -cert BaltimoreCyberTrustRoot.crt -pwd S3-Oracle

$ orapki wallet add    -wallet Wallet/ -trusted_cert -cert DigiCertBaltimoreCA-2G2.crt -pwd S3-Oracle

$ orapki wallet display -wallet
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates.All rights reserved.

Requested Certificates: 
User Certificates:
Trusted Certificates: 
Subject:    CN=DigiCert Baltimore CA-2 G2,OU=www.digicert.com,O=DigiCert Inc,C=US
Subject:    CN=Baltimore CyberTrust Root,OU=CyberTrust,O=Baltimore,C=IE

$ vi Copy-eWallet.pl

...
my $dirname="SSL_WALLET_DIR";
...
my $filename="ewallet.p12";
...

# install necessary packages

$ su -
$ yum install -y perl-DBI
$ yum install -y cpan

# configure environment variables for root

$ export ORACLE_BASE=/usr/lib/oracle
$ export ORACLE_HOME=$ORACLE_BASE/12.2/client64
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ export TNS_ADMIN=$ORACLE_HOME/lib/network/admin
$ export PERL_MM_USE_DEFAULT=1
$ export DYLD_LIBRARY_PATH=$LD_LIBRARY_PATH

# copy a make file into the client area

$ cp /usr/share/oracle/12.2/client64/demo/demo.mk /usr/share/oracle/12.2/client64/demo.mk

#Install Necessary cpan components

$ cpan IO::Tee
$ cpan DBD::Oracle

$ ./Copy-eWallet.pl \
  --USER mike \
  --PASSWD <password> \
  --TNS file-test \
  --LDIR /home/oracle/Wallet

Uploading file ewallet.p12
Files uploaded successfully
02/ size: 4096 bytes
ewallet.p12 size: 2360 bytes

$ sqlplus mike/<password>@file-test @AWS_RDS_TO_S3_PKG.pks

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production


Package created.

SQL> @AWS_RDS_TO_S3_PKG.pkb

Package body created.

SQL> exit;

$ sqlplus mike/<password>@file-test

SET SERVEROUT ON SIZE 20000
SET LINESIZE          132
SET PAGESIZE           64

DECLARE
    blBucketList    AWS_RDS_TO_S3_PKG.BUCKET_LIST;
BEGIN
    blBucketList := AWS_RDS_TO_S3_PKG.getBucketList;

    FOR i IN 1 .. blBucketList.count
    LOOP
        DBMS_OUTPUT.PUT('Bucket: ');
        DBMS_OUTPUT.PUT( blBucketList(i).bucket_name );
        DBMS_OUTPUT.PUT(' Created on: ');
        DBMS_OUTPUT.PUT_LINE( blBucketList(i).creation_date );
    END LOOP;
END;
/

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Bucket: db-freedom Created: 16-JUL-18
Bucket: demos.revittmk.aws.co.uk Created on: 05-JUN-18
Bucket: ec2-config.revittmk.aws.co.uk Created on: 29-JUN-18
Bucket: file-test.revittmk.aws.co.uk Created on: 27-JUL-18
Bucket: labs.revittmk.aws.co.uk Created on: 31-MAY-18

PL/SQL procedure successfully completed.

EXIT;

$ sqlplus mike/<password>@file-test

SET SERVEROUT ON SIZE 20000
SET LINESIZE          132
SET PAGESIZE           64

DECLARE
    olObjectList     AWS_RDS_TO_S3_PKG.OBJECT_LIST;
    bAllFiles        BOOLEAN;
BEGIN
    
    AWS_RDS_TO_S3_PKG.getObjectList
    (
        pBucket          => 'file-test.revittmk.aws.co.uk',
        pFilesRemaining  =>  bAllFiles,
        pObjectList      =>  olObjectList
    );

    for i in 1 .. olObjectList.count loop
        dbms_output.put('Object: ');
        dbms_output.put( olObjectList(i).key);
        dbms_output.put(' Size Bytes: ');
        dbms_output.put( olObjectList(i).size_bytes);
        dbms_output.put(' Modified on: ');
        dbms_output.put_line( olObjectList(i).last_modified);
    end loop;
 END;
/

PL/SQL procedure successfully completed.

EXIT;

$ sqlplus mike/<password>@file-test

BEGIN

    AWS_RDS_TO_S3_PKG.copyFile
    (
        'FILE_COPY',
        'test-file',
        'file-test.revittmk.aws.co.uk'
    );
END;
/

REM re-run AWS_RDS_TO_S3_PKG.getObjectList, you should now see the file

Object: test-file Size Bytes: 70 Modified on: 27-JUL-18

PL/SQL procedure successfully completed.

$ aws s3 cp s3://file-test.revittmk.aws.co.uk/test-file . --profile mike
download: s3://file-test.revittmk.aws.co.uk/test-file to ./test-file

$ cat test-file
This is the first line
This is the second line
This is the third line

$ echo 'This is the fourth line' >> test-file
$ echo 'This is the fifth line'  >> test-file
$ echo 'This is the sixth line'  >> test-file

$ cat test-file
This is the first line
This is the second line
This is the third line
This is the fourth line
This is the fifth line
This is the sixth line

$ aws s3 cp test-file s3://file-test.revittmk.aws.co.uk/ --profile mike
upload: ./test-file to s3://file-test.revittmk.aws.co.uk/test-file

$ sqlplus mike/<password>@file-test

BEGIN

    AWS_RDS_TO_S3_PKG.copyFile
    (
        'file-test.revittmk.aws.co.uk',
        'test-file',
        'FILE_COPY'
    );
END;
/

$ sqlplus mike/<password>@file-test

SET SERVEROUT ON SIZE 20000
SET LINESIZE          132
SET PAGESIZE           64

DECLARE

    fHandle  UTL_FILE.FILE_TYPE;
    vcText   VARCHAR2(132);

BEGIN

    fHandle := UTL_FILE.FOPEN('FILE_COPY', 'test-file', 'r');

    LOOP
        UTL_FILE.GET_LINE(fHandle, vcText );
        DBMS_OUTPUT.PUT_LINE( vcText );
    END LOOP;

    UTL_FILE.FCLOSE(fHandle);

    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        UTL_FILE.FCLOSE(fHandle);
END;
/
This is the first line
This is the second line
This is the third line
This is the fourth line
This is the fifth line
This is the sixth line
date
Mon  6 Aug 2018 10:24:52 BST

PL/SQL procedure successfully completed.

EXIT;

$ sqlplus mike/<password>@file-test

SET SERVEROUT ON SIZE 20000
SET LINESIZE          132
SET PAGESIZE           64

DECLARE

    aBucketList	aws_s3_pkg.BUCKET_LIST;

BEGIN

--  AWS_RDS_TO_S3_PKG.setDebugOn( AWS_RDS_TO_S3_PKG.DEBUG_VERBOSE );
--  AWS_RDS_TO_S3_PKG.setDebugOn( AWS_RDS_TO_S3_PKG.DEBUG_CONN );
    AWS_RDS_TO_S3_PKG.setDebugOn( AWS_RDS_TO_S3_PKG.ON );

    aBucketList := AWS_RDS_TO_S3_PKG.getBucketList;

    FOR i IN 1 .. aBucketList.count loop
        dbms_output.put('Bucket: ');
        dbms_output.put( aBucketList(i).bucket_name);
        dbms_output.put(' Created: ');
        dbms_output.put_line( aBucketList(i).creation_date);
    END LOOP;
END;
/

ORA-29273: HTTP request failed
ORA-28759: failure to open file
ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118