Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
apex_web_service.make_rest_request returns nothing....

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?
Best 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
-
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."}
-
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?
-
-
I have changed the tags for this question. I removed Apex and added ORDS.
-
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;