Huge thanks to Norm Dunbar who shed light on my predicament and offered some sage advice. In the end, I created an autonomous pipelined function that bulk collected the results of the query into a pl/sql temp table, rolled back the fee assessment API, then piped each row of the pl/sql temp table. Thanks Norm!! On Wed, Oct 12, 2011 at 15:13, Charles Schultz <sacrophyte@xxxxxxxxx> wrote: > 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 > -- Charles Schultz -- //www.freelists.org/webpage/oracle-l