CODEWITHSUNDEEP
oracle-databaserestplsqloracle-apex
Beady El
Beady El

Reputation: 11

REST service works via Postman, not using PL/SQL

I am new to programming with REST services, and I'm hitting a bit of a wall.

I need to be able to access a specific set of REST services from Oracle PL/SQL. The guy who knows about the services knows nothing about PL/SQL. Bridging the gap is proving difficult.

This, in POSTMAN, works: (Apparently I'm too new to the site to post screen shots)

Action: POST

URL: http://dev-osb.sh.com/LDAP/LDAPVndrMgmt

Authorization: basic, username & password

Body: Raw, JSON

{
"requestType": "listAccounts",
"uid": "FTTestDlete100",
"vendorRole": "FreightTrackingLATA" }

And I get back all the results I expected in JSON format.

This PL/SQL should (as near as I can tell) do the same, but it doesn't.

DECLARE
    l_clob clob;    
    l_json clob;

BEGIN

  --create the JSON request body  
  apex_json.initialize_clob_output();  
  apex_json.open_object();  
  apex_json.write('requestType', 'listAccounts');  
  apex_json.write('uid', 'FTTestDlete100');  
  apex_json.write('vendorRole', 'FreightTrackingLATA');
  apex_json.close_all();  

  l_json := apex_json.get_clob_output();  
  apex_json.free_output();

 l_clob := apex_web_service.make_rest_request(
              p_url => 'http://dev-osb.sh.com/LDAP/LDAPVndrMgt',
              p_username => 'theUserName',
              p_password => 'thepassword',
              p_http_method => 'POST',
              p_parm_name =>  apex_util.string_to_table('Content-Type'),
              p_parm_value => apex_util.string_to_table('application/json'),
              p_body => l_json
              );

    dbms_output.put_line(to_char(l_clob));
END;

As shown I get "{ "errorMessage" : "No acceptable request representation was found" }"

If I remove the /LDAP/LDAPVndrMgt portion from the URL, I get some HTML code saying:

**Error 404--Not Found

The server has not found anything matching the Request-URI. No indication is given of whether the condition is temporary or permanent.

If the server does not wish to make this information available to the client, the status code 403 (Forbidden) can be used instead. The 410 (Gone) status code SHOULD be used if the server knows, through some internally configurable mechanism, that an old resource is permanently unavailable and has no forwarding address.**

So - something isn't working right. It's like the resource part (starting at /LDAP...) can't be concatenated onto the URL in the PL/SQL function, but without it I get a 404. I'm hoping to avoid having to go the UTL_HTTP route. Any suggestions?

Upvotes: 0

Views: 1478

Answers (1)

Beady El
Beady El

Reputation: 11

Breakthrough - I found out that my PL/SQL was sending what should have been headers as parameters.

Here's the working code:

DECLARE
    l_clob clob;    
    l_json clob;

BEGIN
    apex_web_service.g_request_headers(1).NAME := 'Content-Type';
    apex_web_service.g_request_headers(1).VALUE := 'application/json';   

  --create the JSON request body  
  apex_json.initialize_clob_output();  
  apex_json.open_object();  
  apex_json.write('requestType', 'listAccounts');  
  apex_json.write('uid', 'FTTestDlete100');  
  apex_json.write('vendorRole', 'FreightTrackingLATA');
  apex_json.close_all();  

  l_json := apex_json.get_clob_output();  
  apex_json.free_output();

 l_clob := apex_web_service.make_rest_request(
              p_url => 'http://dev-osb.sh.com/LDAP/LDAPVndrMgt',
              p_username => 'theUserName',
              p_password => 'thepassword',
              p_http_method => 'POST',
              p_body => l_json
              );

    dbms_output.put_line(to_char(l_clob));
END;

Upvotes: 1

Related Questions