What is meant by PRAGMA AUTONOMOUS_TRANSACTION?











up vote
0
down vote

favorite












I need a clarity about pragma autonomous transaction. I have used a overlap select query in trigger and insert query in procedure. If I import two records in single file, the 2nd record is same as 1st record so the second record should be shown as overlap error. Now the select query in trigger executes but the error is not thrown by using the pragma autonomous transaction.






oracle plsql transactions







share|improve this question




























    up vote
    0
    down vote

    favorite












    I need a clarity about pragma autonomous transaction. I have used a overlap select query in trigger and insert query in procedure. If I import two records in single file, the 2nd record is same as 1st record so the second record should be shown as overlap error. Now the select query in trigger executes but the error is not thrown by using the pragma autonomous transaction.






    oracle plsql transactions







    share|improve this question


























      up vote
      0
      down vote

      favorite









      up vote
      0
      down vote

      favorite











      I need a clarity about pragma autonomous transaction. I have used a overlap select query in trigger and insert query in procedure. If I import two records in single file, the 2nd record is same as 1st record so the second record should be shown as overlap error. Now the select query in trigger executes but the error is not thrown by using the pragma autonomous transaction.






      oracle plsql transactions







      share|improve this question















      I need a clarity about pragma autonomous transaction. I have used a overlap select query in trigger and insert query in procedure. If I import two records in single file, the 2nd record is same as 1st record so the second record should be shown as overlap error. Now the select query in trigger executes but the error is not thrown by using the pragma autonomous transaction.






      oracle plsql transactions




      oracle plsql transactions






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 5 hours ago









      APC

      116k15114226




      116k15114226










      asked 7 hours ago









      Nikil

      15




      15
























          1 Answer
          1






          active

          oldest

          votes

















          up vote
          1
          down vote













          AUTONMOUS_TRANSACTION is a nested transaction. It executes DML independently of the calling transaction. So a query issued in an autonomous transaction won't see any uncommitted changes in the outer transaction. This is why you don't see your error message: the invalid state only exists in the uncommitted changes of the transaction.



          Obviously you are using AUTONMOUS_TRANSACTION to avoid a mutating table error. However, a better solution would be to use a COMPOUND DML trigger: use a FOR EACH ROW to store the changes in an array, then verify them for no overlaps in the AFTER statement stage. Find out more.






          share|improve this answer





















            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














             

            draft saved


            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53369429%2fwhat-is-meant-by-pragma-autonomous-transaction%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes








            up vote
            1
            down vote













            AUTONMOUS_TRANSACTION is a nested transaction. It executes DML independently of the calling transaction. So a query issued in an autonomous transaction won't see any uncommitted changes in the outer transaction. This is why you don't see your error message: the invalid state only exists in the uncommitted changes of the transaction.



            Obviously you are using AUTONMOUS_TRANSACTION to avoid a mutating table error. However, a better solution would be to use a COMPOUND DML trigger: use a FOR EACH ROW to store the changes in an array, then verify them for no overlaps in the AFTER statement stage. Find out more.






            share|improve this answer

























              up vote
              1
              down vote













              AUTONMOUS_TRANSACTION is a nested transaction. It executes DML independently of the calling transaction. So a query issued in an autonomous transaction won't see any uncommitted changes in the outer transaction. This is why you don't see your error message: the invalid state only exists in the uncommitted changes of the transaction.



              Obviously you are using AUTONMOUS_TRANSACTION to avoid a mutating table error. However, a better solution would be to use a COMPOUND DML trigger: use a FOR EACH ROW to store the changes in an array, then verify them for no overlaps in the AFTER statement stage. Find out more.






              share|improve this answer























                up vote
                1
                down vote










                up vote
                1
                down vote









                AUTONMOUS_TRANSACTION is a nested transaction. It executes DML independently of the calling transaction. So a query issued in an autonomous transaction won't see any uncommitted changes in the outer transaction. This is why you don't see your error message: the invalid state only exists in the uncommitted changes of the transaction.



                Obviously you are using AUTONMOUS_TRANSACTION to avoid a mutating table error. However, a better solution would be to use a COMPOUND DML trigger: use a FOR EACH ROW to store the changes in an array, then verify them for no overlaps in the AFTER statement stage. Find out more.






                share|improve this answer












                AUTONMOUS_TRANSACTION is a nested transaction. It executes DML independently of the calling transaction. So a query issued in an autonomous transaction won't see any uncommitted changes in the outer transaction. This is why you don't see your error message: the invalid state only exists in the uncommitted changes of the transaction.



                Obviously you are using AUTONMOUS_TRANSACTION to avoid a mutating table error. However, a better solution would be to use a COMPOUND DML trigger: use a FOR EACH ROW to store the changes in an array, then verify them for no overlaps in the AFTER statement stage. Find out more.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered 5 hours ago









                APC

                116k15114226




                116k15114226






























                     

                    draft saved


                    draft discarded



















































                     


                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53369429%2fwhat-is-meant-by-pragma-autonomous-transaction%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    -

                    Popular posts from this blog

                    Volksrepublik China

                    Image
                    Dieser Artikel befasst sich mit der Volksrepublik China und ist nicht zu verwechseln mit der Republik China (Taiwan). Siehe auch China (Begriffsklärung). Dieser Artikel wurde aufgrund von inhaltlichen Mängeln auf der Qualitätssicherungsseite der Redaktion Ostasien eingetragen. Dies geschieht, um die Qualität der Artikel aus dem Themengebiet Ostasien auf ein akzeptables Niveau zu bringen. Dabei werden Artikel gelöscht, die nicht signifikant verbessert werden können. Bitte hilf mit, die inhaltlichen Mängel dieses Artikels zu beseitigen, und beteilige dich bitte an der Diskussion ! 中华人民共和国 Zhōnghuá Rénmín Gònghéguó Volksrepublik China Flagge Emblem Amtssprache Chinesisch (Hochchinesisch) und anerkannte Regionalsprachen 1 Hauptstadt Peking Staatsform Republik Regierungssystem Sozialistisches Einparteiensystem Staatsoberhaupt Staatspräsident Xi Jinping Regierungschef Ministerpräsident Li Keqiang Fläc...
                    Read more

                    How to test boost logger output in unit testing?

                    Image
                    up vote 1 down vote favorite My application uses BOOST_LOG_TRIVIAL to log messages. I thought it's good to check in test (gtest) that correct messages are written there in given scenarios. Is there a way to somehow access what was written there after code was called? Or does it first have to be mocked in some way? I have googled a lot for this and haven't found any instructions, so either I am asking wrong questions or no one thinks it should be done. c++ logging boost googletest boost-log share | improve this question edited yesterday ...
                    Read more

                    Write to the output between two pipeline

                    Image
                    up vote 1 down vote favorite I'm trying to write to the output (echo) within two pipe to have a trace of what I'm doing but I have an error. Get-ChildItem $path -Recurse | Where-Object { $_.Name -match '.+?.log.(d{4})-(d{2})-(d{2})$' -and $_.LastWriteTime -lt $deleteDate } | Write-Output "deleting file" -PassThru | Remove-Item but I have this error : Write-Output : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input. I also tested in the Foreach-Object function Get-ChildItem $path -Recurse | Where-Object { $_.Name -match '.+?.log.(d{4})-(d{2})-(d{2})$' -and $_.LastWriteTime -lt $deleteDate } | Foreach-Object...
                    Read more