apex_web_service.make_rest_request returns nothing....

Geert01

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?

Geert01

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;

Geert01

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

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

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

Geert01

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;