Skip to Main Content

Return Cursor with pragma AUTONOMOUS_TRANSACTION and commit

user12921829Jul 1 2020 — edited Jul 1 2020

Hi, I am creating a function like this and encountering an error when running it based on the select sql that i am using.

Function A returns 200 rows works fine. But similar function(Function B) with a select from different tables returns (should return 7 rows) gives me 'ORA-01002: fetch out of sequence'

when i remove the   'pragma AUTONOMOUS_TRANSACTION' and 'commit', the same function B runs fine.

i need commit because i am using DBLINK. what am i missing? any suggestions.

CREATE OR REPLACE FUNCTION Get_Rows(ABC IN varchar2,DEF IN varchar2) RETURN SYS_REFCURSOR AS

  RC   sys_refcursor;

  pragma AUTONOMOUS_TRANSACTION;

begin

  open RC for select SOMETHING,SOMETHING,SOMETHING from TABLE where XYZ = ABC and ZYX = DEF;

  commit;

  return L_RC;

END;

TIA...

Comments
Post Details
Added on Jul 1 2020
9 comments
363 views