Skip to Main Content
  • Questions
  • Caching for PLSQL packages over ORDS

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, arun.

Asked: March 05, 2019 - 3:50 am UTC

Last updated: November 12, 2020 - 4:35 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

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.

and Chris said...

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?

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 05, 2019 - 1:37 pm UTC


A reader, March 05, 2019 - 1:59 pm UTC

Thanks for the reply.

Good to know that the package state would be reset for each call.
I want to maintain the state for a specific user from which the api call is invoked to avoid re querying of data So that performance of API call can improved.
first call: Query tables and cache the data and store in package variable.
Second call: Check the cache and avoid querying the tables and return the value from package variables.

But if the rest call is from a another session/user the process has to be executed as a new call as if there is no cache.

Can I do this through Etag? (Use the ETag value and store the information in a table and store the values)
I am not sure if I can access the Etag value within the procedure.
Chris Saxon
March 05, 2019 - 4:41 pm UTC

I'm not sure what ETag is - could you clarify?

Have we met?

Racer I., March 06, 2019 - 6:27 am UTC

Hi,

Mmh. Which part resets the state? And how?
If there is some kind of connection pooling involved, I would expect each call to go to a random new connection so even if the package state was maintained you couldn't rely on getting the same session (unless there is some session-affinity-technique I haven't heard of).

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/18.4/aelig/about-REST-configuration-files.html#GUID-37AA1468-DCB3-4D8B-868C-1910A0C04D68

talks about some caching (but that seems to be only file caching, which files?) and pool size settings.

Also :
https://community.oracle.com/thread/4158722
> We create and destroy the session for each and every ORDS call.

I wonder if there is some support for session state or if Oracle sticks to the pure lore and you have to come up with your own workaround.

regards,
Connor McDonald
March 06, 2019 - 11:11 am UTC

If you want to keep state in a stateless environment....easy peasy, use a table.

to whoever comes after

Racer I., March 06, 2019 - 12:04 pm UTC

Hi,

> If you want to keep state in a stateless environment....easy peasy, use a table.

Should have seen that coming ;) Slightly more problematic if the "state" you wish to preserve is simply some caching for performance reasons and accessing a table is considered "too slow".
If recreating is prohibitively expensive however MatViews, RESULT_CACHE or a manual caching-table will help. Especially if it remains in the SGA (with a good interconnect in RAC cases).

regards,
Chris Saxon
March 06, 2019 - 1:04 pm UTC

Yep, if the SQL is too slow there's a whole host of options to make it faster in the database...

URI with query filters.

Rajeshwaran, jeyabal, November 12, 2020 - 3:33 am UTC

Team:

was reading this

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/20.2/aelig/developing-REST-applications.html#GUID-E7E141C4-B9AB-4017-BC4B-C4120C54EF4C

and this below REST End points works fine in Chrome

http://localhost:8080/ords/xe/emp/?q={"deptno":{"$lte":10}}


However when i did this in CURL - got error like this

C:\app\CURL\curl-7.73.0-win64-mingw\bin>curl -i -X GET http://localhost:8080/ords/xe/emp/?q={"deptno":{"$lte":10}}
curl: (3) nested brace in URL position 46:
http://localhost:8080/ords/xe/emp/?q={deptno:{$lte:10}}
                                             ^


Kindly advice, what i am missing here.

Connor McDonald
November 12, 2020 - 4:35 am UTC

double quotes are special to windows, ie, it is interpreting them before it gets to curl, and then once you are in curl, it has interpretation of brackets and braces

So

http://localhost:8089/ords/scott/emp/?q={"EMPNO":{"$or":[{"$eq":"7934"},{"$eq":"7654"}]}}

becomes this

curl -i -X GET --globoff http://localhost:8089/ords/scott/emp/?q={%22EMPNO%22:%20{%22$or%22:%20[{%22$eq%22:%227934%22},{%22$eq%22:%227654%22}]}}

There's probably other ways as well, but its always tough dealing with quotes etc on Windows

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library