pipelined functions and rolling back dml: proper way to do this?

  • From: Charles Schultz <sacrophyte@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 12 Oct 2011 15:13:33 -0500

Good day, listers,
I am relatively new to the world of writing pipelined pl/sql, but after
reading various Oracle documentations, blogs, whitepapers and google in
general, I am not exactly sure how to accomplish a specific task.

*Environment*
Oracle 11.1.0.7, Banner ERP from SunGardHE

*Problem*
Writing a pipelined function that presents rows from a Banner API.
Unfortunately, the Banner API stores the results in a "temporary" table (not
an Oracle TEMP table), and the "official" way to "audit" is to execute the
procedure (API) and then issue ROLLBACK. When trying to craft a pipelined
function, I am getting all sorts of various error messages when trying to
rollback. Here is a snippet of code:

cursor c_processedFees is
  select columns1, 2, 3 from "temporaryTable";

BEGIN

   /* Process audit fee assessment. */
   callBannerAPIhere(with_some_parameters);

  open c_processedFees;
  loop
    fetch c_processedFees into asmnt_out_rec.sfrfaud_pidm,
asmnt_out_rec.bcomp_code, asmnt_out_rec.calc_charge;
    exit when c_processedFees%notFound;
     pipe row (asmnt_out_rec);
  end loop;
  close c_processedFees;

When I try to rollback (does not seem to matter where), I get:
ORA-04092: cannot ROLLBACK in a trigger

*Using AUTONOMOUS pragma*
If I put the rollback after I close the cursor, I get:
ORA-06519: active autonomous transaction detected and rolled back

If I put the rollback before I PIPE ROW (as suggested by Oracle
documentation), I get:
ORA-01002: fetch out of sequence



Help?

TIA

-- 
Charles Schultz


--
//www.freelists.org/webpage/oracle-l


Other related posts: