ora_dev_cloud_pkg.sql

create or replace package ora_dev_cloud_pkg as
/*

   Purpose : Oracle Developer Cloud module

  history
  Version Date        By               Description
  ------- ----------- ---------------- ----------------------------------------------
  0.1     22-FEB-2018 Dimitri          First version, based on Dimitri's blog post

*/

g_username   constant varchar2(100) := 'no-reply@apexrnd.be';
g_password   constant varchar2(100) := 'password';
g_url        constant varchar2(200) := 'https://developer.em2.oraclecloud.com/developerXXX-XXX/rest/developerXXX-XXX_project_XXX';
g_url_issues constant varchar2(200) := g_url || '/issues/v2/issues';

function issue_template(
  p_summary     in varchar2, 
  p_description in varchar2, 
  p_type        in varchar2)
  return clob;

procedure create_issue(
  p_summary     in varchar2, 
  p_description in varchar2, 
  p_type        in varchar2);

end ora_dev_cloud_pkg;
/

create or replace package body ora_dev_cloud_pkg as

function issue_template(
  p_summary     in varchar2, 
  p_description in varchar2,   
  p_type        in varchar2)
  return clob
as
  l_clob           clob;
  l_json_values    apex_json.t_values;   
  l_issue_template varchar2(32767);
  l_return         clob;
begin
  -- do a request for the create form service to get the definition of the Oracle developer cloud issues
  l_clob := apex_web_service.make_rest_request(
              p_url         => g_url_issues || '/create-form',
              p_username    => g_username,
              p_password    => g_password,
              p_http_method => 'GET',
              p_body        => '');

  -- parse the result            
  apex_json.parse(l_json_values, l_clob);
  -- get the issues url
  --l_issues_url := apex_json.get_varchar2(p_path => 'createIssue.links[%d].href', p0 => 2, p_values => l_json_values);
  -- debug
  --sys.htp.p(l_issues_url); 

  -- build the json, probably we need to make this more dynamic later
  l_issue_template := '
        {
            "links": [{
                "rel": "self",
                "href": "'||g_url_issues||'/create-form"
            }, {
                "rel": "create",
                "href": "'||g_url_issues||'"
            }],
            "summary": "#SUMMARY#",
            "description": "#DESCRIPTION#",
            "type": "#TYPE#",
            "severity": {
                "id": 4,
                "value": "normal",
                "sortkey": 400
            },
            "status": {
                "id": 1,
                "value": "UNCONFIRMED",
                "isActive": true,
                "isOpen": true,
                "sortkey": 100
            },
            "priority": {
                "id": 3,
                "value": "Normal",
                "sortkey": 300
            },
            "release": {
                "id": 1,
                "value": "---",
                "sortkey": 0,
                "product": {
                    "id": 1
                }
            },
            "product": {
                "id": 1,
                "name": "APEX RND",
                "description": "APEX RND",
                "isActive": true,
                "defaultRelease": {
                    "id": 1,
                    "value": "---",
                    "sortkey": 0,
                    "product": {
                        "id": 1
                    }
                },
                "defaultComponent": {
                    "id": 1,
                    "product": {
                        "id": 1
                    },
                    "name": "Default",
                    "description": "default component"
                },
                "releases": [{
                    "id": 1,
                    "value": "---",
                    "sortkey": 0,
                    "product": {
                        "id": 1
                    }
                }],
                "components": [{
                    "id": 1,
                    "product": {
                        "id": 1
                    },
                    "name": "Default",
                    "description": "default component"
                }],
                "releaseTags": []
            }
        }  
  ';

  l_return := replace(l_issue_template,'#SUMMARY#', p_summary); 
  l_return := replace(l_return,'#DESCRIPTION#', p_description); 
  l_return := replace(l_return,'#TYPE#', p_type); 
  return l_return;
end issue_template;


procedure create_issue(
  p_summary     in varchar2, 
  p_description in varchar2,   
  p_type        in varchar2)
as
PRAGMA AUTONOMOUS_TRANSACTION;
  l_clob           clob;
  l_issue_template clob;

begin

  l_issue_template := issue_template(
                          p_summary     => p_summary,
                          p_description => p_description,
                          p_type        => p_type);

  -- create a new issue
  apex_web_service.g_request_headers(1).name := 'Content-Type';
  apex_web_service.g_request_headers(1).value := 'application/json'; 

  l_clob := apex_web_service.make_rest_request(
              p_url         => g_url_issues,
              p_username    => g_username,
              p_password    => g_password,
              p_http_method => 'POST',
              p_body        => l_issue_template);

end create_issue;

end ora_dev_cloud_pkg;
/