2

I wrote a function in PL/SQL that calls a webservice with some parameters, parses the answer, and returns a value. It works quite well.

However, the response could be slow sometimes. As the parameters are usually in a very small subset of all possible values, I had the idea to cache the answers in a table. The function looks like the following 

CREATE OR REPLACE FUNCTION myfct(p1 IN VARCHAR2, p2 IN VARCHAR2)
RETURN VARCHAR2
IS
    cache_hit NUMBER ;
    res VARCHAR2(200) ;
BEGIN
    SELECT COUNT(*) INTO cache_hit FROM MYCACHE WHERE param1 = p1 AND param2 = p2 ;

    IF( cache_hit = 1 )
    THEN
        SELECT MYCACHE.result INTO res FROM MYCACHE WHERE param1 = p1 AND param2 = p2 ;

        RETURN res ;
    END IF ;

    -- complex operations 
    res := p1 || p2 ;


    INSERT INTO MYCACHE(param1, param2, result) VALUES(p1, p2, res) ;

    RETURN res ;
END ;

When I tried this function : 

SELECT myfct('ABC', 'DEF') FROM DUAL ;

I got the error :

ORA-14551: cannot perform a DML operation inside a query

Trying to wrap the DML part in a procedure and call this procedure in the function does not help

I found a work-around with PRAGMA AUTONOMOUS_TRANSACTION and COMMIT : 

CREATE OR REPLACE FUNCTION myfct(p1 IN VARCHAR2, p2 IN VARCHAR2)
RETURN VARCHAR2
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    cache_hit NUMBER ;
    res VARCHAR2(200) ;
BEGIN
    SELECT COUNT(*) INTO cache_hit FROM MYCACHE WHERE param1 = p1 AND param2 = p2 ;

    IF( cache_hit = 1 )
    THEN
        SELECT MYCACHE.result INTO res FROM MYCACHE WHERE param1 = p1 AND param2 = p2 ;

        RETURN res ;
    END IF ;

    -- complex operations 
    res := p1 || p2 ;


    INSERT INTO MYCACHE(param1, param2, result) VALUES(p1, p2, res) ;

    COMMIT ;
    RETURN res ;
END ;

But I wonder if it is really a good idea. People that mention this workaround said that could be dangerous, without saying exactly why.

Is my function an example of good use for PRAGMA AUTONOMOUS_TRANSACTION, or is there a better and safer way to do what I want ?

Improve this question
3
  • If you'll use select with your function in PL/SQL may be better to save results into some global collection and after then using forall insert values into table. Commented Sep 9, 2016 at 10:17
  • I thought about it but with all possible combinaisons, we will have several million of rows to store, and we plan to use only ~10 000 combinaisons on a regular basis. This hybrid solution with a cache table allows us to answer to any combinaison but store only the most pertinent ones. Commented Sep 9, 2016 at 10:56
  • Is it possible to keep the results up to date? What happens if the underlying data of your complex selection changes? Maybe you can solve your performance issues with a materialized view? Commented Sep 9, 2016 at 11:05

3 Answers 3

Reset to default
3

There is limitations what kind of PL/SQL functions can be called from SQL context. Don't call your PL/SQL function in SQL context but instead in PL/SQL context and all should be good:

declare
  v_foo constant varchar2(32767) := myfct('foo', 'bar');
begin
  dbmsn_output.put_line(v_foo);
end;

However before implementing you own cache please consider Oracle native PL/SQL Function Result Cache-feature:

The PL/SQL function result caching mechanism provides a language-supported and system-managed way to cache the results of PL/SQL functions in a shared global area (SGA), which is available to every session that runs your application. The caching mechanism is both efficient and easy to use, and relieves you of the burden of designing and developing your own caches and cache-management policies.

When a result-cached function is invoked, the system checks the cache. If the cache contains the result from a previous invocation of the function with the same parameter values, the system returns the cached result to the invoker and does not reexecute the function body. If the cache does not contain the result, the system runs the function body and adds the result (for these parameter values) to the cache before returning control to the invoker.

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

Comments

2

By convention and good software design, functions should not make changes to database and should only return values. Use a procedure instead. This is why there are both in PLSQL vs just functions like in C. Granted, this is mainly considered a stylistic thing by many people. But I strongly suggest you keep their usages separate.

Improve this answer

1 Comment

I could agree with you but then we both will be wrong. What if you need to "log" every call to a function? What if you need to return a json with the number of records inserted/updated/delete? There are so many other database/languages that allow this kind of operation (postgres for example) that the only explanation to me is that Oracle faced some difficulties in implementing dml in functions that they came with that "story" of good practices. They should be flexible and let the developers decide what is or what is not considered a "good" practice.
0

Just call that function in anonymous block. Don't select it. Like this

Declare
V_result varchar2(20);
Begin
V_result := myfct('ABC', 'DEF');
DBMS_OUTPUT.PUT_LINE(V_RESULT);
End;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.