Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 18, 2018 - 11:07 am UTC

Last updated: May 13, 2020 - 1:11 am UTC

Version: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm trying to invoke a RESTful service (using HTTPS and JSON objects) from a procedure.

The version of Oracle installed is:

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

The sys admin configured the right ACL and (I suppose) the wallet, too. If I execute the query select * from V$ENCRYPTION_WALLET I get following result:

"WRL_TYPE"  "WRL_PARAMETER"                       "STATUS"                      
"file"      "/u01/app/oracle/admin/DB11G/wallet"  "OPEN_NO_MASTER_KEY"


This is the code of my procedure:

create or replace procedure tokenize_test (p_data IN VARCHAR2)
AS
   req             UTL_HTTP.req;
   res             UTL_HTTP.resp;
   v_url           VARCHAR2 (4000) := 'my.address.com:443/vts/rest/v2.0/tokenize';
   v_buffer        VARCHAR2 (4000);
   error_message   CLOB;
   content         VARCHAR2 (4000);
BEGIN
   content := '{
  "tokengroup" :"mask_tg" ,
  "data" : "'   || p_data || '",
  "tokentemplate" :"abdul_tt"
}'  ;

   UTL_HTTP.set_wallet ('file:/u01/app/oracle/admin/DB11G/wallet', NULL);
   req :=
      UTL_HTTP.BEGIN_REQUEST (url            => v_url,
                              method         => 'POST',
                              http_version   => 'HTTP/1.1'
                              );
   UTL_HTTP.set_header (req, 'User-Agent', 'Mozilla/5.0');
   UTL_HTTP.set_header (req, 'Content-Type', 'application/json');
   UTL_HTTP.set_header (req, 'Content-Length', LENGTH (content));
   UTL_HTTP.set_header (req, 'authorization', 'Basic dmFyb25lOlZhcm9uZTIwMTg/');
   UTL_HTTP.write_text (req, content);
   res := UTL_HTTP.get_response (req);
   DBMS_OUTPUT.enable (100000000);
   -- process the response from the HTTP call
   DBMS_OUTPUT.put_line ('RESPONSE: ');


   BEGIN
      LOOP
         UTL_HTTP.read_line (res, v_buffer);
         DBMS_OUTPUT.put_line (v_buffer);
      END LOOP;

      UTL_HTTP.end_response (res);
   EXCEPTION
      WHEN UTL_HTTP.end_of_body
      THEN
         UTL_HTTP.end_response (res);
         error_message := UTL_HTTP.get_detailed_sqlerrm;
         DBMS_OUTPUT.put_line (error_message);
   END;

EXCEPTION
   WHEN OTHERS
   THEN
      error_message := UTL_HTTP.get_detailed_sqlerrm;
      DBMS_OUTPUT.put_line (error_message);
      raise_application_error (
         -20001,
            'An error was encountered - '
         || (CHR (13) || CHR (10))
         || (CHR (13) || CHR (10))
         || SQLCODE
         || (CHR (13) || CHR (10))
         || (CHR (13) || CHR (10))
         || ' -ERROR- '
         || SQLERRM
         || (CHR (13) || CHR (10))
         || (CHR (13) || CHR (10))
         || ' -DETAILED_ERROR- '
         || error_message);
END tokenize_test;


When I invoke the procedure, the server returns the following message:

400 Bad Request - The plain HTTP request was sent to HTTPS port


Note: If I invoke the service outside of Oracle, using any client, the service responds correctly.


My questions:

1) Is there anything else to set in the request, to say that it is an https request?
2) Since the server responds, I guess it's not a wallet problem. Am I wrong? In this case, could it depend on the status OPEN_NO_MASTER_KEY of the wallet or the fact that I did not indicate the password (I can not know if it is configured with AUTO LOGIN or not)?

Thanks in advance.

and Connor said...

For starters, change

'my.address.com:443/vts/rest/v2.0/tokenize';

to

' https://my.address.com:443/vts/rest/v2.0/tokenize';

and see how you go.

There's an end to end test that you can compare against here

https://asktom.oracle.com/pls/apex/asktom.search?tag=acl-created-but-accessing-gives-ora-29273-ora-12541


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 20, 2018 - 7:40 am UTC

Thanks for the reply.

I had already tried to add https to the address, but I get the error ORA-29259 end-of-input reached (just like the user of the post you linked me). The same error happens by trying the procedure of your link.
Could it be a problem of certificates and/or proxy? At the moment I do not know if the servers involved are behind proxy (but I do not think so). I will ask the network administrator. But in this case it should not return a different error?
Connor McDonald
July 20, 2018 - 10:46 am UTC

Could be a proxy or some other network issue. If you are not getting a certificate error, that normally indicates that (from the database perspective) all is correctly setup.

Also check Mos note 2213850.1 - it might be relevant to you here.

Time to speak to your network admins I think.

OAoth 2.0 Token authorization request failing with 500 intenal server error

Jagadeesh L, May 10, 2020 - 7:54 am UTC

I am trying to get the token value,they are using Oauth 2.0 authorization method they are using for authentication purpose
I am passing the username,password,cient authentication and grant_type

I have set the proxy and oracle wallet,there is no issue with this as other HTTPS requests are going hard coded token value
can you please advise what is wrong with the Procedure?

can you share sample program for Oauth 2.0 Token authenetication request

I

PROC


