Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PLSQL and the Art of OCI Management

PLSQL and the Art of OCI Management

Presentation for Steven Feuerstein's Office Hours regarding the steps I took to get the Oracle PL/SQL language to interact with the Oracle Cloud (both OCI Classic and OCI).

Robert Pastijn

September 08, 2020
Tweet

Other Decks in Programming

Transcript

  1. Senior Principal Product Manager Oracle Database Product Development, Platform Technology

    Solutions (PTS) Robert Pastijn PL/SQL and the Art of OCI management
  2. 60+ domains for our team to maintain Then there was....

    THE CLOUD Copyright © 2020, Oracle and/or its affiliates 4
  3. Copyright © 2020, Oracle and/or its affiliates 5 Maintenance between

    domains ? 뭐야 ? [root@localhost ~]# time cleanup_domain.sh DBDEV11 -- Cleaning databases zone z11 ORDL1, ORCD2 -- Cleaning instances zone z11 MYINSTANCE1, MYINSTANCE2, STEPSTONE02 -- Creating new DB instance MYINSTANCE1 <Error while creating database> real 54m43.551s user 22m11.300s sys 9m11.002s Lets do this with APEX and PL/SQL and REST in the background !!
  4. Representational state transfer (REST) • Defined in 2000 by Roy

    Fielding in his PhD assertation - Started in 1994 during the design of HTTP 1.1in the 'HTTP Object Model' "Web services that conform to the REST architectural style, called RESTful Web services, provide interoperability between computer systems on the internet. RESTful Web services allow the requesting systems to access and manipulate textual representations of Web resources by using a uniform and predefined set of stateless operations. Other kinds of Web services, such as SOAP Web services, expose their own arbitrary sets of operations." • Uses mostly GET, PUT, POST and DELETE - Yes, yes, the full list is GET, HEAD, POST, PUT, PATCH, DELETE, CONNECT, OPTIONS and TRACE… • Payload and answer usually in XML, HTTP or JSON Quickly - what is a REST API Copyright © 2020, Oracle and/or its affiliates 6
  5. Initial requests based on username and password • Examples using

    cURL, Python and java (but not PL/SQL) Initial test scripts based on UTL_HTTP requests • Initially using UTL_HTTP, switched to APEX_WEB_SERVICE in APEX4.2 Accessing Oracle Public Cloud through REST API APEX_WEB_SERVICE.MAKE_REST_REQUEST( p_url IN VARCHAR2, p_http_method IN VARCHAR2, p_username IN VARCHAR2 default null, p_password IN VARCHAR2 default null, p_proxy_override IN VARCHAR2 default null, p_transfer_timeout IN NUMBER default 180, p_body IN CLOB default empty_clob(), p_body_blob IN BLOB default empty_blob(), p_parm_name IN apex_application_global.VC_ARR2 default empty_vc_arr, p_parm_value IN apex_application_global.VC_ARR2 default empty_vc_arr, p_wallet_path IN VARCHAR2 default null, p_wallet_pwd IN VARCHAR2 default null ) RETURN CLOB; Copyright © 2020, Oracle and/or its affiliates 7
  6. Example APEX_WEB_SERVICE and REST SQL> select apex_web_service.make_rest_request 2> (p_url =>

    'https://sv443.net/jokeapi/v2/joke/Programming', 3> p_http_method => 'GET') as json_output 4> from dual; JSON_OUTPUT =========================================================== {"error": false,"category": "Programming","type": "single", "joke": "How do you tell HTML from HTML5? \ - Try it out in Internet Explorer - Did it work? \ - No? - It's HTML5.", "flags": { "nsfw": false, "religious": false, "political": false, "racist": false, "sexist": false }, "id": 43,"lang": "en" } Copyright © 2020, Oracle and/or its affiliates 8
  7. JSON_VALUE • Retrieve a value from a JSON structure Converting

    a JSON to something a DBA can use SQL> select JSON_VALUE( apex_web_service.make_rest_request ( 2> p_url => 'https://sv443.net/jokeapi/v2/joke/Programming', 3> p_http_method => 'GET'), 4 '$.joke') as json_output 3> from dual; JSON_OUTPUT ================================== How do you tell HTML from HTML5? - Try it out in Internet Explorer - Did it work? - No? - It's HTML5. Copyright © 2020, Oracle and/or its affiliates 9
  8. But we might have more results than just 1.. SQL>

    select apex_web_service.make_rest_request 2> (p_url => 'https://sv443.net/jokeapi/v2/joke/Programming?amount=10', 3> p_http_method => 'GET') as json_output 4> from dual; {"error": false,"jokes": [{"category": "Programming","type": "single","joke": "The generation of random numbers is too important to be left to chance.","flags": {"nsfw": false,"religious": false,"political": false,"racist": false,"sexist": false},"id": 39,"lang": "en"},{"category": "Programming","type": "single","joke": "\"Can I tell you a TCP joke?\"\n\"Please tell me a TCP joke.\"\n\"OK, I'll tell you a TCP joke.\"","flags": {"nsfw": false,"religious": false,"political": false,"racist": false,"sexist": false},"id": 58,"lang": "en"},{"category": "Programming","type": "twopart","setup": "Why do Java programmers hate communism?","delivery": "They don't want to live in a classless society.","flags": {"nsfw": false,"religious": false,"political": false,"racist": false,"sexist": false},"id": 53,"lang": "en"},{"category": "Programming","type": "single","joke": "Algorithm: A word used by programmers when they don't want to explain how heir code works.","flags": {"nsfw": false,"religious": false,"political": false,"racist": false,"sexist": false},"id": 51,"lang": "en"},{"category": "Programming","type": "single","joke": "Your mama's so FAT she can't save files bigger than GB.","flags": {"nsfw": false,"religious": false,"political": false,"racist": false,"sexist": false},"id": 9,"lang": "en"},{"category": "Programming","type": "single","joke": "Hey Girl,\nRoses are #ff0000,\nViolets are #0000ff,\nI use hex odes,\nBut I'd use RGB for you.","flags": {"nsfw": false,"religious": false,"political": false,"racist": false,"sexist": false},"id": 41,"lang": "en"},{"category": "Programming","type": "single","joke": "A SQL statement walks into a bar and sees two tables.\nIt approaches, and asks \"may I join you?\"","flags": {"nsfw": false,"religious": false,"political": false,"racist": false,"sexist": false},"id": 5,"lang": "en"},{"category": "Programming","type": "twopart","setup": "What do ou get if you lock a monkey in a room with a typewriter for 8 hours?","delivery": "A regular expression.","flags": {"nsfw": false,"religious": false,"political": false,"racist": false,"sexist": false},"id": 26,"lang": "en"},{"category": Programming","type": "single","joke": "Judge: \"I sentence you to the maximum punishment...\"\nMe (thinking): \"Please be death, please be death...\"\nJudge: \"Learn Java!\"\nMe: \"Damn.\"","flags": {"nsfw": false,"religious": alse,"political": false,"racist": false,"sexist": false},"id": 45,"lang": "en"},{"category": "Programming","type": "twopart","setup": "How do you know God is a shitty programmer?","delivery": "He wrote the OS for an entire universe, but idn't leave a single useful comment.","flags": {"nsfw": false,"religious": false,"political": false,"racist": false,"sexist": false},"id": 19,"lang": "en"}],"amount": 10} Copyright © 2020, Oracle and/or its affiliates 10
  9. Converting a JSON into something a DBA can use (JSON_TABLE)

    SQL> select * from JSON_TABLE ( <large JSON>, '$.jokes[*]' COLUMNS ( category, type, joke ) ) CATEGORY TYPE JOKE ----------- ---------- ----------------------------------------------------------------------- Programming single A SQL statement walks into a bar and sees two tables. It approaches, and asks "may I join you?" Programming single The generation of random numbers is too important to be left to chance. Programming single "Can I tell you a TCP joke?" "Please tell me a TCP joke." "OK, I will tell you a TCP joke." Programming single Algorithm: A word used by programmers when they do not want to explain how their code works. Programming single Your mama is so FAT she can not save files bigger than 4 GB. Programming single Hey Girl, Roses are #ff0000, Violets are #0000ff, I use hex codes, but I would use RGB for you. … 10 rows selected. Copyright © 2020, Oracle and/or its affiliates 11
  10. Copyright © 2020, Oracle and/or its affiliates 12 This is

    what an OCI Classic result could look like (List databases) {"uri":"https:\/\/dbaas.oraclecloud.com:443\/paas\/service\/dbcs\/api\/v1.1\/instances\/idcs- 270644901bf04406ae7180bd3d995ce6\/", "service_type":"dbaas", "implementation_version":"1.0","services":[{"service_name":"Service1", "service_uuid":"5C65CA50129C45BEA011E797E203AC4F", "version":"11.2.0.4", "status":"Running", "description":"Service1", "identity_domain":"idcs-270644901bf04406ae7180bd3d995ce6", "creation_time":"2019-01-08T21:40:34.453+0000", "last_modified_time":"2020-05-04T08:17:49.865+0000", "created_by":"someone@oracle.com", "sm_plugin_version":"18.4.4-546", "tools_version":"18.4.4-546", "service_uri":"https:\/\/dbaas.oraclecloud.com:443\/paas\/service\/dbcs\/api\/v1.1\/instances\/idcs- 270644901bf04406ae7180bd3d995ce6\/Service1", "legacy":true},{"service_name":"MIGR","service_uuid": "6FC73E264BF540F5ACAB037E4792991B","version":"18.0.0.0","status":"Running", "description":"Migration DB instance","identity_domain":"idcs-270644901bf04406ae7180bd3d995ce6","creation_time":"2018-11-17T05:33:11.048+0000", "last_modified_time":"2018-11-17T06:34:23.306+0000","created_by":"someone@oracle.com","sm_plugin_version":"18.4.2- 586","tools_version":"18.4.2-586", "service_uri":"https:\/\/dbaas.oraclecloud.com:443\/paas\/service\/dbcs\/api\/v1.1\/instances\/idcs- 270644901bf04406ae7180bd3d995ce6\/MIGR","legacy":true}, {"service_name":"otherDB","service_uuid":"074DFC7D24C04A468C939A10C2D60BC7","version":"12.1.0.2","status":"Running", "description":"Proof of Concept","identity_domain":"idcs-270644901bf04406ae7180bd3d995ce6","creation_time":"2019-06- 11T20:39:07.966+0000", "last_modified_time":"2019-06- 11T21:12:21.418+0000","created_by":"someone@oracle.com","sm_plugin_version":"19.2.2-520","tools_version":"19.2.2-520", "backup_supported_version":"16.2.3","service_uri":"https:\/\/dbaas.oraclecloud.com:443\/paas\/service\/dbcs\/api\/v1.1\/instanc es\/idcs-270644901bf04406ae7180bd3d995ce6\/pocdb01", "legacy":true},{"service_name":"MyProjects","service_uuid":"09550624099149D1BB6C08BAEFC09468","version":"18.0.0.0","status":"Ru nning", "description":"My personal Projects","identity_domain":"idcs-270644901bf04406ae7180bd3d995ce6","creation_time":"2018- 08-01T17:05:54.032+0000", "last_modified_time":"2019-05- 15T02:22:03.112+0000","created_by":"someone@oracle.com","sm_plugin_version":"18.3.2-566","tools_version":"18.3.2-566", "service_uri":"https:\/\/dbaas.oraclecloud.com:443\/paas\/service\/dbcs\/api\/v1.1\/instances\/idcs- 270644901bf04406ae7180bd3d995ce6\/MyProjects","legacy":true}],"subscriptions":[]}
  11. So, we create a function and a query.. It almost

    worked.. create function f_db_instances return clob is v_clob clob; v_url varchar2(2000); begin v_url := 'https://dbaas.oraclecloud.com/paas/service/dbcs/api/v1.1/instances/idcs-270644901bf04406ae7180bd3d995ce6/'; apex_web_service.g_request_headers.delete; apex_web_service.g_request_headers(1).name := 'X-ID-TENANT-NAME'; apex_web_service.g_request_headers(1).Value := 'idcs-270644901bf04406ae7180bd3d995ce6'; v_clob := apex_web_service.make_rest_request( p_url => v_url, p_http_method => 'GET', p_username => 'rest_api', p_password => 'cF9LbxYfzYhScHj'); return(v_clob); end; select * from json_table ( f_db_instances, '$.services[*]' COLUMNS ( service_name, version, status, description, creation_time, created_by, legacy ) ) j Copyright © 2020, Oracle and/or its affiliates 13
  12. But THIS worked out fine for the Oracle Public Cloud

    select j.* from (select f_db_instances from dual) d, json_table (d.f_db_instances, '$.services[*]' COLUMNS ( service_name, version, status, creation_time, created_by, legacy ) ) j SERVICE_NAME VERSION STATUS CREATION_TIME CREATED_BY LEGACY ---------------- --------- -------- ----------------------------- ------------------ ------- Service1 11.2.0.4 Running 2019-01-08T21:40:34.453+0000 someone@oracle.com true MIGR 18.0.0.0 Running 2018-11-17T05:33:11.048+0000 someone@oracle.com true otherDB 12.1.0.2 Running 2019-06-11T20:39:07.966+0000 someone@oracle.com true MyProjects 18.0.0.0 Running 2018-08-01T17:05:54.032+0000 someone@oracle.com true 4 rows selected. Copyright © 2020, Oracle and/or its affiliates 14
  13. Import statement directly into APEX • Or create a view

    around it and import it • Using a view, you can even do ON..UPDATE or ON..DELETE triggers - And translate the required behavior back into REST API calls So far, so good... Copyright © 2020, Oracle and/or its affiliates 15
  14. Make it more flexible with a join ! create table

    my_tenancies ( tenant_name, username, password ); insert into my_tenancies values ('tenant1','username1','password1'); insert into my_tenancies values ('tenant2','username2','password2'); insert into my_tenancies values ('tenant3','username3','password3'); create or replace function f_db_instances ( p_tenant varchar2, p_username varchar2, p_password ) return clob is v_clob clob; v_header_name varchar2(2000); v_url varchar2(2000); begin v_header_name := 'X-ID-TENANT-NAME'; v_url := 'https://dbaas.oraclecloud.com/paas/service/'|| 'dbcs/api/v1.1/instances/'||p_tenant||'/'; apex_web_service.g_request_headers.delete; apex_web_service.g_request_headers(1).name := v_header_name; apex_web_service.g_request_headers(1).value := p_tenant; v_clob := apex_web_service.make_rest_request( p_url => v_url, p_http_method => 'GET', p_username => p_username, p_password => p_password ); return(v_clob); end; select mt.tenant_name, j.* from ( select mt.tenant_name, f_db_instances ( tenant_name, username, password )as db_json from my_tenancies) mt, json_table (mt.db_json, '$.services[*]' COLUMNS ( service_name, version, status, creation_time, created_by, legacy ) ) j Copyright © 2020, Oracle and/or its affiliates 16
  15. Request always contained either username/password or Authentication Cookie Username/password •

    Usually hard-coded in the script • Same username and password to access console • Once username/password was hacked, you had a problem Authentication cookie • Cookie only valid for certain amount of time • Requesting cookie took additional time (and was prone to errors) • Once cookie was hacked, you had a problem (for a limited time) Different authentication methods for different API calls Downside of OCI Classic REST APIs Copyright © 2020, Oracle and/or its affiliates 17
  16. But then OCI was born Picture by Steve Buissinne from

    pixabay.com, free for commercial use Copyright © 2020, Oracle and/or its affiliates 18
  17. YEAHHHH • But the authentication is different from OCI Classic...

    NOOOOOO https://tools.ietf.org/html/draft-cavage-http-signatures-08 Incomplete summary: • Generate a public/private .pem key combination - This is not the same as the public/private key combination for SSH access ! • Register your public .pem key with Oracle OCI - Under your username that has the rights that you need • Sign any request you have using your private .pem key before sending - Include the signature in the 'Authorization' header of the REST request to OCI • OCI will validate your request based on the stored public .pem key - Make sure there is less than 5 minutes skew between the signer and the receiver OCI is (IMHO) build around REST APIs Copyright © 2020, Oracle and/or its affiliates 19
  18. Euhhhhh..... Examples exist for several programming languages • But not

    for PL/SQL And I want to use this from APEX • Sssshhhh.. Reality is that I do not speak anything else "Sign any request you have using your private .pem key before sending" 뭐야 ? Copyright © 2020, Oracle and/or its affiliates 20
  19. No DBMS_CRYPTO options But it works in Java.. Wait a

    minute ?!? I can do Java in the database. !! Wait a minute ?!? I do not speak Java !! Wait a minute ?!? I can Google like the best And after a lot of trying and failing, I got it to work !! Oracle PL/SQL cannot sign using a PEM key… Copyright © 2020, Oracle and/or its affiliates 21
  20. I got the signing to work CREATE JAVA SOURCE NAMED

    SHA256RSA AS import java.security.KeyFactory; import java.security.Signature; import java.security.spec.PKCS8EncodedKeySpec; public class SHA256RSA { public static byte[] signSHA256RSA (String input, byte[] b1) throws Exception { PKCS8EncodedKeySpec spec = new PKCS8EncodedKeySpec(b1); KeyFactory kf = KeyFactory.getInstance("RSA"); Signature privateSignature = Signature.getInstance("SHA256withRSA"); privateSignature.initSign(kf.generatePrivate(spec)); privateSignature.update(input.getBytes("UTF-8")); byte[] s = privateSignature.sign(); return s; } }; function java_sign_raw (p_string varchar2, p_PK_raw raw) return raw as language JAVA name 'SHA256RSA.signSHA256RSA (java.lang.String, byte[]) return byte[]'; Copyright © 2020, Oracle and/or its affiliates 22
  21. From the BASH example: To PL/SQL: I scraped the header

    setup from the examples v_timestamp := to_char(systimestamp AT TIME ZONE 'GMT','Dy, DD Mon YYYY HH24:MI:SS')|| ' GMT'; v_sigstring := replace('(request-target): get '||v_url_spec||'\ndate: '||v_timestamp||'\nhost: '|| p_host,'\n',ltrim(rtrim(chr(10)))); v_signature := sign(p_tenancy_id, v_sigstring); v_auth_head := 'Signature version="1", keyId="'<my_tenancy_ocid>/<my_user_ocid/<my_fingerprint>'", algorithm="rsa-sha256",headers="(request-target) date host", signature="'||v_signature||'"'; local escaped_target="$(echo $( rawurlencode "$target" ))" local request_target="(request-target): $request_method $escaped_target" local date_header="date: $now" local host_header="host: $host" local content_sha256_header="x-content-sha256: $content_sha256" local content_type_header="content-type: $content_type" local content_length_header="content-length: $content_length" local signing_string="$request_target\n$date_header\n$host_header" local headers="(request-target) date host" local curl_header_args curl_header_args=(-H "$date_header") local body_arg Copyright © 2020, Oracle and/or its affiliates 23
  22. All REST actions in one package • Never have to

    worry about signing, keys, SHA256 and other things I build myself some nice packages create or replace PACKAGE "LL_PKG_EXEC" as -- Program code to interact with OCI -- 02-JUN-2020 rpastijn initial creation function get (p_tenancy_id number, p_host varchar2, p_method varchar2) return clob; function put_post (p_tenancy_id number, p_type varchar2, p_host varchar2, p_method varchar2, p_body clob, p_content_type varchar2 default 'application/json' ) return clob; function delete (p_tenancy_id number, p_host varchar2, p_method varchar2) return clob; end; Copyright © 2020, Oracle and/or its affiliates 24
  23. Copyright © 2020, Oracle and/or its affiliates 25 List all

    Autonomous Databases in all regions in all compartments: Be aware that this statement (20+ regions) will take several minutes to execute.. And created some nice views CREATE OR REPLACE VIEW "OCI_AUTONOMOUS_VW" as select J."ID",J."COMPARTMENTID",J."DISPLAYNAME",J."DBNAME",J."CPUCORECOUNT",J."LIFECYCLESTATE", J."TIMECREATED",J."LICENSEMODEL",J."DBWORKLOAD",I."REGION" from (select region, name, ll_pkg_exec.get( p_host => endpoint, p_method => '/20160918/autonomousDatabases', p_compartment_id => cm.id ) MyJSON from COMPARTMENTS_MVW cm, ENDPOINTS e where endpoint_type='database') I, JSON_TABLE(i.myjson, '$[*]' COLUMNS (id, compartmentId, displayName, dbName, cpuCoreCount, lifecycleState, timeCreated, licenseModel, dbWorkload ) ) J /
  24. And after some internal initial successes, LiveLabs was born Copyright

    © 2020, Oracle and/or its affiliates 26
  25. Copyright © 2020, Oracle and/or its affiliates 27 LiveLabs: http://bit.ly/golivelabs

  26. Copyright © 2020, Oracle and/or its affiliates 28 Some of

    them, we can host them for you
  27. See result of each REST request, including any errors or

    issues Very easy to monitor and debug.. All steps are pre-defined and executed at create or delete time Copyright © 2020, Oracle and/or its affiliates 29
  28. The signing ! But there are good things as well..

    • Less traffic per REST request • Less options for man-in-the-middle attack • No username/passwords in scripts or on the network • All REST APIs work the same - Looking at authentication So… But why doesn't Oracle... Downside of OCI REST APIs Copyright © 2020, Oracle and/or its affiliates 30
  29. Package DBMS_CLOUD initially developed for Autonomous Database • Currently available

    in Autonomous Database including Freetier • Planned for next release for all OCI databases • On the roadmap for future on-premise databases Available for Oracle Cloud (both versions), Amazon Web Services, Azure Cloud But we do ! DBMS_CLOUD.SEND_REQUEST( credential_name IN VARCHAR2, uri IN VARCHAR2, method IN VARCHAR2, headers IN CLOB DEFAULT NULL, async_request_url IN VARCHAR2 DEFAULT NULL, wait_for_states IN DBMS_CLOUD_TYPES.wait_for_states_t DEFAULT NULL, timeout IN NUMBER DEFAULT 0, body IN BLOB DEFAULT NULL) RETURN DBMS_CLOUD_TYPES.resp; Copyright © 2020, Oracle and/or its affiliates 31
  30. Get the following details: • User OCID, tenancy OCID Generate

    a public/private PEM key combination • Upload the public key under your users API keys and get the fingerprint Create your credential in the database: Step 1: Setup cloud credentials begin DBMS_CLOUD.CREATE_CREDENTIAL ( credential_name => 'OCI_CRED', user_ocid => 'ocid1.user.oc1..aaaaaaaaw4tkvowdatrjqlnijwmztykxr6tkddmflgmfihfigrnkp6dmkj7q', tenancy_ocid => 'ocid1.tenancy.oc1..aaaaaaxx3fcdss2mnuqwijt4yrmkz4wkwpfbloh54cz7azsyjqgbl5quaaga', fingerprint => '5d:6d:3f:3c:97:0d:d8:b3:f5:ef:5f:e9:ec:69:df:f5', private_key => 'MIIEowIBAAK2j1Jx4NTKfZZTuY2tf4e8Ai8Yf3VrAk5mU1ehRQa8rirujoDuOECyzZqBCXaP9KPRvdq3X/'|| 'z/MaDI4j7Yu0j5X0saJewso3dZ8QyI/A7c7TDebXjK2BSJSxbh+AnCs92UpKu6GJpo9SSeM3n0sIg5iQtD'|| 'JvqywZv8MTVTiqc94oGdghjERpSTXb92LIHiJVZuXNkqWgEb8H+dC+dY8Rfrryi3Uejtosjo9bA+BjBdIR'|| 'jGojQwEm4jYofvpQcTIpVuvSGAoU5OTyzEm2hJ6K5i/T/Yn0nk+mGY7xJP6zMcSd0cnjHaSUkpB8zjDyni'|| ... '1qXfVwKBgEXa/hP0cBiTJcxZeqUvyMH3LR7t6Zx9U4uReNP0JsENntbk6Kjz8pgI2aPfQ39V2eZzBCO8S6'|| 'KIiNUocZ1Yb8mEqV6bHJnZMpRibvoQ3m4eBV2+GMhnqqg0yGuWhy'); end; Copyright © 2020, Oracle and/or its affiliates 32
  31. Call a REST API: And use the result: Step 2:

    Start querying ! declare v_response DBMS_CLOUD_TYPES.resp; v_result clob; v_url varchar2(200); v_region varchar2(200) := 'eu-frankfurt-1'; v_compartment varchar2(200) := '<your tenancy OCID or compartment OCID>'; v_credential varchar2(200) := 'OCI_CRED'; begin v_url := 'https://'||v_region||'/20160918/autonomousDatabases/?compartmentId='||v_compartment; v_response := DBMS_CLOUD.SEND_REQUEST( credential_name => v_credential, uri => v_url, method => DBMS_CLOUD.METHOD_GET); v_result := DBMS_CLOUD.GET_RESPONSE_TEXT( resp => v_response ); dbms_output.put_line(v_result); end; [{"additionalDatabaseStatus":null,"autonomousContainerDatabaseId":null,"compartmentId":"ocid1.compartment.oc1..aaa aaaaaja5hmjbi6wxhyfsbk4ysztgzig5uvx7zljgwnenywjex4xmkcolq","connectionStrings": (etc etc) 000Z","timeReclamationOfFreeAutonomousDatabase":null,"usedDataStorageSizeInTBs":1,"whitelistedIps":null}] Copyright © 2020, Oracle and/or its affiliates 33
  32. Copyright © 2020, Oracle and/or its affiliates 34 Oracle Always

    Free Cloud Services • 2 Autonomous Database instances • Each with 1 OCPU and 20GB of data storage • 2 Virtual Machines with regular OS (compute nodes) • Each with 1x 1/8 OCPU and 1GiB of memory • 2 Block Volumes • In total 100GiB (to be used for the compute nodes) • 10 GiB Object Storage • 10 GiB Archive Storage • Loadbalancer, Notifications, Email Service and more Try it yourself !
  33. PL/SQL and OCI REST APIs are FUN ! Links: •

    Play with DBMS_CLOUD for free - Oracle Free Tier: https://www.oracle.com/cloud/free/ • See examples like the DBMS_CLOUD example in simple steps - https://rpastijn.github.io/examples/ • How to sign an OCI request - https://docs.cloud.oracle.com/en-us/iaas/Content/API/Concepts/signingrequests.htm • How to use Oracle Autonomous Database - https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/dbms-cloud.html • Original Java signing code I developed (euh.. googled) - https://github.com/rpastijn/apex_oci Summary Copyright © 2020, Oracle and/or its affiliates 35
  34. Copyright © 2020, Oracle and/or its affiliates 36 Any questions

    ?
  35. None