[RESOLVED] pipelined functions and rolling back dml: proper way to do this?
- From: Charles Schultz <sacrophyte@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 Oct 2011 08:17:31 -0500
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
Other related posts:
