Skip to Main Content
Forums

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Migration mod_plsql parameter to ORDS handler

User_CAOYRJul 11 2021

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?

Comments

fac586
Answer 
eaolson1 wrote:
I've just finished debugging a strange problem in a page process that was giving incorrect results because a page item had the wrong value. We discovered this page item value was getting set as soon as the user's session was created and even before he loaded that page. The V() function would return a value for this item even when there was no value in WWV_FLOW_DATA.
Eventually, I turned on session tracing and found this when V('P1_ITEM') was being called:
SELECT ATTRIBUTE_VALUE FROM WWV_FLOW_PREFERENCES$ WHERE USER_ID=:B3 AND SECURITY_GROUP_ID = :B2 AND PREFERENCE_NAME = 'PERSISTENT_ITEM_'||:B1
There was indeed a value in WWV_FLOW_PREFERENCES$ for PERSISTENT_ITEM_P1_ITEM. We don't really use preferences except for the usual built in report sorting and this "persistent_item" type of preference is new to me. Even when I deliberately set a preference (apex_util.set_preference), it does not add this prefix.
I can't find anything in the documentation on this. Does anyone know what this might be?
This is on Apex 5.1.2.

This is how values are persistently stored for page items where the Maintain Session State property is set to Per User.

This may have been set in error as it is an uncommon but occasionally useful feature.

Marked as Answer by Eric Olson 1 · Sep 27 2020
Eric Olson 1

That must have been what happened. It appears in 5.2 if the item is changed back to Per Session, the preferences remain, and the V() function can retrieve the preference rather than the page item value. As far as I can tell, this has been fixed in 18.2, at least in testing on apex.oracle.com.

1 - 2

Post Details

Added on Jul 11 2021
0 comments
106 views