title: INSERT statement in PL/SQL FUNCTION
body: <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>