{"id":1224,"date":"2023-05-18T09:27:33","date_gmt":"2023-05-18T09:27:33","guid":{"rendered":"http:\/\/wp.chaoyu.nl\/?p=1224"},"modified":"2023-05-19T16:05:33","modified_gmt":"2023-05-19T16:05:33","slug":"how-list-create-delete-files-in-oci-bucket-within-apex-plsql","status":"publish","type":"post","link":"https:\/\/chaoyu.nl\/?p=1224","title":{"rendered":"How List\/Create\/Delete files in OCI bucket within APEX (plsql) + Create Pre-Authenticated Request"},"content":{"rendered":"<ul>\n<li>OCI account<\/li>\n<li>OCI user which has read\/write access to the Bucket<\/li>\n<li>On that User create an API key<\/li>\n<li>Download the private key and click on &#8220;<strong>Add<\/strong>&#8220;<\/li>\n<li><strong>Keep OCI window open<\/strong><\/li>\n<li>Open APEX screen<\/li>\n<li>Go to Workspace Utility<\/li>\n<li>find web credentials<\/li>\n<li>create new web credentials<br \/>give it a name like <strong>OCI_AUTH\u00a0<\/strong><br \/>Authentication Type: Oracle Cloud Infrastructure<br \/>OCI UserID : the OCID from your user<br \/>OCI Private key: which is the one just download, open it with text editor so u can copy and paste it here<br \/>OCI tenancy ID: this should be found on the screen from OCi console.<br \/>OCI Public Key FingerFrint: this should be found on the screen from OCi console.<\/li>\n<li>Apply Changes..<\/li>\n<\/ul>\n\n\n<h2 class=\"wp-block-heading\">To Upload\/Replace<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">if versioning is NOT enabled , it will replace file with the same name<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"http:\/\/wp.chaoyu.nl\/?p=1241\" data-type=\"URL\" data-id=\"http:\/\/wp.chaoyu.nl\/?p=1241\" target=\"_blank\" rel=\"noreferrer noopener\">Issues with HTTPs calls (Wallet issue) ?<\/a><\/p>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">declare\n   l_blob      blob;\n   l_file_name varchar2(10) := 'test.mp3';\n   l_response  clob;\n   cursor c_audio is\n      select t.file_content\n      \n      from   audios_ldff t\n      where  t.ldff_id = 3;\nbegin\n   open c_audio;\n   fetch c_audio\n      into l_blob;\n   close c_audio;\n\n   apex_web_service.g_request_headers(1).name := 'Content-Type';\n   apex_web_service.g_request_headers(1).value := 'audio\/mp3';\n   l_response := apex_web_service.make_rest_request(p_url                  =&gt; 'https:\/\/objectstorage.eu-frankfurt-1.oraclecloud.com\/n\/frpnibrn7ulj\/b\/public\/o\/' ||\n                                                                              l_file_name\n                                                   ,p_http_method          =&gt; 'PUT'\n                                                   ,p_body_blob            =&gt; l_blob\n                                                   ,p_credential_static_id =&gt; 'OCI_AUTH');\n\n   if apex_web_service.g_status_code != 200\n   then\n      dbms_output.put_line('failed with code ' || apex_web_service.g_status_code);\n   else\n      dbms_output.put_line('success uploaded');\n   end if;\n\nend;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">To list objects<\/h2>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">declare\n   l_response  clob;\nbegin\n\n   l_response := apex_web_service.make_rest_request(p_url                  =&gt; 'https:\/\/objectstorage.eu-frankfurt-1.oraclecloud.com\/n\/frpnibrn7ulj\/b\/public\/o\/' \n                                                   ,p_http_method          =&gt; 'GET'\n                                                   ,p_credential_static_id =&gt; 'OCI_AUTH');\n\n   if apex_web_service.g_status_code != 200\n   then\n      dbms_output.put_line('failed with code ' || apex_web_service.g_status_code);\n   else\n      dbms_output.put_line(l_response);\n   end if;\n\nend;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Results are JSON string<\/p>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"json\" class=\"language-json\">{\"objects\":[{\"name\":\"test.mp3\"},{\"name\":\"transform_van_schiphol.mp3\"}]}<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">To delete<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">for delete if successful , a 204 is returned.<\/p>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">declare\n   l_file_name varchar2(10) := 'test.mp3';\n   l_response  clob;\nbegin\n\n   l_response := apex_web_service.make_rest_request(p_url                  =&gt; 'https:\/\/objectstorage.eu-frankfurt-1.oraclecloud.com\/n\/frpnibrn7ulj\/b\/public\/o\/' ||\n                                                                              l_file_name\n                                                   ,p_http_method          =&gt; 'DELETE'\n                                                   ,p_credential_static_id =&gt; 'OCI_AUTH');\n\n   if apex_web_service.g_status_code != 204\n   then\n      dbms_output.put_line('failed with code ' || apex_web_service.g_status_code);\n   else\n      dbms_output.put_line('success deleted' || l_response);\n   end if;\n\nend;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">To get file as blob<\/h2>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">declare\n   l_file_name varchar2(10) := 'test.mp3';\n   l_response  blob;\nbegin\n\n   l_response := apex_web_service.make_rest_request_b(p_url                  =&gt; 'https:\/\/objectstorage.eu-frankfurt-1.oraclecloud.com\/n\/frpnibrn7ulj\/b\/public\/o\/' ||\n                                                                                l_file_name\n                                                     ,p_http_method          =&gt; 'GET'\n                                                     ,p_credential_static_id =&gt; 'OCI_AUTH');\n\n   dbms_output.put_line(apex_web_service.g_status_code || '  ' || round(dbms_lob.getlength(l_response) \/ 1024 \/ 1024) ||\n                        ' Mb');\n\nend;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Outcome : 200 41 Mb<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to Create Pre-Authenticated Request<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">For Buckets in Private model, access to the objects are secured. One can choose to create Pre-authenticated request for objects in the bucket. Here is how.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Offical Docs <a rel=\"noreferrer noopener\" href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/api\/#\/en\/objectstorage\/20160918\/PreauthenticatedRequest\/CreatePreauthenticatedRequest\" data-type=\"URL\" data-id=\"https:\/\/docs.oracle.com\/en-us\/iaas\/api\/#\/en\/objectstorage\/20160918\/PreauthenticatedRequest\/CreatePreauthenticatedRequest\" target=\"_blank\">about the POST Request<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/blog.cloudnueva.com\/apex-oci-par#heading-using-a-par\" data-type=\"URL\" data-id=\"https:\/\/blog.cloudnueva.com\/apex-oci-par#heading-using-a-par\" target=\"_blank\" rel=\"noreferrer noopener\">inspired from here<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The code below, does a call to OCI with <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/api\/#\/en\/objectstorage\/20160918\/datatypes\/CreatePreauthenticatedRequestDetails\" data-type=\"URL\" data-id=\"https:\/\/docs.oracle.com\/en-us\/iaas\/api\/#\/en\/objectstorage\/20160918\/datatypes\/CreatePreauthenticatedRequestDetails\" target=\"_blank\" rel=\"noreferrer noopener\">JSON <\/a>in the request body. Once successful, a json is returned with the URL you can call.<\/p>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">declare\n   l_json_payload clob;\n   l_response     clob;\n   l_access_url   clob;\n   cursor c_json(cp_json in clob) is\n      select accessuri\n            ,timecreated\n            ,timeexpires\n      from   json_table(cp_json\n                       ,'$' columns(accessuri path '$.\"accessUri\"'\n                               ,timecreated path '$.\"timeCreated\"'\n                               ,timeexpires path '$.\"timeExpires\"'));\n\n   r_json c_json%rowtype;\n   l_base_url         constant varchar2(100) := 'https:\/\/objectstorage.eu-frankfurt-1.oraclecloud.com';\n   l_bucket_namespace constant varchar2(100) := '\/n\/frpnibrn7ulj\/b\/public';\n   l_json_obj json_object_t;\nbegin\n   apex_json.initialize_clob_output(p_preserve =&gt; true);\n   apex_json.open_object;\n   apex_json.write('accessType'\n                  ,'ObjectReadWrite'); \/*[ObjectRead,ObjectWrite,ObjectReadWrite,AnyObjectWrite,AnyObjectRead,AnyObjectReadWrite]*\/\n   --apex_json.write('bucketListingAction','Deny');  -- not required - default on Deny\n   apex_json.write('name'\n                  ,'myRequest' || to_char(systimestamp at time zone 'UTC'\n                                         ,'yyyyddmmhh24miss')); -- required, but arbitrary. must be unqiue\n   apex_json.write('objectName'\n                  ,'test.mp3'); -- required if access type is ObjectRead, Duhhhhh..\n\n   apex_json.write('timeExpires'\n                  ,to_char(systimestamp at time zone 'UTC' + numtodsinterval(6\n                                                  ,'HOUR')\n                          ,'yyyy-mm-dd\"T\"hh24:mi:ss\"Z\"')); -- required,  why UTC, because OCI works UTC. What is T and Z, T is just a sperator where Z is UTC 0 ( PAR expiration must conform to RFC 3339: 2023-05-2000:42:59)\n   apex_json.close_object;\n   l_json_payload := apex_json.get_clob_output;\n\n   \/*POST \/n\/{namespaceName}\/b\/{bucketName}\/p\/*\/\n   l_response := apex_web_service.make_rest_request(p_url                  =&gt; l_base_url || l_bucket_namespace || '\/p\/'\n                                                   ,p_http_method          =&gt; 'POST'\n                                                   ,p_body                 =&gt; l_json_payload\n                                                   ,p_credential_static_id =&gt; 'OCI_AUTH');\n   dbms_output.put_line(l_response);\n\n   \/*\n     l_json_obj := json_object_t.parse(l_response);\n     dbms_output.put_line('URL: ' || l_base_url || l_json_obj.get_string('accessUri'));\n   *\/\n   if apex_web_service.g_status_code = 200\n   then\n      open c_json(l_response);\n      fetch c_json\n         into r_json;\n      close c_json;\n      dbms_output.put_line('Full Url: ' || l_base_url || r_json.accessuri || chr(10) || '  created at: ' ||\n                           r_json.timecreated || chr(10) || ' expires at: ' || r_json.timeexpires);\n   end if;\nend;\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>OCI account OCI user which has read\/write access to the Bucket On that User create an API key Download the private key and click on &#8220;Add&#8220; Keep OCI window open Open APEX screen Go to Workspace Utility find web credentials create new web credentialsgive it a name like OCI_AUTH\u00a0Authentication Type: Oracle Cloud InfrastructureOCI UserID :&#8230;<\/p>\n","protected":false},"author":1,"featured_media":1232,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,11,1],"tags":[],"class_list":["post-1224","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-apex","category-oci","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/1224","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1224"}],"version-history":[{"count":14,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/1224\/revisions"}],"predecessor-version":[{"id":1270,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/1224\/revisions\/1270"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/media\/1232"}],"wp:attachment":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1224"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1224"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1224"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}