Using Set_Authentication with utl_http

on

Problem


You have a simple request to fetch data from the internet into SQL so that you can parse.
It seems that nothing is returned.

declare
   lc_entire_msg clob;
   lv_url         varchar2(32000);
   lr_request utl_http.req;
   lr_response utl_http.resp;
   lv_msg varchar2(80);
   l_count number := 0;
   l_response_text  VARCHAR2(32767);
   buf VARCHAR2(32767);
begin
   --Create JSON REQUEST
   lv_url := 'http://blahblah/projects.json';
  
   --Loop through response and add to clob
   begin
      loop
         utl_http.read_text(r => lr_response, data => lv_msg);
         lc_entire_msg := lc_entire_msg || lv_msg;
      end loop;
   exception
      when utl_http.end_of_body then
         utl_http.end_response(lr_response);
      when others then
         utl_tcp.close_all_connections;
   end;

    l_count := dbms_lob.getlength(lc_entire_msg);

    apex_json.parse( lc_entire_msg);

    dbms_output.put_line('Number of Projects: '||l_count);
end;

Solution 1

The above is a script which you can find in lots of forums. Problem is, it's not showing that you've got a problem. So you just get blank data (no errors)
Put these lines in to report issues.

   /* request that exceptions are raised for error Status Codes */
     Utl_Http.Set_Response_Error_Check ( enable => true );

   /* allow testing for exceptions like Utl_Http.Http_Server_Error */
     Utl_Http.Set_Detailed_Excp_Support ( enable => true );

Solution 2

I see now, that I'm actually getting an authentication error. After some homework I realise that I need to provide credentials. These can be supplied like this.

   utl_http.Set_Authentication (
r => lr_request,
username => 'xxuser',
password => 'xx');
   lr_response := utl_http.get_response(r => lr_request); 

That worked for me.
Here's the whole script.

declare
   lc_entire_msg clob;
   lv_url         varchar2(32000);
   lr_request utl_http.req;
   lr_response utl_http.resp;
   lv_msg varchar2(80);
   l_count number := 0;
   l_response_text  VARCHAR2(32767);
   buf VARCHAR2(32767);
begin
   --Create JSON REQUEST
   lv_url := 'http://blahblah/projects.json';

    /* request that exceptions are raised for error Status Codes */
     Utl_Http.Set_Response_Error_Check ( enable => true );

   /* allow testing for exceptions like Utl_Http.Http_Server_Error */
     Utl_Http.Set_Detailed_Excp_Support ( enable => true );

    --send HTTP Request and get response
   lr_request := utl_http.begin_request(url => lv_url, method => 'GET');
   utl_http.Set_Authentication (
r => lr_request,
username => 'xxuser',
password => 'xx');
   lr_response := utl_http.get_response(r => lr_request);
   --Loop through response and add to clob
   begin
      loop
         utl_http.read_text(r => lr_response, data => lv_msg);
         lc_entire_msg := lc_entire_msg || lv_msg;
      end loop;
   exception
      when utl_http.end_of_body then
         utl_http.end_response(lr_response);
      when others then
         utl_tcp.close_all_connections;
   end;

   
   apex_json.parse( lc_entire_msg);

    --l_count := APEX_JSON.get_count(p_path => 'projects');
   dbms_output.put_line('Number of Projects: '||l_count);
end;

Acknowledgment

http://www.explorer-development.uk.com/parsing-json-apex-5-0/#top
https://asktom.oracle.com/pls/asktom/f?p=100:11:::NO:RP:P11_QUESTION_ID:285215954607


Comments

Post a Comment