pl/sql stored function ora-01002 fetch out of sequence SIMPLE

Question

Sorry if this has been asked before but I couldn't find it searching through the site.

I have written the following stored function for an oracle DB with the variable names changes to be easier to read.

create or replace FUNCTION MY_FUNCTION 
(
  INPUT IN NUMBER 
, OUTPUT OUT SYS_REFCURSOR 
) RETURN SYS_REFCURSOR AS 
BEGIN
  OPEN OUTPUT FOR
  select *
  FROM A_USER.A_VIEW myView
  WHERE myView.ID = INPUT;
  RETURN OUTPUT;
END MY_FUNCTION;

So when run/tested in sql developer you get an "output variables" tab. In that tab there are two values: <"Return Value"> which is returned by the function in the traditional sense and "OUTPUT" which is the value of the OUTPUT output variable. They should be the same but the value I am getting for "OUTPUT" is the error in the title and the <"Return Value"> is working as it should.

I am quite new to pl/ sql and never actually studied it in school. Can someone give me some insight into what is going on here and maybe a solution? Thank you Ethan

Answer 1

create or replace FUNCTION MY_FUNCTION 
(
  INPUT IN NUMBER 
, OUTPUT IN OUT SYS_REFCURSOR 
) RETURN SYS_REFCURSOR AS 
BEGIN
  OPEN OUTPUT FOR
  select *
  FROM A_USER.A_VIEW myView
  WHERE myView.ID = INPUT;
  RETURN OUTPUT;
END MY_FUNCTION;

Change the declaration of the output variable (refcursor). And declare one in the calling routine(whatever that my be) as well.