INSERT statement in PL/SQL FUNCTION
<p>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.</p>

<p>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
</p>

<pre><code>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 ;
</code></pre>

<p>When I tried this function :
</p>

<pre><code>SELECT myfct('ABC', 'DEF') FROM DUAL ;
</code></pre>

<p>I got the error :</p>

<pre><code>ORA-14551: cannot perform a DML operation inside a query 
</code></pre>

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

<p>I found a work-around with PRAGMA AUTONOMOUS_TRANSACTION and COMMIT :
</p>

<pre><code>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 ;
</code></pre>

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

<p>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 ?</p>