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

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Fri, 14 Oct 2011 08:17:31 -0500
Message-ID: <CAPZQniX4Wv0OQO=iYXYjDte=ymvGVQqKuJxqaWhweZNaHCpjgg_at_mail.gmail.com>

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_at_gmail.com> 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


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

Received on Fri Oct 14 2011 - 08:17:31 CDT