Forum Stats

  • 3,770,159 Users
  • 2,253,079 Discussions
  • 7,875,349 Comments

Discussions

Migration mod_plsql parameter to ORDS handler

I have some Oracle databases that are exchanging HTTP calls, my central database in on 11g and using mod_plsql for GET and POST call. I want to upgrade may database and start using ORDS without replacing my current code but having some issues when passing unknow number of parameters.

I have the following procedure

PROCEDURE my_post_proc(
    name_array      IN owa.vc_arr,
    value_array     IN owa.vc_arr
)

The HTTP call to this procedure is sending parameter using the URI (Query String) and in addition n number of parameters containing chunks of a long clob value

I was not a able successfully call this procedure by AutoREST enable the procedure so I created an handler using the APEX RESTful Service. I was able to get into the parameters sent from the URI and I was able to get chunks of the clob parameter by specifically using the parameter name.

However this is not good enough as I have idea about the number of chunks I will get.

This is who my handler code is looking

declare
      name_array owa.vc_arr;
      value_array owa.vc_arr;
begin
    
    name_array(1) := ('param_1');
    name_array(2) := ('param_2');
    name_array(3) := ('clob_chunk_1');
    
    value_array(1) := (:param_1);
    value_array(2) := (:param_2);
    
    value_array(3) := (:clob_chunk_1); -- This is working but not what I am after as can be dynamic number of chunks

    --Should be something more like

    for i in 1..(some collection holding the parameters)
    loop
        name_array(i) := 'clob_chunk_'||i;    
        value_array(i) := Collection('clob_chunk_'||i);
    end loop;

    my_post_proc(name_array,value_array);

end;

Is this can be somehow achieved?