• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4594
  • Last Modified:

ORA-06519: active autonomous transaction detected and rolled back

I keep getting this message in a package i created...
here is the pkg

CREATE OR REPLACE PACKAGE pkg_check_po
AS
   FUNCTION check_po_amount (p_invoice_id NUMBER, p_unid NUMBER)
      RETURN NUMBER;

   PROCEDURE change_approver (p_invoice_id IN NUMBER);

   PROCEDURE add_note (
      p_invoice_id   IN   NUMBER,
      p_unid         IN   NUMBER,
      p_status_id    IN   NUMBER,
      p_note         IN   VARCHAR2
   );
END;
/
----------------------
AND THE PKG BODY
-----------------------

/* Formatted on 2006/06/12 15:50 (Formatter Plus v4.8.5) */
CREATE OR REPLACE PACKAGE BODY pkg_check_po
AS
   FUNCTION check_po_amount (p_invoice_id NUMBER, p_unid NUMBER)
      RETURN NUMBER
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;

      CURSOR po_check_cursor
      IS
         SELECT *
           FROM invoice_coding
          WHERE invoice_id = p_invoice_id AND coding_type_id = 3;

      po_check_row                 po_check_cursor%ROWTYPE;
      v_needs_different_approval   NUMBER                    := 0;
      v_amount_available           NUMBER                    := NULL;
   BEGIN
      OPEN po_check_cursor;

      LOOP
         FETCH po_check_cursor
          INTO po_check_row;

         EXIT WHEN po_check_cursor%NOTFOUND;

         SELECT NVL (po_line_amount_avail (po_check_row.po_number,
                                           po_check_row.po_line_id
                                          ),
                     0
                    )
           INTO v_amount_available
           FROM DUAL;

         DBMS_OUTPUT.put_line (   po_check_row.amount
                               || 'mmmmm'
                               || v_amount_available
                              );

         IF po_check_row.amount > v_amount_available
         THEN
            v_needs_different_approval := 1;
--          change_approver (p_invoice_id);
            pkg_check_po.add_note
               (p_invoice_id,
                p_unid,
                9,
                   'PO Line '
                || po_check_row.po_line_number
                || ' for PO Number '
                || po_check_row.po_number
                || ' was coding to an amount greater than the amount available for that line.'
               );
         END IF;
      END LOOP;

      CLOSE po_check_cursor;

      RETURN v_needs_different_approval;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;

   PROCEDURE change_approver (p_invoice_id IN NUMBER)
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
      v_new_approver   NUMBER := NULL;
-- this is who the invoice will go to so they can revise the PO
   BEGIN
      SELECT unid
        INTO v_new_approver
        FROM po_overage_approver;

      UPDATE invoices
         SET pending_approval_from_unid = v_new_approver,
             status_id = 9
       WHERE invoice_id = p_invoice_id;

      COMMIT;
   END;

   PROCEDURE add_note (
      p_invoice_id   IN   NUMBER,
      p_unid         IN   NUMBER,
      p_status_id    IN   NUMBER,
      p_note         IN   VARCHAR2
   )
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO invoice_notes
                  (invoice_id, notes, unid, status_id, note_timestamp
                  )
           VALUES (p_invoice_id, p_note, p_unid, p_status_id, SYSDATE
                  );

      COMMIT;
   END;
END;
/

I dont know why it keeps occurring...i tried taking out the items i thought were causing it but i still get the error
0
jduawa
Asked:
jduawa
  • 2
  • 2
  • 2
1 Solution
 
jrb1Commented:
Do you need the AUTONOMOUS_TRANSACTION in check_po_amount?  If you do, try a commit before the end of the procedure--after the line

CLOSE po_check_cursor;
0
 
jduawaAuthor Commented:
I am not sure where i need the autonomous transaction pragmas...I am not sure what it even does, I am trying to understand more behind the error, so i know how to handle it in the future...I am not sure which transaction it thinks is autonomous
0
 
jrb1Commented:
Every block that uses the autonomous_transaction needs to have a commit or rollback before leaving that block.

You might want to read this:

http://asktom.oracle.com/~tkyte/autonomous/index.html

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
MohanKNairCommented:
>> I keep getting this message in a package i created...

What is the Error message?
0
 
jduawaAuthor Commented:
>> I keep getting this message in a package i created...

>>>What is the Error message?

ORA-06519: active autonomous transaction detected and rolled back
0
 
MohanKNairCommented:
>> FUNCTION check_po_amount (p_invoice_id NUMBER, p_unid NUMBER)

As jrb1 pointed out, rollback/commit is missing in

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now