pipelined functions and rolling back dml: proper way to do this?
Date: Wed, 12 Oct 2011 15:13:33 -0500
Message-ID: <CAPZQniX9j0zinbeK=mSZUhmDkZ0aWkfKnLcWPr71Lmh7b0nCjA_at_mail.gmail.com>
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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 12 2011 - 15:13:33 CDT