{"id":635,"date":"2015-08-25T17:10:17","date_gmt":"2015-08-25T16:10:17","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=635"},"modified":"2015-08-25T17:10:55","modified_gmt":"2015-08-25T16:10:55","slug":"autonomous-transaction-to-the-rescue","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=635","title":{"rendered":"Autonomous transaction to the rescue"},"content":{"rendered":"<style type=\"text\/css\">\n.code, .code pre\n{\n  font-size: small;\n  color: black;\n  font-family: consolas, \"Courier New\", courier, monospace;\n  background-color: #ffffff;\n  \/*white-space: pre;*\/\n}\n.code pre { margin: 0em; }\n.code .rem { color: #ff0000; }\n.code .kwrd { color: #008080; }\n.code .str { color: #0000ff; }\n.code .op { color: #0000c0; }\n.code .preproc { color: #cc6633; }\n.code .asp { background-color: #ffff00; }\n.code .html { color: #800000; }\n.code .attr { color: #ff0000; }\n.code .alt\n{\n  background-color: #f4f4f4;\n  width: 100%;\n  margin: 0em;\n}\n.code .lnum { color: #606060; }<\/style>\n<p>Today, at my current project, I came across an issue where autonomous transactions came in handy.<\/p>\n<p>The situation: I need to create a query to perform an export. A couple of the fields to be selected come from a global temporary table, nothing fancy so far except this global temporary table is filled by a (rather complex) procedure. Another problem is this table is emptied for every row, i.e. it will contain only one row at a time. \u2018Just build a wrapper table function for this procedure and have that function call the procedure\u2019 was my first idea.<\/p>\n<p><!--more--><\/p>\n<p>I created a script that shows the situation<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">GLOBAL<\/span> <span class=\"kwrd\">TEMPORARY<\/span> <span class=\"kwrd\">TABLE<\/span> empdate\r\n(\r\n  empno <span class=\"kwrd\">NUMBER<\/span>(<span class=\"str\">4<\/span>)\r\n, hiredate <span class=\"kwrd\">DATE<\/span>\r\n)\r\n<span class=\"kwrd\">ON<\/span> <span class=\"kwrd\">COMMIT<\/span> <span class=\"kwrd\">DELETE<\/span> <span class=\"kwrd\">ROWS<\/span>\r\n\/<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">OR<\/span> <span class=\"kwrd\">REPLACE<\/span> <span class=\"kwrd\">PROCEDURE<\/span> getthehiredate(empno_in <span class=\"kwrd\">IN<\/span> <span class=\"kwrd\">NUMBER<\/span>) <span class=\"kwrd\">IS<\/span>\r\n<span class=\"kwrd\">BEGIN<\/span>\r\n  <span class=\"kwrd\">DELETE<\/span> <span class=\"kwrd\">FROM<\/span> empdate;\r\n  <span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INT<\/span>O empdate\r\n    (empno\r\n    ,hiredate)\r\n    (SELECT empno\r\n           ,hiredate\r\n       <span class=\"kwrd\">FROM<\/span> emp\r\n      <span class=\"kwrd\">WHERE<\/span> empno = empno_in);\r\n<span class=\"kwrd\">END<\/span> getthehiredate;\r\n\/<\/pre>\n<\/div>\n<p>Then I set out to build a pipelined table function that accepts a cursor as one of its parameters. This function then loops all the values in the cursor, calls the procedure, reads the data from the global temporary table and pipes out the resulting record, nothing really fancy so far.<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">TYPE<\/span> empdate_t <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">OBJECT<\/span>\r\n(\r\n  empno    <span class=\"kwrd\">NUMBER<\/span>(<span class=\"str\">4<\/span>),\r\n  hiredate <span class=\"kwrd\">DATE<\/span>\r\n)\r\n\/<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">TYPE<\/span> empdate_tab <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> empdate_t\r\n\/<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">OR<\/span> <span class=\"kwrd\">REPLACE<\/span> <span class=\"kwrd\">FUNCTION<\/span> getallhiredates(empnos_in <span class=\"kwrd\">IN<\/span> SYS_REFCURSOR) <span class=\"kwrd\">RETURN<\/span> empdate_tab\r\n  PIPELINED <span class=\"kwrd\">IS<\/span>\r\n  l_empno       NUMBER(<span class=\"str\">4<\/span>);\r\n  l_returnvalue empdate_t;\r\n<span class=\"kwrd\">BEGIN<\/span>\r\n  FETCH empnos_in\r\n    <span class=\"kwrd\">INT<\/span>O l_empno;\r\n  WHILE empnos_in%FOUND LOOP\r\n    getthehiredate(empno_in <span class=\"kwrd\">=&gt;<\/span> l_empno);\r\n    <span class=\"kwrd\">SELECT<\/span> empdate_t(ed.empno, ed.hiredate)\r\n      <span class=\"kwrd\">INTO<\/span> l_returnvalue\r\n      <span class=\"kwrd\">FROM<\/span> empdate ed\r\n     <span class=\"kwrd\">WHERE<\/span> 1 = 1\r\n       <span class=\"kwrd\">AND<\/span> ed.empno = l_empno;\r\n    PIPE ROW(l_returnvalue);\r\n    FETCH empnos_in\r\n      <span class=\"kwrd\">INT<\/span>O l_empno;\r\n  <span class=\"kwrd\">END<\/span> LOOP;\r\n  <span class=\"kwrd\">RETURN<\/span>;\r\n<span class=\"kwrd\">END<\/span> getallhiredates;\r\n\/<\/pre>\n<\/div>\n<p>But when I ran a query against this function:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">SELECT<\/span> *\r\n<span class=\"kwrd\">FROM<\/span> <span class=\"kwrd\">TABLE<\/span>(getallhiredates(<span class=\"kwrd\">CURSOR<\/span> (<span class=\"kwrd\">SELECT<\/span> empno\r\n<span class=\"kwrd\">FROM<\/span> emp)))\r\n\/<\/pre>\n<\/div>\n<p>I ran into an error:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"rem\">ORA-14551: cannot perform a DML operation inside a query <\/span><\/pre>\n<\/div>\n<p>So, all the work I done so far had been for nothing? Time wasted? I don\u2019t think so. If there is anything I learned over the years it is that Oracle tries to stop you doing certain things but at the same time supplies you the tools to create a work-around. <\/p>\n<p>There is something like an autonomous transaction, that might help me in this case so I changed the code for the function a bit:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><div class=\"code\" align=\"left\"><pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">OR<\/span> <span class=\"kwrd\">REPLACE<\/span> <span class=\"kwrd\">FUNCTION<\/span> getallhiredates(empnos_in <span class=\"kwrd\">IN<\/span> SYS_REFCURSOR) <span class=\"kwrd\">RETURN<\/span> empdate_tab\r\n  PIPELINED <span class=\"kwrd\">IS<\/span>\r\n  PRAGMA AUTONOMOUS_TRANSACTION;\r\n  l_empno       NUMBER(<span class=\"str\">4<\/span>);\r\n  l_returnvalue empdate_t;\r\n<span class=\"kwrd\">BEGIN<\/span>\r\n  FETCH empnos_in\r\n    <span class=\"kwrd\">INTO<\/span> l_empno;\r\n  WHILE empnos_in%FOUND LOOP\r\n    getthehiredate(empno_in <span class=\"kwrd\">=&gt;<\/span> l_empno);\r\n    <span class=\"kwrd\">SELECT<\/span> empdate_t(ed.empno, ed.hiredate)\r\n      <span class=\"kwrd\">INT<\/span>O l_returnvalue\r\n      <span class=\"kwrd\">FROM<\/span> empdate ed\r\n     <span class=\"kwrd\">WHERE<\/span> 1 = 1\r\n       <span class=\"kwrd\">AND<\/span> ed.empno = l_empno;\r\n    PIPE ROW(l_returnvalue);\r\n    FETCH empnos_in\r\n      <span class=\"kwrd\">INT<\/span>O l_empno;\r\n  <span class=\"kwrd\">END<\/span> LOOP;\r\n  <span class=\"kwrd\">COMMIT<\/span>;\r\n  <span class=\"kwrd\">RETURN<\/span>;\r\n<span class=\"kwrd\">END<\/span> getallhiredates;\r\n\/\r\n<\/pre>\n<\/div>\n<p>But when I ran the query:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">SELECT<\/span> *\r\n<span class=\"kwrd\">FROM<\/span> <span class=\"kwrd\">TABLE<\/span>(getallhiredates(<span class=\"kwrd\">CURSOR<\/span> (<span class=\"kwrd\">SELECT<\/span> empno\r\n<span class=\"kwrd\">FROM<\/span> emp)))\r\n\/\r\n<\/pre>\n<\/div>\n<p>I ran into a different error:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"rem\">ORA-06519: active autonomous transaction detected and rolled back<\/span><\/pre>\n<\/div>\n<p>So this doesn\u2019t work or does it? Pipelined table functions have \u2018exit\u2019 the function multiple times. Whenever a row is piped out. So, I tried to put the COMMIT just before the PIPE ROW command:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><div class=\"code\" align=\"left\"><pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">OR<\/span> <span class=\"kwrd\">REPLACE<\/span> <span class=\"kwrd\">FUNCTION<\/span> getallhiredates(empnos_in <span class=\"kwrd\">IN<\/span> SYS_REFCURSOR) <span class=\"kwrd\">RETURN<\/span> empdate_tab\r\n  PIPELINED <span class=\"kwrd\">IS<\/span>\r\n  PRAGMA AUTONOMOUS_TRANSACTION;\r\n  l_empno       NUMBER(<span class=\"str\">4<\/span>);\r\n  l_returnvalue empdate_t;\r\n<span class=\"kwrd\">BEGIN<\/span>\r\n  FETCH empnos_in\r\n    <span class=\"kwrd\">INT<\/span>O l_empno;\r\n  WHILE empnos_in%FOUND LOOP\r\n    getthehiredate(empno_in <span class=\"kwrd\">=&gt;<\/span> l_empno);\r\n    <span class=\"kwrd\">SELECT<\/span> empdate_t(ed.empno, ed.hiredate)\r\n      <span class=\"kwrd\">INTO<\/span> l_returnvalue\r\n      <span class=\"kwrd\">FROM<\/span> empdate ed\r\n     <span class=\"kwrd\">WHERE<\/span> 1 = 1\r\n       <span class=\"kwrd\">AND<\/span> ed.empno = l_empno;\r\n    <span class=\"kwrd\">COMMIT<\/span>;\r\n    PIPE ROW(l_returnvalue);\r\n    FETCH empnos_in\r\n      <span class=\"kwrd\">INT<\/span>O l_empno;\r\n  <span class=\"kwrd\">END<\/span> LOOP;\r\n  <span class=\"kwrd\">RETURN<\/span>;\r\n<span class=\"kwrd\">END<\/span> getallhiredates;\r\n\/\r\n<\/pre>\n<\/div>\n<p>And when I ran my statement again:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">SELECT<\/span> *\r\n<span class=\"kwrd\">FROM<\/span> <span class=\"kwrd\">TABLE<\/span>(getallhiredates(<span class=\"kwrd\">CURSOR<\/span> (<span class=\"kwrd\">SELECT<\/span> empno\r\n<span class=\"kwrd\">FROM<\/span> emp)))\r\n\/\r\n<\/pre>\n<\/div>\n<p>It worked as I hoped for.<\/p>\n<p>As you can see I have tried to mimic the situation using the EMP and DEPT tables. I think this is a nice little trick, but it should be used with caution. It is not for no reason that Oracle prevents you from running DML inside a query, but in this case I can bypass this restriction.<\/p>\n<pre><\/pre>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Today, at my current project, I came across an issue where autonomous transactions came in handy. The situation: I need to create a query to perform an export. A couple of the fields to be selected come from a global temporary table, nothing fancy so far except this global temporary table is filled by a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,9],"tags":[],"class_list":["post-635","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/635","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=635"}],"version-history":[{"count":7,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/635\/revisions"}],"predecessor-version":[{"id":642,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/635\/revisions\/642"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=635"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=635"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=635"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}