I am trying to call REST API using Oracle APEX provided function apex_web_service.make_rest_request by passing JSON.

JSON is created by using below utility by passing the sys_refcursor.

apex_json.open_object;
apex_json.write(l_sys_refcursor);
apex_json.close_object;
lclob_body := apex_json.get_clob_output;

Now I am passing the value of lclob_body to below

 begin
    apex_web_service.set_request_headers(
        p_name_01        => 'Content-Type',
        p_value_01       => 'application/json',
        p_name_02        => 'User-Agent',
        p_value_02       => 'APEX',
        p_name_03        => 'Authorization',
        p_value_03      =>  'Basic xxxasdasdasdsaddsadsdsasfsafa',
        p_reset          => true,
        p_skip_if_exists => true );
end;

v_response := apex_web_service.make_rest_request
                    (
                        p_url           => 'https://....api_url',
                        p_http_method   => 'POST',
                        p_body          => lclob_body
    );

This is working fine for single JSON but when sys_refcursor returns multiple rows then multiple json is getting created. In this case, only first json is passing to API call.

How to pass each json (for each row returned by sys_refcursor) one by one to the function make_rest_request to call the API?

Edit : 1

I have done this simply by iterating the sys_refcursor into variables.

loop 
fetch l_cursor into p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15;
exit when l_cursor%notfound;
apex_json.open_object;
apex_json.write('field1',p1);
apex_json.write('field2',p2);
apex_json.write('field3',p3);
apex_json.write('field4',p4);
apex_json.write('field5',p5);
apex_json.write('field6',p6);
apex_json.write('field7',p7);
apex_json.write('field8',p8);
apex_json.write('field9',p9);
apex_json.write('field10',p10);
apex_json.write('field11',p11);
apex_json.write('field12',p12);
apex_json.write('field13',p13);
apex_json.write('field14',p14);
apex_json.write('field15',p15);


apex_json.close_object;
    lclob_body := apex_json.get_clob_output;
    


begin
    apex_web_service.set_request_headers(
        p_name_01        => 'Content-Type',
        p_value_01       => 'application/json',
        p_name_02        => 'User-Agent',
        p_value_02       => 'APEX',
        p_name_03        => 'Authorization',
        p_value_03      =>  'Basic xxxasdasdasdsaddsadsdsasfsafa',
        p_reset          => true,
        p_skip_if_exists => true );
end;

v_response := apex_web_service.make_rest_request
                    (
                        p_url           => 'https://....api_url',
                        p_http_method   => 'POST',
                        p_body          => lclob_body
    );
  
 dbms_output.put_line(v_response);

    exception when others then 
    null;
   end;
    
    apex_json.free_output;
    
end loop;

With this approach, able to call API for each json. But not sure if this is right way to achieve this or not. Please suggest if there is any other better way to achieve this.

Thank You!

CodePudding user response：

Well, at the end you have to loop through your cursor and for each iteration, you'd build a JSON object, call APEX_WEB_SERVICE and store the result.

The APEX_JSON.WRITE procedure which accepts the cursor is not the right one for you as it (you already noticed that) produces one JSON object with all the data. But that is not what you need.

I think, the second approach, using native SQL/JSON functions is the best solution - as it's the best fit to your requirements. And native functionality is also typically faster than PL/SQL implementations like APEX_JSON.

CodePudding user response：

Done the same with native JSON functionality as below

    set serveroutput on;
    declare
        v_response   clob;

        cursor c1 is
    SELECT
            json_object('field1' value  'AABBCC',     
            'field2' value t2.col1          ,
            'field3' value  t1.col1, 
            'field4' value  'Corp'  ,            
            'field5'  value 'AABB'||t2.col2 ,
             'field6' value JSON_object (
                    'nested_field1' value  '2029-11-13',
                    'nested_field2' value  'XYZ'      ,
                    'nested_field3' value  'ABC'      ,
                    'nested_field4' value  '2019-11-12', 
                    'nested_field5' value  '1234'       ,
                    'nested_field6' value 'ABC'                    
              ) ,
             
             'field7' value '' ,
             'field8' value 'TEAM' ,
             'field9' value 'true' ,        
             'field10' value '12345' , 
             'field11' value 'Scale' ,
             'field12' value t2.col3 ,        
             'field13' value t1.col2 ,
               'field14' value 'RUP'  ,        
             'field15' value 'ZZ'              
            ) a
          FROM
              tab1 t1,
              tab2 t2
              WHERE
               t1.col1 = t2.col2
           AND t1.col5 IS NULL;
    
    begin
     apex_web_service.set_request_headers(
            p_name_01        => 'Content-Type',
            p_value_01       => 'application/json',
            p_name_02        => 'User-Agent',
            p_value_02       => 'APEX',
            p_name_03        => 'Authorization',
            p_value_03       => 'Basic xxxasdasdasdsaddsadsdsasfsafa',
            p_reset          => true,
            p_skip_if_exists => true );
    
   for i in c1 loop
    
    begin
        
        v_response := apex_web_service.make_rest_request
                        (
                            p_url           => 'https://....api_url',
                            p_http_method   => 'POST',
                            p_body          => i.a
                        );
      
        exception when others then 
        null;
        end; 
    
      end loop;

    end;
 /