create or replace PROCEDURE tokenreq IS
l_http_auth_req UTL_HTTP.req;
l_http_auth_resp UTL_HTTP.resp;
buffer VARCHAR2(32766);
error_message CLOB;
l_response CLOB;
l_response_tmp varchar2(1024);
content VARCHAR2 (32766);
l_http_auth_url VARCHAR2(300) := ' https://api.a2zinc.net/Token';
l_http_ver VARCHAR2(20) := 'HTTP/1.1';
l_http_req_mthd VARCHAR2(20) := 'POST';
l_token_req_payload varchar2(4000);
l_username VARCHAR2(100) := 'username';
l_password VARCHAR2(100) := 'password';
l_clientcred VARCHAR2(200) := l_username|| ':'||l_password;
l_auth_value VARCHAR2(100) := 'Basic '||utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_i18n.string_to_raw(l_clientcred)));
BEGIN
UTL_HTTP.set_detailed_excp_support(TRUE);
UTL_HTTP.SET_RESPONSE_ERROR_CHECK(TRUE);
l_token_req_payload := 'client_id=iplus&client_secret=CLIENT_SECRET&grant_type=password&scope=client:SCOPE';
UTL_HTTP.set_proxy(' http://hkmacedonia.globalsources.com:3333', ' https://api.a2zinc.net:443' );
UTL_HTTP.set_wallet('file:/ndb3000/oracle/software/product/db/12.2.0.1_SE/network/admin/wallet', 'WalletPasswd123');
UTL_HTTP.set_transfer_timeout(180);
l_http_auth_req := utl_http.begin_request(l_http_auth_url, l_http_req_mthd );
UTL_HTTP.set_authentication(l_http_auth_req, l_auth_value);
dbms_output.put_line('setting header...');
UTL_HTTP.set_header (l_http_auth_req, 'Content-Length', LENGTH(l_token_req_payload));
UTL_HTTP.set_header(l_http_auth_req, 'Content-Type', 'application/x-www-form-urlencoded');
-- Write request text data in the HTTP request
--UTL_HTTP.write_text (l_http_auth_req, utl_url.escape(l_token_req_payload));
UTL_HTTP.write_text (l_http_auth_req, l_token_req_payload);
dbms_output.put_line('get response...');
l_http_auth_resp := UTL_HTTP.get_response(l_http_auth_req);
dbms_output.enable(100000000);
dbms_output.put_line('RESPONSE: ');
dbms_output.put_line('HTTP response status code: ' || l_http_auth_resp.status_code);
dbms_output.put_line('HTTP response reason phrase: ' || l_http_auth_resp.reason_phrase);
DBMS_OUTPUT.put_line('GETTING RESPONSE HEADERS! ');
BEGIN
LOOP
utl_http.read_line(l_http_auth_resp, l_response_tmp, FALSE);
l_response := l_response || l_response_tmp;
dbms_output.put_line(l_response);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_auth_resp);
error_message := UTL_HTTP.get_detailed_sqlerrm;
dbms_output.put_line(error_message);
WHEN Utl_Http.request_failed THEN
DBMS_OUTPUT.put_line ('Request_Failed: ' || Utl_Http.get_detailed_sqlerrm);
Utl_Http.end_request (r => l_http_auth_req);
WHEN Utl_Http.http_server_error THEN
DBMS_OUTPUT.put_line ('Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm);
Utl_Http.end_request (r => l_http_auth_req);
WHEN Utl_Http.http_client_error THEN
DBMS_OUTPUT.put_line ('Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm);
Utl_Http.end_request (r => l_http_auth_req);
when others then
DBMS_OUTPUT.put_line('HTTP ERROR: ' || utl_http.get_detailed_sqlerrm);
END;
END tokenreq;

Error

BEGIN tokenreq; END;
Error report -
ORA-29269: HTTP server error 500 - Internal Server Error
ORA-06512: at "SYS.UTL_HTTP", line 1267
ORA-06512: at "SYS.UTL_HTTP", line 651
ORA-06512: at "SYS.UTL_HTTP", line 1257
ORA-06512: at "WWW_ASM.TOKENREQ", line 39
ORA-06512: at line 1
29269. 00000 - "HTTP server error %s"
*Cause: The HTTP response indicated that the HTTP server error occurred.
*Action: Fix the HTTP server error and retry the HTTP request. Contact
the admistrator of the HTTP server when necessary.
Connor McDonald
May 13, 2020 - 1:11 am UTC

Can I suggest a different path to simplify a lot of this

Install APEX and let it do the heavy lifting. Then your code is going to look more like this

declare
  l_response  clob;
  l_target    varchar2(1000) := 'https://my.rest.target.com/...';
  l_token     varchar2(1000) := 'https://my.rest.target.com/.../oauth/token';
  l_cnt       pls_integer;
begin
  apex_session.create_session(...);

  l_response := apex_web_service.make_rest_request(
    p_url                  => l_target,
    p_http_method          => 'GET',
    p_parm_name            => l_parm_names,
    p_parm_value           => l_parm_values,
    p_credential_static_id => 'MY_CRED',
    p_token_url            => l_token);

  if apex_web_service.g_status_code = 200 then
    apex_json.parse(
       p_source=>l_response,
       p_strict=>true);
    l_cnt := apex_json.get_count(p_path => '...');
  end if;
end;
/

where the "MY_CRED" credential is define by calling APEX_CREDENTIAL.SET_PERSISTENT_CREDENTIALS


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library