Passing multiple JSON to call REST API using oracle APEX make_rest_request function

Question

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!

Do you want to make multiple REST Calls (one for each JSON), or do you want to make one(!) call with some sort of aggregated JSON? — Carsten
– Carsten, Commented Nov 2, 2022 at 11:27
@Carsten Yes one call for each json and save response for each separately. if error returned in response ("status" : "ERROR") then It should not stop the execution for other json. — SKG
– SKG, Commented Nov 2, 2022 at 11:50

Carsten · Accepted Answer · 2022-11-03 12:05:29Z

2

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.

answered Nov 3, 2022 at 12:05

Carsten

1,1346 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SKG · Accepted Answer · 2022-11-08 12:00:18Z

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;
 /

Collectives™ on Stack Overflow

Passing multiple JSON to call REST API using oracle APEX make_rest_request function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related