2 Replies Latest reply on Jul 31, 2020 6:17 AM by Tyagi Tushar

    APEX to JIRA attachment API

    Tyagi Tushar

      Hello all,

      i am trying to send a blob file in my table as an attachment to a JIRA isssue from APEX. when i run the below code, the code compiles and run , but i get an empty response and the attachment is also not added to JIRA.

      Content type needs to be multipart/form-data for jira attachments.

      Could you please review and help me what i might be missing in the below code, i have tried many posts but cant seem to figure out the problem

       

      declare

        l_attachment blob;

        lco_boundary constant varchar2(30) := 'gc0p4Jq0M2Yt08jU534c0p';

        l_http_request utl_http.req;

        l_request_body_length number;

        l_http_response utl_http.resp;

        l_response_header_name varchar2(256);

        l_response_header_value varchar2(1024);

        l_response_body varchar2(32767);

        l_offset number := 1;

        l_amount number := 2000;

        l_buffer varchar2(2000);

      begin

       

        SELECT attachment

          into l_attachment

          FROM mytable

         WHERE id = 42;

       

        l_request_body_length := dbms_lob.getlength(l_attachment);

       

        utl_http.set_wallet(

          path => 'mywalletpath',

          password => 'mywalletpwd'

        );

       

        l_http_request := utl_http.begin_request(

                            url => 'https://host.com/jira/rest/api/2/issue/ABC-123/attachments',

                            method => 'POST',

                            http_version => 'HTTP/1.1'

                          );

      utl_http.set_authentication( l_http_request, 'myuser' ,'mypwd!', 'Basic' );

       

        utl_http.set_header(l_http_request, 'Content-Type', 'multipart/form-data; boundary="' || lco_boundary || '"');

        utl_http.set_header(l_http_request, 'Content-Length', l_request_body_length);

        utl_http.set_header(l_http_request, 'X-Atlassian-Token','no-check');

       

        while l_offset < l_request_body_length loop

          dbms_lob.read(l_attachment, l_amount, l_offset, l_buffer);

          utl_http.write_text(l_http_request, l_buffer);

          l_offset := l_offset + l_amount;

        end loop;

       

        l_http_response := utl_http.get_response(l_http_request);

       

        for i in 1 .. utl_http.get_header_count(l_http_response) loop

          utl_http.get_header(l_http_response, i, l_response_header_name, l_response_header_value);

          dbms_output.put_line('Response> ' || l_response_header_name || ': ' || l_response_header_value);

        end loop;

       

        utl_http.read_text(l_http_response, l_response_body, 32767);

        dbms_output.put_line('Response body>');

        dbms_output.put_line(l_response_body);

       

        if l_http_request.private_hndl is not null then

          utl_http.end_request(l_http_request);

        end if;

       

        if l_http_response.private_hndl is not null then

          utl_http.end_response(l_http_response);

        end if;

        apex_application.g_print_success_message := l_response_body;

       

       

      end;

       

      Thanks in advance for the help.

        • 1. Re: APEX to JIRA attachment API
          Pavel_p

          Hi,

          I don't know what might be possibly wrong, it's really tough to say this way without "touching" it... Maybe it does not like the utl_http.write_text procedure (I would definitely recommend to use the utl_http.write_raw instead) and also check the

          status_code and reason_phrase from the resp type

          TYPE resp IS RECORD (
            status_code PLS_INTEGER,
            reason_phrase VARCHAR2(256),
            http_version VARCHAR2(64));
          

          as it might give you some clue what could be possibly wrong.

          But the main question is why you use the utl_http package? If there is no special reason for using it (and I can not imagine such reason), I would strongly suggest not to use it at all (not that it cannot do what you need, definitely it can but you'll have to write much more code and it needs execute permissions) and rather use the APEX_WEB_SERVICE.MAKE_REST_REQUEST (it uses utl_http under the hood anyway). You can achieve what you need in just one call - just specify request headers https://docs.oracle.com/en/database/oracle/application-express/20.1/aeapi/Setting-Cookies-and-HTTP-Headers.html#GUID-7F4… and the p_body_blob parameter (if the wallet is not configured on APEX instance level, you'll also need to specify wallet related parameters in the web service call) + login credentials in p_username and p_password.

          Do they have by any chance any testing environment?

          Regards,

          Pavel

          • 2. Re: APEX to JIRA attachment API
            Tyagi Tushar

            Thanks Pavel_P for the reply. I was in fact using the apex_web_Service package earlier, but was getting the same issue and it returned unsupported data type.

            Then i read somewhere that apex_web_Service  does not support multipart/form-data post requests, and i tried to do it with utl_http.

            But same thing happens with this as well.

            Looks like i can go back to using apex_web_Service .