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