0

I have a URL I need to call.

http://myurl.mycompany.net/api/people?filter=(cn=kxxxxx)

"cn" is the logon of the user, and it returns user details.

However I need to formulate this type of URL:

http://myurl.mycompany.net/api/people?filter=(cn=kxxxxx)&appid=GPVC

i.e. to send in the app_id.

I may also want to specify which fields I want to return:

http://myurl.mycompany.net/api/people?filter=(cn=kxxxxx)&appid=GPVC&attrs=sn,displayName

i.e. return only the surname and display name in the response

declare
  l_clob clob;
  l_buffer         varchar2(32767);
  l_amount         number;
  l_offset         number;
begin
 
  l_clob := apex_web_service.make_rest_request(
              p_url => 'http://myurl.mycompany.net/api/people?filter=(cn=kxxxxx)',
              p_http_method => 'GET',
              p_parm_name => apex_util.string_to_table('appid'),
              p_parm_value => apex_util.string_to_table('GPVC')
              );
            
 
    DBMS_OUTPUT.put_line(l_clob);
end;

But the response I get is:

{
    "error": {
        "message": "Consumer application identification is now enforced - please go to http://myurl.mycompany.net and contact us to obtain an appid for your requests."
    }
}

So I am hitting the web service, just probably not creating a URL in the right format.

If I add the &appid=GPVC to the URL in the PLSQL, I get a pop up box asking me to pass in the bind parameter at run time.....

l_clob := apex_web_service.make_rest_request(
                  p_url => 'http://myurl.mycompany.net/api/people?filter=(cn=kxxxxx)&appid=GPVC',
                  p_http_method => 'GET'
                  );

"set define off" (as people are suggesting) to ignore the ampersand doesn't work. I would really like to know why make_rest_request isnt working with the parameters. "set define off" just generates this error:

*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

The url is good....works fine in the browser and gives me a response

smackenzie
  • 2,880
  • 7
  • 46
  • 99
  • Does this answer your question? [How do I ignore ampersands in a SQL script running from SQL Plus?](https://stackoverflow.com/questions/118190/how-do-i-ignore-ampersands-in-a-sql-script-running-from-sql-plus) – Koen Lostrie Nov 17 '22 at 14:32
  • It doesn't, was more hoping to understand how to use make_rest_request properly, thanks though – smackenzie Nov 17 '22 at 14:35
  • How are you invoking that script. In an apex app ? In apex sql commands window ? sqldeveloper ? sqlplus ? You show code but don't mention in what client environment the code is executed. – Koen Lostrie Nov 17 '22 at 14:55
  • Hi, adding the & key value pairs into the script works, if I limit the response (i.e. reduce the payload being returned in terms of fields of interest). The issue seems to be it was blowing the DBMS buffer. I would still like to know why the parameter syntax doesn't work!! – smackenzie Nov 17 '22 at 16:10
  • I'd love to give more tips but without answer to my last comment I can't. As for blowing the dbms buffer: dbms_output.put_line takes a varchar2, not a clob. Write a procedure that prints out 4k chunks of the clob instead – Koen Lostrie Nov 17 '22 at 16:25
  • I am invoking it via SQL Developer. Its working now with the URL fully formed in the URL with key value pairs, if I only bring back a few fields in the response. – smackenzie Nov 17 '22 at 16:42

0 Answers0