I need to cache few values when the plsql procedure is called through a rest service multiple times i.e. when it is executed from a same user multiple times for optimization.
I am calling below package procedure through ORDS rest service.
Below is the package code.
create or replace
PACKAGE rest_api_cache
as
g_val number;
procedure g_proc (p_val in number,
p_found in out varchar2);
END rest_api_cache;
create or replace
PACKAGE BODY rest_api_cache
as
procedure g_proc (p_val in number,
p_found in out varchar2)
is
l number;
begin
if rest_api_cache.g_val is null then
rest_api_cache.g_val := p_Val;
p_found := 'FALSE';
else
p_found := 'TRUE';
end if;
end g_proc;
END rest_api_cache;
---------------------------------
I have created the package as ORDS plsql procedure using below script
BEGIN
ORDS.define_module(
p_module_name => 'testmodule6a',
p_base_path => 'testmodule6a/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'testmodule6a',
p_pattern => 'restcache');
ORDS.define_handler(
p_module_name => 'testmodule6a',
p_pattern => 'restcache',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN
rest_api_cache.g_proc(
p_val => :val,
p_found => :found
);
END;',
p_items_per_page => 0);
ORDS.define_parameter(
p_module_name => 'testmodule6a',
p_pattern => 'restcache',
p_method => 'POST',
p_name => 'cachefound',
p_bind_variable_name => 'found',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT'
);
ORDS.define_parameter(
p_module_name => 'testmodule6a',
p_pattern => 'restcache',
p_method => 'POST',
p_name => 'p_value',
p_bind_variable_name => 'val',
p_source_type => 'RESPONSE',
p_param_type => 'INT',
p_access_method => 'IN'
);
COMMIT;
END;
/
----------------------------------------------------
I am executing the procedure through rest service by
Creating a payload file out-param-payload.json with below input parameter to call the procedure
{ "val": 7499 }
Executed the procedure using below command
curl -i -X POST --data-binary @out-param-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/testuser1/testmodule6a/restcache
Output of the above call is
HTTP/1.1 200 OK
Date: Tue, 05 Mar 2019 03:13:21 GMT
Content-Type: application/json
Transfer-Encoding: chunked
{"cachefound":"FALSE"}
For immediate execution from the same user, the value is returned as "FALSE" i.e. the package state is getting cleared, Is there a way that I can store the package variable for the same user for repeated executions.
REST is a stateless protocol. So each call to ORDS resets package state.
How you solve this depends on what you're trying to do. You could use application contexts or store data in tables.
Or create an API that does all the work in one call.
So the question is: why do you want to maintain state across calls?