Oracle SQL - function returning result set

Question

I have one question regarding Oracle SQL 4.0.0 version ... it is regarding function that should return small data set

So procedure has 3 incoming parameters and data set as out going ... this is what I have so far

CREATE OR REPLACE FUNCTION "PARTS"(a IN VARCHAR,
                                   b IN VARCHAR2,
                                   c IN VARCHAR2)
  RETURN SYS_REFCURSOR
AS
  final       SYS_REFCURSOR;
  par         VARCHAR(32);
  description VARCHAR(32);
  inputvars   VARCHAR(1024);
  a1          VARCHAR(32);
  BEGIN

    IF a IS NULL THEN
      a1 := 'NOT_REQD';
    ELSE
      a1 := a;
    END IF;

    OPEN final FOR
      SELECT substr(number1, 3)
      INTO par
        , description1
      FROM class_event_element
      WHERE substr(a, 4) = a1
            AND name = b
            AND vart = c;

    RETURN final;

    EXCEPTION
      WHEN no_data_found THEN RETURN ('NONE');
      WHEN OTHERS THEN RETURN ('ERROR');
  END;

SO explanation of this function is that I want to call it multiple times, that is why I am making it at first place of course. So my data inside of table it can, but it doesn't have to have a , that is reason why I have IF.

Then I just have simple Select statement , I need substr function for number there.

My MAIN PROBLEM is that, after searching google for half of day ... I can't figure out how to return my SYS_REFCURSOR Final. Is there any advice on this, or at least idea what to change to make this work so my function can return result set I need?

Answer 1

Work with this set of "starter" changes, remove the into clause as well :

TYPE ResultSet is REF CURSOR;

create or replace PROCEDURE  PARTS (iclassname IN varchar, idevice_name IN varchar2,  idevice_variant IN varchar2, partsfinal out Resultset)

AS


part varchar2(32):=NULL;
description varchar(32):=NULL;
inputvars varchar2(1024):=NULL;
classname1 varchar2(32):=NULL;

begin

IF iclassname is null THEN
classname1 := 'NOT_REQD';
ELSE 
classname1 := iclassname;
END IF;

OPEN partsFinal FOR SELECT substr(partnumber1,3) part, partdescription1 
   FROM         
   lookup_atm_class_event_element
WHERE 
   substr(classname,4) = classname1
   and device_name = idevice_name
   and device_variant = idevice_variant;

RETURN;

exception
when others then
  open partsfinal for select 'ERR', 'Error' from dual;
end;

You will want to tweak the error return part to reflect what you need to have on the code calling this procedure to deal with errors.