Forum Stats

  • 3,782,596 Users
  • 2,254,669 Discussions
  • 7,880,132 Comments

Discussions

apex_web_service.make_rest_request returns nothing....

Geert01
Geert01 Member Posts: 141 Bronze Badge
edited Dec 9, 2021 9:17AM in APEX Discussions

All I want to do is the equivilent of the following curl command

curl --location --request POST 'https://api.meethue.com/oauth2/refresh?grant_type=refresh_token' \

--header 'Content-Type: application/x-www-form-urlencoded' \

--header 'Authorization: Basic xxxxxxxxxxxx' \

--data-raw 'refresh_token=yyyyyyyyyy'

but every time I execute:

declare  

  l_secret varchar2(100);

  l_client_id varchar2(200);

  l_refresh_token varchar2(200);

  l_creds varchar2(200);

  l_client_creds varchar2(200);

  l_req_body clob;

  l_result  clob;

begin

  l_refresh_token := 'xxxxxx';

  l_client_id := 'yyyyyy';

  l_secret := 'zzzzzzzz';

  l_creds  := l_client_id||':'||l_secret; --Client credentials unencoded

  l_client_creds :=replace(replace(replace(utl_encode.text_encode(l_creds,'WE8ISO8859P1', UTL_ENCODE.BASE64),chr(9)),chr(10)),chr(13)); -- BASE64 - encodes credentials

  apex_web_service.g_request_headers(1).name := 'Authorization';

  apex_web_service.g_request_headers(1).value := 'Basic '||l_client_creds;

  apex_web_service.g_request_headers(2).name := 'Content-Type';

  apex_web_service.g_request_headers(2).value := 'application/x-www-form-urlencoded';

  --l_req_body := 'refresh_token='||l_refresh_token; 

l_req_body := '{"refresh_token": "||l_refresh_token||'"}' ; 

  l_result := apex_web_service.make_rest_request

      ( p_url => 'https://api.meethue.com/oauth2/refresh?grant_type=refresh_token'

   , p_body => l_req_body

   , p_http_method => 'POST'

   );

 dbms_output.put_line( l_result );

end;


I get nothing back.

The only thing I am not sure of is the way l_req_body should be defined.


I have exeperimented with casting it to raw using

l_req_body_raw := UTL_RAW.CAST_TO_RAW('refresh_token:'||l_refresh_token); 

and also using

  apex_json.initialize_clob_output ();

  apex_json.open_object ();

  apex_json.write ('refresh_token', l_refresh_token);

  apex_json.close_all ();

  l_req_body := apex_json.get_clob_output ();

  apex_json.free_output ();

But nothing I do gets me the refresh token I need.

Using the curl command, and Postman gives me the result I am looking for. So the rest-api is working and returning values.I just cannot get it working in pl/sql


What am I doing wrong?

Tagged:

Best Answer

  • Geert01
    Geert01 Member Posts: 141 Bronze Badge
    Accepted Answer

    For those who are struggling with the same issue, this is the code that solves the problem (solution is given by Carsten Czarski)

    declare
        l_response clob;
    begin
        apex_web_service.g_request_headers.delete;
        apex_web_service.g_request_headers( 1 ).name  := 'Content-Type';
        apex_web_service.g_request_headers( 1 ).value := 'application/x-www-form-urlencoded';
        --
        -- do not build the Authorization header yourself, APEX_WEB_SERVICE does it for you
        --
        l_response := apex_web_service.make_rest_request(
            p_url         => 'https://api.meethue.com/oauth2/refresh?grant_type=refresh_token',
            p_http_method => 'POST',
            p_username    => '{client-id}',
            p_password    => '{client_secret}',
            p_body        => 'refresh_token=yyyyyyyy' );
        --
        -- this one *should* also work, as the "urlencoded" request header indicates that all
        -- parameters are in the body, in URL format
        --
        l_response := apex_web_service.make_rest_request(
            p_url         => 'https://api.meethue.com/oauth2/refresh',
            p_http_method => 'POST',
            p_username    => '{client-id}',
            p_password    => '{client_secret}',
            p_body        => 'grant_type=refresh_token&refresh_token=yyyyyyyy' );
        --
        -- process "l_response" here ...
    end;
    


Answers

  • Geert01
    Geert01 Member Posts: 141 Bronze Badge

    When I remove the &grant_type=refresh_token from the url and add it to the request call

     l_parm_names(1)  := 'grant_type';  

     l_parm_values(1)  := 'refresh_token';

      l_result := apex_web_service.make_rest_request

          ( p_url => 'https://api.meethue.com/oauth2/refresh'

       , p_body => l_req_body

       , p_http_method => 'POST'

        , p_parm_name => l_parm_names

        , p_parm_value => l_parm_values

    .....

    I get:

    g_status_code=400

    Return Value {"ErrorCode" : "invalid_request", "Error" :"The request is missing required parameters."}

  • Geert01
    Geert01 Member Posts: 141 Bronze Badge
    edited Dec 1, 2021 5:00PM

    I have created a demo application on apex.oracle.com

    Workspace name : HUE_API

    User name: FORUM_MEMBERS

    Password: Help_Nov_21

    You will see that, when pressing the 'Get Lights' button that the API executes and shows the lights. You will also see the api status 200

    When pressing the 'Refresh token' button you will see an error message.

    I have added the curl command so you can see what needs to be done, and so you can verify that the token can be refreshed.

    If you do execute the curl command, and you see the new access token and refresh token, please do not forget to update those values using the Update Tokens button. If you do not do that, I will need to get a new access token.


    The code that executes these api's are the only two database procedures in this workspace.


    What is the pl/sql equivalent for the curl command?

  • Geert01
    Geert01 Member Posts: 141 Bronze Badge

    I have changed the tags for this question. I removed Apex and added ORDS.

  • Geert01
    Geert01 Member Posts: 141 Bronze Badge
    Accepted Answer

    For those who are struggling with the same issue, this is the code that solves the problem (solution is given by Carsten Czarski)

    declare
        l_response clob;
    begin
        apex_web_service.g_request_headers.delete;
        apex_web_service.g_request_headers( 1 ).name  := 'Content-Type';
        apex_web_service.g_request_headers( 1 ).value := 'application/x-www-form-urlencoded';
        --
        -- do not build the Authorization header yourself, APEX_WEB_SERVICE does it for you
        --
        l_response := apex_web_service.make_rest_request(
            p_url         => 'https://api.meethue.com/oauth2/refresh?grant_type=refresh_token',
            p_http_method => 'POST',
            p_username    => '{client-id}',
            p_password    => '{client_secret}',
            p_body        => 'refresh_token=yyyyyyyy' );
        --
        -- this one *should* also work, as the "urlencoded" request header indicates that all
        -- parameters are in the body, in URL format
        --
        l_response := apex_web_service.make_rest_request(
            p_url         => 'https://api.meethue.com/oauth2/refresh',
            p_http_method => 'POST',
            p_username    => '{client-id}',
            p_password    => '{client_secret}',
            p_body        => 'grant_type=refresh_token&refresh_token=yyyyyyyy' );
        --
        -- process "l_response" here ...
    end;